Hi all

I have a query hat does :

DELETE FROM TABLE_A WHERE ID NOT IN (SELECT ID FROM TABLE_B)

and it's taking ages to run (it's not finished yet after 20 minutes)


TABLE_A has 64,000 rows
TABLE_B has 13 rows

So I would assume Oracle would compare each of the 64,000 rows in TABLE_A,
to 13 values it stored in memory, and that should happen in a flash. I mean,
64k rows is nothing.

The plan does a full table scan on both tables, which is normal, and the
query is 95% CPU and this machine is normally pretty fast.

Can anyone comment ? I may be missing something obvious.

Thanks
Syltrem

On Jun 22, 2:06*pm, "Syltrem" <syltremz... (AT) videotron (DOT) ca> wrote:
Any particular version you are running on? Any clues in the wait
tables?

jg
--
@home.com is bogus.
https://twitter.com/#!/LarryEllison_

"joel garry" <joel-garry (AT) home (DOT) com> wrote

On Jun 22, 2:06 pm, "Syltrem" <syltremz... (AT) videotron (DOT) ca> wrote:
There was next to no wait, just CPU
Oracle 10.2.0.4

I rephrased it and it ran in a few seconds :

delete from TABLE_A A
where not exists (select 1
from TABLE_B B
where B.id = A.id )

Thanks for replying and have a good night, if night it is in your part of
the world !

Syltrem

Syltrem wrote:
Table A was locked.