Oracle 11g引用分区reference partition

解决的问题：

如果父表是分区表，子表想要按照父表的方式进行分区。

父表中被引用的主键列不一定要是分区键。

 

一个例子：

下面是设计分区方案过程中的一个典型问题，并非所有表都需要根据其进行分区的列。假设要创建一个销售系统，该系统包括两个简单的表（sales 和customers）。

 

customers 表的创建如下所示：

 

create table customers  

(  

   cust_id   number primary key,  

   cust_name varchar2(200),  

   rating    varchar2(1) not null  

)  

partition by list (rating)  

(  

   partition pA values ('A'),  

   partition pB values ('B')  

);  

 

sales表的创建如下所示。它是customers表的一个子表。

create table sales  

(  

   sales_id    number primary key,  

   cust_id     number not null,  

   sales_amt   number,  

   constraint  fk_sales_01  

    foreign key (cust_id)  

    references customers  

);  

在理想情况下，希望用相同的方式对sales表和customers表进行分区，根据rating列进行列表分区。但有一个严重的问题，sales表没有名为rating的列，那么如何根据一个不存在的列进行分区呢？

 

在Oracle 11g中，可以使用一个称为引用分区的新特性。下面的示例显示了如何将该特性应用于sales表：

 

create table sales  

(  

   sales_id    number primary key,  

   cust_id     number not null,  

   sales_amt   number,  

   constraint  fk_sales_01  

    foreign key (cust_id)  

    references customers  

)  

partition by reference (fk_sales_01);  

这段代码创建了与父表customers中相同的分区。注意，虽然没有名为rating的列，但仍根据该列对表进行了分区。partition by reference (fk_sales_01)子句包括了分区定义中的外键名。该语句指示 Oracle 11g确认通过父表（在该示例中为 customers）中使用的方案进行了分区。注意cust_id列的NOT NULL约束，这是引用分区所必需的。

 

检查 sales 表中分区的分区边界：

 

SQL> select partition_name, high_value  

  2  from user_tab_partitions  

  3  where table_name = 'SALES';  

 

PARTITION_NAME  HIGH_VALUE  

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

PA  

PB  

HIGH_VALUE为空，这意味着此处的边界派生自父表。分区的名称与父表中的名称相同。可以通过查询user_part_tables视图来检查分区的类型。一个名为ref_ptn_constraint_name的特殊列显示了外键约束名称。

 

SQL> select table_name, partitioning_type, ref_ptn_constraint_name  

  2  from user_part_tables  

  3  where table_name in ('CUSTOMERS','SALES');  

 

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME  

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

CUSTOMERS                      LIST  

SALES         

如果希望按照父表分区的方式对子表进行分区，但没有相同的列，又不仅为了分区而引入这些列，此时引用分区将非常方便。而且，不必针对每个子表显式声明一个很长的分区子句。

关于父表：

JIM@ prod> col high_value for a30

JIM@ prod> select partition_name , table_name , high_value from user_tab_partitions where table_name = 'PARTITIONED_TABLE2' ;

 

PARTITION_NAME                 TABLE_NAME           HIGH_VALUE

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

P2                             PARTITIONED_TABLE2   20000

P3                             PARTITIONED_TABLE2   30000

P4                             PARTITIONED_TABLE2   40000

P5                             PARTITIONED_TABLE2   50000

P6                             PARTITIONED_TABLE2   60000

P7                             PARTITIONED_TABLE2   70000

P8                             PARTITIONED_TABLE2   80000

P9                             PARTITIONED_TABLE2   90000

P10                            PARTITIONED_TABLE2   MAXVALUE

 

9 rows selected.

 

JIM@ prod> alter table partitioned_table2 modify id primary key ;

 

Table altered.

 

创建子表，报错如下：

JIM@ prod> create table test11 ( id number , msg varchar2(20) , constraint fk_test11 foreign key (id) references partitioned_table2 (id) )

  2  partition by reference ( fk_test11 ) ;

partition by reference ( fk_test11 )

                         *

ERROR at line 2:

ORA-14652: reference partitioning foreign key is not supported

原因是引用列没有NOT NULL约束。Oracle不知道NULL值进入哪个分区。

 

给引用列添加NOT NULL约束，子表创建成功。

JIM@ prod> create table test11 ( id number not null , msg varchar2(20) , constraint fk_test11 foreign key (id) references partitioned_table2 (id) )

  2  partition by reference ( fk_test11 ) ;

 

Table created.

 

查看子表的分区情况，可以看到HIGH_VALUE（分区条件列）是空的，说明是引用分区，可以查看。

JIM@ prod> select partition_name , table_name , high_value from user_tab_partitions where table_name = 'TEST11' ;

 

PARTITION_NAME                 TABLE_NAME           HIGH_VALUE

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

P2                             TEST11

P3                             TEST11

P4                             TEST11

P5                             TEST11

P6                             TEST11

P7                             TEST11

P8                             TEST11

P9                             TEST11

P10                            TEST11

 

9 rows selected.

 

可以通过下面这个查询，得到分区所依靠的外键。

JIM@ prod>  select table_name , partitioning_type , ref_ptn_constraint_name from user_part_tables where table_name = 'TEST11' ;

 

TABLE_NAME           PARTITION REF_PTN_CONSTRAINT_NAME

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

TEST11               REFERENCE FK_TEST11

 

可以通过下面这个查询，得到外键所依赖的主键或者唯一约束。

JIM@ prod> select constraint_name , r_owner , r_constraint_name from user_constraints where constraint_name = 'FK_TEST11' ;

 

CONSTRAINT_NAME                R_OWNER              R_CONSTRAINT_NAME

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

FK_TEST11                      JIM                  SYS_C0016613

 

可以通过下面这个查询，得到上面的约束所在的表和列。

JIM@ prod> col COLUMN_NAME for a20

JIM@ prod> select OWNER , TABLE_NAME , COLUMN_NAME from user_cons_columns where CONSTRAINT_NAME = 'SYS_C0016613' ;

 

OWNER                          TABLE_NAME           COLUMN_NAME

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

JIM                            PARTITIONED_TABLE2   ID