一直想系統的聊聊分割槽。網路上Oracle技術中，討論的最多的話題之一就是Partition。各種分割槽型別、分割槽組合和隨之而來的各種優缺點，一直被大家討論。但是，實際中，我們往往看到很多以偏概全、捨本逐末的分割槽使用現象。

各種分割槽型別和建立語句，是很多文章探討的中心。一些開發設計人員，甚至是DBA也感覺分割槽成了大表的萬靈藥。筆者這個系列文章不想討論各種語法和分割槽使用，而是集中在幾個常見話題難點。從“思想問題”，到“細枝末節”，歸納一下對於分割槽Partition技術，我們應該瞭解什麼。

本篇作為系列的開篇，首先看看為什麼要分割槽。

1、“大表要分割槽”

幾年前，還經常有朋友在網路或者技術討論沙龍中跳出來，發表所謂的設計經驗集，其中就包括“大表要分割槽”。是不是資料表一大，我們就要分割槽，就要用各種手段將其“大卸八塊”呢？

筆者的答案是：不一定。

讓我們一起先看看Oracle分割槽技術的出發點和發展軌跡。

Oracle最早的分割槽版本，就已經定義了分割槽的原則：段segment的分割。傳統的認識中，一個資料表或者索引作為獨立的段物件，是佔據磁碟儲存空間，並且單獨進行計量。

而分割槽Partition技術的出現，改變了這個情況。一個資料表未必是一個段物件，而是多個段物件。當然，一個段也可能承載多個資料表。傳統的分割槽實現了兩個功能，一是人為的在資料物件定義層面，將其劃分為多個邏輯儲存部分，第二是確定了資料記錄歸屬原則，什麼樣的資料，放在哪個分割槽中。

下面是一個最簡單的範圍分割槽資料表定義。

SQL> create table t

2 partition by range (object_id)

3 ( partition p1 values less than (10000),

4 partition p2 values less than (maxvalue)

5 ) as select * from dba_objects;

Table created

SQL> select partition_name, segment_type from dba_segments where owner='SCOTT' and segment_name='T';

PARTITION_NAME SEGMENT_TYPE

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

P1 TABLE PARTITION

P2 TABLE PARTITION

在定義中，SQL語句確定了分割槽資料表被分割在多個資料邏輯段segment中，如果條件允許，這些Segment是可以分佈在多個表空間、以致到多個物理儲存裝置上的。分割槽提供了在定義階段確定的一種資料儲存規劃策略。

Oracle分割槽Partition技術的原始出發點是應對海量資料表，這也就是為什麼Partition至今歸屬在Oracle DW（Data Warehouse）產品技術序列的原因。從根本上看，Oracle Partition技術帶給我們的是兩方面的好處：效能和管理。

效能提升，可能是大多數開發架構師和資料庫設計人員選擇Partition的原始初衷。好像一張資料表大了之後，我們只要分割槽了，就可以多少倍的提升效能。但是，很多時候事與願違。在Oracle的世界中，同樣沒有“Silver Bullet”。一項技術的引入，必然有前提和適應範圍。如果使用正確，分割槽配合適當的索引方案的確可以提高效能。

管理優勢其實並不算Oracle Partition的初衷。但是隨著版本的升級，Oracle對於分割槽管理手段支援提供了更多的功能特性。管理方面的優勢已經在很多方面超越效能，成為我們選擇分割槽技術的首要因素。

管理優勢是一個非常大的範圍，主要是運維範疇和領域的問題。具體來說包括：管理便捷性（Ease of Administration）、資料刪除（Data Purge）、資料歸檔（Data Archive）、資料全生命週期管理（Data Lifecycle Management）和高效備份（Efficiency Backup）幾個層面。

下面筆者將從幾個方面分別介紹分割槽的這些特性優勢。

2、環境介紹

筆者選擇Oracle 11g來進行試驗，中間過程使用之前建立的海量資料表T。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

Executed in 0.031 seconds

SQL> select count(*) from t;

COUNT(*)

----------

72761

Executed in 0.016 seconds

3、Partition效能最佳化

Partition最早的推出，就是為了效能上的最佳化。傳統的Segment概念下，一個資料表對應一個資料段。而Partition的推出，將一個資料物件（資料表或者索引）拆分為多個段物件，進而可以放在不同的表空間裡。

Partition對於效能的提升，主要體現在分散IO和分割槽裁剪（partition pruning）兩個方面。

先聊聊分散IO。

我們在過去一些Oracle最佳化建議中，經常看到一條“秘籍”：將資料表段和索引段分佈在不同的表空間裡。為什麼會有這個概念呢？

表空間是由不同的資料檔案構成。一個資料檔案只能對應一個表空間，一個表空間可以有多個資料檔案進行對應。在同一個表空間裡，我們不能控制資料究竟是放在哪個檔案上。

將資料段和索引段分佈在不同的表空間裡，才能保證兩者在不同的資料檔案裡面。這個不是最關鍵的問題。最關鍵的問題在於：只有在不同的資料檔案裡面，我們才可能將其分佈在不同的磁碟上。

我們的磁碟是IO的重要裝置，效能領域中IO是一直需要關注的方面，也是非常容易形成瓶頸的方面。磁碟儲存裝置的IO體現在TPS上，這個往往是由於裝置的物理上限決定的。通常我們的Oracle調優手段，比如索引、IOT，目的都是為了減少IO量，也就是減少SQL語句對IO的需求量。

但是需求通常是無限的。當我們的應用進行軟最佳化之後，的確需要如並行或者高IO讀取的時候，IO就成為應用的底線。一般來說，儲存裝置的上限TPS是硬體引數，沒有過高的超越空間存在。解決的方法之一就是並行，將一個SQL的IO過程分散在多個磁碟裝置上進行。多個磁碟裝置同時工作的時候，是有並行的效果的。如果匯流排或者網路條件允許的話，並行合力是可以體現出超過單個IO盤的吞吐量的。

如果我們規劃過程，將一個資料表拆分為多個段segment結構，進而放在不同的表空間，最後放在不同的磁碟上。針對資料表不同分割槽的訪問就可以實現IO“分散”的效果。總體合力上就可以實現效能提升。

但是，隨著技術的發展，這樣的優勢已經不存在，或者說已經不需要了。硬體層面，RAID技術的不斷髮展，特別是條帶化（Stripe），已經實現了資料分散在多個儲存裝置上，獲取硬體資源提升。軟體層面上，OS提供的Logical Volume、Oracle ASM都是將資料“打散”的技術。

再說說分割槽裁剪（Partition Pruning）。

Oracle的分割槽中有一個重要方面是分割槽規則的確定，一旦確定分割槽規則，資料行就按照規則“自動”分配到各個分割槽段Segment中（11g中System Partition例外）。分割槽裁剪的基本思想在於：原有需要訪問全部資料表的資料才能確定的結果，在分割槽的情況下，借用分割槽規則帶來的規律性，我們可以節省一部分的IO消耗量。

下面是一個分割槽裁剪的例子。

SQL> create table t_part

2 partition by list (owner)

3 (

4 partition p1 values ('SYS'),

5 partition p2 values ('PUBLIC'),

6 partition p3 values (default)

7 )

8 as select * from t where 1=0;

Table created

SQL> insert into t_part select * from t;

72761 rows inserted

SQL> commit;

Commit complete

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

PL/SQL procedure successfully completed

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

PL/SQL procedure successfully completed

我們對t_part採用owner分割槽策略，之後比較按照owner進行檢索的效能差異。

SQL> explain plan for select * from t where owner='SCOTT';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 13 | 1261 | 289 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 13 | 1261 | 289 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

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

13 rows selected

SQL> explain plan for select * from t_part where owner='SCOTT';

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2970683307

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

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

| 0 | SELECT STATEMENT | | 25 | 2450 | 53 (0)| 00:00:01 |

| 1 | PARTITION LIST SINGLE| | 25 | 2450 | 53 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL | T_PART | 25 | 2450 | 53 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

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

14 rows selected

從上面的執行計劃，我們的確看到了效能上的提升。當我們使用適合分割槽工作的where條件時，Partition是可以有不錯的效能提升的。

當我們沒有分割槽的時候，Oracle檢索owner=’SCOTT’的時候，因為是堆表Heap Table，儲存是隨機的，Server Process需要訪問每一個資料表塊，才能確認結果集合。在分割槽的時候，由於分割槽鍵和owner有關，Oracle可以明確的判定說，分割槽p1和p2裡面肯定沒有owner=’SCOTT’的記錄，所以就不用檢查p1和p2了，從而能夠提高效能，降低成本。

但是，應該看到分割槽裁剪的兩個問題。首先，如果分割槽表情況下，要使用分割槽裁剪，就只能（注意是隻能）順應分割槽鍵約束。如果應用的SQL語句是和分割槽鍵無關的，那麼SQL成本通常是更高的。

SQL> explain plan for select * from t;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 72761 | 6892K| 289 (1)| 00:00:04 |

| 1 | TABLE ACCESS FULL| T | 72761 | 6892K| 289 (1)| 00:00:04 |

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

8 rows selected

SQL> explain plan for select * from t_part;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2002420342

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

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

| 0 | SELECT STATEMENT | | 72761 | 6892K| 267 (1)| 00:00:04 |

| 1 | PARTITION LIST ALL| | 72761 | 6892K| 267 (1)| 00:00:04 |

| 2 | TABLE ACCESS FULL| T_PART | 72761 | 6892K| 267 (1)| 00:00:04 |

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

9 rows selected

上面的SQL語句中，如果我們的條件中沒有owner分割槽鍵，就會訪問所有的分割槽，也就是Partition List ALL操作。相同資料量情況下，檢索所有分割槽，進行所有分割槽的訪問成本要高於不分割槽的資料表。這就形成了我們使用分割槽表，藉助分割槽裁剪的一個現實條件：SQL語句中，要出現分割槽鍵。

我們說：資料是由業務活性的。無論是業務分割槽、還是時間，很多這種活性都是我們選擇分割槽鍵，新增入SQL語句的依據。但是困難在於兩個方面：一個是分割槽鍵的選擇，設計人員是否可以“預見”到應用系統中SQL語句必然加入的條件。第二個困難是如何保證所有的開發人員都“自覺”將分割槽條件加入到SQL語句，即使很多時候不是很需要。

另一方面，使用分割槽裁剪，大部分情況下需要Local Index的配合。我們在實際中經常遇到這樣的現象，分割槽之後的資料表SQL：要不就出現了分割槽裁剪，在單個分割槽中進行FTS（全表掃描），要不就走了建立的索引路徑，浪費了分割槽“地利”。

SQL> create index idx_t_part_id on t_part(object_id);

Index created

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

PL/SQL procedure successfully completed

SQL> explain plan for select * from t_part where owner='SCOTT' and object_id=20000;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2004327352

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

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

| 0 | SELECT STATEMENT | | 1 | 98 |

|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_PART | 1 | 98 |

|* 2 | INDEX RANGE SCAN | IDX_T_PART_ID | 5 | |

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

Predicate Information (identified by operation id):

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

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

2 - access("OBJECT_ID"=20000)

15 rows selected

這種時候，設計人員就需要額外的資料庫知識，就是本地索引。只有借用本地索引，才有可能兩者兼得。

SQL> drop index idx_t_part_id;

Index dropped

SQL> create index idx_t_part_idp on t_part(object_id) local;

Index created

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

PL/SQL procedure successfully completed

SQL> explain plan for select * from t_part where owner='SCOTT' and object_id=20000;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1293386573

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

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

| 0 | SELECT STATEMENT | | 1 | 98 |

| 1 | PARTITION LIST SINGLE | | 1 | 98 |

|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_PART | 1 | 98 |

|* 3 | INDEX RANGE SCAN | IDX_T_PART_IDP | 1 | |

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

Predicate Information (identified by operation id):

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

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

3 - access("OBJECT_ID"=20000)

16 rows selected

在很多時候，我們的確是需要借用Local Index來同時發揮分割槽+索引的優勢。可能很多朋友會說，在實際中我們需要藉助兩個的優勢嗎？筆者的回答是：非常有必要！

分割槽表對應的通常是海量表，不管建立的初衷是什麼。每個分割槽的體積通常是很大的，生產環境上一個分割槽都是幾個G左右。即使分割槽了，檢索一個分割槽的FTS也是很可怕的。所以要在最佳化層面上進行多層次、多角度的最佳化，才能綜合形成結果。所以，真正有效的分割槽表索引，通常是本地索引Local Index。

在分割槽表索引問題上，很多時候需要討論Prefix和Non-Prefix，筆者會在本系列的其他部分進行更詳細的介紹。

Partition的提出，最初就是為了效能。但是應該說隨著硬體技術的發展，IO方面已經有了很好的提升。而且分割槽裁剪在側重一方面效能提升的情況下，是會起身其他訪問方式的SQL效能的。相當於無形之中，資料表的使用被人為加入很多限制。而且，分割槽裁剪在很多時候要求開發設計人員具有一定的基礎知識能力。

相對於效能方面的優勢，管理方面的優勢是近年來更加受到側重選擇的方面。

聊聊分割槽Partition——我們為什麼要分割槽（上）

相關文章