RE: advice for massive delete querry
- From: "Gogala, Mladen" <MGogala@xxxxxxxxxxxxxxxxxxxx>
- To: 'bobmetelsky ' <bobmetelsky@xxxxxxxxxxx>, 'oracle-l ' <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 31 Aug 2005 21:06:08 -0400
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
table
(`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."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
