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,
Types of hashing
There are three different types of hash functions you can choose for a hash cluster. You can specify:
Oracle's internal hashing function.
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.
Any SQL function.
In order to create a hash cluster, you use the syntax shown in the following Tooltip:
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
HASH IS MOD(lot_id, 100);