Oracle11g新特性:引用分區(reference partitioning)

引用分區(reference partitioning)是Oracle Database 11g Release 1及以上版本的一個新特性。它處理的是父/子對等分區的問題。也就是說，要以某種方式對子表分區，使得各個子表分區分別與一個你表分區存在一對一的關系。在某些情況下這很重要，例如假設有一個數據倉庫，你希望保證一定數量的數據在線(例如最近5年的ORDER信息)，而且要確保相關聯的子表數據(ORDER_LINE_ITEMS數據)也在線。在這個經典的例子中，ORDERS表通常有一個ORDER_DATE列，所以可以很容易地按月分區，這也有利于保證最近5年的數據在線。隨著時間推移，只需加載下一個朋的分區，并刪除最老的分區。不過，考慮ORDER_LINE_ITEMS表時會看到存在一個問題。它沒有ORDER_DATE列，而且ORDER_LINE_ITEMS表中根本沒法有可以據以分區的列，因此無法幫助清除老信息或加載新信息。

過去，在引用分區出現之前，開發人員必須對數據逆規范化(denormalize)，具體做法是：從父表ORDERS將ORDER_DATE屬性復制到子表ORDER_LINE_ITEMS。這會引入冗余數據，相應地帶來數據冗余存在的一系列常見問題，比如存儲開銷增加、數據加載資源增加、級聯更新問題(如果修改父表，還必須確保更新父表數據的所有副本)，等等。另外，如果在數據庫中啟用了外鍵約束(而且確實應當啟用外鍵約束)，會發現無法截除或刪除父表中原來的分區。例如，下面來創建傳統的ORDERS和ORDER_LINE_ITEMS表。先看ORDERS表：

zx@ORCL>create table orders
  2  ( 
  3    order#      number primary key,
  4    order_date  date NOT NULL,
  5    data       varchar2(30)
  6  )
  7  enable row movement
  8  PARTITION BY RANGE (order_date)
  9  (
 10    PARTITION part_2016 VALUES LESS THAN (to_date('01-01-2017','dd-mm-yyyy')) ,
 11    PARTITION part_2017 VALUES LESS THAN (to_date('01-01-2018','dd-mm-yyyy')) 
 12  )
 13  /

Table created.

zx@ORCL>insert into orders values 
  2  ( 1, to_date( '01-jun-2016', 'dd-mon-yyyy' ), 'xxx' );

1 row created.

zx@ORCL>insert into orders values 
  2  ( 2, to_date( '01-jun-2017', 'dd-mon-yyyy' ), 'xxx' );

1 row created.

zx@ORCL>commit;

Commit complete.

現在來創建ORDER_LINE_ITEMS表，并插入一些數據指向ORDERS表：

zx@ORCL>create table order_line_items
  2  ( 
  3    order#      number,
  4    line#       number,
  5    order_date  date, -- manually copied from ORDERS!
  6    data       varchar2(30),
  7    constraint c1_pk primary key(order#,line#),
  8    constraint c1_fk_p foreign key(order#) references orders
  9  )
 10  enable row movement
 11  PARTITION BY RANGE (order_date)
 12  (
 13    PARTITION part_2016 VALUES LESS THAN (to_date('01-01-2017','dd-mm-yyyy')) ,
 14    PARTITION part_2017 VALUES LESS THAN (to_date('01-01-2018','dd-mm-yyyy')) 
 15  )
 16  /

Table created.

zx@ORCL>insert into order_line_items values 
  2  ( 1, 1, to_date( '01-jun-2016', 'dd-mon-yyyy' ), 'yyy' );

1 row created.

zx@ORCL>insert into order_line_items values 
  2  ( 2, 1, to_date( '01-jun-2017', 'dd-mon-yyyy' ), 'yyy' );

1 row created.

zx@ORCL>commit;

Commit complete.

現在如果要刪除包含2016年數據的ORDER_LINE_ITEMS分區，也可以刪除對應2016年的ORDERS分區而不會違反引用完整性約束。盡管我們都很清楚這一點，但數據庫并不知道：

zx@ORCL>alter table order_line_items drop partition part_2016;

Table altered.

zx@ORCL>alter table orders           drop partition part_2016;
alter table orders           drop partition part_2016
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

所以，對數據逆規范化的做活很笨拙，會耗費資源，而且可能破壞數據的完整性。不僅如此，它還會妨礙管理分區表時經常需要做的一項工作：清除老信息。

下面來看引用分區。采用引用分區，子表會繼承父表的分區機制，而不必對分區鍵逆規范化，而且更重要的是，它會讓數據庫了解這個子表與父表之間存在對等分區特點。也就是說，截除或刪除生意人子表分區時，也能刪除或截除父表分區。

要重新實現前面的例子，語法很簡單，如下所示，這里將重用現胡的你表ORDERS，只需要截除這個表：

zx@ORCL>drop table order_line_items cascade constraints;

Table dropped.

zx@ORCL>truncate table orders;

Table truncated.

zx@ORCL>insert into orders values 
  2  ( 1, to_date( '01-jun-2016', 'dd-mon-yyyy' ), 'xxx' );

1 row created.

zx@ORCL>insert into orders values 
  2  ( 2, to_date( '01-jun-2017', 'dd-mon-yyyy' ), 'xxx' );

1 row created.

zx@ORCL>commit;

Commit complete.

創建一個新的子表：

zx@ORCL>create table order_line_items
  2  ( 
  3    order#      number NOT NULL,
  4    line#       number NOT NULL,
  5    data       varchar2(30),
  6    constraint c1_pk primary key(order#,line#),
  7    constraint c1_fk_p foreign key(order#) references orders
  8  )
  9  enable row movement
 10  partition by reference(c1_fk_p)
 11  /

Table created.

zx@ORCL>insert into order_line_items values 
  2  ( 1, 1, 'yyy' );

1 row created.

zx@ORCL>insert into order_line_items values 
  2  ( 2, 1, 'yyy' );

1 row created.

zx@ORCL>commit;

Commit complete.

神奇之處就在CREATE TABLE語句的第10行。在這里，我們將區間分區語句替換為PARTITION BY REFERENCE。

這允許我們指定要使用的外鍵約束，從而發現分區機制。在這里可以看到外鍵指向ORDERS表——數據庫讀取ORDERS表的結構，并發現它有兩個分區。因此，子表會有兩個分區。實際上，如果現在查詢數據字典可以得到：

zx@ORCL>set linesize 200
zx@ORCL>col table for a20
zx@ORCL>col partition_name for a20
zx@ORCL>select table_name, partition_name
  2    from user_tab_partitions
  3   where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )
  4   order by table_name, partition_name
  5  /

TABLE_NAME                                                                                 PARTITION_NAME
------------------------------------------------------------------------------------------ --------------------
ORDERS                                                                                     PART_2016
ORDERS                                                                                     PART_2017
ORDER_LINE_ITEMS                                                                           PART_2016
ORDER_LINE_ITEMS                                                                           PART_2017

可以看到兩個表的結構完全相同。另外，由于數據庫知道這兩個表是相關聯的，可以刪除父表分區，并讓它自動清除相關的子表分區(因為子表從父表繼承而來，所以父表分區結構的任何調整都會向下級聯傳遞到子表分區)：

zx@ORCL>alter table orders drop partition part_2016 update global indexes;

Table altered.

zx@ORCL>select table_name, partition_name
  2    from user_tab_partitions
  3   where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )
  4   order by table_name, partition_name
  5  /

TABLE_NAME                                                                                 PARTITION_NAME
------------------------------------------------------------------------------------------ --------------------
ORDERS                                                                                     PART_2017
ORDER_LINE_ITEMS                                                                           PART_2017

因此，之前不允許完成的DROP現在則是完全允許的，它會自動級聯傳遞到子表。另外如果使用ADD增加一個分區：

zx@ORCL>alter table orders add partition
  2  part_2018 values less than
  3  (to_date( '01-01-2019', 'dd-mm-yyyy' ));

Table altered.

zx@ORCL>select table_name, partition_name
  2    from user_tab_partitions
  3   where table_name in ( 'ORDERS', 'ORDER_LINE_ITEMS' )
  4   order by table_name, partition_name
  5  /

TABLE_NAME                                                                                 PARTITION_NAME
------------------------------------------------------------------------------------------ --------------------
ORDERS                                                                                     PART_2017
ORDERS                                                                                     PART_2018
ORDER_LINE_ITEMS                                                                           PART_2017
ORDER_LINE_ITEMS                                                                           PART_2018

可以看到，這個操作也會向下級聯傳遞。父表與子表之間存在一種一對一的關系。

參考《ORACLE DATABASE 9I10G11G編程藝術》

官方文檔：http://docs.oracle.com/cd/E11882_01/server.112/e25523/partition.htm#CACIHDII