3

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Reference partitioning schema

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.

Improve this answer
4
  • Thanks for the reply. Is it possible to create the partition on the fly with some sort of pre-insert trigger in a similar manner to the way that interval partitioning works? I'm considering what could happen in x amount of time if the admins "loose" the knowledge that they have to add partitions before the data and the whole system starts falling over.
    – drekka
    Jul 19, 2012 at 13:33
  • 1
    No, you can't use DDL in a trigger, especially the table you are updating. You will have to create the partitions before. If your project team is susceptible to "forgetting" how to do this, maybe you should not use partitioning :) (it's an "advanced" extra option of Oracle Entreprise, shouldn't you focus on DBA reliability first?)
    – Vincent Malgrat
    Jul 19, 2012 at 14:10
  • 1
    That being said, you could run a job that creates partitions every month. You probably want to drop partitions manually though. What happens when the job fails in X years and nobody knows how it works is something to think about though :)
    – Vincent Malgrat
    Jul 19, 2012 at 14:13
  • THanks. I was thinking of a cron job to reate partitions. The admins are already wanting a cron job to handle the removal of old partitions. There is no requirement to do anything except delete old data so dropping partitions seems like the best method as I understand it.
    – drekka
    Jul 19, 2012 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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