Difference between Synonym and View

I've created a View X as follows :

CREATE VIEW X AS SELECT * FROM EMP;

as well i've created a SYNONYM Y on Table EMP

What are the difference between the two(X & Y) in terms of operations that can/cannot be performed, performance, internal representation etc.
 
send2sachinAsked:
Who is Participating?
 
asimkovskyConnect With a Mentor Commented:
** How are X and Y represented by oracle internally ?

This question was answered by dbaora.



**what all operations can be performed on X which can't be performed on Y and Vice versa ?

A view is just a stored query.  So any user with the appropriate privileges can query, insert, update, delete the data.  The synonym is a pointer to the base table itslef.  So any user with the appropriate privileges can query, insert, update, delete the data, and also perform DDL on the base table, such as adding/dropping columns. Of course, all that can be controlled by properly managing privleges anyway.

The one you choose to use will depend on the requirements of your system.  Perhaps you can expand on that a little bit and somebody can give you an answer closer to what you're looking for?


Andrew

0
 
dbaoraConnect With a Mentor Commented:
Hi send2sachin,
a view is a look on a table's data with restrictions or specific data - a subset of the table's data.
a synonym is a way to share any object from your
scheme with other schemes.
a synonym is created to avoid using the user.object standard while calling to other scheme objects.
any privilege you have on the object, will affect the synonymm if you grant select only - no insert or update is allowed using synonym (a synonym is a pointer to an object).

example:
say you want to share scott's emp table, you simply grant select on the table to other users, than those users can relate to the emp table using SCOTT.EMP. to avoid that simply create a public synonym called emp for SCOTT.EMP.
now every user that will select from EMP will get data from SCOTT.EMP.

regards,
dbaora.
0
 
send2sachinAuthor Commented:
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 ?
0
 
moduloCommented:
Dear: dbaora

I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See:        http://www.experts-exchange.com/jsp/communityNews.jsp
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

Thanks !

modulo

Community Support Moderator
Experts Exchange
0
 
jpkempCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Split between dbaora & asimkovsky
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer
0
All Courses

From novice to tech pro — start learning today.