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?