Selecting index strategy for Oracle pagination query

Question

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.

https://community.oracle.com/thread/2436621

Answer 1

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:

An index fast full scan reads the index blocks in unsorted order, as they exist on disk. This scan does not use the index to probe the table, but reads the index instead of the table, essentially using the index itself as a table.

and

Unlike a full scan, a fast full scan cannot eliminate a sort operation because it does not read the index in order.

Now as to why the Oracle optimizer chooses fast full scan for VARCHAR2(3000) columns but full scan for integer (I presume you mean NUMBER) 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 your VARCHAR2 values so it has to assume the worst case (which I guess could be up to 6002 bytes with single-byte characters).

Answer 2

I've run into similar issues in Oracle and I worked around the problem by adding hints to the query. Hints can get you into trouble sometimes but the query that you have is so simple. If you are confident that the best plan will always be to take advantage of the natural sorting of the index then a hint should be pretty safe.

The FIRST_ROWS(1) hint may be helpful:

The FIRST_ROWS(n) hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently.

You can also consider the INDEX_ASC hint:

The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values.

As well as the NO_INDEX_FFS hint:

The NO_INDEX_FFS hint causes the optimizer to exclude a fast full index scan of the specified indexes on the specified table.