Bad Hash Clusters

Hash clusters are a great way to reduce IO on some tables, but they have their downside.

Oracle provides some reasonable advice on selecting the size per cluster key and number of hash keys. See the Oracle online doco SQL Reference manual, CREATE CLUSTER statement.

How can you tell if your cluster is inefficient?

You need to know two things: the distribution of your data, and the config settings of your cluster.

Run the following SQLs:

In the first SQL, Oracle reserves key_size bytes for each hash key, and hashkeys hash keys. ie. The minimum table size is therefore key_size x hashkeys bytes.

In the second SQL(s), keycount is the number of different cluster keys, mean is the average number of rows per cluster key, and stddev is the standard deviation of the number of rows per cluster key.

©Copyright 2003