A collection of tutorials, code and tools to help you get better using Oracle
25 August 2011
Partitioned tables with a foreign key relationship have always been a problem until Oracle 11g.
Consider this common pattern - a table of bill records, partitioned by month:
SQL11G> create table bills (
bill_id integer primary key,
bill_date date not null,
bill_total number not null
)
partition by range (bill_date)
(
PARTITION P201101 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')),
PARTITION P201102 VALUES LESS THAN (TO_DATE('01-FEB-2011', 'DD-MON-YYYY')),
PARTITION P201103 VALUES LESS THAN (TO_DATE('01-MAR-2011', 'DD-MON-YYYY')));
And a table of bill_details records, with a foreign key back to bills, also partitioned on date:
SQL11G> create table bill_details (
line_item_id integer primary key,
bill_id integer not null,
bill_date date,
cost number not null,
description varchar2(255) not null,
constraint bill_details_bills_fk foreign key (bill_id) references bills
)
partition by range (bill_date)
(
PARTITION P201101 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')),
PARTITION P201102 VALUES LESS THAN (TO_DATE('01-FEB-2011', 'DD-MON-YYYY')),
PARTITION P201103 VALUES LESS THAN (TO_DATE('01-MAR-2011', 'DD-MON-YYYY')));
There two problems with this design.
Most obvious is that the bill_date column is repeated in both tables and repeating it for each record in the Bill_Details table means the data is not properly normalised. If you want both the tables partitioned on bill date, then until 11g, there was no choice but to compromise and repeat the date column in both tables. Bad for normalisation and a waste of disk space.
The other problem is less obvious. First insert a few rows of data into each table:
SQL11G> insert into bills
values (1, to_date('20101231', 'YYYYMMDD'), 100.00);
SQL11G> insert into bill_details
values (1, 1, to_date('20101231', 'YYYYMMDD'), 100.00, 'Line Item Details');
There is now a row in the oldest partition in each table. Imagine it is time to purge the data, and you want to drop the oldest partition. Simple, just drop the child partition, and then the parent partition, and all will be good - or will it?
SQL11G> alter table bill_details drop partition P201101;
table BILL_DETAILS altered.
SQL11G> alter table bills drop partition P20110101;
Error report:
SQL Error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Try as you might, Oracle will not let you drop that parent partition unless you disable the foreign key between Bills and Bill_Details. If the tables are large, re-enabling that constraint is going to be an expensive operation, and probably best avoided.
Jonathan Lewis has a neat trick to work around this issue, but it still doesn't solve the normalisation problem. Luckily Oracle 11G comes to the rescue with Reference Partitioning.
First drop the existing tables to clean up:
SQl11G> drop table bill_details;
SQL11G> drop table bills;
Next, create the Bills table in exactly the same way as before:
SQL11G> create table bills (
bill_id integer primary key,
bill_date date not null,
bill_total number not null
)
partition by range (bill_date)
(
PARTITION P201101 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')),
PARTITION P201102 VALUES LESS THAN (TO_DATE('01-FEB-2011', 'DD-MON-YYYY')),
PARTITION P201103 VALUES LESS THAN (TO_DATE('01-MAR-2011', 'DD-MON-YYYY')));
Finally, create the Bill_Details table, but instead of specifying how to partition it, tell it to inherit it's partitioning from the parent table:
SQL11G> create table bill_details (
line_item_id integer primary key,
bill_id integer not null,
cost number not null,
description varchar2(255) not null,
constraint bill_details_bills_fk foreign key (bill_id) references bills
)
PARTITION BY REFERENCE (bill_details_bills_fk);
If you check the partitions in each table you will see that Bills has the partitions you specified, and Bill_Details has a matching set with the same names:
SQL11G> select table_name, partition_name
from user_tab_partitions where table_name in ('BILLS', 'BILL_DETAILS');
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
BILLS P201101
BILLS P201102
BILLS P201103
BILL_DETAILS P201101
BILL_DETAILS P201102
BILL_DETAILS P201103
Now the magic really starts. Insert the same pair of rows as before:
SQL11G> insert into bills
values (1, to_date('20101231', 'YYYYMMDD'), 100.00);
SQL11G> insert into bill_details
values (1, 1, to_date('20101231', 'YYYYMMDD'), 100.00, 'Line Item Details');
If you join these two tables, Oracle knows that rows that come from a given partition in Bills must be in the matching partition in Bill_Details, so a query like the following with only access a single partition in Bill_Details:
SQl11G> select bd.*
from bills b, bill_details bd
where b.bill_id = bd.bill_id
and b.bill_id = 1;
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 194 | 16 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 1 | 194 | 16 (0)| 00:00:01 | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| BILLS | 1 | 26 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX UNIQUE SCAN | SYS_C0012476 | 1 | | 1 (0)| 00:00:01 | | |
| 4 | PARTITION REFERENCE SINGLE | | 1 | 168 | 14 (0)| 00:00:01 | KEY | KEY |
|* 5 | TABLE ACCESS FULL | BILL_DETAILS | 1 | 168 | 14 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------
This means you can use a local index on billid in BillDetails to speed up dropping partitions without impacting query performance.
The other nice thing about reference partitioning is that you no longer have any problems with data purging. If you drop the parent partition, Oracle knows it must drop the child partition too, and it does just that:
SQL11G> alter table bills drop partition P201101;
table BILLS altered.
SQL11G> select table_name, partition_name
from user_tab_partitions where table_name in ('BILLS', 'BILL_DETAILS');
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
BILLS P201102
BILLS P201103
BILL_DETAILS P201102
BILL_DETAILS P201103