Solved

Difference between Synonym and View

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

Suggested Solutions

Title # Comments Views Activity
What is the version of ojdbc6.jar 2 60
PAYER_ID has both atributes 4 39
SQL query question 8 78
capture vmstat info and insert it into an oracle table 31 20
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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

809 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