This is an example for the 12c new feature of creating interval partitioned tables as parent tables for reference partitioning.
1. Partitions in a reference-partitioned table corresponding to interval partitions in the parent table are created when inserting records into the reference partitioned table.
CREATE TABLE parent (pk INT CONSTRAINT parent_pk PRIMARY KEY, i INT) PARTITION BY RANGE(i) INTERVAL (10) (partition p_p0 values less than ( 0), partition p_p1 values less than (10), partition p_p2 values less than (20), partition p_p3 values less than (30)); Table created.
SQL> CREATE TABLE child(fk INT NOT NULL, i INT, CONSTRAINT child_fk FOREIGN KEY(fk) REFERENCES parent(pk)) PARTITION BY REFERENCE(child_fk); Table created.
SQL> SELECT table_name, partition_name,partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT','CHILD');
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE INTERVAL
---------- -------------------- ------------------ -------------------- ---------
CHILD P_P0 1 NO
CHILD P_P1 2 NO
CHILD P_P2 3 NO
CHILD P_P3 4 NO
PARENT P_P0 1 0 NO
PARENT P_P1 2 10 NO
PARENT P_P2 3 20 NO
PARENT P_P3 4 30 NO
8 rows selected.
2. At this moment there are no interval partitions present. After the following inserts, 2 interval partitions are created in the parent table and none in the child.
INSERT INTO parent VALUES(15, 15); INSERT INTO parent VALUES(35, 35); INSERT INTO parent VALUES(45, 45);
SQL> SELECT table_name, partition_name,partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT','CHILD');
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE INTERVAL
---------- -------------------- ------------------ -------------------- ---------
CHILD P_P0 1 NO
CHILD P_P1 2 NO
CHILD P_P2 3 NO
CHILD P_P3 4 NO
PARENT P_P0 1 0 NO
PARENT P_P1 2 10 NO
PARENT P_P2 3 20 NO
PARENT P_P3 4 30 NO
PARENT SYS_P4543 5 40 YES
PARENT SYS_P4544 6 50 YES
10 rows selected.
3. Some inserts will be made into the child table:
insert into child values (15,10); insert into child values (35,11);
SQL> SELECT table_name, partition_name,partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT','CHILD');
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE INTERVAL
---------- -------------------- ------------------ -------------------- ---------
CHILD P_P0 1 NO
CHILD P_P1 2 NO
CHILD P_P2 3 NO
CHILD P_P3 4 NO
CHILD SYS_P4543 5 YES
PARENT P_P0 1 0 NO
PARENT P_P1 2 10 NO
PARENT P_P2 3 20 NO
PARENT P_P3 4 30 NO
PARENT SYS_P4543 5 40 YES
PARENT SYS_P4544 6 50 YES
11 rows selected.
It can be seen that an interval partition was created in the child table as well, and as per the rule, the same name for the partition as per parent table was inherited.
4. Operations that transform interval partitions to conventional partitions in the parent table, such as ALTER TABLE SPLIT PARTITION on an interval partition, perform the corresponding transformation in the child table, creating partitions in the child table as necessary.
SQL> ALTER TABLE parent SPLIT PARTITION FOR (35) AT (35) INTO (partition px,partition py); Table altered.
SQL> SELECT table_name, partition_name,partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PARENT','CHILD');
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE INTERVAL
---------- -------------------- ------------------ -------------------- ---------
CHILD P_P0 1 NO
CHILD P_P1 2 NO
CHILD P_P2 3 NO
CHILD P_P3 4 NO
CHILD PX 5 NO
CHILD PY 6 NO
PARENT P_P0 1 0 NO
PARENT P_P1 2 10 NO
PARENT P_P2 3 20 NO
PARENT P_P3 4 30 NO
PARENT PX 5 35 NO
PARENT PY 6 40 NO
PARENT SYS_P4544 7 50 YES
13 rows selected.
5. The SQL ALTER TABLE SET INTERVAL statement is not allowed for reference-partitioned tables, but can be run on tables that have reference-partitioned children. For example:
SQL> SELECT table_name, partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI') ;
TABLE_NAME PARTITION_POSITION HIGH_VALUE INTERVAL
---------- ------------------ -------------------- ---------
CHI 1 NO
CHI 2 YES
PAR 1 10 NO
PAR 2 20 YES
PAR 3 30 YES
PAR 4 40 YES
6 rows selected.
SQL> ALTER TABLE CHI SET INTERVAL (20); ALTER TABLE CHI SET INTERVAL (20) * ERROR at line 1: ORA-14759: SET INTERVAL is not legal on this table.
6. In particular, ALTER TABLE SET INTERVAL removes the interval property from the targeted table and converts any interval-reference children to ordinary reference-partitioned tables.
SQL>ALTER TABLE par SET INTERVAL (20); Table altered.
SQL> SELECT table_name, partition_position, high_value, interval FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI');
TABLE_NAME PARTITION_POSITION HIGH_VALUE INTERVAL
---------- ------------------ -------------------- ---------
CHI 1 NO
CHI 2 NO
PAR 1 10 NO
PAR 2 20 NO
PAR 3 30 NO
PAR 4 40 NO
6 rows selected.
7. After INSERT INTO par VALUES(45,45) is made a new interval partition can be created in parent table:
SQL> SELECT table_name, partition_position, high_value, interval
FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI');
TABLE_NAME PARTITION_POSITION HIGH_VALUE INTERVAL
---------- ------------------ -------------------- ---------
CHI 1 NO
CHI 2 NO
PAR 1 10 NO
PAR 2 20 NO
PAR 3 30 NO
PAR 4 40 NO
PAR 5 60 YES
7 rows selected.
Also in a similar manner, the SQL ALTER TABLE SET STORE IN statement is not allowed for reference-partitioned tables but can be run on tables that have reference-partitioned children.