Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> advice for massive delete querry

advice for massive delete querry

From: bobmetelsky <bobmetelsky_at_comcast.net>
Date: Wed, 31 Aug 2005 18:12:40 -0400
Message-ID: <43162B58.8080509@comcast.net>


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-l
Received on Wed Aug 31 2005 - 17:14:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US