oracle 索引升降序及排序條件 對查詢計劃的影響

建立一個常規的堆表，並在上面建一個複合索引

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;

執行計劃
----------------------------------------------------------
Plan hash value: 2370119825

-----------------------------------------------------------------------------
| Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 4835 |   132K|    17   (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1_1 | 4835 |   132K|    17   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OWNER">='T' AND "OWNER"<='Z')
       filter("OBJECT_TYPE" IS NOT NULL AND "OWNER">='T' AND
              "OWNER"<='Z')

Note
-----
   - dynamic sampling used for this statement

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

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 desc,object_type desc;

執行計劃
----------------------------------------------------------
Plan hash value: 2755489192

----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          | 4835 |   132K|    17   (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN DESCENDING| IDX_T1_1 | 4835 |   132K|    17   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OWNER">='T' AND "OWNER"<='Z')
       filter("OBJECT_TYPE" IS NOT NULL AND "OWNER">='T' AND "OWNER"<='Z')

Note
-----
   - dynamic sampling used for this statement

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

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 desc;

執行計劃
----------------------------------------------------------
Plan hash value: 1804762688

--------------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 4835 |   132K|       |    60   (4)| 00:00:01 |
|   1 | SORT ORDER BY    |          | 4835 |   132K|   360K|    60   (4)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN| IDX_T1_1 | 4835 |   132K|       |    17   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER">='T' AND "OWNER"<='Z')
       filter("OBJECT_TYPE" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement

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

SQL> create table t2 as select * from all_objects;

表已建立。

SQL> create index idx_t2_1 on t2(owner,object_type desc);

索引已建立。

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

SQL> select owner,object_type from t2
2 where owner between 'T' and 'Z'
3 and object_type is not null
4 order by owner,object_type desc;

執行計劃
----------------------------------------------------------
Plan hash value: 2241212547

-----------------------------------------------------------------------------
| Id | Operation        | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 1004 | 28112 |     5   (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T2_1 | 1004 | 28112 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OWNER">='T' AND "OWNER"<='Z')
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_TYPE")) IS NOT
              NULL AND "OWNER">='T' AND "OWNER"<='Z')

Note
-----
   - dynamic sampling used for this statement

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

SQL> select owner,object_type from t2
2 where owner between 'T' and 'Z'
3 and object_type is not null
4 order by owner,object_type;

執行計劃
----------------------------------------------------------
Plan hash value: 3901850472

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          | 1004 | 28112 |     6 (17)| 00:00:01 |
|   1 | SORT ORDER BY    |          | 1004 | 28112 |     6 (17)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN| IDX_T2_1 | 1004 | 28112 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER">='T' AND "OWNER"<='Z')
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_TYPE")) IS NOT
              NULL)

Note
-----
   - dynamic sampling used for this statement

SQL> spool off

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