Blog - comments

Hi Greg,

Thanks

great job Dave ! thanks

greg

Nice writeup

yup
using System; using System.Diagnostics; namespace ConsoleApplication1 { class Program { ...
praveen rathore
sql server is one of the best for utilize the data recovery. and its very informative for all the us...
Server instalattion
Blog Michael Schwalm Oracle Database 12c: Partitioning enhancements Part I - Reference partitioning

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Oracle Database 12c: Partitioning enhancements Part I - Reference partitioning

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

T
B_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.

Rate this blog entry:
2

Michael Schwalm is Consultant at dbi Services and has more than two years of experience in Oracle database administration. He has a broad knowledge in the realization of virtualization infrastructures such as vMware vSphere. He took his first steps in database administration as an integrator of a web applications on Unix, Oracle, and Websphere environments. Michael Schwalm is Oracle Certified Professional 11g. Prior to joining dbi services, Michael Schwalm was application administrator at SOGETI Est (F) on behalf of PSA Peugeot Citroen and responsible for the realization and managing of Unix environments and Oracle databases in the context of migration projects. Michael Schwalm holds a BTS diploma in Information System Management from Belfort (F) and a TSAR diploma in advanced network administration from Strasbourg (F). His branch-related experience covers Automotive, Software industry, Financial Services / Banking, etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Friday, 19 September 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter