Clustering Tables   «Prev  Next»

Lesson 7 Creating Hash Cluster
Objective Create an Oracle Hash cluster.

Creating Oracle Hash Cluster versus Standard Cluster

Just as a hash cluster is different from a normal cluster, creating a hash cluster is different from creating a standard cluster.
When Oracle creates a hash cluster table, it immediately allocates all the space that will be required by all the data in the hash cluster. Oracle determines the total amount of space for the hash cluster by using the SIZE parameter, which you learned about earlier, and another parameter called HASHKEYS. The value for the HASHKEYS parameter limits the total number of unique values for the result of the hashing function. If you specify a HASHKEYS value of 100, there will be no more than 100 different values for the result of the hashing function. If there are more than 100 possible results, Oracle will still only create 100 different areas to correspond to 100 different values, so some collisions, where multiple values of the hash function are stored together, will occur.

Types of hashing

There are three different types of hash functions you can choose for a hash cluster. You can specify:
  1. Oracle's internal hashing function.
  2. The value of the cluster key as the result of the hash function. If the value of the cluster key is greater than the value specified for HASHKEYS, the value is divided by the HASHKEYS value and the remainder is used as the hash key.
  3. Any SQL function.

Syntax

In order to create a hash cluster, you use the syntax shown in the following Tooltip:

  1. Required keywords
  2. The unique name for the cluster.
  3. The name of the column or columns that will make up the cluster key. These names are unique to the cluster, but they do not have to match the names of the corresponding columns in the clustered tables.
  4. One of the standard Oracle column datatypes, except for LONG RAW and RAW. Although the names of the columns in the clustered table do not have to match the -column_name, the datatypes of the corresponding columns do have to match.
  5. The total number of unique values for the hash key. Oracle will round this value up to the closest prime number.
  6. Optional keywords that will be used if you are specifying your own hash value or using the cluster key for the hash value.
  7. An SQL function that generates an integer result that is used as the hash value.
  8. A required keyword.
  9. The size of the cluster needed to store all the rows with the same value for the cluster key. HASHKEYS, A required keyword for a hash cluster.
  10. Contains the same types of storage options as the storage clause for a table, such as TABLESPACE and PCTFREE.
CREATE CLUSTER cluster_name (column_name Datatype)
HASHKEYS number_of_hashkeys
[HASH IS function]
SIZE size_value
storage_clause

Oracle Create Hash Cluster

Hash Cluster Example

If you wanted to create a hash cluster for the LOT table, base it on value of the LOT_ID column, allow 100 values, and use the MOD SQL function to return a remainder from dividing the LOT_ID column by 100, you would use the following command: 

CREATE CLUSTER lot_cluster (lot_id NUMBER)
 SIZE 1 M
 HASHKEYS 100
 HASH IS MOD(lot_id, 100);
The next lesson explains how to delete clusters.