8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Interval-Reference Partitioning in Oracle Database 12c Release 1
In previous releases you were able to do reference partitioning and interval partitioning, but you couldn't use an interval partitioned table as the parent for a reference partitioned table. Oracle 12c lifts that restriction, so you can now use interval-reference partitioning.
Related articles.
Interval-Reference Partitioning
The following code creates an interval partitioned table (T1) with yearly partitions and an interval-reference partitioned table (T2).
DROP TABLE t2 PURGE; DROP TABLE t1 PURGE; CREATE TABLE t1 ( id NUMBER, description VARCHAR2(50), created_date DATE, CONSTRAINT t1_pk PRIMARY KEY (id) ) PARTITION BY RANGE (created_date) INTERVAL (NUMTOYMINTERVAL(12,'MONTH')) (PARTITION part_01 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users); CREATE TABLE t2 ( id NUMBER NOT NULL, t1_id NUMBER NOT NULL, description VARCHAR2(50), created_date DATE, CONSTRAINT t2_pk PRIMARY KEY (id), CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE ) PARTITION BY REFERENCE (t2_t1_fk);
As we insert data for each year, we can see the partitions are created for both tables.
-- Insert rows to 2014. INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY')); INSERT INTO t2 VALUES (1, 1, 't2 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY')); COMMIT; COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 SELECT table_name, partition_name FROM user_tab_partitions ORDER BY 1,2; TABLE_NAME PARTITION_NAME -------------------- -------------------- T1 PART_01 T2 PART_01 SQL> -- Insert rows to 2015. INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY')); INSERT INTO t2 VALUES (2, 2, 't2 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY')); COMMIT; SELECT table_name, partition_name FROM user_tab_partitions ORDER BY 1,2; TABLE_NAME PARTITION_NAME -------------------- -------------------- T1 PART_01 T1 SYS_P835 T2 PART_01 T2 SYS_P835 SQL> -- Insert rows to 2016. INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY')); INSERT INTO t2 VALUES (3, 3, 't2 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY')); COMMIT; SELECT table_name, partition_name FROM user_tab_partitions ORDER BY 1,2; TABLE_NAME PARTITION_NAME -------------------- -------------------- T1 PART_01 T1 SYS_P835 T1 SYS_P836 T2 PART_01 T2 SYS_P835 T2 SYS_P836 SQL>
For more information see:
- Creating Interval-Reference Partitioned Tables
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...