SQL> drop index idx_test_id ;
SQL> create index idx_test_id on p_test(object_name,0);
select * from (select * from (select a.*,rownum rn from (select /*+ index(p_test idx_test_id ) */ * from p_test order by object_name) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID 25pm0f6b6m49x, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(p_test idx_test_id ) */ * from p_test order by object_name) a )
where rownum <=10) where rn >=1
Plan hash value: 246970912
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 10 | 2 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 10 | 2 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 10 | 2 |
| 3 | VIEW | | 1 | 63696 | 10 |00:00:00.01 | 10 | 2 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 10 | 2 |
| 5 | VIEW | | 1 | 63696 | 10 |00:00:00.01 | 10 | 2 |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| P_TEST | 1 | 63696 | 10 |00:00:00.01 | 10 | 2 |
| 7 | INDEX FULL SCAN | IDX_TEST_ID | 1 | 63696 | 10 |00:00:00.01 | 4 | 2 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
31 rows selected.
Linux公社的RSS地址：https://www.linuxidc.com/rssFeed.aspx