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
>

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 Received on Wed Sep 17 2003 - 16:58:14 CDT