Re: need help tuning a very large delete

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0309181126.58fa6ce_at_posting.google.com>...
> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3F698937.608F_at_yahoo.com>...
> > 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?
> >
> > Standard stuff: disable indexes, triggers etc
> >
> > Other options: Generate some rowid ranges and run multiple deletes in
> > parallel
> >
> > hth
> > connor
>
> the slowdown is the sort. its huge.

How many values does delete_column have? It is easy to see skewed data on the concerned columns. If you create a histogram on that column it may reduce full table scans.
Careful consideration as to which table is your driving/driven table would most probably reduce the amount of sorting

Michael Tubuo Ngong Received on Fri Sep 19 2003 - 08:07:21 CDT