12c新特性之Interval Reference Partitioning

Oracle 11g引进了一种新的分区方法，它用于主从表之间的关联性上。若主表建立分区，从表可以根据外键约束来建立对应的主表分区。
Oracle自动维护在从表上的分区，不需要手工干预。
11g中关联分区限制：
1. 不支持Interval分区
2. 不支持索引组织表、外部表、域索引
3. 关联的主键和唯一约束不能支持虚拟列
从12c开始，你可以使用interval分区表作为reference partitioning的主表。
示例如下：
1. 创建表

SQL> 
SQL> conn hcn/oracle
Connected.
SQL> 
SQL> 
SQL> 
SQL> CREATE TABLE parent(pk INT CONSTRAINT par_pk PRIMARY KEY, i INT)
  2   PARTITION BY RANGE(i) INTERVAL (10)
  3   (PARTITION p1 VALUES LESS THAN (10));
Table created.
SQL> 
SQL> CREATE TABLE child(fk INT NOT NULL, i INT,
SQL>   2   CONSTRAINT chi_fk FOREIGN KEY(fk) REFERENCES parent(pk))
  3   PARTITION BY REFERENCE(chi_fk);
Table created.
SQL> 
SQL> 
SQL> INSERT INTO parent VALUES(15, 15);
1 row created.
SQL> INSERT INTO parent VALUES(25, 25);
1 row created.
SQL> INSERT INTO parent VALUES(35, 35);
1 row created.
SQL> 
SQL> set lines 200
SQL> col table_name for a20
SQL> col partition_position for a30
SQL> col high_value for a40
SQL> col partition_position for 999999
SQL> SELECT table_name, partition_position, high_value, interval
  2     FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT', 'CHILD')
  3     ORDER BY 1, 2;
TABLE_NAME           PARTITION_POSITION HIGH_VALUE                               INT
-------------------- ------------------ ---------------------------------------- ---
CHILD                                 1                                          NO
PARENT                                1 10                                       NO
PARENT                                2 20                                       YES
PARENT                                3 30                                       YES
PARENT                                4 40                                       YES
SQL>

2. 对父表进行操作

SQL> 
SQL> ALTER TABLE parent SPLIT PARTITION FOR (25) AT (25) INTO (partition x, partition y);
Table altered.
SQL> 
SQL> 
SQL> SELECT table_name, partition_position, high_value, interval
  2     FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT', 'CHILD')
  3     ORDER BY 1, 2;
TABLE_NAME           PARTITION_POSITION HIGH_VALUE                               INT
-------------------- ------------------ ---------------------------------------- ---
CHILD                                 1                                          NO
CHILD                                 2                                          NO
CHILD                                 3                                          NO
CHILD                                 4                                          NO
PARENT                                1 10                                       NO
PARENT                                2 20                                       NO
PARENT                                3 25                                       NO
PARENT                                4 30                                       NO
PARENT                                5 40                                       YES
9 rows selected.
SQL>

子表建立时为非分区表，当对父表进行split处理时，子表也自动维护，并变为了分区表。具体转换过程还可以进一步分析。