Re: slow delete sql

Hey thanks Mariusz. It does run much faster using this sql.

columnX+columnY aren't the primary keys, but instead of a filter on both tables it does now a hash join. Really great. I would never thought of doing it this way. Thanks, mate!

"MariuszR" <rustek2_at_poczta.onet.pl> wrote in message news:<c4ujdr$1q19$1_at_news2.ipartners.pl>...
> > I have a delete sql that runs too slow (around 15 mins). I would like
> > to improve it.
> >
> > It looks like this:
> >
> > delete from table_a a
> > where exists (select *
> > from table_b b
> > where b.columnX = a.columnX
> > and b.columnY = a.columnY)
> >
> > There are two indexes. IndexA on table_a(columnX,columnY) and IndexB
> > on table_b(columnX,columnY).
>
>
> I suppose that columnX+columnY makes the primary key in both tables.
> Try this:
>
> delete from table_a where (columnX,columnY) in (
> select a.columnX, a.columnY from table_a a, table_b b where a.columnX =
> b.columnX and a.columnY = b.columnY
> )
>
> You should have full scan on one table only.
>
> Greetings
> Mariusz
Received on Tue Apr 06 2004 - 22:46:59 CDT