Store a row in an indexed table or cluster - Functions - Oracle Clusters

Discussion

• Nirja Shah   -Posted on 02 Sep 15
  - 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.

➨ Post your comment / Share knowledge