上篇(http://space.itpub.net/17203031/viewspace-712904)中，我們著重討論了分割槽技術和全域性索引在分割槽表中的一些現象。注意，我們討論的是無前導索引，那麼如果是有前導prefix索引，現象是如何的呢？

SQL> drop index idx_t_id;

Index dropped

--分割槽鍵單獨作為前導列；

SQL> create index idx_t_owner on t(owner);

Index created

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

PL/SQL procedure successfully completed

SQL> select segment_name, partition_name,segment_type, bytes from dba_segments where wner='SCOTT' and segment_name='IDX_T_OWNER';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES

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

IDX_T_OWNER INDEX 3145728

此時，我們觀察一下三個應用場景的執行計劃。

SQL> explain plan for select * from t where object_id=1000;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3845649146

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

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

| 0 | SELECT STATEMENT | | 2 | 188 | 351 (2)| 00:00:05 |

| 1 | PARTITION LIST ALL| | 2 | 188 | 351 (2)| 00:00:05 |

|* 2 | TABLE ACCESS FULL| T | 2 | 188 | 351 (2)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

2 - filter("OBJECT_ID"=1000)

14 rows selected

上面的一種SQL場景，是我們設計Oracle分割槽表和分割槽索引的一種大忌！如果上面的SQL是該資料表的一種主要訪問方式，我們可以認為該設計是存在瑕疵和潛在效能問題的。

如果一個分割槽表，沒有進行分割槽裁剪的進行全表掃描，要跨過多個分割槽進行操作。這樣的總成本消耗量要遠遠大於不進行分割槽操作。所以，如果對一個分割槽表進行操作，要優先將分割槽鍵作為檢索條件的一部分，利用上分割槽裁剪特性！

在上面的SQL中，object_id=1000既不是分割槽鍵，也不是索引列。所以，只能對所有分割槽進行檢索（PARTITION LIST ALL），分割槽內部進行全表掃描。

SQL> explain plan for select * from t where object_id=1000 and wner='SCOTT';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 882533222

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

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

| 0 | SELECT STATEMENT | | 1 | 85 | 3 (0)| 00:00:01 |

| 1 | PARTITION LIST SINGLE| | 1 | 85 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL | T | 1 | 85 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("OBJECT_ID"=1000)

14 rows selected

SQL> explain plan for select * from t where object_id=1000 and wner='SYS';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 882533222

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

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

| 0 | SELECT STATEMENT | | 2 | 186 | 154 (2)| 00:00:02 |

| 1 | PARTITION LIST SINGLE| | 2 | 186 | 154 (2)| 00:00:02 |

|* 2 | TABLE ACCESS FULL | T | 2 | 186 | 154 (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

2 - filter("OBJECT_ID"=1000)

14 rows selected

當全域性Global索引，以分割槽鍵作為索引的時候，如果檢索條件中出現分割槽鍵等於條件，那麼直接分割槽定位檢索就會出現在成本很低的執行計劃中。就如同上面的兩個SQL執行計劃，優先進行分割槽定位，之後在進行分割槽內部的檢索。

從上面的三個場景，我們可以得到一部分的結論：如果分割槽表，同時使用分割槽鍵作為Global索引的索引列時，通常索引是在執行計劃中不會發生很重要的作用的。而且當SQL條件中沒有出現分割槽鍵，執行計劃是存在很大的效能風險的。

那麼，我們換一下帶prefix的組合索引，觀察一下執行情況如何？

SQL> drop index idx_t_owner;

Index dropped

--以分割槽鍵作為前導列，結合關鍵搜尋條件；

SQL> create index idx_t_ownerid on t(owner,object_id);

Index created

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

PL/SQL procedure successfully completed

SQL> select segment_name, partition_name,segment_type, bytes from dba_segments where wner='SCOTT' and segment_name='IDX_T_OWNERID';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES

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

IDX_T_OWNERID INDEX 4194304

此時，我們看一下三個關鍵SQL的執行情況。

SQL> explain plan for select * from t where object_id=1000;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3525553998

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

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

| 0 | SELECT STATEMENT | | 2 | 188 |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 2 | 188 |

|* 2 | INDEX SKIP SCAN | IDX_T_OWNERID | 2 | |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1000)

filter("OBJECT_ID"=1000)

15 rows selected

當分割槽鍵不出現，同時存在object_id包含的索引時，索引路徑是執行計劃中必然出現的一種。注意：由於object_id在索引中位於非前導列的位置，所以此處Oracle的執行計劃是進行index skip scan跳躍式檢索。

下面兩個是帶有分割槽鍵的資料SQL。

SQL> explain plan for select * from t where object_id=1000 and wner='SCOTT';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 882533222

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

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

| 0 | SELECT STATEMENT | | 1 | 85 | 3 (0)| 00:00:01 |

| 1 | PARTITION LIST SINGLE| | 1 | 85 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL | T | 1 | 85 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("OBJECT_ID"=1000)

14 rows selected

SQL> explain plan for select * from t where object_id=1000 and wner='SYS';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3490045046

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

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

| 0 | SELECT STATEMENT | | 2 | 186 |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 2 | 186 |

|* 2 | INDEX RANGE SCAN | IDX_T_OWNERID | 5 | |

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

Predicate Information (identified by operation id):

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

2 - access("OWNER"='SYS' AND "OBJECT_ID"=1000)

14 rows selected

兩句帶分割槽鍵的“同型異值”SQL又出現了執行計劃不相同的情況。我們分別進行說明。

在第一個SQL（owner=’SCOTT’）中，由於scott分割槽很小，一次性的分割槽裁剪可以減少很多的麻煩。同時，即使使用索引，Global對應的索引結構體積甚至可能會超過scott分割槽的大小，這樣還不如進行FTS全表掃描操作的成本更可以接受。

第二個SQL中，有所不同，因為SYS分割槽體積很大，超過了索引結構。這樣，Oracle放棄了分割槽裁剪的路徑，直接選擇直接的索引樹定位結果rowid的方法。

也就是說，當全域性索引中，採用prefix方式的組合索引列時，分割槽裁剪和索引路徑難以共存。

總結論是：在分割槽表情況下，Global索引有無prefix意義不大。

下面我們來看看分割槽索引的情況。分割槽索引預設的方式我們選擇Local，也就是與資料表分割槽對應的方式。

SQL> drop index IDX_T_OWNERID;

Index dropped

--加入local關鍵字之後，表明建立Local分割槽索引；

SQL> create index IDX_T_ID on t(object_id) local;

Index created

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

PL/SQL procedure successfully completed

SQL> select segment_name, partition_name,segment_type, bytes from dba_segments where wner='SCOTT' and segment_name='IDX_T_ID';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES

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

IDX_T_ID T_LIST_SYS INDEX PARTITION 917504

IDX_T_ID T_LIST_SCOTT INDEX PARTITION 65536

IDX_T_ID T_LIST_HR INDEX PARTITION 65536

IDX_T_ID T_LIST_OTHERS INDEX PARTITION 2097152

注意：所謂的分割槽索引，就是索引段（Index Segment）也被分割為多個段物件。如果我們不指定索引分割槽鍵，那麼採用的分割槽方式同分割槽表的方式是相同的。

那麼，我們一起看看分割槽索引的效果。

SQL> explain plan for select * from t where object_id=1000;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1743619291

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

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

| 0 | SELECT STATEMENT | | 2 | 188 | 7

| 1 | PARTITION LIST ALL | | 2 | 188 | 7

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 2 | 188 | 7

|* 3 | INDEX RANGE SCAN | IDX_T_ID | 2 | | 5

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

Predicate Information (identified by operation id):

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

3 - access("OBJECT_ID"=1000)

15 rows selected

當使用object_id=1000時，執行計劃要注意兩個細節。首先，由於分割槽鍵owner沒有出現的SQL語句中，無論是分割槽資料表還是分割槽索引，都無法進行分割槽裁剪操作的。

此處，Oracle CBO選擇出的最優執行計劃：首先是檢索每一個索引分割槽，也就是其中的PARTITION LIST ALL，在每個分割槽中檢索object_id=1000的條件，進行Index Range Scan。將查詢出符合條件的rowid返回，進行回表操作Table Access By Local Index Rowid。

SQL> explain plan for select * from t where object_id=1000 and wner='SCOTT';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 4224690800

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

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

| 0 | SELECT STATEMENT | | 1 | 85 | 2

| 1 | PARTITION LIST SINGLE | | 1 | 85 | 2

|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 1 | 85 | 2

|* 3 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1

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

Predicate Information (identified by operation id):

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

2 - filter("OWNER"='SCOTT')

3 - access("OBJECT_ID"=1000)

16 rows selected

SQL> explain plan for select * from t where object_id=1000 and wner='SYS';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 4224690800

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

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

| 0 | SELECT STATEMENT | | 2 | 186 | 3

| 1 | PARTITION LIST SINGLE | | 2 | 186 | 3

|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 2 | 186 | 3

|* 3 | INDEX RANGE SCAN | IDX_T_ID | 2 | | 1

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

Predicate Information (identified by operation id):

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

2 - filter("OWNER"='SYS')

3 - access("OBJECT_ID"=1000)

16 rows selected

注意，雖然scott分割槽同sys分割槽資料差異值很大，但是執行計劃是相同的。這個是我們所追求的現象。因為，如果該SQL是使用繫結變數，對應最有執行計劃不同，帶來的Bind Peeking風險是不能忽視的。

執行計劃中，顯然進行了分割槽裁剪動作。這裡的兩句SQL中，均出現了分割槽鍵owner條件，這裡面的分割槽裁剪，是索引分割槽的選取裁剪過程，而不是資料表分割槽裁剪過程。

在每一個分割槽索引中，對應使用object_id=1000查詢條件，從區域性索引的角度進行回表操作。

下篇中，我們將研究prefix在Local Index的作用和效果。

分割槽索引（Partition Index）與SQL執行計劃（中）

相關文章