Oracle tables partition related

Question

Here is my doubt. Is it possible to apply a partition to one table depending in the partitions values of another one?

Why I am asking? I have one table where information headers are persisted, and other table where information details are stored. As you can imagine, the second one is much bigger than the first one. The one containing headers, has an automatic partition by date, so I guess it would be desirable to partition the one with the details in order to perform queries. Here the example:

HEADER_TABLE
ID DATE
-----------------------------
1 10/10/2010
2 10/10/2010 Partition 1
3 10/10/2010
-----------------------------
4 11/10/2010
5 11/10/2010 Partition 2
-----------------------------

DETAIL_TABLE
ID HEADER_FK
-----------------------------
01 1
02 1
03 2 Partition 1
04 2
05 3
06 3
-----------------------------
07 4
08 4 Partition 2
09 5
10 5
-----------------------------

One way I think this could be done, is adding the date field to the details table, and to partition it in the same way than the header one, but the thing is that altering the details table in production and updating the rows (hundred millons) could be a problem.

Any idea?

Answer 1

You can use partition by reference option Example :

create table trans (
 trans_id number not null,
 res_id number not null,
 trans_date date not null,
 amt number,
 constraint fk_trans_01
 foreign key (res_id)
 references res
)
partition by reference
 (fk_trans_01);

Some restricions :

• Parent table Primary Key and Unique constraints used for reference partitioning must be enabled and non-deferrable.
  
• Child table "partitioning constraints" must be enabled and non-deferrable
  
• All child table columns used in "partitioning constraints" must be defined as NOT NULL. This is required to assure that every child row maps to exactly one parent row.
• Child tables created using reference partitioning can never be disassociated from their parents without being dropped. – There is no ALTER TABLE command to convert a reference partitioned table into a non-reference partitioned table.
• As with other tables referenced via foreign key constraints, parent tables cannot be dropped until all foreign key constraints referencing them are removed. – In the case of reference partitioning, this means all descendent tables must first be dropped, since the partitioning constraints on the child tables cannot be disabled or dropped.

NOTE:

• Reference Partitioning has strong advantages and strong limitations, so its use requires careful planning.

  Reference Partitioning should not simply be used wherever possible.

• Use of Reference Partitioning appears best suited for very large tables and related tables, whose contents share the same life cycle.

Answer 2

if you have at least oracle 11g you could try use Reference Partitioning which is based on foreign keys. Please look at http://www.dba-oracle.com/t_11g_new_reference_partitioning.htm

• Emiliano Schiano: We have recently upgraded from 10 to 11g and I did´t know that feature. Thanks for the information.