Re: Slow query, can't see why
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Sun, 24 Jun 2012 18:58:03 +0200
Message-ID: <4fe746fb$0$3108$ba620e4c_at_news.skynet.be>
Syltrem 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: Sun, 24 Jun 2012 18:58:03 +0200
Message-ID: <4fe746fb$0$3108$ba620e4c_at_news.skynet.be>
Syltrem 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
>
>
Table A was locked. Received on Sun Jun 24 2012 - 11:58:03 CDT