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.

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>