Hi,
Here's my requirement. My version is 19.12, Standard Edition 2.
I have a volatile table T1 that gets populated by triggers on various business tables.
T1 is used by a batch process to feed another table T for producing files.
Because T1 can still be populated while the batch is going on, the content of T1 is transferred into a temp table, and then from the temp table to T. Once the insert from temp to T has been done, the rows from T1 that were transferred are deleted from T1, based on the ids present in the temp table. This works fine as long as the row volume isn't that big, but causes performance issue if millions of rows are to be deleted.
So, to summarize:
T1 can get populated with a huge data volume (say 100 millions rows)
Session S1 starts the batch process. Rows are transferred from T1 to temp table (using append and parallel hints). This takes about 5 minutes.
Meanwhile, sessions S2 and S3 have already inserted rows into T1, but haven't commited. So these rows are not extracted by the batch.
Session S1 transfers the 100 million rows from temp table to T, with a bit a business logic. This takes about 10 min
Now, the rows processed can be purged from T1 :
delete T1 where t1.id in (select tmp.id from tmp); -- > this is the killer, takes several hours
Ideally this should be replace by a truncate, but the uncommited inserts against the table would prevent it.
The only solution I coulf think of is enable a trigger that would forbid inserts into T1 and redirect the rows into another T2 table. Once the transfer is done, truncate T1 and insert the rows from T2 into T1. Disable the trigger. The problem is using a trigger is apparently risky as it might get enabled by mistake (and this would be bad as the transactions from the app would fail).
Any other solutions you could think of?
Thanks