We have a table that grows at a approximately 6.4 million rows / month that is partitioned, and we periodically (monthly) drop a partition. We have recently introduced a join table on the primary key of this table (with cascading deletes). This introduces problems with referential integrity where-in we can't drop the partition because the join table refers to rows inside it.
We've received advice to make our application code do it, but we felt this was a less than optimal solution.
I've since read this: https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1007479 and it appears that they are recommending to first DELETE FROM table partition (partitionID); and then ALTER TABLE table DROP PARTITION partitionID;
We are worried about:
I'm wondering if someone has a better idea. Or can re-assure me as to this not being a bad idea.
Wernfried's answer is very helpful, however what I was after was the ability to partition the join table like the main table. In this way we can drop the join table's partition, then the parent partition. This avoids the referential integrity problems without requiring a delete. The delete would not have been an option due to the size of the table, our undo/redo logs would more than likely not have taken the strain.
This can be achieved in two ways, the first is the easiest but may not work on older database versions. We can make a reference partition on the join table, which will keep the two partitions in sync.
The other option is to include into the child partition the same rows as we partition on the parent. This has the downside of increases the size of the join table but it will allow the table partitions to stay in-sync.
Oracle has more information here: https://docs.oracle.com/database/121/VLDBG/GUID-54D18B18-6838-4115-9389-E1FB0D20A8CA.htm