What Are the Pros/Cons of Using a Synonym VS. a View

What are the pros/cons of using a synonym vs. a view?

They are different things. A synonym is an alias for the object directly, a view is a construct over one or more tables.

Some reasons to use a view:

• May need to filter, join or otherwise frig with the structure and semantics of the result set

• May need to provide legacy support for an underlying structure that has changed but has dependencies that you do not want to re-work.

• May provide security where some of the contents of the table should be visible to a class of users but not all. This could involve removing columns with sensitive information or filtering out a subset of the records.

• May wish to encapsulate some business logic in a form that is accessible to users for reporting purposes.

• You may wish to unify data from more than one source.

... Plus many more.

Reasons to use a synonym:

• You may wish to alias an object in another database, where you can't (or don't want to) hard code the reference to the specific database.

• You may wish to redirect to a source that changes over time, such as an archive table.

• You want to alias something in a way that does not affect the query optimiser.

... Plus many more.

Difference between view and synonym in Oracle

Table is a basic unit of data storage in an oracle database. It holds all user accessible data.

View is a virtual table

• It can be created on a table or another view.
• It is just like a window through which we can access or change base table data.
• It does not contain data of its own. It always takes data from its base table.
• It is stored as a query in data dictionary. Whenever you query a view it gets data from its based table using this query.

Main advantage of using views are

• You can restrict access to predetermined set of rows and columns of a table
• You can hide complexity of query
• You can hide complexity of calculation

Synonym is alternate name given to table, view, sequence or program unit.

• It is used to mask real name and owner of the object.
• You can provide public access to tables by creating public synonyms.

Reference : here

Other already answered similar questions and references.

• https://stackoverflow.com/a/869124/3492139
• https://in.answers.yahoo.com/question/index?qid=1006020105719

When establishing access to data on a remote database, are views or synonyms preferred?

Use the synonym for simplicity.

The view which you have defined is an unnecessary complication if the local database should be seeing all the rows and columns from the remote database. It adds complexity because there might be some additional logic which exists in the view. Someone seeking to understand the system will need to spend time to examine the view definition and understand it.

Perhaps the view makes things more complicated for the Oracle optimiser.

There are some valid reasons for having a view:

• You want to restrict the columns which are visible
• If new columns are added to the remote table, you do not want them to show up locally
• You want to restrict the rows which are visible, join to other tables, etc.
• You want the view to be invalid if changes are made at the remote site (such as dropping a column) rather than finding out at application runtime

The time for creating the view is when these conditions exist. Even then, I would make the view select from the synonym.

Alternative design for a synonyms table?

After reading the answers, another approach came to my mind. It's using a single column table with all synonym words, each wrapped in word boundary marker. With that i mean something like

|in particular|particularly|specifically|

Then I'd query the table with

SELECT * FROM `synonyms` WHERE `word` LIKE '%|$word|%'

And in code I trash the preceding and trailing |s and do an explode and have the synonyms:

$synonyms = $row['word'];
$synonyms = explode('|', substr($synonyms, 1, -1));
unset($synonyms[$word]);

Have view object number in oracle?

Views and synonyms are objects, so they have an object number, but a view does not consume physical space in the database, so it does not have a segment

SQL> create or replace view V as select * from dual;

View created.

SQL> create synonym SYN for V;

Synonym created.

SQL> select object_id, object_name
  2  from user_objects
  3  where object_name in ('V','SYN');

 OBJECT_ID OBJECT_NAME
---------- ----------------------------------------
     73166 SYN
     73165 V

SQL>
SQL> select * from user_segments
  2  where segment_name = 'V';

no rows selected

What's the best way to change hard coded database references in view definitions (SQL Server 2008 R2)?

You might want to consider using SQL Synonyms for the purpose of referencing tables in another database rather than your current view strategy. See this question for a discussion of the pros and cons. This won't solve your current problem, but you may find you get better performance with synonyms. What you really want is a SQL Synonym that references a database rather than a specific table, and you are not alone.

My preferred method to handle this problem would be to script all of your views using SQL Sever Management Studio:

1. Right-click the database and select Generate Scripts... from the
   Tasks menu
2. On the Choose Objects page, choose Select specific database objects and click the Views checkbox to select all views.
3. On the Set Scripting Options page, click the Advanced button and select Script DROP and CREATE as an option.
4. Modify the resulting script by searching and replacing the database name. Include the brackets and the dot (.) to avoid accidentally renaming other objects, e.g. replace '[ABCMain].' with '[XYZMain].'.

This isn't very elegant, but it gets the job done fairly quickly. Hopefully Microsoft will be adding database synonyms at some point.

---

Related Topics