對Maxvalue上限範圍分割槽進行spilt操作

 

Oracle分割槽表是對關鍵資料表，特別是海量資料表的一種普適性很好的技術方案。藉助分割槽表，通常都可以有效的提升資料表訪問效能，增強系統可管理能力。對分割槽表設計通常具有三個方面的決策點，分別為分割槽型別分割槽鍵、建表分割槽規劃和分割槽儲存規劃。

 

ü        分割槽型別分割槽鍵：資料表使用什麼分割槽鍵和分割槽型別進行分割槽。這個通常是分割槽表設計中最重要的環節。目前，Oracle支援範圍Range、列表List、雜湊Hash等分割槽型別。不同型別的分割槽還可以實現組合Composite分割槽。分割槽鍵的選取方式很多，大都是對資料列或者列的處理值進行。選擇分割槽型別、分割槽鍵的準則有兩個：易於管理和均衡化訪問。易於管理的含義就是讓系統中分割槽的資料不宜過多，過多的分割槽只能帶來管理上的困難。均衡化訪問就是儘可能實現將資料訪問集中在分割槽內部，儘可能避免出現跨分割槽型別操作；

ü        建表分割槽規劃：在開發和測試環境下，我們可以比較隨意的進行資料表分割槽的增加修改和刪除。但是對真正的海量資料，特別是生產環境下的海量資料，要提前規劃出預想分割槽。比如，對於一個按自然月分割槽的資料表，最好事先規劃出提前兩到三年的資料分割槽。如果分割槽設定過小或者缺少，那麼在生產環境下進行表重構的壓力是比較大的；

ü        分割槽儲存規劃：同一般資料表的不同，分割槽表是多個段segment組成的。這樣，資料表就可以貯存在不同tablespaces中的可能，更進一步可以分佈在不同的資料檔案和磁碟上。分割槽表是否要使用多tablespace儲存以分散IO，要進行統一嚴謹的規劃；

 

範圍分割槽（Range Partition）是我們經常使用分割槽型別。Range Partition將資料依據資料列範圍上下限設定為不同的分割槽。最後的MaxValue關鍵字，可以保證資料一定會落入分割槽。問題來了，如果我們沒有預先規劃好分割槽，就可能出現多個資料分割槽集中的情況。

 

比如：我們使用日期進行分割槽，2010年每個月都劃分了分割槽，大於2010年12月的月份都用Maxvalue進行收底。但是到了2011年，發現系統仍在執行，2011年資料全部集中到了最後分割槽。這種情況如何處理呢？本篇使用spilt partition語句進行型別情況的處理演示。

 

1、 資料環境準備

 

我們使用Oracle 10gR2環境進行試驗。

 

 

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

 

 

資料表emp_par設定為分割槽表，使用sal列進行範圍分割槽。資料表採用2000和4000兩個範圍。最後使用Maxvalue進行過大值容納。

 

 

SQL> create table emp_par (sal number(7,2))

  2  partition by range(sal)

  3  (partition emp_p1 values less than (2000),

  4   partition emp_p2 values less than (4000),

  5   partition emp_po values less than (MAXVALUE));

 

Table created

 

Executed in 0.031 seconds

 

//插入了一些資料；

 

SQL> select table_name, partition_name, high_value, tablespace_name, num_rows from dba_tab_partitions where table_name='EMP_PAR';

 

TABLE_NAME PARTITION_NAME HIGH_VALUE      TABLESPACE_NAME NUM_ROWS

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

EMP_PAR    EMP_P1         2000            SYSTEM                8

EMP_PAR    EMP_P2         4000            SYSTEM                5

EMP_PAR    EMP_PO         MAXVALUE        SYSTEM                3

 

Executed in 0.078 seconds

 

 

三個分割槽中，均包括了一些資料，其中的emp_po分割槽中包括資料如下。

 

 

SQL> select * from emp_par where sal>4000;

 

      SAL

---------

  5000.00

  6000.00

  8000.00

 

Executed in 0.047 seconds

 

 

 

2、Spilt分割槽操作

 

需求：將emp_po分割槽進行再次劃分，將4000到6000的資料作為一個新的分割槽出現。emp_po之後就包括6000以上的資料。

 

實現這個分割槽操作，可以按照如下步驟完成：

 

ü        建立資料表，儲存新分割槽資料

 

建立一個資料表，使用新的分割槽條件將資料儲存出來。之後，將原有資料表中相應記錄剔除掉。

 

 

SQL> create table emp_temp as select * from emp_par where sal>=4000 and sal<6000;

Table created

 

Executed in 0.078 seconds

 

SQL> delete emp_par where sal>=4000 and sal<6000;

1 row deleted

 

Executed in 0 seconds

 

 

ü        使用spilt partition劃分目標分割槽

 

使用alter table xxx spilt partition進行分割槽。

 

 

SQL> alter table emp_par split partition EMP_PO at(6000) into (partition EMP_P3, partition EMP_PO);

 

Table altered

 

Executed in 0.031 seconds

 

 

從emp_po的基礎上，劃分出新的分割槽emp_p3和emp_po。分割槽標準點在6000的位置上。

 

ü        使用exchange partition進行分割槽替換

 

下面，要使用exchange partition進行分割槽資料表替換。將資料表emp_temp替換為emp_p3分割槽。

 

 

SQL> alter table emp_par exchange partition EMP_P3 with table emp_temp;

 

Table altered

 

Executed in 0.046 seconds

 

 

在之前筆者的文章中，已經探討過exchange partition的本質(http://space.itpub.net/17203031/viewspace-704826)。exchange partition不是進行資料複製，而是進行後設資料替換。將兩個段segment物件後設資料進行交換。所以相對於複製，exchange partition操作更高效些。

 

此時，我們檢視資料資訊。

 

 

SQL> select table_name, partition_name, high_value, tablespace_name, num_rows from dba_tab_partitions where table_name='EMP_PAR';

 

TABLE_NAME  PARTITION_NAME HIGH_VALUE      TABLESPACE_NAME    NUM_ROWS

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

EMP_PAR     EMP_P1         2000            SYSTEM             8

EMP_PAR     EMP_P2         4000            SYSTEM             5

EMP_PAR     EMP_P3         6000            SYSTEM             

EMP_PAR     EMP_PO         MAXVALUE        SYSTEM             3

 

Executed in 0.078 seconds

 

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

 

PL/SQL procedure successfully completed

 

 

SQL> select table_name, partition_name, high_value, tablespace_name, num_rows from dba_tab_partitions where table_name='EMP_PAR';

 

TABLE_NAME  PARTITION_NAME       HIGH_VALUE      TABLESPACE_NAME NUM_ROWS

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

EMP_PAR     EMP_P1               2000            SYSTEM          8

EMP_PAR     EMP_P2               4000            SYSTEM          5

EMP_PAR     EMP_P3               6000            SYSTEM          1

EMP_PAR     EMP_PO               MAXVALUE        SYSTEM          2

 

Executed in 0.063 seconds

 

 

 

 

3、 結論

 

分割槽表是一種需要我們傾注很多研究精力和管理精力的物件。運維DBA日常工作中很多操作都是圍繞著分割槽表進行的。對關鍵資料表採用分割槽表技術，意味著很多的決策方案的制定。進行合理的分割槽規劃方案，進行合理的未來預計，可以減少我們很多的維護工作。