Empty Hash Cluster

A hash cluster is like a wall of Post Office Boxes, or pigeon holes. In a regular table, a row can be placed in any old block, but in a hash cluster a row belongs in a specific block based on its cluster key (one or more columns of the table).

When you create a hash cluster, you must nominate how much space to reserve for each hash key (ie. the size of the pigeon hole) and how many hash keys to allow (the number pigeon holes). The minimum size of the hash cluster is therefore the size attribute multiplied by the hashkeys attribte. eg. A hash cluster with 2Kb per hash key, and 100,000 hash keys will have an initial size of 200Mb.

Oracle sets the High Water Mark of the cluster to this pre-allocated size. Like the High Water Mark problem in regular tables, this creates problems if the hash cluster is empty or under-utilised. A full table scan must read every block in the cluster, not just those blocks containing rows. In the example above, even if the cluster is empty, the SQL SELECT * FROM my_clust_table will take some time because it must read the entire 200Mb

The solution is to avoid full table scans completely, or create your cluster at a sensible size and fill it up. This means that if the table/cluster is continually growing then the DBA will have to regularly rebuild it to maintain its efficiency. If you are performing regular full table scans, then perhaps a Hash Cluster is the wrong structure for you. You might be better off with an indexed cluster or a regular old table and index.

©Copyright 2003