Re: Query optimization puzzle.

From: L. Carl Pedersen <l.carl.pedersen_at_dartmouth.edu>
Date: Thu, 10 Mar 1994 13:49:55 -0500
Message-ID: <l.carl.pedersen-100394134955_at_kip-sn-341.dartmouth.edu>


In article <2l5u7g$b88_at_pandora.sdsu.edu>, gtupper_at_monkfish.nosc.mil (Greg Tupper) wrote:

>
> I am having a problem with a query with a where clause.
>
>
> There is an index on filename on tableA and tableB.
>
> The following query take 5 to 6 minutes and does
> not seem to be doing disk access:
>
> select count(*) from tableA
> where filename = 'wally folder'
> and (col1,col2) in (
> select col1, col2 from tableB where filename = 'wally folder'
> );
> COUNT(*)
> ----------
> 364
>
>
> select count(*) from tableA where filename='wally folder';
>
> COUNT(*)
> ----------
> 1625
> The count is returned in less than 1 second.
>
>
> select count(*) from tableB where filename='wally folder';
>
> COUNT(*)
> ----------
> 1501
> The count is also returned in less than 1 second.
>
>
>
> select count(*) from tableA
> where (col1, col2) in (
> select col1, col2 from tableB
> where filename = 'wally folder'
> );
>
> COUNT(*)
> ----------
> 1015
>
> will return in 5 seconds.
>
> Why would the query at the top take so long?
> There is only one more condition on
> the query than the one just above that returns in five seconds,
> and there is an index built on filename.
>
> Any hints would be appreciated.
>
>
> Greg Tupper
> SAIC Comsystems
> gtupper_at_nosc.mil

Well, suppose it was doing the sub-query 1625 times, taking "less than 1 second" each? Would that explain it?

IMHO, the ORACLE optimzer is not always very bright about how it does these queries.

You can probably get it to go a lot faster by any of a number of methods. Try changing you query into a join between the two tables, for example. Try indexing (col1,col2). Use the EXPLAIN command to see what it's actually doing. Received on Thu Mar 10 1994 - 19:49:55 CET

Original text of this message