Performance & space use are optimal - Oracle Clusters

Discussion

• Nirja Shah   -Posted on 02 Sep 15
  - When creating a hash cluster, it is important to choose the cluster key correctly and set the HASH IS, SIZE, and HASHKEYS parameters so that performance and space use are optimal.
  
  - The following guidelines describe how to set these parameters.
  
  1. Choosing the Key
  - Choosing the correct cluster key is dependent on the most common types of queries issued against the clustered tables
  - For hash clusters that contain a single table, the cluster key is typically the entire primary key of the contained table.
  - The key of a hash cluster, like that of an index cluster, can be a single column or a composite key (multiple column key).
  - A hash cluster with a composite key must use the internal hash function of the database.
  
  2. Setting HASH IS
  - Specify the HASH IS parameter only if the cluster key is a single column of the NUMBER datatype, and contains uniformly distributed integers.
  - If these conditions apply, you can distribute rows in the cluster so that each unique cluster key value hashes, with no collisions (two cluster key values having the same hash value), to a unique hash value.
  - If these conditions do not apply, omit this clause so that you use the internal hash function.
  
  3. Setting SIZE
  - SIZE should be set to the average amount of space required to hold all rows for any given hash key. Therefore, to properly determine SIZE, you must be aware of the characteristics of your data:
  
  - If the hash cluster is to contain only a single table and the hash key values of the rows in that table are unique (one row for each value), SIZE can be set to the average row size in the cluster.
  
  - If the hash cluster is to contain multiple tables, SIZE can be set to the average amount of space required to hold all rows associated with a representative hash value.
  
  4. Setting HASHKEYS
  - For maximum distribution of rows in a hash cluster, the database rounds the HASHKEYS value up to the nearest prime number.

➨ Post your comment / Share knowledge