上面，我們討論了Local Index的分割槽特性。如果我們將分割槽鍵作為字首加入到索引中，是什麼現象呢？

SQL> drop index IDX_T_ID;

Index dropped

SQL> create index idx_t_owner on t(owner) 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_OWNER';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES

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

IDX_T_OWNER T_LIST_SYS INDEX PARTITION 917504

IDX_T_OWNER T_LIST_SCOTT INDEX PARTITION 65536

IDX_T_OWNER T_LIST_HR INDEX PARTITION 65536

IDX_T_OWNER T_LIST_OTHERS INDEX PARTITION 2097152

我們建立了索引idx_t_owner，選擇分割槽鍵owner作為索引列。當選擇Local型別時，也就意味著每個索引分割槽只包括相同的owner值。對應執行計劃，效果如何呢？

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

對於只包括object_id=1000的查詢條件。無疑，Oracle會檢索所有資料表分割槽，並且在每個分割槽中進行全表掃描。

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: 1903319282

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

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

| 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_OWNER | 50 | | 1

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

Predicate Information (identified by operation id):

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

2 - filter("OBJECT_ID"=1000)

3 - access("OWNER"='SCOTT')

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: 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

兩個同型SQL的執行計劃完全不同。當owner=’SCOTT’時，Oracle會去檢索索引分割槽，定位到owner=’SCOTT’的索引分割槽上，之後回表使用object_id=1000的條件檢索。

但是，對於owner=’SYS’的語句，情況有所不同。由於該分割槽較大，所以Oracle選擇直接去進行資料表分割槽的選取，最後在進行分割槽內部的全表掃描。

說明：我們說，在分割槽索引情況下，採用資料表分割槽鍵作為索引分割槽鍵，同時將分割槽鍵作為索引是沒有什麼意義的。

上面一直在討論預設分割槽條件。我們在建立分割槽索引的時候，是可以選擇非分割槽鍵作為索引分割槽鍵。

SQL> drop index IDX_T_OWNER;

Index dropped

SQL> create index idx_t_id on t(object_id)

2 global partition by range(object_id)

3 (

4 partition t_list_01 values less than (30000),

5 partition t_list_02 values less than (70000),

6 partition t_list_other values less than (maxvalue)

7 );

Index created

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

PL/SQL procedure successfully completed

上面程式碼中注意兩個方面。一個是索引的global關鍵字。在本系列的開篇，我們就已經分清了local和global的區別和標準。這裡，我們建立了三個索引分割槽，與資料表的四個分割槽首先就是不同的，所以這裡我們只能使用global關鍵字。

另一個就是我們可以自己選擇索引分割槽的分割槽鍵以及分割槽型別。這裡，我們就選擇了object_id作為範圍分割槽的分割槽鍵。

建立之後，可以清晰的看到分割槽段空間。

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_01 INDEX PARTITION 2097152

IDX_T_ID T_LIST_02 INDEX PARTITION 1048576

IDX_T_ID T_LIST_OTHER INDEX PARTITION 196608

此時，我們的執行計劃就按照如下的規則進行。

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: 3010955244

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

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

| 0 | SELECT STATEMENT | | 2 | 188 | 3

| 1 | PARTITION RANGE SINGLE | | 2 | 188 | 3

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

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

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

Predicate Information (identified by operation id):

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

3 - access("OBJECT_ID"=1000)

15 rows selected

Global索引使用對應的Table Access By Global Index Rowid，而Local索引使用對應Table Access By Local Index Rowid。

從上面的執行計劃看，進行了Partition Range Single操作，屬於分割槽裁剪動作。在SQL中，沒有涉及到owner的問題，所以這個剪裁動作必然是針對索引而言的。

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: 2862326821

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

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

| 0 | SELECT STATEMENT | | 1 | 85 | 3

| 1 | PARTITION RANGE SINGLE | | 1 | 85 | 3

|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 1 | 85 | 3

|* 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: 2862326821

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

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

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

| 1 | PARTITION RANGE SINGLE | | 2 | 186 | 3

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

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

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

Predicate Information (identified by operation id):

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

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

3 - access("OBJECT_ID"=1000)

16 rows selected

上面兩個執行計劃，顯然是利用索引分割槽的裁剪，先定位到具體的索引分割槽，之後進行owner條件查詢篩選。

4、非分割槽表的情況

並不是只有分割槽表才能對應分割槽索引。如果一個非分割槽表有需要，是可以將其索引分割槽的。

SQL> create table t as select * from dba_objects;

Table created

SQL> insert into t select * from t;

53295 rows inserted

SQL> commit;

Commit complete

SQL> create index idx_t_id on t(object_id)

2 global partition by range(object_id)

3 (

4 partition t_list_01 values less than (30000),

5 partition t_list_02 values less than (70000),

6 partition t_list_other values less than (maxvalue)

7 );

Index created

普通資料表T，對應分割槽資料索引。我們觀察一下結果情況。

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_01 INDEX PARTITION 2097152

IDX_T_ID T_LIST_02 INDEX PARTITION 851968

IDX_T_ID T_LIST_OTHER INDEX PARTITION 196608

三個關鍵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: 381899048

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

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

| 0 | SELECT STATEMENT | | 2 | 188 | 3 (0)| 0

| 1 | PARTITION RANGE SINGLE | | 2 | 188 | 3 (0)| 0

| 2 | TABLE ACCESS BY INDEX ROWID| T | 2 | 188 | 3 (0)| 0

|* 3 | INDEX RANGE SCAN | IDX_T_ID | 2 | | 1 (0)| 0

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

Predicate Information (identified by operation id):

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

3 - access("OBJECT_ID"=1000)

15 rows selected

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: 381899048

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

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

| 0 | SELECT STATEMENT | | 1 | 94 | 3 (0)| 0

| 1 | PARTITION RANGE SINGLE | | 1 | 94 | 3 (0)| 0

|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 94 | 3 (0)| 0

|* 3 | INDEX RANGE SCAN | IDX_T_ID | 2 | | 1 (0)| 0

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

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: 381899048

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

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

| 0 | SELECT STATEMENT | | 1 | 94 | 3 (0)| 0

| 1 | PARTITION RANGE SINGLE | | 1 | 94 | 3 (0)| 0

|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 94 | 3 (0)| 0

|* 3 | INDEX RANGE SCAN | IDX_T_ID | 2 | | 1 (0)| 0

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

Predicate Information (identified by operation id):

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

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

3 - access("OBJECT_ID"=1000)

16 rows selected

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: 381899048

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

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

| 0 | SELECT STATEMENT | | 1 | 94 | 3 (0)| 0

| 1 | PARTITION RANGE SINGLE | | 1 | 94 | 3 (0)| 0

|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 94 | 3 (0)| 0

|* 3 | INDEX RANGE SCAN | IDX_T_ID | 2 | | 1 (0)| 0

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

Predicate Information (identified by operation id):

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

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

3 - access("OBJECT_ID"=1000)

16 rows selected

索引分割槽裁剪、內部索引回表操作在上面的執行計劃中體現的很明顯。

5、結論

分治是一種古老的考慮方法，將複雜問題進行簡化，大面積的削減備選集合，是很多現代演算法和現代系統設計的核心要義。

分割槽索引是我們在設計分割槽表中不能迴避的問題。針對不同的應用場景，選擇合適的分割槽索引，才能做到真正的設計最佳化。

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

相關文章