Solved

Difference between Synonym and View

Posted on 2002-06-05
5
1,286 Views
Last Modified: 2012-06-22
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.
 
0
Comment
Question by:send2sachin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Assisted Solution

by:dbaora
dbaora earned 25 total points
ID: 7058520
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
 

Author Comment

by:send2sachin
ID: 7060984
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
 
LVL 4

Accepted Solution

by:
asimkovsky earned 25 total points
ID: 7062181
** 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
 

Expert Comment

by:modulo
ID: 7429526
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
 
LVL 5

Expert Comment

by:jpkemp
ID: 9025074
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question