Tom Kyte has written some nice notes about these top n and pagination queries. You can find the details here and here.
If you have created a suitable index Oracle also can use this index to avoid a sort operation from taking place by using the index to retrieve the rows already in the requested sorted order without an additional sort operation required. You can notice this in the execution plan by the absence of any ORDER BY operation although you specified a ORDER BY in your statement.
Note however in case char columns are used to order the result set depending on your session's NLS_SORT setting a normal index might not be used, since the index is by default based on the "NLS_SORT = binary" sort order, whereas your session might use a different sort order.
There are two options in this case to still take advantage of the NOSORT operation: Use the "NLS_SORT = binary" setting in your session, or create a function based index using the NLSSORT function. This should work fine if all resp. most of your clients share the same NLS_SORT session setting, but keep in mind that it is a client controlled setting and therefore theoretically could be different for each of your clients.
A short sample script run on 10.2.0.4 (32-bit Windows) shall demonstrate the issue.
SQL>
SQL> drop table nosort_order_by_test purge;
Table dropped.
Elapsed: 00:00:00.09
SQL>
SQL> create table nosort_order_by_test as
2 select * from all_objects;
Table created.
Elapsed: 00:00:02.97
SQL>
SQL> alter table nosort_order_by_test add constraint pk_nosort_order_by_test
2 primary key (object_id);
Table altered.
Elapsed: 00:00:00.23
SQL>
SQL> create index nosort_order_by_test_ix1 on nosort_order_by_test (
2 owner desc,
3 object_name
4 );
Index created.
Elapsed: 00:00:00.17
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'NOSORT_ORDER_BY_TEST')
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.93
SQL>
SQL> alter session set nls_sort = german;
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL> select * from nls_session_parameters where parameter = 'NLS_SORT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_SORT GERMAN
Elapsed: 00:00:00.01
SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- this apparently should use the index nosort_order_by_test_ix1 but it doesn't due to the NLS_SORT setting
SQL> select * from (select * from nosort_order_by_test order by owner desc, object_name) where rownum <= 10;
10 rows selected.
Elapsed: 00:00:00.19
Execution Plan
----------------------------------------------------------
Plan hash value: 3856848661
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | | 1312 (2)| 00:00:16 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 50097 | 6262K| | 1312 (2)| 00:00:16 |
|* 3 | SORT ORDER BY STOPKEY| | 50097 | 4745K| 12M| 1312 (2)| 00:00:16 |
| 4 | TABLE ACCESS FULL | NOSORT_ORDER_BY_TEST | 50097 | 4745K| | 193 (3)| 00:00:03 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
691 consistent gets
0 physical reads
0 redo size
1755 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
SQL>
SQL> alter session set nls_sort = binary;
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL> -- this uses the index
SQL> select * from (select * from nosort_order_by_test order by owner desc, object_name) where rownum <= 10;
10 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2815166345
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | 10 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 10 | 1280 | 10 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| NOSORT_ORDER_BY_TEST | 50097 | 4745K| 10 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | NOSORT_ORDER_BY_TEST_IX1 | 10 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1758 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL>
SQL> -- create a function based index using the NLSSORT function
SQL> create index nosort_order_by_test_ix2 on nosort_order_by_test (
2 nlssort(owner, 'NLS_SORT = GERMAN') desc,
3 nlssort(object_name, 'NLS_SORT = GERMAN')
4 );
Index created.
Elapsed: 00:00:00.37
SQL>
SQL> alter session set nls_sort = german;
Session altered.
Elapsed: 00:00:00.01
SQL>
SQL> -- now this works as supposed using the function based index nosort_order_by_test_ix2
SQL> select * from (select * from nosort_order_by_test order by owner desc, object_name) where rownum <= 10;
10 rows selected.
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 3350033264
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | 11 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 10 | 1280 | 11 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| NOSORT_ORDER_BY_TEST | 50097 | 4745K| 11 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | NOSORT_ORDER_BY_TEST_IX2 | 10 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
14 consistent gets
2 physical reads
0 redo size
1755 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL>
SQL> spool off
Note that the first attempt to use the index fails due to the non-default NLS_SORT session setting.
Although the SORT ORDER BY STOPKEY operation is used we still scan the whole table rather than taking advantage of the index.
If the NLS_SORT is set to the default value of "binary" the index is going to be used.
If you have a common, non-default setting of NLS_SORT you can still use an index by creating a suitable function based index.
Note that similar issues regarding index usage can arise when using non-default NLS_COMP session settings. See here a note by Jonathan Lewis covering this and some other issues.
