RE: staggered/throttled delete
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <thump@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 15 Oct 2004 20:18:41 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of David
Sent: Friday, October 15, 2004 4:19 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
