Purging/Deleting Large Amounts of Data Oracle 7.3.4 via PL/SQL

[wellster34]

We are running into Major Performance Problems trying to Purge data. The Tables and Indexes are analyzed and the column used in the Purge is a Date. There is an index on that column considering that is the driver of the purge. There are approx 14 million rows in the table. Each day of information that is trying to be purged is an average of 180,000 rows. This script is run on an UNIX environment via sqlplus and using PL/SQL logic to execute the commands.

We currently have a Looping structure:

LOOP

DELETE FROM View of the table
WHERE DATE < (SYSDATE-90)
AND ROWNUM < 500

EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;

END LOOP;

COMMIT;

Now, is a View better than the table name? Is a Cursor better? Any suggestions on how to purge the data efficiently? I really do not know, any help on this subject would be greatly appriciated.

Thanks.

 

[EdWall]

If there are other indexes on the table, these might be detrimental to performance because the indexes have to be updated after each delete. You could remove these before deleting and then recreate them afterwards.

It might be worth considering recreating the date index after each deletion too - not really for performance but to reclaim a bit of space.

Have you considered partitioning the table on the transaction date?

Ed

 

[MikeLacey]

A view isn't better than the table name, it's just another layer between you and the real table.

A cursor might well be better, you could delete 500 rows and then commit - pretty much as you're doing above.

Dropping and recreating indexes is a common technique when you're bulk-loading a table. I'm not sure you'd save time in this scenario, and it would certainly take a long time to rebuild the indexes afterwards.

This is not going to be a fast operation whatever you do. Mike
&quot;Experience is the comb that Nature gives us after we are bald.&quot;

Is that a haiku?
I never could get the hang
of writing those things.

 

[sem]

Try to increase batch size (ROWNUM < 10000). The only reason for deleting in such manner is a size of rollback segment so if you have a lot of free memory you may try to delete all records by one statement (you may also use SET TRANSACTION to specify rollback segment explicitly).
Try also to explain plan: sometimes index is not used because of its large size. If you're sure it's sufficiently small to fit a memory feel free to add a hint.