SQL> create index idx_test_3 on t_test(object_id,object_name,0);
SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_3) */ * from t_test order by object_id,object_name desc) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------
SQL_ID bapu5jn5vn22r, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_3) */ * from t_test order by
object_id,object_name desc) a ) where rownum <=10) where rn >=1
Plan hash value: 3547841113
-----------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.10 | 1490 | 426 | | | |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.10 | 1490 | 426 | | | |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.10 | 1490 | 426 | | | |
| 3 | VIEW | | 1 | 80700 | 10 |00:00:00.10 | 1490 | 426 | | | |
| 4 | COUNT | | 1 | | 10 |00:00:00.10 | 1490 | 426 | | | |
| 5 | VIEW | | 1 | 80700 | 10 |00:00:00.10 | 1490 | 426 | | | |
| 6 | SORT ORDER BY | | 1 | 80700 | 10 |00:00:00.10 | 1490 | 426 | 10M| 1272K| 9811K (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 80700 | 71903 |00:00:00.06 | 1490 | 426 | | | |
| 8 | INDEX FULL SCAN | IDX_test_3 | 1 | 80700 | 71903 |00:00:00.03 | 427 | 426 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.