Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL performs better ! , but why?
This is a case of the Oracle optimizer choosing a poor access path.
You'll have to examine the explain plan to see that (sorry, I don't have
the time to walk through them right now).
I believe a better solution would be a hint...
select /*+ ordered */ 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
Richard
Sunil wrote:
>
> 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 - 12:28:26 CDT