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

 

我們繼續來討論分割槽的動機。分割槽技術Partition已經脫離了原有的效能出發點，而更多的關注海量資料管理問題。本篇會集中在管理中的資料轉移、生命週期管理和備份幾個角度。

 

7、資料轉移

 

海量資料一個很常見的模式就是ETL動作。資料分析的基礎就是一系列的group彙總，針對交易業務資料的彙總動作。從SQL角度看，group by是一個很消耗資源的動作。而且，group by天然是沒有劃分這個動作的。

比如，我們基礎交易資料t_part，希望將其彙總為t_part_summary，根據owner彙總，以object_id進行加總。

 

 

SQL> create table t_part_sum as select owner, sum(object_id) sum_value from t_part where

 

1=0 group by owner;

 

Table created

 

SQL> select count(*) from t_part_sum;

 

  COUNT(*)

----------

         0

 

 

SQL> desc t_part_sum;

Name      Type         Nullable Default Comments

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

OWNER     VARCHAR2(30) Y                        

SUM_VALUE NUMBER       Y  

 

最直接、最傳統的做法就是簡單的進行group by之後insert。

 

SQL> set timing on;

SQL> insert into t_part_sum select owner, sum(object_id) from t_part group by owner;

 

32 rows inserted

 

Executed in 0.218 seconds

 

這種方式應該說很簡單，一些熟悉Oracle的朋友可能會選擇加入append和nologging進行最佳化。這種手法在資料量少的時候，的確是不錯的選擇。但是，凡事均有一個適應範圍，這種方法潛在兩個問題。

首先，直接的insert動作，會產生大量的undo和redo資料，而且是瞬間性的。這個在生產環境下是可能會引起一些效能問題。一般朋友的處理方法就是使用append和nologging，來減少redo資料的生成。此外還可以增大redo log member體積，緩解壓力。

第二個問題在於事務的規模。我們一次性的生成資料表全部資料，底層業務資料在group by的全過程中是要一次性讀入並且彙總計算。這個操作時間是很難控制的，同時group by動作的調整是和PGA調優、Temp空間調優緊密相關。我們經常遇到的一個場景，就是長時間的group by動作，最後以Temp空間耗盡告終。

筆者有一個論點：SQL語句最大的迷惑之處在於需要開發人員意識到資料量的問題。不同的操作物件選擇的操作方法是不同的。

針對第一個問題，也許直接的insert+append+nologging可以暫時滿足非功能性需要。針對第二個問題，也許目前系統效能Temp空間可以支援。但是，隨著底層業務資料的膨脹，這種狀況能持續多久？

解決的方法在於資料操作的平緩化。將一個海量的、不可知總量的操作轉化為一系列可控的操作系列是我們處理海量資料的最常規思路。

結合Partition，我們可以實現的更好。

我們首先還是建立彙總表，但是這次的彙總表是一個分割槽表。

 

 

SQL> drop table t_part_sum;

Table dropped

 

Executed in 1.248 seconds

 

SQL> create table t_part_summary

  2  partition by list (owner)

  3  (

  4     partition p0 values ('SYS'),

  5     partition p1 values ('PUBLIC'),

  6     partition p2 values (default)

  7  )

  8  as select owner, object_type, sum(object_id) sum_value

  9  from t_part

 10  where 1=0

 11  group by owner, object_type;

Table created

 

Executed in 0.156 seconds

 

建立出一個臨時表，按照分割槽的方式進行資料部分彙總動作。注意：這邊一次性的操作只是在一個分割槽裡面進行，帶來的負載是相對小。

 

SQL> create table t_part_sum_temp as select owner, object_type, sum(object_id) sum_value  from t_part where owner='SYS' group by owner, object_type;

 

Table created

 

Executed in 0.093 seconds

 

最後，透過exchange方式進行替換。

 

SQL> alter table t_part_summary exchange partition p0 with table t_part_sum_temp including indexes;

Table altered

 

Executed in 0.156 seconds

 

SQL> select count(*) from t_part_summary;

 

  COUNT(*)

----------

        40

 

Executed in 0.016 seconds

 

SQL> select count(*) from t_part_sum_temp;

 

  COUNT(*)

----------

         0

 

Executed in 0.031 seconds

 

將一個insert+group by轉化為一系列的group by和exchange partition，是可以適應海量資料維護工作的。

 

8、資料生命週期

 

這種管理思路的基礎在於一個假設：資料是有生命週期的，是有明顯的活性。對大部分的OLTP系統而言，這種說法是有道理的。

一個系統的資料剛剛進入系統中，其活性是非常強的。在很短的時間內，往往要經過系統最複雜的交易邏輯、最嚴格的執行演算法，最後進入報表模組形成交易彙總。這部分對於系統而言，效能是至關重要的。所有的設計都是圍繞著這個環節。

當資料經過這個階段，進入沉積期之後，操作動作就會快速減少。很多時候，系統有沒有將這個資料刪除，完全不影響系統工作了。這個時期，這部分系統的效能要求是不高的。

我們的資料效能中，很大一個因素在於IO。IO直接是從成本決定的。如果我們有快的IO，還有慢的IO，就可以利用分割槽技術將不同活性的資料分散在不同的儲存裝置上，從而獲得最優均衡。

 

 

SQL> select partition_name, tablespace_name from dba_tab_partitions where

 

table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME                 TABLESPACE_NAME

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

P1                             SYSTEM

P2                             SYSTEM

P3                             SYSTEM

 

Executed in 0.062 seconds

 

 

SQL> alter table t_part move partition p3 tablespace users update global indexes;

 

Table altered

 

Executed in 0.28 seconds

 

SQL> select partition_name, tablespace_name from dba_tab_partitions where

 

table_owner='SYS' and table_name='T_PART';

 

PARTITION_NAME                 TABLESPACE_NAME

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

P1                             SYSTEM

P2                             SYSTEM

P3                             USERS

 

Executed in 0.078 seconds

 

 

9、資料備份

 

Partition能夠最佳化備份工作。這點的源頭在於不同資料活性的事實。如果一個分割槽資料沒有活性，不會發生變化了。一種好的做法是將其單獨放在一個表空間裡面，設定表空間為只讀read only。

 

SQL> alter tablespace users read only;

 

Tablespace altered

 

Executed in 0.156 seconds

 

一旦表空間被設定為read only，從備份角度是存在最佳化空間的。Oracle進行備份識別的時候，只會備份這個表空間一次。在第二次進行備份的時候，這個表空間就不會進行備份了。

 

10、結論

 

Partition是Oracle的一項重要技術，也是我們常見的最佳化手段。使用分割槽，首先要明確出使用的目的。在實踐中，分割槽可能會成為雙刃劍。分割槽策略的選擇，效能和管理，是需要設計人員明確的重要取捨。如果更關注效能，而且需求層面有很強烈的資料訪問規律性，那麼可以使用分割槽。如果更關注管理，資料活躍性是否明顯是我們考量的方面。