Solved

Difference between Synonym and View

Posted on 2002-06-05
5
1,277 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

791 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