Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> advice for massive delete querry
Guys I have this statement from a developer
DELETE target
WHERE (1_num,2_num,3_num,4_num)
IN
(SELECT 1_num,2_num,3_num,4_num)
FROM source s
WHERE s.4_num BETWEEN 20040101 AND 20041231;
The statement is excepted to delete around 150M rows, the sub query
returns about 300M rows. !!!
all datatypes are number(n)
I'm not a performance guru but I suggested to use the commit_every(n) package - from Steve Adams site, and CTAS e.g.
begin
commit_every(100);
create table t as select * from source where 4_num < 20040101 AND 4_num >
20041231;
end;
What are the options for a massive delete statement such as this? I've googled and poked around orafaq but didn't come up with much.
I recall the helpful mindset from Wolfgang Brietling (sp) as "the quickest way to do something is often not to do it", so Im thinking ctas with commit_every() as a viable alternative to the delete.
Ideas from the more experienced?
thanks!
bob
-- "Oracle error messages being what they are, do not highlight the correct cause of fault, but will identify some other error located close to where the real fault lies." -- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 31 2005 - 17:14:42 CDT
![]() |
![]() |