Re: need help tuning a very large delete

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