Re: need help tuning a very large delete

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:<3f69a50b$1$13416$afc38c87_at_news.optusnet.com.au>...
> "Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0309171145.77216dac_at_posting.google.com...
> > Stage Table has 17 million records with Delete_column = 'Y';
> >
> > MAster has 27 million records.
> >
> > so I need to
> > delete from master
> > where master.pk = stage.pk
> > and stage.pk = 'Y';
> >
> > i tried writing a 'create table as' with a 'not exists' and was
> > running for 8 hours. killed it. didnt even go to 'killed' status which
> > means the DML hadnt even started it.
> >
> > I tried doing the following: but cant get the outer join right. Is
> > that faster than a minus?
> >
> > any ideas?
>
> insert into temp dump Select from master
> where it does NOT match stage,
> then
> truncate master,
> load back into master from temp dump?
>
> If you can afford the period where master is not available.

we tried things similiar for this and killed it after about 18 hours and the scary part is it died immediately. no rollback. We have issues sizing up sort_area_size because we have 8 instances on this server(not my call.. we just use these instances for staging so most of the day they are idle).

I jacked it up to 500MB and did a create table as and it hit the out of memory error probably about 10-15 minutes before completion.

Im just gonna turn autoextend on my temp tablespace and do a

create table as new
select cols
from tabl1
minus
select cols
from tab2
where flag = 'delete'

its gonna run all night. Tired of messing around. Ill do it in parallel. but the big slow down is the sorting to temp. Its evil.

4.2 GB table minus a 2.5GB table then a create table as of a 1.7GB table. with just 83MB of sort_area_size. if i could jack up the sort_area_size to 1GB we would be fine. Received on Thu Sep 18 2003 - 14:25:45 CDT