Query optimization puzzle.

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'
	);

       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 Received on Fri Mar 04 1994 - 01:09:20 CET