SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test) */ * from t_test where owner='SYS' order by object_id) a ) where rownum <=10) where rn >=1;
SQL> alter session set statistics_level=all;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------
SQL_ID dfwkpppbtc8h7, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test) */ * from t_test where owner='SYS' order by
object_id) a ) where rownum <=10) where rn >=1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 |
| 3 | VIEW | | 1 | 28483 | 10 |00:00:00.01 | 5 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 |
| 5 | VIEW | | 1 | 28483 | 10 |00:00:00.01 | 5 |
|* 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 28483 | 10 |00:00:00.01 | 5 |
| 7 | INDEX FULL SCAN | IDX_test | 1 | 80700 | 10 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
6 - filter("OWNER"='SYS')
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.
从上面的执行计划可以看出，两条sql都走了 index full scan，第1条sql从索引中扫描了71901条数据（id=7 A-Rows=71901），在回表的时候对数据进行了大量过滤（id=6），最后得到10条数据，耗费了1245个逻辑读。
第2条sql从索引中扫描了10条数据，耗费了5个逻辑读。可以看出，第二条sql的执行计划是正确的，而第一条sql的执行计划是错误的，应该尽量在索引扫描的时候就取得10行数据。
（为什么上面的两条sql只有过滤条件不一样，而第一条sql的执行计划就错了呢？这是因为第一条sql的过滤条件where owner='SCOTT'，在表中只有很少数据，通过扫描object_id列的索引，然后在回表去匹配owner='SCOTT'，因为owner='SCOTT'数据量很少，要搜索大量数据才能匹配上。而第二条sql的过滤条件owner='SYS'，因为数据量多，只需要搜索少量的数据就能匹配上。）
---优化第一条sql：（就必须让过滤条件的列出现在索引中，如下：）
SQL> create index idx_test_all on t_test(owner,object_id); ---创建一个组合索引，包含owner字段
SQL> select * from (select * from (select a.*,rownum rn from (select /*+ index(t_test idx_test_all) */ * from t_test where owner='SCOTT' order by object_id) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------、
SQL_ID 9mm61b7j943sf, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(t_test idx_test_all) */ * from t_test where owner='SCOTT' order
by object_id) a ) where rownum <=10) where rn >=1
Plan hash value: 3696904346
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 6 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 6 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 6 |
| 3 | VIEW | | 1 | 12 | 10 |00:00:00.01 | 6 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 6 |
| 5 | VIEW | | 1 | 12 | 10 |00:00:00.01 | 6 |
| 6 | TABLE ACCESS BY INDEX ROWID| t_test | 1 | 12 | 10 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | IDX_test_ALL | 1 | 12 | 10 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
7 - access("OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.
（这时候会发现，sql走了索引范围扫描，从索引中扫描了10条数据，一共耗费了6个逻辑读。这说明现在的执行计划是正确的。）
注意：