隨著CBO時代的到來，Oracle優化器越來越智慧，生成的執行計劃也是越來越巧妙。一些與傳統方式路徑用法，在新引入的執行計劃中得到了體現。

索引Index的引入目的就是提高資料檢索速度，減少邏輯物理IO讀取。最常見的索引使用場景就是在where條件後出現的資料列，如果有索引，適當情況下就可以執行索引路徑。此外，在一些特殊的SQL語句中，雖然不是常規的用途，索引也是可以發揮卓越的作用。本篇就是列舉兩個場景，描述藉助索引構造出的高效執行計劃。

1. 環境準備

首先我們準備一下環境。

//選擇Oracle 11g R2

SQL> select * from v$version where rownum<2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

SQL> create table t as select * from dba_objects;

Table created

//構建索引；

SQL> create index idx_t_id on t(object_id);

Index created

//收集統計量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

2. 獲取最大max值路徑

我們通常進行max/min極值定位的時候，使用max/min函式。下面我們來檢視這個執行計劃。

SQL> explain plan for select max(object_id) from t;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------

Plan hash value: 2448092560

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_T_ID | 1 | 5 | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

已選擇9行。

此處我們的SQL語句中，並沒有出現where條件。如果在RBO時代，這樣的場景一定是進行全表掃描。而此處Oracle選擇了索引物件路徑，執行Index full scan(MIN/MAX)操作。

這種方式路徑的選擇就是CBO智慧化的一種體現。理解起來也不難。索引的結構本質上就是將索引列所有的取值，經過排序之後，作為葉子節點的一顆B*樹。葉子節點分別是索引列值和對應的rowid資訊。藉助這樣的結構，如果可以直接找到索引樹葉子節點的兩端，就可以直接獲取到索引列的取值。而不需要訪問資料表segment物件。

優秀巧妙的執行計劃，建立在對資料表資訊的完全掌握（統計資訊）和豐富的內建操作選項（執行計劃中每步的操作）上。“INDEX FULL SCAN (MIN/MAX)”就是按照索引葉子順序掃描，獲取兩端最大或者最小值的操作選項。

那麼，如果我們同時選擇獲取最大和最小值，執行路徑是如何呢？

SQL> explain plan for select max(object_id),min(object_id) from t;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

Plan hash value: 2966233522

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 282 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | TABLE ACCESS FULL| T | 72583 | 354K| 282 (1)| 00:00:04 |

---------------------------------------------------------------------------

已選擇9行。

此時，Oracle放棄了路徑。那麼，如何讓這個SQL也提高執行操作呢？

3. Count涉及的索引路徑

當我們進行count操作時，是否可以藉助索引的力量呢？因為檢索索引葉子節點的IO讀取畢竟要小於進行全表掃描。

SQL> explain plan for select count(*) from t;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------

Plan hash value: 2966233522

-------------------------------------------------------------------

-------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 72583 | 282 (1)| 00:00:04 |

-------------------------------------------------------------------

已選擇9行。

此處我們失算了，Oracle不認為檢索索引樹可以獲取全部行數。那麼，如何讓Oracle認為讀取索引樹一定可以獲取到全部資料行數呢？其中的障礙，想必就是空值null的問題。

我們選擇的列object_id，是一個可以為空的資料列。Oracle認為該列中可以出現空值，而空值是不會進入索引樹結構的。所以，檢索索引樹葉子節點，也許會落下那些空值。於是，Oracle決定保險的進行全表掃描。

那麼，我們如何處理呢？就是要告訴CBO，說這個表的所有行都出現在葉子節點上，不會有落下的。

//修改屬性列屬性

SQL> alter table t modify object_id not null;

Table altered

//重新收集統計量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

此時，進行執行路徑獲取。

SQL> explain plan for select count(*) from t;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

Plan hash value: 3570898368

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 45 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| IDX_T_ID | 72582 | 45 (0)| 00:00:01 |

--------------------------------------------------------------------------

已選擇9行。

果然，我們提示告訴Oracle該列的資料資訊之後，CBO智慧的得出了我們希望的執行路徑。

此時，我們重新嘗試第二部分的那個同時獲取max和min的SQL語句。

SQL> explain plan for select min(object_id),max(object_id) from t;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------

Plan hash value: 3570898368

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 45 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | INDEX FAST FULL SCAN| IDX_T_ID | 72582 | 354K| 45 (0)| 00:00:01 |

----------------------------------------------------------------------------------

已選擇9行。

原來的全表掃描，也在豐富列資訊之後發生了優化。

4. 結論

進入CBO時代之後，Oracle生成SQL執行計劃的智慧化程度越來越高。藉助有限的資源，生成高效的執行路徑已經成為可能。對我們資料庫設計人員和SQL書寫人員來說，應該注意幾個方面。

ü 儘可能豐富對資料資訊的描述。本篇的轉折點就是對索引列非空的設定。很多時候，開發設計人員對如預設值、非空選項等不是很關注。其實，這些因素不僅僅是一種約束。對CBO而言，也是瞭解結構、生成更高效執行計劃的資訊基礎。“不是我做不到，你要告訴我可以做到”；

ü 書寫謹慎的SQL。SQL是一種描述語言，不同的方式可能針對相同的資料需求，對應不同的執行計劃。絕對不要將資料庫作為一個黑箱，要為每一句SQL負責任。要做到這點，需要了解Oracle執行原理和方法，才能寫一手“漂亮”的SQL；

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

索引優化執行路徑兩例

1. 環境準備

2. 獲取最大max值路徑

3. Count涉及的索引路徑

4. 結論

相關文章