RE: advice for massive delete querry

Well, there are things that will slow you down:

1) writing rollback information
2) writing redo information
3) reading both table and index block into the buffer cache.

So, do the following:
1) Set all the indexes that you will not use for data access to "unusable"

   state.

2) Grow your undo tablespace for approximately 300MB 
3) Bring database no NOARCHIVELOG mode if you can.
4) Grow buffer pool to accomodate all those blocks from both indexes and

   (`300MB)
5) Byte the bullet.

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

From: bobmetelsky
To: oracle-l
Sent: 8/31/2005 6:12 PM
Subject: 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-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Aug 31 2005 - 20:12:05 CDT