Following the Oracle docs here, I'm trying to paginate a query on a table with almost 1 million rows.
The problem is that at the end of the day, when table has millions of rows, performance becomes bad. It seems that the index used by Oracle (Fast Full Index Scan) is not taking into account the sorted index, so the whole data is being sorted again.
As an example:
create table sample1(
id int primary key,
c1 varchar2(3000) not null,
c2 varchar2(3000) not null,
c3 varchar2(10));
create unique index index_unique3 on sample1(c1, c2);
create sequence seq_id1
minvalue 0
maxvalue 999999999999
start with 1
increment by 1
cache 20;
INSERT INTO sample1
SELECT seq_id1.nextval,
dbms_random.string('U',trunc(dbms_random.value(1,3000))),
dbms_random.string('U',trunc(dbms_random.value(1,3000))),
dbms_random.string('U',trunc(dbms_random.value(1,1)))
FROM dual
CONNECT BY level <= 5000;
analyze table sample1 compute statistics;
select * from (
select a.*, rownum r
from (
select c1, c2
from sample1
order by c1, c2
) a
where rownum <= 24
)
where r >= 2;
With the first rows, execution plan shows "Full Index Scan", but after inserting a couple of thousand rows (and gather statistics again), it begins to use "Index Fast Full Scan".
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 72408 | 1595 | 00:00:01 |
| * 1 | VIEW | | 24 | 72408 | 1595 | 00:00:01 |
| * 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 2002 | 6014008 | 1595 | 00:00:01 |
| * 4 | SORT ORDER BY STOPKEY | | 2002 | 6072066 | 1595 | 00:00:01 |
| 5 | INDEX FAST FULL SCAN | INDEX_UNIQUE3 | 2002 | 6072066 | 326 | 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("R">=2)
* 2 - filter(ROWNUM<=24)
* 4 - filter(ROWNUM<=24)
And, if the columns c1
and c2
are defined as ints, then the execution plan turns
to "Index Full Scan" and timings are pretty good.
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 936 | 3 | 00:00:01 |
| * 1 | VIEW | | 24 | 936 | 3 | 00:00:01 |
| * 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 24 | 624 | 3 | 00:00:01 |
| 4 | INDEX FULL SCAN | INDEX_UNIQUE1 | 1420002 | 14200020 | 3 | 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("R">=2)
* 2 - filter(ROWNUM<=24)
How can I eliminate the sort operation in the varchar table in order to speed up the query? There is a lot of docs explaining how to do the query, but none tells you how to optimize it.
select * from v$version;
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Update
For the varchar table, modifying the parameter optimizer_index_cost_adj with value 10 makes Oracle to use Full Index Scan instead of Fast Full Index Scan, but it's still sorting data according to the execution plan.
SELECT STATEMENT, GOAL = ALL_ROWS
VIEW Object owner=SYS
COUNT STOPKEY
VIEW
SORT ORDER BY STOPKEY
INDEX FULL SCAN
Update 1
OK, after some research I found that the problem was the value of NLS_SORT = binary. Setting the value to a language, the problem dissapears.
Best Answer
Not exactly sure what your question is. Index fast full scan cannot eliminate the subsequent sort, because it reads index blocks in the order they exist on the media, not necessarily in the index sort order, as explained in documentation:
and
Now as to why the Oracle optimizer chooses fast full scan for
VARCHAR2(3000)
columns but full scan for integer (I presume you meanNUMBER
) columns, I can only speculate that in the latter case the optimizer expects that the index leaf pages will be more densely packed with values, requiring fewer reads. It cannot know the actual lengths of yourVARCHAR2
values so it has to assume the worst case (which I guess could be up to 6002 bytes with single-byte characters).