12c Database : Reference Partitioning Enhancement – Part 1

From 12c, you can truncate partitions from a referenced partition with cascade option.

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)(
	PARTITION P_2013_06 VALUES LESS THAN (to_date('30-06-2013','dd-mm-yyyy')) NOCOMPRESS,
	PARTITION P_2013_07 VALUES LESS THAN (to_date('31-07-2013','dd-mm-yyyy')) NOCOMPRESS,
	PARTITION P_2013_08 VALUES LESS THAN (to_date('31-08-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, partition_name from user_tab_partitions where 
	      table_name in ('EMPLOYEE','EMP_SALES');

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

	6 rows selected.

Let's truncate the partition P_2013_06 which is having child records of emp sales.

	SQL> alter table employee truncate partition P_2013_06 ;
	alter table employee truncate partition P_2013_06
		    *
	ERROR at line 1:
	ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Error has been thrown as there are child records, behaviour until 11g,

	SQL> alter table employee truncate partition P_2013_06 cascade;

	Table truncated.

As you see above, the parent partition has been truncated with cascade option and the child records

Lets check the data in both tables

No data in the child table

	SQL> select * from emp_sales;

	no rows selected

Only data in parent table after truncation of one partition.

	SQL> select * from employee;

	EMPLOYEE_ID EMPLOYEE_NAME             JOINING_D
	----------- ------------------------- ---------
		  3 Mahesh                    30-JUL-13
		  5 Sarvesh                   18-AUG-13

SQL>

-Thanks
Geek DBA