Re: need help tuning a very large delete

<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
>
>
>

doesnt exist always force a full table scan on the sub-query? Received on Wed Sep 17 2003 - 16:35:22 CDT