詳述Oracle 11g中的Reference Partition

詳述Oracle 11g中的Reference Partition

Data Partition是Oracle早期提出的一項針對大資料對象的解決方案。經過若干版本的演變，Partition依然是目前比較流行、應用廣泛並且接受程度較高的技術策略。

從Oracle產品線角度，Partition的成功是與Oracle不斷豐富完善分區技術和方案是分不開的。在每一個版本中，Partition技術都推出一些新的進步和發展。無論是8、8i還是11g、12c，Partition技術都是在不斷的向前進步，來滿足更加複雜的實際應用需求。

本篇主要介紹11g新推出的Reference Partitioin。Reference Partition針對的業務情境是主外部索引鍵關聯。主表分區之後，藉助Reference Partition可以實現自動的子表分區（不管子表上有無分區鍵）。經過Reference Partition分區之後，在同一個主表分區中的資料記錄，對應到的子表記錄，全部都在相同的子表分區上。

這種特性和分區類型，從效能和管理兩個方面，都可以給日常營運帶來很多好處方便。下面筆者將通過一系列實驗來介紹Reference Partiton。

1、實驗環境介紹

筆者選擇Oracle 11g進行測試，具體版本為11.2.0.4。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

2、Reference Partition資料表建立

和普通主外鍵資料表建立沒有過多差異，首先我們需要建立帶分區的主表。

SQL> create table t_master

  2  ( object_id number,

  3    owner varchar2(100),

  4    object_name varchar2(100),

  5    object_type varchar2(100)

  6  )

  7  partition by list(owner) –List分區類型

  8  (

  9    partition p0 values ('PUBLIC'),

 10    partition p1 values ('SYS'),

 11    partition p3 values (default)

 12  )

 13  ;

--添加主鍵約束

SQL> alter table t_master add constraint pk_t_master primary key (object_id);

Table altered

建立子表，注意：Reference Partition並不要求子表中包括分區鍵，參考關聯性就是子表的分區依據。另外：使用Reference Partition要求建立子表和定義外鍵約束在同一個語句中。

SQL> create table t_detail

  2  ( object_id number,

  3    master_id number,

  4    obj_comment varchar2(100),

  5    obj_type varchar2(100),

  6    constraint fk_mas_det foreign key (master_id) references t_master(object_id)

  7  ) partition by reference(fk_mas_det);

 

create table t_detail

( object_id number,

  master_id number,

  obj_comment varchar2(100),

  obj_type varchar2(100),

  constraint fk_mas_det foreign key (master_id) references t_master(object_id)

) partition by reference(fk_mas_det)

ORA-14652: 不支援引用分區外鍵

我們收到了一個Oracle報錯。首先我們看一下定義reference partition的文法，在create table語句中要建立定義好外鍵約束的名稱。之後，利用partition by語句，將外鍵作為劃分依據進行定義。

當前報錯ORA-14652，檢查一下官方對於這個錯誤的解釋。

[oracle@localhost ~]$ oerr ora 14652

14652, 00000, "reference partitioning foreign key is not supported"

// *Cause:  The specified partitioning foreign key was not supported

//          for reference-partitioned tables. All columns of the

//          partitioning foreign key must be constrained NOT NULL with

//          enabled, validated, and not deferrable constraints. Furthermore,

//          a virtual column cannot be part of the partitioning foreign key.

//* Action: Correct the statement to specify a supported

//          partitioning foreign key.

說明中提示了錯誤原因，如果使用Reference Partition，外鍵列是不允許為空白的。標準外鍵定義並沒有規定外鍵列必須為空白，但是如果使用引用分區技術，就必須要求外鍵列不可為空。

這種約束其實也好理解。Reference Partition不需要明確指定分區鍵，但是實際上還是需分區鍵（或者稱為分區因素）。如果沒有外索引值，也就失去了到主表分區的定位功能，Oracle必然不會允許建立。修改建表語句如下：

SQL> create table t_detail

  2  ( object_id number,

  3    master_id number not null,

  4    obj_comment varchar2(100),

  5    obj_type varchar2(100),

  6    constraint fk_mas_det foreign key (master_id) references t_master(object_id)

  7  ) partition by reference(fk_mas_det);

Table created

下面從分區表角度觀察兩個資料表。

SQL> select partition_name, high_value, partition_position from dba_tab_partitions where table_owner='SYS' and table_name='T_MASTER';

PARTITION_NAME      HIGH_VALUE      PARTITION_POSITION

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

P0                  'PUBLIC'                        1

P1                  'SYS'                            2

P3                  default                          3

SQL> select partition_name, high_value, partition_position from dba_tab_partitions where table_owner='SYS' and table_name='T_DETAIL';

PARTITION_NAME      HIGH_VALUE      PARTITION_POSITION

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

P0                                                    1

P1                                                    2

P3                                                    3

注意兩點：子表t_detail的high_value列為空白，說明該資料表並沒有一個明確的分區鍵，主表分區鍵owner在子表中也不存在。另外，子表分區結構、數量和名稱與主表完全相同。

從段結構segment上，我們可以看到11g的defered segment creation並沒有應用。

SQL> select segment_name, partition_name, segment_type from dba_segments where owner='SYS' and segment_name in ('T_MASTER','T_DETAIL');

SEGMENT_NAME    PARTITION_NAME      SEGMENT_TYPE

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

T_MASTER        P3                  TABLE PARTITION

T_MASTER        P1                  TABLE PARTITION

T_MASTER        P0                  TABLE PARTITION

T_DETAIL        P3                  TABLE PARTITION

T_DETAIL        P1                  TABLE PARTITION

T_DETAIL        P0                  TABLE PARTITION

6 rows selected

3、資料插入實驗

下面進行資料插入和分區分布實驗。首先進行主表資料插入：

SQL> insert into t_master select object_id, owner, object_name, object_type from dba_objects;

120361 rows inserted

SQL> commit;

Commit complete

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

PL/SQL procedure successfully completed

子表資料插入：

SQL> insert into t_detail select seq_t_detail.nextval, object_id, object_name, object_type from dba_objects where object_name not in 

('SEQ_T_DETAIL');

120361 rows inserted

SQL> commit;

Commit complete

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

PL/SQL procedure successfully completed

按照當前的資料關係，應該是一條主表記錄，對應一條子表記錄的關係。我們檢查資料字典情況。

SQL> select table_name, partition_name, high_value,num_rows from dba_tab_partitions where table_owner='SYS' and table_name in ('T_MASTER');

TABLE_NAME        PARTITION_NAME      HIGH_VALUE        NUM_ROWS

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

T_MASTER          P0                  'PUBLIC'            33996

T_MASTER          P1                  'SYS'                37817

T_MASTER          P3                  default              48548

SQL> select table_name, partition_name, high_value,num_rows from dba_tab_partitions where table_owner='SYS' and table_name in ('T_DETAIL');

TABLE_NAME        PARTITION_NAME      HIGH_VALUE        NUM_ROWS

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

T_DETAIL          P0                                        33996

T_DETAIL          P1                                        37817

T_DETAIL          P3                                        48548

資料分布正確。說明：子表分區分布的依據完全在於主表記錄對應的分區編號。相同主表分區記錄對應的子表記錄，一定在相同的子表分區上。不同主表分區記錄對應的子表記錄，不可能在相同的子表分區上。

為便於實驗，多插入一些資料：

SQL> insert into t_detail select seq_t_detail.nextval, object_id, object_name, object_type from dba_objects where object_name not in 

('SEQ_T_DETAIL');

120361 rows inserted

SQL> commit;

Commit complete

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

PL/SQL procedure successfully completed

SQL> select table_name, partition_name, high_value,num_rows from dba_tab_partitions where table_owner='SYS' and table_name in ('T_DETAIL');

TABLE_NAME    PARTITION_NAME      HIGH_VALUE        NUM_ROWS

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

T_DETAIL      P0                                        67992

T_DETAIL      P1                                        75634

T_DETAIL      P3                                        97096

那麼，Reference Partition在實際營運情境下的意義在於何處呢？

經過筆者之前的討論，Partition技術的出發點無非在於效能和管理兩個角度。效能上最典型的代表是分區裁剪、本地索引；管理最典型代表就是分區摘除、Swip分區歸檔。最早期Partition是針對效能方面因素比較多，近年來隨著硬體效能的提升，管理方面帶來的優勢，越來越受到重視。

下篇中，我們將從效能和管理兩個角度，討論Reference Partition的作用。