Clustered Tables in Oracle

Clustered Tables
If two or more tables are frequently accessed together (for example, an order table and a line item
detail table), then creating a clustered table might be a good way to boost the performance of
queries that reference those tables. In the case of an order table with an associated line-item detail
table, the order header information could be stored in the same block as the line-item detail

records, thus reducing the amount of I/O needed to retrieve the order and line-item information.
Clustered tables also reduce the amount of space needed to store the columns the two tables
have in common, also known as a cluster key value. The cluster key value is also stored in a
cluster index. The cluster index operates much like a traditional index in that it will improve
queries against the clustered tables when accessed by the cluster key value. In our example with
orders and line items, the order number is only stored once, instead of repeating for each lineitem
detail row.
The advantages to clustering a table are reduced if frequent insert, update, and delete
operations occur on the table relative to the number of select statements against the table. In
addition, frequent queries against individual tables in the cluster may also reduce the benefits
of clustering the tables in the first place.
Hash Clusters
A special type of clustered table, a hash cluster, operates much like a regular clustered table,
except that instead of using a cluster index, a hash cluster uses a hashing function to store and
retrieve rows in a table. The total estimated amount of space needed for the table is allocated
when the table is created, given the number of hash keys specified during the creation of the
cluster. In our order-entry example, let’s assume that our Oracle database needs to mirror the
legacy data-entry system, which reuses order numbers on a periodic basis. Also, the order number
is always a six-digit number. We might create the cluster for orders as in the following example:
create cluster order_cluster (order_number number(6))
size 50
hash is order_number hashkeys 1000000;
create table cust_order (
order_number number(6) primary key,
order_date date,
customer_number number)
cluster order_cluster(order_number);
Hash clusters have performance benefits when you select rows from a table using an equality
comparison, as in this example:
select order_number, order_date from cust_order
where order_number = 196811;
Typically, this kind of query will retrieve the row with only one I/O if the number of hashkeys
is high enough and the hash is clause, containing the hashing function, produces an evenly
distributed hash key.