In addition to the regular tables you've seen in previous lessons, Oracle has an object known as a cluster, which is used to modify the way table data is stored. In fact, there are two different types of clusters available within the Oracle RDBMS: the index cluster, sometimes just referred to as a cluster, and the hash cluster. In this lesson you will learn about both the index cluster and the hash cluster--what they are, how they work, and how to effectively use them.

New Term: Today you will also learn how to use Oracle procedures and functions. Procedures and functions are similar; in fact, they are so much alike that they are typically referred to indiscriminately as stored procedures when they are used in packages and stored in the database. Procedures and functions are subprograms made up of PL/SQL code that take a set of parameters given to them by the calling program and perform a set of actions. Both can modify and return data passed to them as a parameter. The difference between a procedure and a function is that a function will always return a single value to the caller, whereas a procedure does not. Usually, procedures are used unless only a single return value is needed. A procedure or function that has been stored in the library cache is referred to as a stored procedure or a stored function; typically both procedures and functions are collectively referred to as procedures.

Because these procedures and functions are stored in the library cache in an already-parsed form, the parsing stage of execution can be bypassed. Also, because the SQL statements are identical each time they are used, they will be taken from the shared SQL area in the SGA if they have been executed recently. These features of stored procedures, in conjunction with the fact that network traffic is reduced (because the SQL statements are not transmitted), greatly enhance performance.

This lesson also deals with database links, which are exactly what you would think--links from one database to another. Using database links greatly simplifies accessing these other databases, thus making it easier for users to take advantage of remote data.

Clusters

New Term: A cluster, sometimes called an index cluster, is an optional method of storing tables in an Oracle database. Within a cluster, multiple related tables are stored together to improve access time to the related items. Tables that share a common column can be clustered around this column, thus speeding access to those rows that are accessed based on that column. The existence of a cluster is transparent to users and to applications; the cluster affects only how data is stored.

Remember that when Oracle retrieves data from disk, that operation is done on data blocks, not rows; therefore, if the data is stored together it will be copied from disk to memory together in the same data block. When the data block is read, all data from the clustered tables in that data block is read. If you will be using both pieces of data most of the time, this can be a real advantage; in fact, this is a major reason why you would create a cluster. So if the data that is clustered is primarily used in your application with a join operation, a cluster is advantageous.

If you have two tables that have related data and are frequently accessed together, using clusters can improve performance by preloading the related data into the SGA. Because you frequently use the data together, having that data already in the SGA greatly reduces access time.

Clusters are beneficial in joins where the join occurs on the cluster data because the data is all retrieved in one I/O operation. For example, suppose you are keeping a database of information for a dog club. (Because I am a dog lover and have several dogs of my own, I can easily relate to this.) In this database, you want to keep track of all the dogs and their owners as well as some information about each of the dogs. To do this, you must create several tables. First, you need a table of all the dogs who are members of the dog club. You also need a table of the dog owners, as shown in Figure 14.1. (This is the same table used in previous lessons.)

Figure 14.1.

The Dogs and Dog_Owners tables.

By combining the two tables into a cluster, you can save time when retrieving the data (because the dog owner information for a particular dog is essentially read into the SGA when the information for that dog is read in). The common column(s) of the cluster is called the cluster key, and must be indexed.

Figure 14.2 shows what the tables look like as a cluster. Note that the cluster key is the owner identification number.

If the data from the two tables is frequently used together, this cluster arrangement is a performance win. It provides the ease of use of individual tables but the additional performance of a cluster.

If you do not typically use their information together, there is no performance benefit of putting tables into a cluster. There is even a slight disadvantage because more SGA space is taken up by the additional data when the cluster is read in.

Figure 14.2.

The cluster including the Dogs and Dog_Owners tables.

An additional disadvantage of clusters is a reduction of the performance of INSERT statements. This performance loss is caused by the fact that the data must be inserted based on the cluster key and the fact that there are multiple tables in the same block. The clustered table also spans more blocks than the individual tables, thus causing more data to be scanned.

In summary, a cluster can be useful for tables where data is primarily accessed together in a join. The reduced I/O needed to bring the additional data into the SGA and the fact that the data is already cached can be a big advantage.

If the tables have a large number of INSERT statements or if the data is not frequently accessed together, a cluster is not useful and should not be used.

---

WARNING: Do not cluster tables if full-table scans are often performed on only one of the tables in the cluster. The additional space required by the cluster and the additional I/O will reduce performance.

---

Hash Clusters

New Term: A hash cluster is similar to a cluster but uses a hash function rather than an index to reference the cluster key. A hash function is a numeric function that determines the data block in the cluster based on the value of the cluster key. A hash cluster stores the data based on the result of a hash function. Figure 14.3 shows a hash cluster.

Figure 14.3.

A hash cluster.

To find the data block in an index cluster, there must first be one or more I/Os to the cluster index to find the correct data block. In a hash cluster, the cluster key itself tells Oracle where the data block is, an arrangement that can reduce to one the number of I/Os needed to retrieve the row.

In contrast to the index cluster, which stores related data together based on the row's cluster-key value, the hash cluster stores related rows together based on their hash values.

The number of hash values is determined by the value of the HASHKEYS parameter of the CREATE CLUSTER command. The number and size of the cluster keys are very important and should be carefully calculated.

Do not use hash clusters on tables where table scans are often performed on only one of the tables in the cluster. The additional space required by the cluster and the additional I/O required can reduce performance.

Also, do not use a hash cluster on a table where the application frequently modifies the cluster key or when the table is constantly being modified. Because the cluster key is based on a calculation, significant overhead is involved in constantly recalculating the key.

When to Hash

Although hash clusters can be used in a similar fashion to index clusters, you do not have to cluster multiple tables. In fact, it is frequently useful to create a single table as a hash cluster to take advantage of the hashing feature. By using hashing, you might be able to retrieve your data with only one I/O rather than the multiple I/Os required to retrieve data using a B*-tree index.

Because hashing uses the value of the data to calculate the data block in which the desired data is located, hashing is best used on tables that have unique values for the cluster key and where the majority of queries are equality queries on the cluster key. For equality queries, the data is usually retrieved in one read operation; the cluster key does not have to be a single column. If the typical query uses an equality on a set of columns, use these columns to create a composite key. A composite key is one that is made up of more than one column.

Hashing is also most optimal when the table or tables are fairly static in size. If the table stays within its initial storage allocation, hashing usually does not cause a performance degradation. If the table grows out of its initial allocation, however, performance can degrade because overflow blocks are required.

Hashing might degrade the performance of table scans because the hashing process reads blocks that may not have much data in them. Because the table is originally created by laying out the data into the cluster based on the value of the cluster key, some blocks might have only a few rows.

Hashing can also degrade performance when the value of the cluster key changes. Because the location of the block in which the data resides is based on the cluster key's value, a change in that value can cause the row to migrate in order to maintain the cluster.

A good candidate for hashing has the following properties:

• The cluster-key value is unique.

• The majority of queries are equality queries on the cluster key.

• The size of the table is static; very little growth occurs.

• The value of the cluster key does not change.

An example of a good hashing candidate is a table used for storing parts information. By using a hash cluster keyed on the part number, access can be extremely efficient and fast. Any time you have a somewhat static table with a unique column value or set of column values, consider creating a hash cluster.

Just as with index clusters, there are both advantages and disadvantages in using hash clusters. Hash clusters are efficient in retrieving data based on equality queries on the cluster key. If you are not retrieving data based on that key, the query is not hashed. As with the index cluster, you see a performance decrease when executing INSERT statements in a hashed table.

With both index clusters and hash clusters, make a careful determination about whether a cluster can help performance based on the access patterns on the tables. As with many aspects of RDBMS, tuning based on a wrong decision can end up costing in performance.

If you can take advantage of hashing by meeting somewhat strict criteria, you can see very good performance. Hashing is extremely efficient if you can meet the criteria described in this section.

Managing Clusters

Once you have decided whether to use an index cluster or a hash cluster, you can start the cluster-creation process. As with almost all the operations you have seen thus far, this can also be accomplished via the Schema Manager or via the CREATE CLUSTER command.

Cluster creation can actually be thought of as a three-step process. These steps consist of the following operations:

1. Create the cluster. This first step involves using the Schema Manager or the CREATE CLUSTER command to create the actual cluster itself. This cluster is a logical structure that will hold the clustered tables.

2. Create the tables in the cluster. This can be accomplished using the CREATE TABLE command with the CLUSTER parameter.

3. Create the index on the cluster key. Before any rows can be inserted into the tables, the cluster index must be created.

The remainder of this section goes through the process of creating the cluster, the tables, and the index.

Creating Clusters

As stated, the cluster-creation process can be accomplished via the Schema Manager or the CREATE CLUSTER command. To create a cluster using the Schema Manager, right-click the Cluster icon.

From the options available from this menu, select the Create button. This will invoke the Create Cluster screen, a completed version of which is shown in Figure 14.4.

After you have invoked the Create Cluster screen you must fill in the values for the cluster name, the schema, the cluster size, whether it is an index cluster or a hash cluster, and the cluster columns. The size value is important; it specifies the average size that you expect a cluster key and its associated rows to consume. This parameter will be used to reserve space for each key value and rows. This does not limit the amount of data that can be used by a cluster value, but will improve efficiency.

Figure 14.4.

The Create Cluster screen for the Dogs table.

When you have completed this operation, the cluster will be created. You can also create a cluster with the CREATE CLUSTER command. Here is an example:

CREATE CLUSTER dogs (owner_id NUMBER(4))
TABLESPACE dogs;

The complete syntax for the CREATE CLUSTER command can be found in the Oracle documentation. Of course, you can add storage parameters to this command, such as the storage clause, parallelism, and so on. The next step involved is creating the clustered tables.

Creating Clustered Tables

You can create the cluster tables by using the CREATE TABLE command, as shown in earlier lessons in this book. The CREATE TABLE command must include the CLUSTER parameter. To create the two tables (here we'll use the tables Dogs and Dog_owners that you saw on Day 12, "Working with Tables, Views, and Synonyms") as a cluster, you can use SQL commands with the Server Manager (see Listings 14.1 and 14.2).

INPUT:

Listing 14.1. The SQL for the Dogs table.

CREATE TABLE "ETW".Dogs (
ID NUMBER NULL, 
NAME VARCHAR2(40) NULL, 
OWNER_ID NUMBER NULL, 
BREED_ID NUMBER NULL, 
RANK NUMBER NULL, 
NOTES VARCHAR2(80) NULL)  
CLUSTER dogs(OWNER_ID);

INPUT:

Listing 14.2. The SQL for the Dog_owners table.

CREATE TABLE "ETW".Dog_owners (
ID NUMBER NOT NULL, 
Last_Name VARCHAR2(40) NULL, 
First_Name VARCHAR2(20) NULL, 
Street VARCHAR2(40) NULL, 
City VARCHAR2(20) NULL, 
State CHAR(2) NULL, 
Zip VARCHAR2(10) NULL, 
Phone VARCHAR2(15) NULL, 
Notes VARCHAR2(80) NULL) 
CLUSTER dogs(ID); 

Notice that the CLUSTER parameter specifies the cluster name and the cluster-key value. For more information on the CREATE TABLE command, refer to the Oracle documentation.

The final stage involved in creating a cluster is to create the index on the cluster key. This must be accomplished before any data can be loaded into the tables.

Creating the Cluster Index

Creating the cluster index can be accomplished either via the Schema Manager or the CREATE INDEX command. To create the cluster index using the Schema Manager, right-click the icon of the cluster on which you want to create the index and select the Create Index On option from the menu that pops up.

This will invoke the Create Index screen that you have seen in previous lessons. There are, however, a few differences this time. In this screen the Cluster button has already been selected for you, and you cannot select any columns for indexing. The cluster index is on the cluster key only. The filled-out Create Index screen is shown in Figure 14.5.

You can use the Schema Manager to easily create the cluster index. If, however, you want to use the CREATE INDEX command, you also have that option. It is often convenient to script the entire database-, table-, cluster-, and index-creation process so that it can be used again or as a template for other database creations. To create the cluster index using the CREATE INDEX command, use the following syntax:

CREATE INDEX "ETW".Dog_owners_IX1 ON CLUSTER  "ETW"."Dogs";

At this point your cluster is created and ready for use. In the next part of this lesson you will learn how to use procedures, functions, and packages (also known as stored procedures).

Figure 14.5.

The Create Index screen, with information for this example filled in.

Procedures, Functions, and Packages

New Term: Procedures and functions are subprograms made up of PL/SQL code that take a set of parameters given to them by the calling program and perform a set of actions. The only real difference between a procedure and a function is that a function will include a single return value. Both functions and procedures can modify and return data passed to them as a parameter. Usually, procedures are used unless only one return value is needed.

A procedure or function that has been stored in the library cache is referred to as a stored procedure or a stored function. A stored procedure or stored function has the following characteristics:

• It has a name--This is the name by which the stored procedure or function is called and referenced.

• It takes parameters--These are the values sent to the stored procedure or function from the application.

• It returns values--A stored procedure or function can return one or more values based on the purpose of the procedure or function.

• It is stored in the data dictionary--The stored procedure or function is stored in a parsed form in the data dictionary.

Procedures

A procedure is a set of PL/SQL statements that form a subprogram. The subprogram is designed and created to perform a specific operation on data in your database. A procedure takes zero or more input parameters and returns zero or more output parameters. The syntax of a procedure is as follows:

SYNTAX:

PROCEDURE procedure_name [( parameter_declaration )] IS
    [local declarations]
BEGIN
    PL/SQL Statements
[EXCEPTION
    Optional Exception Handler(s)]
END [procedure_name];

In this syntax, the parameter_declaration has the following format:

parameter_name [IN | OUT | IN OUT] datatype

The parameter qualifiers have the following meanings:

• IN--This parameter is used as an input value only.

• OUT--This parameter is used as an output value only.

• IN OUT--This parameter is used as both an input and an output variable.

The procedure is made up of two parts: the declaration and the body of the procedure. The declaration begins with the keyword PROCEDURE and ends with the last parameter declaration. The body begins with the keyword IS and ends with the keyword END.

The declaration section is used to define which variables are passed to the procedure and which values are returned from the procedure back to the calling program. The body of the procedure is where the real work is done. The body is made up of the PL/SQL statements that perform the desired task.

Functions

A function, like a procedure, is a set of PL/SQL statements that form a subprogram. The subprogram is designed and created to perform a specific operation on data in your database. A function takes zero or more input parameters and returns just one output value. If more than one output value is required, a procedure should be used. The syntax of a function is as follows:

SYNTAX:

FUNCTION function_name [( parameter_declaration )] RETURN datatype IS
    [local declarations]
BEGIN
    PL/SQL Statements
[EXCEPTION
    Optional Exception Handler(s)]
END [function_name];

The parameter_declaration has the same format as it does with a procedure:

parameter_name [IN | OUT | IN OUT] datatype

The parameter qualifiers have the following meanings:

• IN--This parameter is used as an input value only.

• OUT--This parameter is used as an output value only.

• IN OUT--This parameter is used as both an input and an output variable.

As with a procedure, a function is made up of two parts: the declaration and the body. The declaration begins with the keyword FUNCTION and ends with RETURN statement. The body begins with the keyword IS and ends with the keyword END.

The declaration section is used to define which variables are passed to the function and which values are returned from the function back to the calling program. The body of the function is where the real work is done. The body is made up of the PL/SQL statements that perform the desired task.

The difference between a procedure and a function is the return value. A function has the return declaration as well as a RETURN function within the body of that function that returns a value. This RETURN function is used to pass a return value to the calling program. If you do not intend to return a value to the calling program, or you want to return more than one value, use a procedure.

---

NOTE: For the remainder of this lesson, the term procedure is used to refer to both procedures and functions because both are similar in nature and function.

---

How Procedures and Functions Operate

Procedures and functions use the same basic syntax in the program body with the exception of the RETURN keyword, which can only be used by functions. The body itself is made up of PL/SQL blocks that perform the desired function and return the desired data to the calling program. The goal of the body of the procedure is both to minimize the amount of data to be transmitted across the network (to and from the calling program) and to perform the PL/SQL statements in the most efficient manner possible.

The PL/SQL Language

PL/SQL is a block-structured language offered by Oracle to facilitate the use of the Oracle RDBMS. It has the following properties and features that can be used to aid in application development:

• Block structure--The block structure allows blocks to contain nested subblocks.

• Block declarations--Each block can have its own declarations, which means that you can logically separate functions.

• Variable declaration--Variables can be declared and used within a PL/SQL block.

• Constant declaration--Constants can be declared and referenced within a PL/SQL block.

• Conditional statements--PL/SQL allows for conditional processing with IF...THEN...ELSE, WHILE...LOOP, FOR...LOOP, EXIT...WHEN, and GOTO functions.

These features make PL/SQL a powerful SQL processing language. Using PL/SQL has several major advantages over using standard SQL statements (in addition to allowing the use of stored procedures and functions). Among these are ease of use, portability, and higher performance.

The primary performance difference between PL/SQL and SQL is the fact that PL/SQL statements are transmitted to Oracle as a block of statements rather than as individual state-ments. In a network application, the additional overhead needed to transmit individual statements can be quite high. It takes very little additional CPU and network resources to send a larger packet than it does to send a smaller one.

The RETURN Statement

In the declaration portion of a function, a RETURN parameter is used to declare the type of the return value. Later, in the body of the function, the RETURN statement is used to exit the function and return the specified value to the calling program. With a procedure, the RETURN statement can also be used, but not to return a value. In a procedure, the RETURN statement can be used only to exit the procedure. No values can be associated with the RETURN statement in a procedure.

The EXCEPTION Statement

In both procedures and functions, you can add optional exception handlers. These exception handlers allow you to return additional information based on certain conditions (such as no data found or some user-specified condition). By using exception handlers and allowing the stored procedure to notify you of some special conditions, you can minimize the amount of return-value checking that must be done in the application code. Because the work to determine that no data has been selected has already been done by the RDBMS engine, you can save on resources if you take advantage of this information.

The RDBMS_OUTPUT Package

To visually represent data selected within a stored procedure or function, you can use the RDBMS_OUTPUT package supplied by Oracle. To see data returned by RDBMS_OUTPUT in SQL*Plus or Server Manager, you must set the SERVEROUTPUT option by issuing the command SET SERVEROUTPUT ON. Also be sure to terminate the procedure with a slash (/) to invoke it. When using the RDBMS_OUTPUT package, you can select several options for inputting or outputting data. The following procedures are available in the RDBMS_OUTPUT package:

• RDBMS_OUTPUT.ENABLE--Enables output processing.

• RDBMS_OUTPUT.DISABLE--Disables output processing.

• RDBMS_OUTPUT.PUT_LINE--Places a newline-terminated string in the buffer.

• RDBMS_OUTPUT.PU--Places a string in the buffer (no newline).

• RDBMS_OUTPUT.GET_LINE--Gets one line from the buffer.

• RDBMS_OUTPUT.GET_LINE--Gets an array of lines from the buffer.

In this manner, you can use a stored procedure for ad-hoc functions that require data to be displayed in SQL*Plus. The typical stored procedure is used to return data that has been bound to variables in a program.

How to Create Stored Procedures and Stored Functions

New Term: There are advantages to using procedures and functions; however, the greatest advantage of using functions and procedures happens when the procedures and functions are stored in the database. Such procedures and functions are referred to as stored procedures and stored functions. A stored procedure or stored function has the advantage of being stored in the library cache in an already parsed form, thus reducing parsing time. In this section you will see how to parse the procedure and store it in the database.

To create a stored procedure or function, use the keywords CREATE PROCEDURE or CREATE FUNCTION with the same syntax as the PROCEDURE and FUNCTION commands shown earlier in this lesson. When creating a procedure or function, however, the IS keyword is replaced with the AS keyword. Listing 14.3 shows an example of how to create a stored procedure to retrieve some information from the Dogs table.

---

NOTE: The typical stored procedure or function is called by an application program. In the following example, however, to better illustrate how a stored procedure is coded, I chose to use SQL*Plus.

---

INPUT:

Listing 14.3. Creating the stored procedure old_dogs.

SQL> CREATE OR REPLACE PROCEDURE 
  2      old_dogs
  3  AS
  4      CURSOR dog_cursor IS
  5      SELECT 
  6          dogname, age, owner
  7      FROM dogs
  8      WHERE age > 8;
  9  BEGIN
 10      RDBMS_OUTPUT.PUT_LINE(`Dogs older than 8 years old');
 11      RDBMS_OUTPUT.PUT_LINE(`Name  Age  Owner');
 12      FOR dog IN dog_cursor LOOP
 13      RDBMS_OUTPUT.PUT_LINE(dog.dogname||'  `||dog.age||'  `||dog.owner);
 14      END LOOP;
 15  END old_dogs;
 16  /

Procedure created.

To view the output of this stored procedure from SQL*Plus, you must to enable the SERVEROUTPUT option, as follows:

INPUT:

SQL> set serveroutput on

INPUT:

SQL> execute old_dogs;

The resulting output of this procedure is

OUTPUT:

Dogs older than 8 years old
Name  Age  Owner
Shasta  9  Jones
Jessy  10  Wilson
Ruff  9  King

PL/SQL procedure successfully completed.

ANLYSIS:

As you can see, to enable the stored procedure to return multiple rows selected from the Dogs table, it is necessary to declare a cursor. By looping through this cursor, you can output all the lines that were selected.

How to Replace Procedures and Functions

If the procedure or function is already stored in the library cache, you must replace, rather than create, the procedure or function. You do this by using the command CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION. With this command, an already-present procedure or function is replaced; if it is not already present, it is created.

Packages

Packages are sets of related procedures or functions that are compiled and stored together in the data dictionary. They allow you to group together PL/SQL types, objects, and subprograms into a logical unit. When you link these logically related entities together, it can be easier to program and modify modules based on their function and relation. Performance is enhanced because the entire package is loaded into memory when it is first called, thus increasing the chance for a cache hit on a related function or object that is likely to be called soon.

Packages are actually created in a statement with two different parts. The first is the declaration part, where the package is defined. Then there is the package body definition, where the body of the package is defined. The syntax of the statement used to create the package definition is as follows:

SYNTAX:

CREATE PACKAGE package_name AS package_specification
    public type and object declaration
    subprogram definition
END [package_name];

This definition part of the package creation declares the parts of the package available to the user. The rest of the package definition is used by the user, but is not visible to the user. This second part has the following syntax:

SYNTAX:

CREATE PACKAGE BODY package_name AS package_body
    private type and object declaration
    subprogram bodies
[BEGIN
    initialization statements]
END [package_name];

The user application must have knowledge of the package specification in order to call the package correctly. The arrangement of the package-creation process has several advantages:

• Portability--The body of the package can change without requiring any changes to the application--as long as the package specification does not change.

• Security--The package can access tables you might not want the user to see. Because the package body is hidden from the user, some security can be maintained.

• Modularity--With packages, modules can have specific functions that can be logically grouped and specified.

• Ease of design--The specification part of the package can be completed first, thus allowing different teams to work on the package body and the application. Once the specification is completed, both groups can write to that specified interface.

• Better performance--Because the entire package is loaded into memory when the first component is accessed, additional calls to the package do not invoke disk I/O.

Using Procedures, Functions, and Packages

Using procedures, functions, and packages can improve performance in several ways--for example, through a reduction in the amount of data that must be transmitted across the network and an increase in hits in the shared SQL cache.

New Term: Packages are sets of related procedures or functions compiled and stored together in the data dictionary. Packages allow you to group PL/SQL types, objects, and subprograms into a logical unit. If you link these logically related entities together, it can be easier to program and modify modules based on their function and relation. You enhance performance because the entire package is loaded into memory when it is first called, increasing the chance for a cache hit on a related function or object that is likely to be called soon.

Because a procedure, function, or package is stored within the library cache, it is available for immediate use by your applications. Because these objects are stored in an already-parsed form, performance is also improved.

Procedures, functions, and packages are used to call certain SQL statements that are used over and over again. Any set of SQL statements that you use frequently in your application can benefit from being made into a stored procedure or function.

Stored Procedures' Use of the Library Cache

As you know, the library cache contains the shared SQL and PL/SQL areas. By increasing the cache-hit rate in the library cache, you increase performance. This increase comes from reducing the overhead needed to parse the SQL statements in the shared SQL area and from retrieving those statements from cache (reducing the need to retrieve those statements from disk).

A cache miss in the shared SQL area occurs either when a parse statement is called and the already-parsed statement does not exist in the shared SQL area or when an application tries to execute a SQL statement and the shared SQL area containing the parsed statement has been deallocated from the library cache.

Here is a review of the requirements necessary for a SQL statement to take advantage of the library cache. For a SQL statement to take advantage of SQL or PL/SQL statements that have already been parsed, the following criteria must be met:

• The text of the SQL statement must be identical to the SQL statement that has already been parsed. This includes whitespaces and case.

• References to schema objects in the SQL statements must resolve to the same object.

• Bind variables must match the same name and data type.

• The SQL statements must be optimized using the same approach and, in the case of the cost-based approach, the same optimization goal as the already parsed statement.

You might think that these conditions make it difficult to take advantage of the shared SQL areas, but by reusing application code you can quite easily meet these conditions. When writing applications, you should strive to use the same SQL statements to access the same data and ensure that these SQL statements can meet these criteria.

Use stored procedures and functions whenever possible to guarantee that the same shared PL/SQL area is used. Another advantage of using stored procedures is that they are stored in a parsed form, eliminating runtime parsing altogether.

Standardizing on naming conventions for bind variables and spacing conventions for SQL and PL/SQL statements can also increase the likelihood of reusing shared SQL statements.

Using Database Links

New Term: A database link is simply a link within the local database to another database on the network. By setting up a database link, it is a simple matter for a user connected to a local database to access data in the remote database from the local instance. This access to the remote database is transparent to the user.

Let's say there is a database link called club. club is a link to the service named club_server. To access the dogs table on the club server, you would use the following SQL statement:

SELECT * FROM dogs@club;

Because the link is transparent, the data is retrieved from the server named club_server, which was accessed with the link named club.

To create a database link, right-click the Database Link icon in the Schema Manager or use the CREATE DATABASE LINK command. The Create Database Link screen is displayed, as shown in Figure 14.6.

Simply fill in the name, whether it is a public or private link, and what the service name is. You can set up the link to use an anonymous or a named link, where all access to this database uses the same username and password that was used to define the link. You can create a database link with a SQL statement; for this example, it would be

CREATE DATABASE LINK CLUB.WORLD  USING `CLUB_SERVER';

Database links provide a transparent way for users to access data in remote databases very easily. The database link is very easy to use.

Figure 14.6.

The Create Database Link screen.

Summary

Clusters can be very useful under certain conditions, as you have seen in this lesson. A cluster enables you to store multiple tables in the same space. This allows data that is typically used together to be retrieved together, thus reducing I/O. A cluster can be either an index cluster or a hash cluster depending on which one suits your needs. An index cluster uses an index on the cluster key, whereas the hash cluster uses a hash function on the cluster key. Regardless of which type of cluster you use, the function is similar.

If you have two tables with related data that are frequently accessed together, using clusters can improve performance by preloading the related data into the SGA. Because you frequently use the data together, having that data already in the SGA greatly reduces access time. Clusters are beneficial in joins where the join occurs on the cluster data because the data is all retrieved in one I/O operation.

Also in this lesson you have seen the use of functions, procedures, and packages to improve performance. These performance enhancements include reduction in the amount of data that must be transmitted across the network and an increase in hits in the data dictionary cache.

Because a procedure, function, or package is stored within the data dictionary, it is available for immediate use by your applications. Because stored procedures and functions are stored in the library cache in an already-parsed form, performance is improved. Any set of SQL statements that your application frequently uses can benefit from being made into a stored procedure or function.

There are very few SQL statements that cannot benefit from the use of procedures, functions, and packages. By storing these subprograms in the database, you reduce network traffic and increase performance in accessing these programs. Whenever possible, use stored procedures and packages; there is no disadvantage associated with their use.

What's Next?

In Day 15's lesson, "Managing Job Queues and Using Oracle Auditing," you will see how to enable the Oracle auditing feature, how to view the data, and how to interpret that data. The auditing feature is part of Oracle security and is very useful when that level of security is required.

Q&A

Q What is the difference between an index cluster and a hash cluster?

A An index cluster is a cluster that has an index on the cluster key, whereas the hash cluster uses a hashing function to access the cluster key.

Q How is table access different in a cluster?

A Table access is no different between a cluster and an ordinary table. There is a performance difference, however--if you are accessing multiple tables that are clustered, as in a join operation, you will see a significant performance improvement and reduction of I/O.

Q Why are stored procedures useful?

A Stored procedures not only cut down on network traffic, but improve the cache-hit rate in the shared SQL area by making sure that SQL statements are identical.

Q Why would I want to use a database link?

A Database links can simplify access to tables and hide details that you don't want users to know about.

Workshop

The workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. Answers to the quiz questions appear in Appendix A, "Answers."

Quiz

1. What is the best criterion for creating a cluster?

2. Give two criteria for using hash clustering.

3. What benefit do you get from using a cluster?

4. What kind of criteria indicate that clustering is not a good idea? (Name two things.)

5. What is a stored procedure?

6. How do you benefit from stored procedures?

7. Can stored procedures perform programmatical operations?

8. What are database links used for?

Exercises

1. Create an indexed cluster using the Schema Manager.

2. Create two tables using the same cluster key using the CREATE TABLE command.

3. Create an index on the cluster using the Schema Manager.

4. Using the Schema Manager, create a database link to another server.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.