slow delete sql

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)

any suggestions?

Thanks Received on Tue Apr 06 2004 - 07:36:17 CDT