Home » RDBMS Server » Performance Tuning » Pleaese clear my doubt about View and synonym having same name (UNIX 11.35)
Pleaese clear my doubt about View and synonym having same name [message #671974] Wed, 26 September 2018 09:29 Go to next message
Akmmhto
Messages: 25
Registered: September 2018
Junior Member
Suppose a db has view and synonym having same name,
Say the name is "ABC".
If I query:
Select * from ABC;

Then from where db will extract data...from view or synonym.
Is there any chances performance issue...

Thanks in advance
Re: Pleaese clear my doubt about View and synonym having same name [message #671977 is a reply to message #671974] Wed, 26 September 2018 09:49 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
You need to think about namespaces and precedence. Within a schema, views and synonyms share ther same namespace so the situation you describe is impossible:
pdby1> create view v1 as select * from emp;

View created.

pdby1> create synonym v1 for dept;
create synonym v1 for dept
               *
ERROR at line 1:
ORA-00955: name is already used by an existing object


pdby1>
However, public synonyms live in their own namespace so I can do this:
pdby1> create public synonym v1 for dept;

Synonym created.

pdby1>
so now, test what happens when you run
select * from v1;
and your question will be answered.

Re: Pleaese clear my doubt about View and synonym having same name [message #671980 is a reply to message #671977] Wed, 26 September 2018 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Very good demo. /forum/fa/2115/0/

Re: Pleaese clear my doubt about View and synonym having same name [message #672007 is a reply to message #671974] Thu, 27 September 2018 09:42 Go to previous messageGo to next message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
Akmmhto wrote on Wed, 26 September 2018 15:29
Suppose a db has view and synonym having same name,
Say the name is "ABC".
If I query:
Select * from ABC;

Then from where db will extract data...from view or synonym.
Is there any chances performance issue...

Thanks in advance
Synonyms don't cause performance issues. They may cause bugs if they are pointing to a different object than the one you think they're pointing at.
Re: Pleaese clear my doubt about View and synonym having same name [message #672008 is a reply to message #671977] Thu, 27 September 2018 09:43 Go to previous messageGo to next message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
And of course you can have a view in one schema and a private synonym of the same name in a different schema that points to the view.
Re: Pleaese clear my doubt about View and synonym having same name [message #672009 is a reply to message #672008] Thu, 27 September 2018 09:45 Go to previous messageGo to next message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you run
select * from name

then oracle will check to see if there is a table, view or synonym in the current schema and if so query that object (or object the synonym points to).

If it can't find a match then it'll look for a public synonym with the name and use that.

In a given schema you can't have a view or table or synonym with the same name.
Re: Pleaese clear my doubt about View and synonym having same name [message #672037 is a reply to message #672009] Fri, 28 September 2018 12:07 Go to previous message
Akmmhto
Messages: 25
Registered: September 2018
Junior Member
Thank a lot , You all helped me to clear my confusion...
Previous Topic: Index on order by column
Next Topic: Transaction Isolation Level
Goto Forum:
  


Current Time: Fri Dec 06 10:37:48 CST 2019