Oracle10g Overview of Hash Clusters
In a hash cluster, a row is stored after a hash function has
been applied to the row’s cluster key value. All rows with the
same key value are stored together on the disk.
It is preferable to use hash clusters over an indexed table or
index cluster when a table is queried frequently with equality
queries. For example, to return all rows for “salesman_id of 10”
and other such queries, the specified cluster key value is hashed.
The resulting hash key value points directly to the area on disk
that stores the rows. In order to improve the hash clusters,
Oracle 10g Database introduces the sorted hash cluster.
In a sorted hash cluster, the rows corresponding to each value
of the hash function are sorted on a specified set of columns in
ascending order. When the rows are inserted into a sorted hash
clustered table, they are placed in cluster blocks in the order
specified. This then becomes the default return order when the
query uses the hash columns.
By using sorted hash clusters, you can avoid using the ORDER BY
clause, as well as sort operations. When you are accessing a
single row of data by a hashed column, sorted hash clustered
tables offer faster data retrieval than index-organized tables (IOTs).
The query response time is independent of the size of the table,
since the hash function determines the hash key. The response for
querying a sorted hash clustered table depends on the number of
rows sharing the same hash key and not the number of rows in the
table.
How sorted hash clusters perform depends on whether or not
you’ve used the right number of hash functions, number of keys,
size of the hash keys, and the sort columns. |
