Slow query, can't see why

[Syltrem]

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

[joel garry]

Any particular version you are running on? Any clues in the wait
tables?

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

[Syltrem]

"joel garry" <joel-...@home.com> wrote in message
news:f2e03843-5295-4f39...@u1g2000pbb.googlegroups.com...

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

[Gerard H. Pille]

Table A was locked.