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:

1 10/10/2010
2 10/10/2010 Partition 1
3 10/10/2010
4 11/10/2010
5 11/10/2010 Partition 2

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?

2 Answers 2


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

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.


  • 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.


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

  • We have recently upgraded from 10 to 11g and I did´t know that feature. Thanks for the information. Feb 27, 2014 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.