| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Synonyms Urgent!!!!!!
A copy of this was sent to pbhanu_at_yahoo.com
(if that email address didn't require changing)
On Wed, 30 Dec 1998 22:41:07 GMT, you wrote:
>Hello All,
>
>I have the following requirement :-
>I need to have a list as of all synonyms as follows:
>
>synonym_name ,table_owner , table_name , object_type
>
>basically the object type is from all_objects and my requirement is to find
>all synonyms and their base object types whether the synonym is for a table ,
>view or for a synonym itself etc .
>
>I am using the following query , but this seems to be very slow and i dont
>think this is accurate too
>
>select synonym_name ,table_owner , table_name , object_type
>from all_synonyms a , all_objects b
>where a.table_name = b.object_name(+)
>and table_owner = b.owner(+)
>order by synonym_name , table_name , object_type;
>
well, the query is missing one thing to make it 'correct'. The above query will return >1 row for some synonyms since (owner,object_name) is not unique in all_objects -- for example, a package OWNER.PACKAGE_NAME is in there 2 times. Another example, I can have a table named X and a trigger named X, therefore OWNER.X can be in there twice. What you need to add is a filter to get object types for things synonyms can point to, for example, adding:
and nvl(object_type,'Bad Link') in
( 'Bad Link' , 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SNAPSHOT', 'SYNONYM' ) to the where clause. As to the performance, I ran the following set of queries. The first two are equivalent and return all synonyms defined and what they point to (returns Bad Link if the synonym points to a non-existent object which is valid and happens)
select synonym_name ,table_owner , table_name , nvl(object_type, 'Bad Link')
from all_synonyms a , all_objects b
where a.table_name = b.object_name(+)
and table_owner = b.owner(+)
and nvl(object_type,'Bad Link') in
( 'Bad Link' , 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION',
'PACKAGE', 'SNAPSHOT', 'SYNONYM' )
order by synonym_name , table_name , object_type
/
select /*+ FIRST_ROWS */ synonym_name ,table_owner , table_name ,
nvl(object_type, 'Bad Link')
from all_synonyms a , all_objects b
where a.table_name = b.object_name(+)
and table_owner = b.owner(+)
and nvl(object_type,'Bad Link') in
( 'Bad Link' , 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION',
'PACKAGE', 'SNAPSHOT', 'SYNONYM' )
order by synonym_name , table_name , object_type
/
I have a large data dictionary (relatively large) and about 1,100 synonyms. The first query, without the hint, ran in 11 seconds. The second query typically took 5 seconds to run and starts returning data faster to the application as well. Don't analyze the underlying tables of these views -- the hint just makes the optimize choose lots more indexes to get data.
The last query I ran is the fastest but it only returns information about synonyms that point to existent objects (no bad links). If this is ok with you, it will be the best performer:
select synonym_name ,table_owner , table_name , object_type
from all_synonyms a , all_objects b
where a.table_name = b.object_name
and a.table_owner = b.owner
and object_type in ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION',
'PACKAGE', 'SNAPSHOT', 'SYNONYM' )
order by synonym_name , table_name , object_type
/
If you don't care about synonyms that don't point to anything useful, this will be the better query.
>
>Any help in this would be appreciated..
>
>Thanks
>
>Bhanu
>pbhanu_at_yahoo.com
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Dec 31 1998 - 08:03:44 CST
 |
 |