Home » SQL & PL/SQL » SQL & PL/SQL » Difference between Synonym and View
Difference between Synonym and View [message #20582] Wed, 05 June 2002 20:37 Go to next message
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.
Re: Difference between Synonym and View [message #20590 is a reply to message #20582] Wed, 05 June 2002 22:26 Go to previous messageGo to next message
Maaher
Messages: 7061
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
Re: Difference between Synonym and View [message #20592 is a reply to message #20582] Thu, 06 June 2002 02:46 Go to previous messageGo to next message
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.
Re: Difference between Synonym and View [message #20611 is a reply to message #20582] Thu, 06 June 2002 19:35 Go to previous message
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 ?
Previous Topic: DB table creation errors
Next Topic: How to calculate year, month & days between to date's
Goto Forum:
  


Current Time: Fri Dec 14 12:58:54 CST 2018