Re: slow delete sql

arjan wrote:
> Hi there,
>
> 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).
>
> Looking at the statement in explain plan, it shows that it's doing
> full scans on both tables. It's not picking up an index hint like
> this:
>
> delete /*+ INDEX(a IndexA) */ from table_a a
> where exists (select *
> from table_b b
> where b.columnX = a.columnX
> and b.columnY = a.columnY)
>

Stop throwing darts in the dark.
Get facts before proceeding.
Otherwise all you are doing is....

Ready, Fire, Aim!

ALTER SESSION SET SQL_TRACE=TRUE
DELETE.... then go run TKPROF & actually see where Oracle is spending all the time. Received on Tue Apr 06 2004 - 19:33:00 CDT