Re: Slow query, can't see why
From: joel garry <joel-garry_at_home.com>
Date: Fri, 22 Jun 2012 16:33:01 -0700 (PDT)
Message-ID: <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
Date: Fri, 22 Jun 2012 16:33:01 -0700 (PDT)
Message-ID: <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_Received on Fri Jun 22 2012 - 18:33:01 CDT