Concatenated Cluster Keys

Oracle permits up to 16 columns in the cluster key of a hash cluster. However, to perform a Hash Cluster Scan, a SQL must include equals predicates on all key columns in separate AND clauses.

Consider the following examples on table trans_hist, which is clustered on cust_id and trans_mth:

The first example satisfies all conditions for performing a hash cluster scan.

The second example uses trans_mth in a range comparison (BETWEEN), so it will not perform a cluster scan. Note that if the cluster was an Index Cluster, or the same two columns were simply indexed, then Oracle would be able to perform an Index scan.

If trans_mth can contain only discrete integer values, then the third example is the same as the second, except Oracle is able to perform a hash cluster scan.

The fourth example does not use the entire cluster key, so it cannot perform a hash cluster scan

As these exapmles demonstrate, hash clusters are far less versatile than indexes. Read the Oracle documentation carefully before you implement a hash cluster, as indexes may be more appropriate to your situation.

©Copyright 2003