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

Question

Consider a scenario where you have two databases: On database A you need to access data on database B.

You could create a view on database A that selects from the table on database B.

CREATE OR REPLACE VIEW Demographics.Employees(
employee_id, employee_dept
)
AS
    SELECT employee_id, employee_dept
    FROM Employee@DB_B;
/

Or, you could create a synonym on database A that points to the table on database B.

CREATE OR REPLACE SYNONYM Demographics.Employees FOR Employee@DB_B;

What are the advantages and disadvantage to using a view across the DB link versus a synonym across the DB link?

If the databases are within an Exadata platform, does that change the recommendation?

Answer 1

When you create a view over a database link, you're creating an object on the local database. This means you have dictionary information about the remote object, locally.

Whereas the synonym is just a pointer to the remote object. So all you have is the synonym definition.

create database link loopback
  using 'localhost/pdb1';

create table t (
  c1 int
);

create or replace synonym s
  for t@loopback;

create or replace view v as 
  select * from t@loopback;

select table_name, column_name, data_type
from   user_tab_cols
where  table_name in ( 'S', 'V' )
order  by table_name, column_name ;

TABLE_NAME   COLUMN_NAME   DATA_TYPE   
V            C1            NUMBER 

So using a view can be handy if you have tools that inspect the structure of the table (columns, data types, etc.).

On the flip side, because the view is a local object, changes to the remote table aren't automatically propagated:

alter table t 
  add ( c2 date ); 

info s

TABLE: T 
     LAST ANALYZED: 
     ROWS         : 
     SAMPLE SIZE  : 
     INMEMORY     :DISABLED 
     COMMENTS     : 

Columns 
NAME         DATA TYPE      NULL  DEFAULT    COMMENTS
 C1          NUMBER(38,0)   Yes              
 C2          DATE           Yes 

info v

Columns 
NAME         DATA TYPE      NULL  DEFAULT    COMMENTS
 C1          NUMBER(38,0)   Yes 

So you need to re-create the view to see changes. On the flip-flip side this can be an advantage if you don't want remote DDL changes to show up on the local database immediately.

Note that there's no dependency between the local view and the remote table. So breaking changes (such as dropping a column) don't invalidate the view. You'll only find this out when you query it:

alter table t
  drop ( c1 );

select status from user_objects
where  object_name = 'V';

STATUS   
VALID    

select * from v;

ORA-00904: "C1": invalid identifier

select * from s;

no rows selected

Answer 2

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.

Answer 3

Personally, I would favor the synonym. That's what synonyms are for, giving you a shorter syntax for accessing an object. A view is more for saving a particular query for later reuse. Now, if you wanted to restrict the available columns on the source table or something, that's when you'd use a view.