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

markg22003_at_yahoo.com (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.

Try using EXPLAIN PLAN and see what differs in the Execution plan.

The Parser will implicitly convert the IN to ( tablespace_name = 'PTA'
OR
 tablespace_name = 'ILIS')

an OR with 2 values may be may be less efficient 1 equate..?? Received on Thu Feb 05 2004 - 12:45:14 CST