Environment: Oracle 12.1.0.2 on Exadata
I have a table with 30 columns, one of which is a CLOB, that contains about 26 million rows.
I have a purge job (PL/SQL packaged procedure) that DELETEs rows from nine (9) other tables based on a list of IDs from the driver table that contains the CLOB.
I save the list of IDs in a global temporary table and use that to delete the associated rows from the other tables that are done in the correct order to maintain all the FK relationships.
I am running a test today that has identified about 98,000 IDs to be purged but I have about 5 million that need to be purged to 'catch up' and going forward I'll have about 10,000 per day to purge.
The DELETE on the other 9 tables runs very quickly.
The table with the CLOB column is taking many times longer than the others, like several hours as opposed to several minutes.
I'm guessing there is a better and more efficient way to remove the rows from the table containing the CLOB and to regain the storage used by the deleted CLOB.
I'm currently issuing just a 'DELETE FROM <table> WHERE ID IN (SELECT ID FROM <gtt>', where ID is the PK.
I would appreciate any suggestions and order of operational steps to accomplish this purge, both for the 'catch up' and the daily run of 10,000.
Thanks in advance for all the help!! Much appreciated!
-gary