Database Reference
In-Depth Information
INSERT INTO table (ID NUMBER) VALUES (sequence.NEXTVAL)
RETURNING ID INTO vID;
This brings us to the second topic of discussion for this chapter, synonyms.
22.2
Synonyms
A synonym can effectively provide an alias to any object in any schema in
the database, assuming that the user has privileges to view the underlying
objects. It makes an object appear as if you own it, because you do not have
to use a schema prefix when querying or performing other tasks with the
object. Synonyms can provide the following benefits:
Transparency
. A synonym masks the name of the schema owning the
object. The object can even be in a remote database when you include
a database link in the definition of the synonym. A database link is a
direct gateway from one database to another database.
Simplified SQL code
. Code is simplified because schema names do
not have to be included for table accesses where objects are in differ-
ent schemas.
Easy Changes
. Moving objects to different schemas or databases in
distributed environments does not require application changes
because only synonyms need to be changed.
There is one potential problem with using too many synonyms: A table
is a logical overlay on top of physical data on disk. A synonym is another
overlay onto a table, a logical overlay overlaying a logical overlay (the table).
Why can this be a problem? Let's put it into perspective using a large-scale
environment. Assume that you run a database for an online bookstore.
Every time a user accesses a table, the application code accesses the table
through the synonym. If there are thousands of concurrent users, then both
the synonym and table are accessed thousands of times per second. That
makes for double the number of system queries into database metadata.
Large-scale systems could have problems as a result. No amount of buffer
tuning and physical organization will solve an issue such as this one. The
same concept applies to views (see Chapter 19). Always be aware of poten-
tially sacrificing database performance for the sake of ease and neatness of
application coding.
1
In large OLTP and data warehouse environments, this
