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 — Oracle rounds the value you specify up to the nearest prime number, so a HASHKEYS value of 100 actually yields 101 possible hash results, not exactly 100. If there are more possible cluster key values than the cluster was sized for, Oracle will still only create that many distinct areas, so some collisions, where multiple values of the hash function are stored together, will occur.

A collision is not an error, but it does have a cost. When two different cluster key values happen to hash to the same result, Oracle stores both keys' rows in the same allocated area, chaining additional blocks if the combined data outgrows the original SIZE allocation — the same chaining behavior discussed in the sizing lesson, just triggered by a hash collision rather than a single key's data simply growing over time. A lookup for one of those keys still goes straight to the correct area with no index traversal, but it may have to skip past rows belonging to the other key sharing that slot. This is usually still far cheaper than an index cluster's separate index lookup, but it is one reason HASHKEYS should be sized generously enough to keep collisions infrequent, rather than set to the bare minimum needed for today's data.

An index cluster is created with just the cluster columns, an optional SIZE, and a storage clause — Oracle allocates space incrementally as cluster key values are inserted, and a separate cluster index must be built afterward before the cluster can be used. A hash cluster front-loads that work instead: HASHKEYS and SIZE together tell Oracle exactly how much space to reserve for every possible hash result before a single row is ever inserted, and no separate index step follows creation at all — the hash function itself takes the place of the index. This is the direct cause of the collision behavior described above: an index cluster can always allocate one more block for a growing key, while a hash cluster's total hash-result space is fixed the moment HASHKEYS is set.

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. For example, with HASHKEYS 150 and a cluster key value of 400, Oracle would use 400 MOD 150 = 100 as the effective hash result.
3. Any SQL function.

Choosing Between the Three Hashing Types

Which of the three to use depends mainly on what the cluster key values already look like:

• Use Oracle's internal hashing function (the default, when no HASH IS clause is given) when the cluster key values are not already well-distributed on their own — text values, composite keys, or numeric keys with an uneven distribution all benefit from letting Oracle's function spread them across hash results evenly.
• Use the cluster key itself as the hash value when the key is already a dense, evenly distributed set of integers, such as a sequence-generated ID. This avoids the overhead of computing a hash at all, at the cost of needing HASHKEYS set high enough (or a deliberate MOD-style wraparound) to accommodate the key's actual range.
• Use a custom SQL function (typically MOD) when you want the cluster key's own distribution but need to fold a wide range down into a smaller, fixed number of hash results — the LOT_ID example later in this lesson is exactly this case.

Estimating a Good HASHKEYS Value

Choosing HASHKEYS well means estimating how many distinct cluster key values the table will hold — not the number of rows, but the number of unique key values those rows share. For the trial_cluster example below, that means asking how many distinct trialno values are expected, not how many rows exist across all trials.

A reasonable starting point: query the source data (or a close estimate of it) for the count of distinct key values, then round up generously. Oracle rounds to the nearest prime regardless of what you specify, but setting HASHKEYS too close to today's exact count leaves no room for growth before collisions start increasing. For example, if a query shows 118 distinct trialno values today and the table is expected to keep growing, setting HASHKEYS to something like 200 — which Oracle rounds to 211 — leaves meaningful headroom. Setting it to exactly 118, which rounds to 127, would start producing collisions almost as soon as new trial numbers are added.

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

CREATE CLUSTER cluster_name (column_name Datatype)
    HASHKEYS number_of_hashkeys
    [HASH IS function]
    SIZE size_value
    storage_clause;

A hash cluster is created using a CREATE CLUSTER statement, but you specify a HASHKEYS clause. The following example contains a statement to create a cluster named trial_cluster that stores the trial table, clustered by the trialno column (the cluster key); and another statement creating a table in the cluster.

CREATE CLUSTER trial_cluster (trialno NUMBER(5,0))
TABLESPACE users
STORAGE (INITIAL 250K NEXT 50K
MINEXTENTS 1 MAXEXTENTS 3
PCTINCREASE 0)
HASH IS trialno HASHKEYS 150;

CREATE TABLE trial (
trialno NUMBER(5,0) PRIMARY KEY,
...)
CLUSTER trial_cluster (trialno);

As with index clusters, the key of a hash cluster can be a single column or a composite key (multiple column key). In this example, it is a single column. The HASHKEYS value, in this case 150, specifies and limits the number of unique hash values that can be generated by the hash function used by the cluster. The database rounds the number specified to the nearest prime number. If no HASH IS clause is specified, the database uses an internal hash function. If the cluster key is already a unique identifier that is uniformly distributed over its range, you can bypass the internal hash function and specify the cluster key as the hash value, as is the case in the preceding example. You can also use the HASH IS clause to specify a user-defined hash function. You cannot create a cluster index on a hash cluster, and you need not create an index on a hash cluster key.

The Datatype restriction in the syntax table above excludes LONG and LONG RAW from cluster key columns — the same restriction that applies to standard index clusters. Both datatypes predate Oracle's LOB (Large Object) types and have been superseded by CLOB and BLOB for years: a table can hold at most one LONG or LONG RAW column, neither can be used in WHERE clauses or most SQL functions, and both are unsupported by a growing number of newer Oracle features. If a table you're clustering — hash or index — still carries a LONG or LONG RAW column for large text or binary data, converting it to CLOB or BLOB is worth doing as part of the same modernization pass. For more detail on this migration, see Migrating LONG and LONG RAW to LOB Types.

If you wanted to create a hash cluster for the LOT table, base it on the value of the LOT_ID column, 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);

Note that HASHKEYS 100 rounds up to 101, while MOD(lot_id, 100) can only ever produce values 0 through 99 — so one hash slot in this cluster will simply go unused. That's harmless here since the wasted slot costs a little space, not correctness, but it's worth knowing when sizing HASHKEYS around a fixed-range function like MOD: matching HASHKEYS to a value the function can actually produce (99, which rounds to 101 all the same, or adjusting the MOD divisor) avoids reserving space that can never be used.