Re: Slow query, can't see why

"joel garry" <joel-garry_at_home.com> wrote in message news:f2e03843-5295-4f39-8d34-a297834fad08_at_u1g2000pbb.googlegroups.com... On Jun 22, 2:06 pm, "Syltrem" <syltremz..._at_videotron.ca> wrote:
> 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
>
> Any particular version you are running on? Any clues in the wait
> tables?
>
> jg
> --
> _at_home.com is bogus.
> https://twitter.com/#!/LarryEllison_
>

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 Received on Fri Jun 22 2012 - 20:51:03 CDT