分割槽技術（Partition）是Oracle從8版本開始推出的一個新技術。經歷若干年的發展，分割槽技術不斷成熟、在Oracle各產品線中處的地位愈發重要。從技術方案佈局上看，分割槽技術還是屬於Oracle VLDB（Very Large Database）解決方案。

1、從分割槽Partition技術到分割槽索引

就分割槽技術的目的，Oracle是要實現三個優勢目標，歸納起來就是管理和效能：

ü 最佳化管理：分割槽技術主要解決的還是管理問題。一個很大的資料物件，如果資料活躍程度或者處理存在活躍差異，就可以將其分割在不同的分割槽。這樣，在進行分割槽管理，如刪除、新增加、移動等操作的時候，就不會影響到其他分割槽；

ü 資料庫可用性提升：一般海量資料表，存在更大的壞塊風險和I/O均衡壓力，我們可以將分割槽放置在不同的位置上。這樣，分割槽之間相互不影響，如果一個分割槽有故障，其他分割槽的訪問仍能正常進行；

ü 效能提升：這裡說的效能提升指的是分割槽裁剪（Partition Pruning）。在業務合理的情況下，當SQL條件where中出現適當的分割槽條件，那麼執行過程只會對特定的分割槽進行操作，這樣可以大大提高執行效能；

對分割槽技術，我們要重點關注兩個問題：

ü 資料是不是海量，完全不是我們規劃分割槽的根本理由。確定分割槽技術的關鍵在於資料物件在業務操作上是否有分割槽特性。應用分割槽技術之後，可否提升分割槽效率；

ü 分割槽表不是唯一可分割槽的物件。索引Index也是具有分割槽屬性的，而且索引的分割槽與對應資料表是否分割槽無關；

本篇，我們一起對分割槽索引（Partition Index）進行簡單的分析研究，探討應用的場景和現象。

2、分割槽索引的型別

分割槽索引從分類上，有兩個標準：區域性（Local）和全域性（Global）、前導（Prefix）和非前導（Non-Prefix）。

區域性（Local）和全域性（Global）是針對與對應的分割槽鍵而言的，如果一個分割槽索引符合下面的條件，就可以稱為Local索引：

ü 與分割槽資料表具有相同資料的分割槽/子分割槽；

ü 與分割槽資料表具有相同的分割槽限制，也就是分割槽條件相同；

ü 與分割槽資料表具有相同的分割槽鍵；

如果一個分割槽索引不是Local的，那麼就是Global的。

前導Prefix和非前導Non-Prefix是針對分割槽表的主鍵是否出現在索引index的左側前導列中。如果出現，我們稱之為Prefix Index，否則就是Non-Prefix Index。

針對不同的資料表（分割槽和非分割槽）和不同的資料訪問方式，使用適當型別的索引，可以讓CBO最佳化器獲取到最好的執行效率。

下面，我們針對不同的資料表和索引型別，分析執行計劃情況。

我們先根據分割槽表的情況進行分析。

3、分割槽表下的各型別索引情況

我們在10g下進行試驗，針對的是分割槽表。

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> create table t

2 partition by list(owner)

3 (

4 partition t_list_sys values ('SYS'),

5 partition t_list_scott values ('SCOTT'),

6 partition t_list_hr values ('HR'),

7 partition t_list_others values (default)

8 )

9 as select * from dba_objects where 1=0;

Table created

--選取分割槽鍵owner，劃分分割槽；

SQL> insert into t select * from dba_objects;

106610 rows inserted

SQL> commit;

Commit complete

此時，根據分割槽表特性，Oracle會建立出多個segment物件與資料表對應。

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='T';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES

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

T T_LIST_SYS TABLE PARTITION 6291456

T T_LIST_SCOTT TABLE PARTITION 65536

T T_LIST_HR TABLE PARTITION 65536

T T_LIST_OTHERS TABLE PARTITION 7340032

下面是索引，首先我們建立普通的全域性索引，就是一個資料表對應一個索引型別。

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

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 INDEX 3145728

下面分別尋找三個查詢業務場景，對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: 2226237847

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=1000)

14 rows selected

注意，這個執行計劃值得關注的是資料表索引訪問方式。條件object_id=1000，正好落在全域性索引的葉子節點上，可以直接定位到條件的rowid。在根據rowid定位資料行的過程中，檢索了資料表分割槽。這個操作就是根據全域性索引返回rowid定位資料行過程，稱作“TABLE ACCESS BY GLOBAL 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: 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: 3317687338

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

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

| 0 | SELECT STATEMENT | | 2 | 186 | 6

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

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

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

Predicate Information (identified by operation id):

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

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

2 - access("OBJECT_ID"=1000)

15 rows selected

上面兩個SQL體現出CBO的運算規則和工作方式。

當查詢條件為object_id=1000 and wner=’SCOTT’的時候，Oracle選擇了先在一個分割槽上定位（owner=’SCOTT’），也就是“PARTITION LIST SINGLE”。定位之後，Oracle有兩個選擇，一個是搜尋Global Index，依據條件object=1000。另一種是進行分割槽內全表掃描。

全域性索引只是一個段segment物件，體積包括了所有的object_id值。進行檢索消耗的成本要大於只對scott分割槽全表掃描的成本。所以此處，Oracle CBO選擇了全表掃描子分割槽。

當查詢條件為owner=’SYS’之後，事情有所不同。SYS分割槽大小超過全域性索引大小。如果選擇落入分割槽空間，之後全表掃描或者索引掃描，成本都是不容易接受的。所以，Oracle放棄了SYS條件，先搜尋索引樹，按照條件object_id=1000檢索，最後根據全域性索引返回的rowid，直接定位到結果行。

這兩個SQL，條件相同，不同在於條件的取值不同，統計量引起計算成本有差別。最終造成選擇出的執行計劃有差異。

下面，我們繼續討論當有分割槽索引的時候，執行計劃是如何進行選取？

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

相關文章