Re: slow delete sql

> 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 - 10:46:13 CDT