Use synonym chains

To continue the preceding example, suppose that a new computer is added to your network. Its name is db_crunch. The customer table and other tables are moved to it to reduce the load on avignon. You can reproduce the table on the new database server easily enough, but how can you redirect all access to it? One way is to install a synonym to replace the old table, as the following example shows:

DATABASE stores_demo@avignon EXCLUSIVE;
RENAME TABLE customer TO old_cust;
CREATE SYNONYM customer FOR stores_demo@db_crunch:customer;
CLOSE DATABASE;

When you execute a query within stores_demo@avignon, a reference to table customer finds the synonym and is redirected to the version on the new computer. Such redirection also happens for queries that are executed from database server nantes in the previous example. The synonym in the database stores_demo@nantes still redirects references to customer to database stores_demo@avignon; the new synonym there sends the query to database stores_demo@db_crunch.

Chains of synonyms can be useful when, as in this example, you want to redirect all access to a table in one operation. However, you should update the databases of all users as soon as possible so their synonyms point directly to the table. If you do not, you incur extra overhead when the database server handles the extra synonyms, and the table cannot be found if any computer in the chain is down.

You can run an application against a local database and later run the same application against a database on another computer. The program runs equally well in either case (although it can run more slowly on the network database). If the data model is the same, a program cannot tell the difference between one database and another.