12c Database : Interval Partitioning enhancements – 2

From 12c, interval partitions has been enhanced to use reference partitioning,
With this you can create a parent/child table with references in them using interval partitioning.

The following is the test case.

	Create table EMPLOYEE (
	EMPLOYEE_ID number primary key,
	EMPLOYEE_NAME varchar(25),
	JOINING_DATE date)
	PARTITION BY RANGE (JOINING_DATE) INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
	(
	PARTITION P_2013_06 VALUES LESS THAN (to_date('31-12-2011','dd-mm-yyyy')) NOCOMPRESS,
	PARTITION P_2013_07 VALUES LESS THAN (to_date('31-12-2012','dd-mm-yyyy')) NOCOMPRESS,
	PARTITION P_2013_08 VALUES LESS THAN (to_date('31-12-2013','dd-mm-yyyy')) NOCOMPRESS) tablespace users; 

	Create table EMP_SALES (SALE_ID number primary key,
	SALES_DATE date,
	EMPLOYEE_ID number not null,
	Constraint fk_empid foreign key (employee_id) references EMPLOYEE(employee_id) ON DELETE CASCADE)
	Partition by reference (fk_empid) tablespace users; 

	SQL> insert into EMPLOYEE values (1,'Geek DBA',to_date('11-06-2013','dd-mm-yyyy'));
	SQL> insert into EMPLOYEE values (2,'Ramesh',to_date('26-06-2013','dd-mm-yyyy'));
	SQL> insert into EMPLOYEE values (3,'Mahesh',to_date('30-07-2013','dd-mm-yyyy'));
	SQL> insert into EMPLOYEE values (4,'Naresh',to_date('27-06-2013','dd-mm-yyyy'));
	SQL> insert into EMPLOYEE values (5,'Sarvesh',to_date('18-08-2013','dd-mm-yyyy'));
	SQL> commit; 
	
	Insert the child records

	SQL> insert into EMP_SALES values (1, to_date('21-06-2013','dd-mm-yyyy'),1);
	SQL> insert into EMP_SALES values (2, to_date('15-06-2013','dd-mm-yyyy'),2);
	SQL> commit; 

Lets check the partitions for both tables

	SQL> select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables 
	     where table_name in ('EMPLOYEE','EMP_SALES');
	
	TABLE_NAME                               PARTITION REF_PTN_CONSTRAINT_NAME
	---------------------------------------- --------- ----------------------- 
	EMPLOYEE                                 RANGE
	EMP_SALES                                REFERENCE FK_EMPID
	
	SQL> select table_name, partition_name from user_tab_partitions 
	     where table_name in ('EMPLOYEE','EMP_SALES');
	
	TABLE_NAME                               PARTITION_NAME
	---------------------------------------- ----------------------------------------
	EMPLOYEE                                 P_2013_06
	EMPLOYEE                                 P_2013_07
	EMPLOYEE                                 P_2013_08
	EMP_SALES                                P_2013_06
	EMP_SALES                                P_2013_07
	EMP_SALES                                P_2013_08
	
	6 rows selected.
	
	SQL>

Let's insert a new row which falls beyond new interval
and see whether the partition is created in master and child table

	SQL> insert into EMPLOYEE values (7,'Mangesh',to_date('11-06-2014','dd-mm-yyyy'));
	SQL> insert into EMP_SALES values (4, to_date('15-07-2014','dd-mm-yyyy'),7);

As you see below, the interval partitions (P726) created for both EMPLOYEE and EMP_SALES created automatically,

	SQL>  select table_name, partition_name from user_tab_partitions 
	where table_name in ('EMPLOYEE','EMP_SALES');

	TABLE_NAME                               PARTITION_NAME
	---------------------------------------- ----------------------------------------
	EMPLOYEE                                 P_2013_06
	EMPLOYEE                                 P_2013_07
	EMPLOYEE                                 P_2013_08
	EMPLOYEE                                 SYS_P726
	EMP_SALES                                P_2013_06
	EMP_SALES                                P_2013_07
	EMP_SALES                                P_2013_08
	EMP_SALES                                SYS_P726

-Thanks
Geek DBA