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[...]

Hugo Toledo, Jr. (hugo_at_mcs.net) responded:
: 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.

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

We've found some maintenance issues involved, mainly with private synonyms. For instance, a public synonym exists to table BLAH, but one user needs to point to BLAH_VIEW but with the same name (due to restrictions of a query tool), so needs a private synonym. Who creates it? Who tracks who has been given which synonyms? Does the user already have that private synonym that they created themselves? Did they delete it by mistake?

I'm no DBA, and I'm sure there are sound approaches to each of the questions and issues raised above. But as of now, before we've been given the time to wrestle with them, they are points that may trip you up.

En paz,
Steve

--
Stephen W. Thompson, U. of PA, Data Administration, 215-898-1236
thompson_at_isc.upenn.edu -OR- (thru May) st_at_eniac.seas.upenn.edu
URL: http://pobox.upenn.edu/~thompson