OraFAQ Forum: Performance Tuning » Pleaese clear my doubt about View and synonym having same name

Home » RDBMS Server » Performance Tuning » Pleaese clear my doubt about View and synonym having same name (UNIX 11.35)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Pleaese clear my doubt about View and synonym having same name [message #671974]

Wed, 26 September 2018 09:29

Akmmhto
Messages: 38
Registered: September 2018

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

Report message to a moderator

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

John Watson
Messages: 8805
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.

Report message to a moderator

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

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

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

cookiemonster
Messages: 13904
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.

Report message to a moderator

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

cookiemonster
Messages: 13904
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.

Report message to a moderator

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

cookiemonster
Messages: 13904
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.

Report message to a moderator

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

Akmmhto
Messages: 38
Registered: September 2018

Member

Thank a lot , You all helped me to clear my confusion...

Report message to a moderator

Previous Topic:	Index on order by column
Next Topic:	Transaction Isolation Level

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 30 21:08:33 CDT 2023