RelationalDBDesign RelationalDBDesign



Clustering Tables   «Prev 

About Hash Clusters

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 Database locates the rows in a table using key values that the database stores in a separate index. To use hashing, you create a hash cluster and load tables into it. The database physically stores the rows of a table in a hash cluster and retrieves them according to the results of a hash function. Oracle Database uses a hash function to generate a distribution of numeric values, called hash values, that 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, the database applies the hash function to the cluster key value of the row. The resulting hash value corresponds to a data block in the cluster, which the database 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:
  1. One or more I/Os to find or store the key value in the index
  2. Another I/O to read or write the row in the table or cluster
In contrast, the database 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.

Oracle Hash Table Clusters

SELECT * FROM hash_table WHERE TYPE =22;
A user issues a SELECT statement based on the value of the cluster key in the hashed cluster.

SELECT * FROM hash_table WHERE TYPE =22;
Oracle parses the value with the specified hash cluster.

SELECT * FROM hash_table WHERE TYPE =22;
Oracle uses the resultant hash value to directly access the appropriate cluster.