Oracle: Interval partitioning and dependant tables I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to another 80M records between them. This database is basically a log of data so the data going in is written once and only read after that. 

The admims want to partition the data per month because they want to quickly remove old data after two year and avoid having to rebuild all the indexes. So their intent to to remove old partitions.

After reading a lot, my idea was to do interval partitioning as per many examples I found on the net, however I then found that the other two tables cannot then employ partition by reference. Why oracle does not allow this, no-one here knows.

So my question is what, in your experience, would you do to partition the data? given it's is spread across the 3 tables. And is this the best technique for handing a rolling removal of old data like this?
You can't use interval partitioning with reference partitioning, however, It's possible to mix range partitioning and reference partitioning, as shown in the example of reference partitioning in the 11g doc:


  The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.


The child table doesn't need to contain the partition key (here the log date). It inherits the partition key from the parent table.



Range partitioning involves a bit more maintenance because you have to create the partitions  yourself. However, once the partitions are created, range and interval work similarly.