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:
SELECT * FROM trans_hist WHERE cust_id = 1001 AND trans_mth = 200506 SELECT * FROM trans_hist WHERE cust_id = 1001 AND trans_mth BETWEEN 200506 AND 200508 SELECT /*+ USE_CONCAT*/ * FROM trans_hist WHERE cust_id = 1001 AND trans_mth IN (200506, 200507, 200508) SELECT * FROM trans_hist WHERE cust_id = 1001
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.
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.