Dropping Synonyms

Oracle provides a statement for eliminating a synonym—the DROP SYNONYM statement.

Its syntax is as follows:

DROP [PUBLIC] SYNONYM synonym-name;

The variable is defined as follows:

synonym-name is the name of the existing synonym that you want to eliminate.

Hiding Table Ownership with Public Synonyms

Consider the following example. You've developed a project accounting application at your company. You now must support two groups of users: those users who are running version 1.0 and those running version 2.0. However, version 2.0 requires some database changes; the database used by version 1.0 cannot be used by version 2.0. The tables used by both versions can be stored in the same Oracle database, provided that they are owned by separate Oracle accounts.

Suppose that the version 1.0 tables are owned by an Oracle account named PAV10. The version 2.0 tables are owned by another Oracle account named PAV20. If you wanted to support a group of software testers who need to switch back and forth between the two versions, you could construct two SQL*Plus scripts. The first script drops the existing synonyms and creates synonyms that point to the version 1.0 tables, as shown in Listing 19.2.

Listing 19.2. Using synonyms to point to the first version of the tables.

drop synonym Account_Number;
...
drop synonym Task_Header;
drop synonym Task_Detail;
...
...
create synonym Account_Number for PAV10.Account_Number;
...
create synonym Task_Header for PAV10.Task_Header;
create synonym Task_Detail for PAV10.Task_Detail;

The second script, shown in Listing 19.3, also drops the existing synonyms but creates synonyms that point to the version 2.0 tables.

Listing 19.3. Using synonyms to point to the second version of the tables.

drop synonym Account_Number;
...
drop synonym Task_Header;
drop synonym Task_Detail;
...
...
create synonym Account_Number for PAV20.Account_Number;
...
create synonym Task_Header for PAV20.Task_Header;
create synonym Task_Detail for PAV20.Task_Detail;

With these two scripts, a user can switch back and forth between the two versions of the project accounting tables.

Synonyms, the Navigator, and Schema
Manager

Personal Oracle for Windows 95 enables you to manipulate synonyms with the Navigator. To look at a list of the existing synonyms in your local database, double-click the Synonym folder. A list of all synonyms is displayed on the right side of the Main window. (See Figure 19.6.)

Figure 19.6.
The Navigator
displays a list of
synonyms in the Local
database.

Page 524

Examining a Synonym with the Navigator

To examine the definition of a synonym, select the synonym and right-click Properties. The Navigator displays a window that identifies the name of the synonym, its owner, and the table (or view) that it references (see Figure 19.7). However, if the owner name and table name are lengthy, you may not be able to read the full table name.

Figure 19.7.
The Navigator
displays the definition
of a synonym.

Creating a Synonym with the Navigator

Follow these steps to create a synonym with the Navigator:

1. Select the Synonym folder and right-click New.
2. In the Create Synonym window, enter a name for the new synonym in the Name field.
3. You have a choice for the synonym type—it may either be public or private.
4. In the Database field, select Personal Oracle and select the table from the drop-down list in the Table field. Click OK to create the synonym. Figure 19.8 illustrates how a public synonym is created.
5. If you want to create a private synonym, you must select the Private To radio button and identify the user that will own the synonym in the drop-down list to the right.

Figure 19.8.
Creating a public
synonym with the
Navigator.

Deleting a Synonym with the Navigator

To delete a synonym, select the synonym that you want to delete, and right-click Delete. The Navigator will ask you to confirm that you really want to delete the synonym.

Where's the Bottleneck?

Users are the best judges of the performance of an information system. Users measure performance in terms of response time, report turnaround time, and data transfer speed. A very inefficient system may be perceived by users as providing excellent turnaround time. Conversely, a group of users might consider the performance of a very efficient information system to be inadequate. Ignore the perception of the users at your peril.

Suppose you have to analyze a system that's the object of complaints about lackluster performance. You will need to examine four elements of a client/server architecture:

• The network
• The server
• The Oracle database
• The application

Your goal in performance tuning should be to make the biggest immediate improvement with the least amount of disruption to existing software, hardware, and procedures. The following sections look at each of the four elements in some detail.

Network

The network used in a client/server architecture may be a LAN, WAN, or a combination of both. Seek the help of a networking expert to determine the saturation level of the network. Characterize the network demands of a single client. If the network appears to be the performance bottleneck, investigate the client application software to discover whether you can reduce the number of SQL requests to the server. Changing the client application software has a huge impact; this strategy should be your last resort!

If you're just beginning to design an application, you can reduce the network traffic by identifying functionality that can be implemented with stored procedures and functions in the database.

Identifying Application Bottlenecks

Oracle furnishes two optimizers—the rule-based optimizer and the cost-based optimizer. Both optimizers are concerned with maximizing the performance of queries. The rule-based optimizer looks at the structure of the SQL statement to be optimized, determines what indexes exist for the table, and constructs an execution plan; it doesn't use any information about the contents of the table to be queried, or its index values.

NEW TERM
The rule-based optimizer is a mechanism in the Oracle RDBMS that looks at the database objects referenced by a SQL statement to determine the most efficient plan for performing the tasks specified by the SQL statement. The cost-based optimizer is an alternative to the rule-based optimizer; it uses the characteristics of the table and index contents to determine the most efficient plan for performing the tasks specified by the SQL statement.

The cost-based optimizer looks at statistics regarding the table, its columns, and its indexes and then calculates an execution plan based on the lowest cost path—the number of database blocks that must be read to retrieve the query results. Oracle stores these statistics in the data dictionary tables whenever an analysis of the tables and indexes is performed.

An initialization parameter named OPTIMIZER_MODE controls the choice between the rule-based and cost-based optimizer; this parameter has three possible values: