RE: staggered/throttled delete

If your predicates to select the candidate rows are trivial to process,

then

delete from tab where ....your predicates..... and rownum <= constant;
commit;

where constant is the integer number of the size of delete chunk you want is pretty much okay.

Then loop until you don't get anything to delete.

If the selection of candidate row is expensive, then you pick a different size that is the monolith size you're willing to accept for storing just the rowids to delete in another table, then you loop through that table using a different smaller rownum limit to delete whole rows (I'm assuming here you're trying to keep the uncommitted monolith size reasonable), and deleting those same rows from the list to delete table. Now if your chunk size is pretty big, you'll be rescan empty space from the table with the list or rowids to delete, so, you also include a default valued column that you set to NULL instead of deleting on the delete list table, index that column, and

delete from tab where rowid in (select c_rowid from d_tab where c_stillthere is not NULL and rownum < constant);
update d_tab set c_stillthere = NULL where rownum < constant; commit;

and then loop around that. So far Oracle will get the same rows, but to make this enduringly bullet proof you actually should get a list of the rowids from d_tab to update in d_tab when you get the c_rowid from the same table on which to do the delete.

OR, if your access pattern allows a small outage and you're deleting about 1/3 of the table or more (mileage will vary depending on indexes, etc.) then copy out the keepers, and drop the original, and rename where you copied out to back to the original. You can play games with SYNONYMs to resume insert access from other sessions sooner to the new destination if that is needed.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of David Sent: Friday, October 15, 2004 4:19 PM
To: oracle-l_at_freelists.org
Subject: staggered/throttled delete

Hi All,
Does any one have an example they can share on performing a large delete in small chunks?

Thanks
--

..
David
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 15 2004 - 19:15:34 CDT