OraFAQ Forum: SQL & PL/SQL » Difference between Synonym and View

Home » SQL & PL/SQL » SQL & PL/SQL » Difference between Synonym and View

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

Difference between Synonym and View [message #20582]

Wed, 05 June 2002 20:37

Sachin
Messages: 52
Registered: May 2001

Member

I've created a View X as follows

CREATE VIEW X AS SELECT * FROM EMP;

as well i've created a SYNONYM on Table EMP

What are the difference between the two in terms of operations that can/cannot be performed, performance, internal representation etc.

Report message to a moderator

Re: Difference between Synonym and View [message #20590 is a reply to message #20582]

Wed, 05 June 2002 22:26

Maaher
Messages: 7065
Registered: December 2001

Senior Member

A synonym is a pointer to the physical database object. usually synonyms are created for other users objects:
user scott has table EMP
user willy has a grant on the table SCOTT.EMP. He'll have to issue any access to the table as follows:
SELECT .... FROM SCOTT.EMP.
If Willy creates a synonym EMP for SCOTT.EMP, he can issue the same command as follows:
SELECT .... FROM EMP.

A view is a stored SELECT, which is not the same. using your example view x (select * from emp), the following selects are similar (though complete senseless, but you'll get the point)
SELECT empno FROM x
SELECT empno FROM ( SELECT * FROM emp)

MHE

Report message to a moderator

Re: Difference between Synonym and View [message #20592 is a reply to message #20582]

Thu, 06 June 2002 02:46

shyampaliyath
Messages: 22
Registered: May 2002

Junior Member

hai sachin,

Synonym is representation of a table. it has two subdivision namely private and public. public sysnonym can be created only from the DBA user which can be accessed by all the user but private synonym cannot be accessed by all the user

Major difference between the synonym and view is that any sysnonym can represent only one table and cannot represent a result of a subquery but the view can represent even a subquery.

with regards
shyam.

Report message to a moderator

Re: Difference between Synonym and View [message #20611 is a reply to message #20582]

Thu, 06 June 2002 19:35

Sachin
Messages: 52
Registered: May 2001

Member

I'll repeat my question one again ....

what exactly i am expecting is consider the following two SQL statements

1: CREATE VIEW X AS SELECT * FROM EMP;
2: CREATE SYNONYM Y FOR EMP;

now can anyone tell me what all operations can be performed on X which can't be performed on Y and Vice versa ? How are X and Y represented by oracle internally ?

Report message to a moderator

Previous Topic:	DB table creation errors
Next Topic:	How to calculate year, month & days between to date's

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 16:39:23 CDT 2024