Re: Synonym or Not Synonym

Mason Liu wrote:
>
> What's the pros and cons of using Synonym? Any performance impact since a
> synonym may require two table look up? If not using sysnonyms, the table
> owner schema must be always attached as a table qualifier, that can be
> inconvenient and inflexible for multiple sites.
>
> What's your opinion. Thanks.
>
> Mason

Synonyms in conjunction with database links are required in order to achieve the location transparency requirements of true distributed database management systems as outlined in C.J.Date's 12 Rules for Distributed Database Systems.

The benefits of using synonyms far outweigh the minor latency in resolving them, especially after the initial resolution from either SQL*Net and/or Oracle Names. You can relocate resources and employ multiple listeners and provide single-named access to replicated tables using synonyms/links. Otherwise, the requirement that the schema be provided along with an embedded link, ala SCOTT.EMP_at_SYS1, prevents your easily relocating the resource.

Additionally, private synonyms can be used to ease the use of multiple versions of resources such as development and production views.

Hope this helps.

-- 
Hugo Toledo, Jr.   Author of "Oracle Networking" from Oracle Press
312.951.8012               Oracle WebSystem - Oracle Mobile Agents
mailto:hugo_at_mcs.com      Oracle Power Objects - Windows - Unix - C
http://www.mcs.com/~hugo/oracle.html        CompuServe: 72700,1705