SQL performs better ! , but why?

From: Sunil <sunil_franklin_at_hotmail.com>
Date: Wed, 24 Apr 2002 11:53:21 +0530
Message-ID: <Besx8.20$w43.140@news.oracle.com>

All,

    I had the following query which was taking ages to run :- <
select s.owner || '.' || s.synonym_name, from dba_objects o, dba_synonyms s
where s.owner in ('SYSTEM', 'PUBLIC')

and o.object_type in ('TABLE', 'VIEW')
and s.table_owner = o.owner
and s.table_name = o.object_name

>

    I changed it to the following and it was performing much better.  <
select s.owner || '.' || s.synonym_name
from dba_synonyms s
where exists ( select 'x' from dba_objects o

        where s.table_name = o.object_name
      and s.table_owner = o.owner
      and o.object_type in ('TABLE', 'VIEW')

    )
and s.owner in ('SYSTEM', 'PUBLIC')
 >

    Is this way of rewriting the query ok? and can some expert out there explain why the second query is performing much faster. ( I am running on Oracle 9i ).

Thanks,
Sunil. Received on Wed Apr 24 2002 - 01:23:21 CDT