How-to: Oracle Syntax - Clusters and Hash Clusters

Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. This improves disk access time.

The related columns of the tables in a cluster are called the cluster key. The cluster key is indexed so that rows of the cluster can be retrieved with a minimum amount of I/O.

No matter how many tables within the cluster contain the cluster key value, it is stored only once each in the cluster and the cluster index. Therefore, less storage is required.

Whether or not a table is part of a cluster is transparent to users and to applications. Data stored in a clustered table is accessed by SQL in the same way as data stored in a nonclustered table.

Hash Clusters cluster table data in a manner similar to normal, index clusters.

To find or store a row in a hash cluster, Oracle applies a hash function to the row's cluster key value. The resulting hash value corresponds to a data block in the cluster.
All rows with the same key value are stored together on disk.

Hash clusters are a better choice than using an indexed table or index cluster when a table is often queried with equality queries (for example, WHERE product_id=123). For 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.

This reduces the amount of I/Os that must be performed to locate and read/write a row of data.

"The continued use of cluster bombs has cost thousands of civilian lives, denied land to the poor and disenfranchised and is now costing the international community millions to eradicate the unexploded submunitions… ~ Rae McGrath



Copyright © 1999-2024
Some rights reserved