测试验证
如下SQL：
select * from test where owner='SCOTT' order by object_id;
select * from test where owner='SYS' order by object_id;第一条SQL语句的过滤条件时where owner=‘SCOTT’,该过滤条件能过滤表中绝大部分数据。
第二条SQL语句的过滤条件时where owner=‘SYS’，该过滤条件能过滤表中一半数据。
将SQL代入正确的分页框架中并且强制走索引（object_id列索引）：
select *
from (select *
from (select a.*, rownum rn from (需要分页的SQL) a)
where rownum <= 10)
where rn >= 1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 9cvywc3r6avma, child number 0
-------------------------------------
select * from (select * from (select a.*, rownum rn
from (select /*+index(test idx_test)*/
* from test
where owner = 'SCOTT' order by object_id) a)
where rownum <= 10) where rn >= 1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 1287 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 1287 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 1287 |
| 3 | VIEW | | 1 | 12 | 10 |00:00:00.01 | 1287 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 1287 |
| 5 | VIEW | | 1 | 12 | 10 |00:00:00.01 | 1287 |
|* 6 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 12 | 10 |00:00:00.01 | 1287 |
| 7 | INDEX FULL SCAN | IDX_TEST | 1 | 75798 | 72567 |00:00:00.01 | 183 |
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
6 - filter("OWNER"='SCOTT')
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID dd6hp92072cz6, child number 0
-------------------------------------
select * from (select * from (select a.*, rownum rn
from (select /*+index(test idx_test)*/
* from test
where owner = 'SYS' order by object_id) a)
where rownum <= 10) where rn >= 1
Plan hash value: 1201925926
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 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 | 35485 | 10 |00:00:00.01 | 5 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 |
| 5 | VIEW | | 1 | 35485 | 10 |00:00:00.01 | 5 |
|* 6 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 35485 | 10 |00:00:00.01 | 5 |
| 7 | INDEX FULL SCAN | IDX_TEST | 1 | 75798 | 10 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
6 - filter("OWNER"='SYS')从执行计划看到，两条SQL都走了INDEX FULL SCAN：
第一条SQL从索引扫描了72567行数据（Id=7 A-Rows=72567），在回表的时候对数据进行了大量过滤（Id=6），最后得到10条数据，耗费了1287个逻辑读（buffer=1287）。
第二条SQL从索引中扫描了10条数据，耗费了5个逻辑读（buffer=5）。
所以第一个执行计划是错误的，第二个是正确的，应该尽量在索引扫描的时候就取得10行数据。
差距为何这么大？
因为第一条SQL过滤条件是owner=‘SCOTT’，这个条件在表中只有很少数据，通过扫描object_id列的索引，然后回表再去匹配owner=‘SCOTT’，因为owner='SCOTT’数据量很少，要搜索大量数据才能匹配上。
而第二条SQL的过滤条件是owner=‘SYS’，因为owner='SYS’数据量多，只需要搜索少量数据就能匹配上。
创建新的索引：
对于第一条SQL，将过滤列（owner）包含在索引中，排序列是object_id：
SQL> create index idx_test_owner_id on test(owner,object_id);
Index created.SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 0fm4v6gtgj17a, child number 0
-------------------------------------
select * from (select * from (select a.*, rownum rn
from (select /*+index(test idx_test_owner_id)*/
* from test
where owner = 'SCOTT' order by object_id)
a) where rownum <= 10) where rn >= 1
Plan hash value: 3600574413
--------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 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 | 13 | 10 |00:00:00.01 | 6 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 6 |
| 5 | VIEW | | 1 | 13 | 10 |00:00:00.01 | 6 |
| 6 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 13 | 10 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | IDX_TEST_OWNER_ID | 1 | 13 | 10 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
7 - access("OWNER"='SCOTT')从执行计划看到，SQL走了索引范围扫描，从索引中扫描了10条数据，一共耗费6个逻辑读，该执行计划是正确的。
如果在创建索引时，将object_id列在前，owner列在后：
SQL> create index idx_test_id_owner on test(object_id,owner);
Index created.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID 4ncks7wy73y9x, child number 0
-------------------------------------
select * from (select * from (select a.*, rownum rn
from (select /*+index(test idx_test_id_owner)*/
* from test
where owner = 'SCOTT' order by object_id)
a) where rownum <= 10) where rn >= 1
Plan hash value: 2811585238
---------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 225 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 225 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 225 |
| 3 | VIEW | | 1 | 13 | 10 |00:00:00.01 | 225 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 225 |
| 5 | VIEW | | 1 | 13 | 10 |00:00:00.01 | 225 |
| 6 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 13 | 10 |00:00:00.01 | 225 |
|* 7 | INDEX FULL SCAN | IDX_TEST_ID_OWNER | 1 | 303 | 10 |00:00:00.01 | 222 |
---------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
7 - access("OWNER"='SCOTT')
filter("OWNER"='SCOTT')从执行计划看出，SQL走了索引全扫，从索引中扫描了10条数据，但是索引耗费了222个逻辑读，因为要边扫描索引边过滤数据（owner=‘SCOTT’)，SQL一共耗费225个逻辑读，与走object_id列的执行计划（耗费1287个逻辑读）相比，提升了性能，但是最好的性能还是走IDX_TEST_OWNER_ID 索引（逻辑读为6）。
可不可以只在owner列创建索引？
- 如果过滤条件能够过滤掉大部分数据（owner=‘SCOTT’），那么这时不将排序列包含在索引中也是可以的，因为只会涉及到对少量数据排序，对性能没什么影响。
- 如果只能过滤掉一部分数据，也就是说返回数据量很多（owner=‘SYS’），这时我们必须将排序列包含在索引中，如果不将排序列包含在索引中，就需要对大量数据进行排序。在实际生产环境中，过滤条件一般都是绑定变量，无法控制传参的值，就不能确定返回数据行数究竟是多还是少，所以，建议最好将排序列包含在索引中。
- 如果排序列有多个列，创建索引时，要将所有的排序列包含在索引中，并且要注意排序列先后顺序（语句中怎么排序，创建索引的时候就对应排序），而且还要注意列是升序还是降序。如果分页语句中排序列只有一个列，但是降序显示出来，创建索引的时候就没必要降序创建，可以使用HINT：index_desc让索引降序扫描。
