Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Best practice to delete rows with a CLOB column

User_NHWMXAug 4 2020 — edited Sep 8 2020

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

This post has been answered by Jonathan Lewis on Sep 3 2020
Jump to Answer

Comments

Alex Keh-Oracle

Looks like it's a known issue. You can try the recommended steps in the thread to make sure you have updated all your dependencies.

User_2SBJ9

@bjarne-callewaert do you get any successful answer to fix this issue ? I would like to use the same cache library however I'm getting the same error

1 - 2

Post Details

Added on Aug 4 2020
31 comments
5,432 views