"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<WN4ab.76$DV7.16_at_news02.roc.ny>...
> <Kenneth Koenraadt> wrote in message news:3f68c498.3658049_at_news.inet.tele.dk...
> > On 17 Sep 2003 12:45:59 -0700, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:
> >
> > >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?
> >
> > Hi,
> >
> > I suppose you mean "stage.Delete_column = 'Y' instead of stage.pk ='Y'
> > above.
> >
> > Try
> >
> > delete from master a
> > where exists (select 1
> > from stage b
> > where a.pk = b.pk
> > and b.Delete_column = 'Y');
> >
> > Depending on the actual number or rows deleted and the size (row
> > width) of master table, the transcation may become very large, be sure
> > to have enough rollback space.
> >
> > Indexes on STAGE.PK and MASTER.PK would help a lot.
> >
> > Depending on the different column value distribution in STAGE you
> > might also consider a bitmax index on STAGE.DELETE_COLUMN.
> >
> > - Kenneth Koenraadt
> >
>
> Adding to above (since I believe that above solution will be very slow):
> Firstly, Try not deleting, if you are deleting so many rows. Instead do a:
>
> create table master_new
> as
> select m.* from master m, stage s
> where m.pk = s.pk(+)
> and s.delete_column = 'Y'(+)
> and s.pk is null
> /
>
> followed by (creating the appropriate indexes on the master_new .. using parallel option ... and big sort_area_size)
> and then
> rename master to master_old;
> rename master_new to master;
> ... recompile invalid objects // create triggers etc....
>
> OR if you have to delete, see if this is faster:
>
> delete from master m
> where m.pk in
> (select m.pk from master
> minus
> select s.pk from stage where s.delete_column = 'Y')
> /
>
> OR you could try a delete on a query:
>
> OR truncate table master and do an insert select..
>
> :) Many ways to skin this cat. Test before implementing ..
>
> Anurag
performance of minus is highly dependent on sort area size right?
Received on Thu Sep 18 2003 - 06:35:34 CDT
