Oracle Database 12c offers several enhancements for partitioning. This blog posting will present some enhancements in relation to reference partitioning. The differences between the Oracle 11g R2 and Oracle 12c releases will be shown using practical examples.
TRUNCATE and EXCHANGE with the CASCADE option
With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when truncating or exchanging a partition.
As an example, I created the two following objects on an Oracle 11g R2 and an Oracle 12c databases:
- A parent table TB_CUSTOMER containing a list of customers and the date of their inscription to an online shop
- A child-referenced table TB_INVOICE containing the id and date of invoices performed by customers on this shop.
SQL> Create table TB_CUSTOMER (
CUST_ID number primary key,
CUST_NAME varchar(25),
CUST_DATE date)
PARTITION BY RANGE (CUST_DATE)(
PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')) NOCOMPRESS,
PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')) NOCOMPRESS,
PARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy')) NOCOMPRESS);
SQL> Create table TB_INVOICE (INV_ID number primary key, INV_DATE date, CUST_ID number not null, Constraint fk_custid foreign key (cust_id) references TB_CUSTOMER(cust_id) ON DELETE CASCADE) Partition by reference (fk_custid);
We can see that all three partitions defined in the TB_CUSTOMER creation time have automatically been created in the TB_INVOICE table too:
SQL> select table_name, partition_name from user_tab_partitions; TABLE_NAME PARTITION_NAME ------------------------------ ----------------------------------- TB_INVOICE P_2013_31_12 TB_INVOICE P_2012_31_12 TB_INVOICE P_2011_31_12 TB_CUSTOMER P_2013_31_12 TB_CUSTOMER P_2012_31_12 TB_CUSTOMER P_2011_31_12
This behavior is common to 11g R2 and 12c releases and is proper to the reference partitioning.
To finalize my example, I have populated the table with customers and invoices:
SQL> insert into TB_CUSTOMER values (1,'OE',to_date('11.03.2012','dd.mm.yyyy'));
SQL> insert into TB_CUSTOMER values (2,'SCOTT',to_date('26.04.2012','dd.mm.yyyy'));
SQL> insert into TB_CUSTOMER values (3,'TIGER',to_date('30.08.2012','dd.mm.yyyy'));
SQL> insert into TB_CUSTOMER values (4,'HR',to_date('27.06.2013','dd.mm.yyyy'));
SQL> insert into TB_CUSTOMER values (5,'BI',to_date('18.02.2011','dd.mm.yyyy'));
SQL> commit;
SQL> insert into TB_INVOICE values (1, to_date('21.04.2013','dd.mm.yyyy'),2);
SQL> insert into TB_INVOICE values (2, to_date('01.02.2013','dd.mm.yyyy'),2);
SQL> insert into TB_INVOICE values (3, to_date('16.06.2013','dd.mm.yyyy'),1);
SQL> insert into TB_INVOICE values (4, to_date('17.06.2013','dd.mm.yyyy'),2);
SQL> commit;
Oracle 11g R2
I tried to truncate the partition containing customers registered in 2012 (P_2012_31_12). This partition features the customers OE, SCOTT and TIGER.
SQL> Alter table tb_customer truncate partition P_2012_31_12; Alter table tb_customer truncate partition P_2012_31_12 * ERROR at line 1:ORA-02266: unique/primary keys in table referenced by enabled foreign keys
The error ORA-02266 is returned because normally, the CASCADE option must be used when performing operations on data with references constraints. If I try to truncate the partition with the CASCADE option in 11g, I am not successful:
SQL> Alter table tb_customer truncate partition P_2012_31_12 cascade; Alter table tb_customer truncate partition P_2012_31_12 cascade * ERROR at line 1:ORA-14054: invalid ALTER TABLE TRUNCATE PARTITION option
Oracle 12c
I performed the same test on the Oracle 12c database:
SQL> Alter table tb_customer truncate partition P_2012_31_12 cascade; Table truncated.
The statement was accepted. To check if the cascade operation is effective, we can count the number of rows for both TB_CUSTOMER and TB_INVOICE tables:
SQL> select count (*) from tb_customer; COUNT(*) -------- 2
The customers OE, SCOTT, and TIGER, registered in 2012 (partition P_2012_31_12) were removed from the table TB_CUSTOMER.
SQL> select count (*) from tb_invoice; COUNT(*) -------- 0
The corresponding invoices were removed from the table TB_INVOICE with the cascade option.
Support of interval partitioning for parent tables
Prior to Oracle 12c, it was not possible to use an interval-partitioned table as a parent table for reference partitioning.
Oracle 11g R2
SQL> Create table TB_CUSTOMER (
CUST_ID number primary key,
CUST_NAME varchar(25),
CUST_DATE date
)PARTITION BY RANGE (CUST_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR'))(
PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')),
PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')),
PARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy')));
Table created
SQL> Create table TB_INVOICE ( INV_ID number primary key, INV_DATE date,CUST_ID number not null, Constraint fk_custid foreign key (cust_id) references TB_CUSTOMER (cust_id) ON DELETE CASCADE ) Partition by reference (fk_custid);
Create table TB_INVOICE ( * ERROR at line 1:ORA-14659: Partitioning method of the parent table is not supported
The error ORA-14659 is explicit: interval partitioning is not supported for the parent table.
Oracle 12c
This limitation has gone with Oracle 12c.
SQL> drop table tb_customer; Table dropped.
SQL> Create table TB_CUSTOMER (
CUST_ID number primary key,
CUST_NAME varchar(25),
CUST_DATE date
)PARTITION BY RANGE (CUST_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR'))(
PARTITION P_2011_31_12 VALUES LESS THAN (to_date('31.12.2011','dd.mm.yyyy')),
PARTITION P_2012_31_12 VALUES LESS THAN (to_date('31.12.2012','dd.mm.yyyy')),
PARTITION P_2013_31_12 VALUES LESS THAN (to_date('31.12.2013','dd.mm.yyyy')));
Table created.
SQL> Create table TB_INVOICE ( INV_ID number primary key, INV_DATE date, CUST_ID number not null, Constraint fk_custid foreign key (cust_id) references TB_CUSTOMER (cust_id) ON DELETE CASCADE) Partition by reference (fk_custid); Table created.
We can see that both parent and child tables have been created:
SQL> select table_name, partitioning_type, ref_ptn_constraint_name from user_part_tables; TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME -------------------- --------------------- -------------------------- TB_CUSTOMER RANGE TB_INVOICE REFERENCE FK_CUSTID
The partitions for the child table have been automatically created from the parent reference table.
SQL> select table_name, partition_name from user_tab_partitions; TABLE_NAME PARTITION_NAME ------------------------------ ---------------------------------- TB_CUSTOMER P_2011_31_12 TB_CUSTOMER P_2012_31_12 TB_CUSTOMER P_2013_31_12 TB_INVOICE P_2011_31_12 TB_INVOICE P_2012_31_12 TB_INVOICE P_2013_31_12
We can check if new interval partitions will automatically be created in the child table by creating a new customer on a non-existing range (Ex: 2014). This will generate a new interval partition in TB_CUSTOMER table.
SQL> insert into TB_CUSTOMER values (1,'OE',to_date('11.03.2014','dd.mm.yyyy'));
SQL> commit;
SQL> select table_name, partition_name from user_tab_partitions; TABLE_NAME PARTITION_NAME ------------------------------ ----------------------------------------------- TB_CUSTOMER P_2011_31_12 TB_CUSTOMER P_2012_31_12 TB_CUSTOMER P_2013_31_12 TB_CUSTOMER SYS_P401 TB_INVOICE P_2011_31_12 TB_INVOICE P_2012_31_12 TB_INVOICE P_2013_31_12
Only one new partition – for TB_CUSTOMER – has been created. But when creating new records in TB_INVOICES for customers registered in 2014, a new partition will automatically be created in TB_INVOICE table.
SQL> insert into TB_INVOICE values (1, to_date('21.04.2013','dd.mm.yyyy'),1);
SQL> commit;
SQL> select table_name, partition_name from user_tab_partitions; TABLE_NAME PARTITION_NAME ------------------------------ -------------------------------------------------- TB_CUSTOMER P_2011_31_12 TB_CUSTOMER P_2012_31_12 TB_CUSTOMER P_2013_31_12 TB_CUSTOMER SYS_P401 TB_INVOICE P_2011_31_12 TB_INVOICE P_2012_31_12 TB_INVOICE P_2013_31_12 TB_INVOICE SYS_P401
As a conclusion, we can see that interval partitioning is completely supported for reference partitioning: The partitions are created from the parent table at child table creation time and new required partitions are created automatically when the child table is filled.
Coming soon in my next blog posting: “Oracle 12c: Partitioning enhancements Part II – Other improvements”, if you want to know more about partitioning enhancements with Oracle 12c.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)