Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: slow delete sql

Re: slow delete sql

From: MariuszR <rustek2_at_poczta.onet.pl>
Date: Tue, 6 Apr 2004 17:46:13 +0200
Message-ID: <c4ujdr$1q19$1@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 - 10:46:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US