Oracle7 Server Administrator's Guide

Guidelines for Managing Hash Clusters

This section describes guidelines to consider before attempting to manage hash clusters, and includes the following topics:

Advantages of Hashing

Disadvantages of Hashing

Estimate Size Required by Hash Clusters and Set Storage Parameters

Storing a table in a hash cluster is an optional way to improve the performance of data retrieval. A hash cluster provides an alternative to a non-clustered table with an index or an index cluster. With an indexed table or index cluster, Oracle locates the rows in a table using key values that Oracle stores in a separate index. To use hashing, you create a hash cluster and load tables into it. Oracle physically stores the rows of a table in a hash cluster and retrieves them according to the results of a hash function.

Oracle uses a hash function to generate a distribution of numeric values, called hash values, which are based on specific cluster key values. The key of a hash cluster, like the key of an index cluster, can be a single column or composite key (multiple column key). To find or store a row in a hash cluster, Oracle applies the hash function to the row's cluster key value; the resulting hash value corresponds to a data block in the cluster, which Oracle then reads or writes on behalf of the issued statement.

To find or store a row in an indexed table or cluster, a minimum of two (there are usually more) I/Os must be performed:

one or more I/Os to find or store the key value in the index

another I/O to read or write the row in the table or cluster

In contrast, Oracle uses a hash function to locate a row in a hash cluster; no I/O is required. As a result, a minimum of one I/O operation is necessary to read or write a row in a hash cluster.

Advantages of Hashing

If you opt to use indexing rather than hashing, consider whether to store a table individually or as part of a cluster.

Hashing is most advantageous when you have the following conditions:

Most queries are equality queries on the cluster key:

		SELECT . . . WHERE cluster_key = . . . ;

In such cases, the cluster key in the equality condition is hashed, and the corresponding hash key is usually found with a single read. In comparison, for an indexed table the key value must first be found in the index (usually several reads), and then the row is read from the table (another read).

The tables in the hash cluster are primarily static in size so that you can determine the number of rows and amount of space required for the tables in the cluster. If tables in a hash cluster require more space than the initial allocation for the cluster, performance degradation can be substantial because overflow blocks are required.

Disadvantages of Hashing

Hashing is not advantageous in the following situations:

Most queries on the table retrieve rows over a range of cluster key values. For example, in full table scans, or queries like the following, a hash function cannot be used to determine the location of specific hash keys; instead, the equivalent of a full table scan must be done to fetch the rows for the query:

		SELECT . . . WHERE cluster_key < . . . ;

With an index, key values are ordered in the index, so cluster key values that satisfy the WHERE clause of a query can be found with relatively few I/Os.

The table is not static and continually growing. If a table grows without limit, the space required over the life of the table (its cluster) cannot be pre-determined.

Applications frequently perform full-table scans on the table and the table is sparsely populated. A full-table scan in this situation takes longer under hashing.

You cannot afford to pre-allocate the space that the hash cluster will eventually need.

See Also: For more information about creating hash clusters and specifying hash functions see the Oracle7 Server SQL Reference.

For information about hash functions and specifying user-defined hash functions, see the Oracle7 Server Concepts manual.

Even if you decide to use hashing, a table can still have separate indexes on any columns, including the cluster key. See the Oracle7 Server Application Developer's Guide for additional recommendations.

Estimate Size Required by Hash Clusters and Set Storage Parameters

As with index clusters, it is important to estimate the storage required for the data in a hash cluster.

Oracle guarantees that the initial allocation of space is sufficient to store the hash table according to the settings SIZE and HASHKEYS. If settings for the storage parameters INITIAL, NEXT, and MINEXTENTS do not account for the hash table size, incremental (additional) extents are allocated until at least SIZE*HASHKEYS is reached. For example, assume that the data block size is 2K, the available data space per block is approximately 1900 bytes (data block size minus overhead), and that the STORAGE and HASH parameters are specified in the CREATE CLUSTER command as follows:

STORAGE (INITIAL 100K

   NEXT 150K

   MINEXTENTS 1

   PCTINCREASE 0)

SIZE 1500

HASHKEYS 100

In this example, only one hash key can be assigned per data block. Therefore, the initial space required for the hash cluster is at least 100*2K or 200K. The settings for the storage parameters do not account for this requirement. Therefore, an initial extent of 100K and a second extent of 150K are allocated to the hash cluster.

Alternatively, assume the HASH parameters are specified as follows:

SIZE 500 HASHKEYS 100

In this case, three hash keys are assigned to each data block. Therefore, the initial space required for the hash cluster is at least 34*2K or 68K. The initial settings for the storage parameters are sufficient for this requirement (an initial extent of 100K is allocated to the hash cluster).

See Also: To estimate the size of a hash cluster, use the procedure given in "Estimating Space Required by Clusters" , along with the supplemental information in "Estimating Space Required by Hash Clusters" .