使用索引消除排序

壹頁書發表於2014-01-04
索引排序
    索引是有順序的，如果一個查詢使用索引，可以利用索引的順序讀取資料，這樣可以消除OrderBy的排序。
    前提:索引列的順序和OrderBy的順序完全一致

    實驗使用Hr模式的Employees表，查詢某個工種(job_id)的所有員工，並且按照入職時間降序排列。檢視索引對排序的影響。
  1. create table test as select * from hr.employees;
  2. create index ind_test on test(job_id,hire_date desc);
  3. exec dbms_stats.gather_schema_stats('EDMOND');
1.檢視job_id為IT_PROG和ST_MAN的員工，按照工種(Job_id),入職時間(Hire_date)降序排列
  1. select * from test
  2. where job_id='IT_PROG' or job_id='ST_MAN'
  3. order by job_id,hire_date desc;
可以看到根據索引的順序讀取資料，消除了OrderBy排序


單獨給Job_id列建立索引，則結果如下（SORT ORDER BY不能避免）


2.查詢工種（Job_id）為IT_PROG的員工，並且按照入職時間降序排列。
可以看到，單一的Job_id作為過濾條件，也可以使用排序消除的特性
  1. select * from test
  2. where job_id='IT_PROG'
  3. order by hire_date desc;


3.查詢工種（Job_id）為IT_PROG的員工，並且按照入職時間升序排列。
使用單一的Job_id作為過濾條件，甚至升序排列，也能夠做到排序消除（INDEX RANGE SCAN DESCENDING）


4.檢視job_id為IT_PROG和ST_MAN的員工，按照工種(Job_id),入職時間(Hire_date)升序排列

  1. select * from test
  2. where job_id='IT_PROG' or job_id='ST_MAN'
  3. order by job_id,hire_date;


可以看到，這種情況下，索引不能消除排序。

總結
建立A、B列的組合索引，create index ind_test on test(a,b desc)
order by a,b desc 可以消除排序
但是order by a,b 則不能消除排序。

如果a作為單獨的過濾條件，則order by b 或者 order by b desc 都是可以消除排序的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1067134/，如需轉載，請註明出處，否則將追究法律責任。

相關文章