Re: question about long running query with membership condition "in (...)"

Mark wrote:

> We are running oracle 8.17 on sunos 5.8
>
> Can anyone please explain why when I run the following individual
> queries they run for 1 second, but when I combine them, it runs
> forever.
>
> INDIVIDUAL QUERIES:
>
> select SUM(bytes),
> tablespace_name
> from sys.dba_free_space where tablespace_name in ('PTA')
> group by tablespace_name;
>
> select SUM(bytes),
> tablespace_name
> from sys.dba_free_space where tablespace_name in ('ILIS')
> group by tablespace_name;
>
> COMBINED QUERY:
>
> select SUM(bytes),
> tablespace_name
> from sys.dba_free_space where tablespace_name in ('PTA' ,'ILIS')
> group by tablespace_name;
>
> Thank you very much.

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Feb 5 12:40:04 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> set timing on
SQL> select SUM(bytes),

   2       tablespace_name
   3      from sys.dba_free_space where tablespace_name in ('PTA' ,'ILIS')
   4      group by tablespace_name;

no rows selected

Elapsed: 00:00:00.03
SQL> You've got a problem. Are you absolutely sure you don't have a syntax error that you didn't copy to your post?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)