I have a business process that inserts parent records into a queue table. Multiple detail records are written to two child tables. Once the item is processed by the business, the parent record is deleted and inserted into a log table. The detail records remain in their child tables. We run a nightly job that purges the log table and all related detail records. The log table is very large and this process takes about 45 minutes to complete. Previously we were purging all records older than 30 days. I was using the smallest key in the parent table to delete all records in the child tables with a key less than the parent key. This seemed to work well since it used an indexed column to determine the delete criteria and avoided any table joins.
We now have a new requirment to purge log records based on a client's retension criteria. Most records we still purge after 30 days, however, some records we now keep 60 or 90 days. My previous method of using the smallest key no longer works since there will be gaps in the data. I can't use a cascade delete since the primary key is shared between the two queue and log tables. I could use something similar to the following, but given the size of the log table it seems very inefficient.
DELETE FROM TRANSACTION_LOG_PROPERTIES
WHERE TRANSACTION_ID IN
( SELECT TRANSACTION_ID FROM TRANSACTION_LOG
WHERE TRANS_COMPLETE_DATE < cutOfDate);
What is the best way to clean up my detail records? Thanks. Also, we are running an Oracle 10g database.