advice for massive delete querry
- From: bobmetelsky <bobmetelsky@xxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 31 Aug 2005 18:12:40 -0400
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:
