This discussion is archived
1 Reply Latest reply: Sep 23, 2012 10:37 PM by kgronau RSS

Views versus Synonyms in HS environment

tx103108 Newbie
Currently Being Moderated
OS: Red Hat Linux 5.4 64-bit
DB: Oracle Ent Ed 11gR1 64-bit
Gateway: Oracle Gateway for ODBC 64-bit
Database and gateway reside on same Linux Server.
Connecting to remote Sybase Anywhere 10 server on WindowsXP.
Using FreeTDS odbc driver and unixODBC driver manager.
===================================

In a HS env, does creating views in Oracle that use the dblink to the Sybase database degrade query performance? Would synonyms be better or worse or the same? Or is it best to just run the query using the dblink?

Viiew
-------
create view mytable as select * from sybasetable@dblink;
select * from mytable;

Synonym
------------
create synonym mytable for sybasetable@dblink;
select * from mytable;

Thank u.
  • 1. Re: Views versus Synonyms in HS environment
    kgronau Guru
    Currently Being Moderated
    I wasn't able to figure out a performance difference between using a view, a synonym or the link directly.

    Using a view or synonym is more comfortable then uisig the link directly as you do not always have to specify "@<db link>" syntax to the remote object. It also allows you to hide the real source of the data to the end user.

    But please keep in mind there's one big difference between using a view and a synonym. When you use a view, the view is created in Oracle using the current column names and data types that are present at the remote database right at the time when you create the view. A synonym is more dynamic and it checks out the source table definition all the time it is being called.

    Let me give you here an example to demonstrate:
    Foreign database table:
    create table klaus (col1 numeric);


    When I now use the gateway to describe the table I get:
    SQL> desc "klaus"@MYSQL_DG4ODBC_EMGTW_1123_DB
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    col1 NUMBER(10)

    So let's create the view and synonym:

    SQL> create synonym klaus_syn for "klaus"@MYSQL_DG4ODBC_EMGTW_1123_DB;

    Synonym created.

    SQL> create view klaus_view as select * from "klaus"@MYSQL_DG4ODBC_EMGTW_1123_DB;

    View created.

    SQL> desc klaus_syn
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    col1 NUMBER(10)

    SQL> desc klaus_view
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    col1 NUMBER(10)


    As you can see all 3 ways currently show the correct information. So let's now change the source table using a char(20) instead of a numeric data type:
    SQL> desc "klaus"@MYSQL_DG4ODBC_EMGTW_1123_DB
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    col1 CHAR(20 CHAR)

    SQL> desc klaus_syn
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    col1 CHAR(20 CHAR)

    SQL> desc klaus_view
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    col1 NUMBER(10)


    You see, the view shows the wrong data type as during a create view the data type and the amount of columns is stored into the Oracle database. So using views might always cause an impact when the source model is changed or when you switch the Oracle database from an 8 bit character set to a unicode character set.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points