索引及排序對執行計劃的影響

csbin發表於2012-05-23

建立一個常規的堆表,並在上面建一個複合索引
SQL> create table t1 as select * from all_objects;
表已建立。

SQL> create index idx_t1_1 on t1(owner,object_type);
索引已建立。

下面這個查詢語句比較特殊,
order by的條件與select的列表欄位都在符合索引內,
所以這個查詢的檢索根本沒有涉及到堆表,而完全在索引內完成。
而且因為索引本身就是依序儲存的,所以查詢計劃中並沒有sort這一項。

SQL> set autotrace traceonly explain;
SQL> select owner,object_type from t1
2 where owner between 'T' and 'Z'
3 and object_type is not null
4 order by owner,object_type;

image


下面的查詢也類似,雖然排序條件中指定使用降序,但是db需要做的只是反向掃描索引。
所以仍然沒有sort的步驟

select owner,object_type from t1
where owner between 'T' and 'Z'
   and object_type is not null
order by owner desc,object_type desc;

image


可以看出,下面的查詢使查詢計劃多出了一個sort的步驟。
這是因為,雖然order by和select列表的欄位都在索引內,且資料也只會在索引儲存內取得,
但是因為索引都是依升序排列的,所以在取得資料後,db還是要針對第二個欄位進行排序。

select owner,object_type from t1
where owner between 'T' and 'Z'
   and object_type is not null
order by owner,object_type desc;

image

接下來建立一個相同的堆表,但是建複合索引時指定第二個欄位為降序

SQL> create table t2 as select * from all_objects;
表已建立。

SQL> create index idx_t2_1 on t2(owner,object_type desc);
索引已建立。

執行以下查詢,會發現,因為索引定義時指定第二個欄位為降序,該語句的執行計劃沒有sort這一步驟。

select owner,object_type from t2
where owner between 'T' and 'Z'
   and object_type is not null
order by owner,object_type desc;

image
但是當order by條件變為全部為升序時,sort的步驟再次出現。

select owner,object_type from t2
where owner between 'T' and 'Z'
   and object_type is not null
order by owner,object_type;

image
SQL> spool off

以上是一個簡單的例子。當表變得比較大時,一個排序的任務可能會佔用較多的資源和時間。
當遭遇效能問題時,有時可以嘗試結合複合索引及排序條件看有沒有改善空間。

http://hi.baidu.com/ljm0211/blog/item/11c6962b8feee0fde7cd40ab.html

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

相關文章