Deciding which Tables to Cluster

The most important step in creating a cluster is deciding which table or tables to cluster, and how. Although a clustered table can deliver a terrific performance improvement in the right circumstances, a poorly chosen cluster can decrease performance. Once you have properly selected the table or tables you wish to cluster, you must go through a three-step process to create this database structure.

A cluster is a schema object that stores the data for one or more tables together in the same physical data blocks, grouped by a shared cluster key value. In a non-clustered table, Oracle stores each table's rows in its own segment, so retrieving related rows from two different tables — say, an order header and its order detail lines — usually means reading blocks from two separate locations on disk. In a cluster, rows that share the same key value are physically stored near each other, sometimes in the very same block, regardless of which table they belong to. For queries that repeatedly join tables on that key, this can significantly cut down on I/O, since a single block read can satisfy rows from multiple tables at once.

That benefit is not free. Clustering trades off write performance and flexibility for read performance on a specific access pattern. Deciding whether a table belongs in a cluster — and if so, which other tables should join it there — is the central design decision this lesson covers.

Oracle supports two cluster storage mechanisms, and choosing between them is part of deciding "how" to cluster a table, not just "which" table to cluster.

An index cluster stores rows keyed by cluster key value and relies on a separate cluster index to locate the data blocks for a given key. This is the default cluster type, and it is a reasonable choice when the cluster key values are not known in advance, when new key values are added unpredictably, or when the table is queried with range conditions on the cluster key rather than pure equality lookups.

A hash cluster stores rows by applying a hash function to the cluster key value, and uses the resulting hash value to compute the data block location directly — no separate index lookup is required. Hash clusters can outperform index clusters for equality lookups on the cluster key, precisely because there is no index to traverse. They work best when the number of distinct key values (or a reasonable estimate of it) is known ahead of time, since that estimate feeds the HASHKEYS setting used when the cluster is created.

CREATE CLUSTER cluster_name (column_name Datatype)
    SIZE size_value
    STORAGE clause;

A hash cluster uses the same CREATE CLUSTER statement, with the addition of a HASHKEYS clause that tells Oracle how many distinct hash values to plan for:

CREATE CLUSTER cluster_name (column_name Datatype)
    SIZE size_value
    HASHKEYS hash_value
    STORAGE clause;

HASHKEYS should be set to your best estimate of the number of distinct cluster key values the table will hold. Oracle rounds this value up to the nearest prime number internally and allocates space accordingly, so it is worth erring on the side of a reasonable estimate rather than an arbitrary round number. Underestimating leads to hash collisions and degraded lookup performance; overestimating wastes storage.

The CREATE CLUSTER statement has an optional clause, SIZE, which is the estimated number of bytes required by an average cluster key and its associated rows. The database uses the SIZE parameter when performing the following tasks:

1. Estimating the number of cluster keys (and associated rows) that can fit in a clustered data block
2. Limiting the number of cluster keys placed in a clustered data block. This maximizes the storage efficiency of keys within a cluster.

SIZE does not limit the space that can be used by a given cluster key. For example, if SIZE is set such that two cluster keys can fit in one data block, any amount of the available data block space can still be used by either of the cluster keys.
By default, the database stores only one cluster key and its associated rows in each data block of the cluster data segment. Although block size can vary from one operating system to the next, the rule of one key for each block is maintained as clustered tables are imported to other databases on other machines. If all the rows for a given cluster key value cannot fit in one block, the blocks are chained together to speed access to all the values with the given key. The cluster index points to the beginning of the chain of blocks, each of which contains the cluster key value and associated rows. If the cluster SIZE is such that more than one key fits in a block, blocks can belong to more than one chain.

A cluster can hold rows from a single table, or from several tables that share a common key — this second case is often called a join cluster. The decision of which tables belong together in one cluster comes down to how the tables are actually queried.

A single-table cluster still provides a benefit when a table is heavily accessed by equality lookups on its cluster key, since rows sharing a key value are packed tightly together, reducing the blocks needed to satisfy the query.

A join cluster is worth considering when two or more tables are consistently joined together on the same key in your application's queries — a parent/child relationship like an order and its line items, for example. Storing both tables' rows for a given key in the same physical blocks means a single read can satisfy both sides of the join, instead of Oracle fetching from two separate table segments. Tables that are rarely or never joined on that key gain little from being placed in the same cluster, and should generally be left as ordinary heap tables instead.

Clustering is a targeted optimization, not a general-purpose one, and applying it to the wrong table can hurt performance rather than help it. A few practical guidelines:

Clustering tends to help when: the table is frequently queried by equality or range conditions on the cluster key; the table is frequently joined to other tables on that same key; and the cluster key values change infrequently once a row is written.

Clustering tends to hurt when: the table receives a high volume of INSERT activity, since Oracle must locate the correct data block for the cluster key on every insert rather than simply appending to the end of a segment; the table is frequently scanned in full without reference to the cluster key, since a full table scan against a multi-table cluster must skip over rows belonging to other tables in the same blocks; or the cluster key values are updated often, since changing a row's cluster key value can require physically relocating that row.

In short: cluster the tables whose read patterns justify the write and maintenance cost, and leave everything else as ordinary tables.

Once you create a cluster, you then create the table or tables that the cluster will contain.
The syntax for creating tables that are a part of a cluster is exactly the same syntax that is used for non-clustered tables, with one exception. The final clause in the CREATE TABLE statement is:

CLUSTER cluster_name (column_name)

The cluster_name is the same name that was given to the cluster in the CREATE CLUSTER command. The column_name is a list of columns in the table being created that match up with the columns in the already created cluster.

The final step, for an index cluster, is to create a cluster index. You will learn to do this in Lesson 5 of this module. A hash cluster does not require this step — its rows are located by computing the hash value directly, so no separate cluster index is built.

The Datatype restriction above excludes LONG and LONG RAW from cluster key columns, and this restriction is really a symptom of a broader, older limitation: both datatypes are largely superseded in modern Oracle database design. LONG and LONG RAW predate Oracle's LOB (Large Object) datatypes, and Oracle has recommended migrating away from them for years — a table can contain at most one LONG or LONG RAW column, they cannot be used in WHERE clauses, GROUP BY clauses, or most SQL functions, and they are unsupported in a growing number of newer features.

If a table you are considering for clustering still uses LONG or LONG RAW for large text or binary data, converting those columns to CLOB or BLOB first is worth doing as part of the same modernization pass, rather than working around the limitation indefinitely. For more detail on this migration, see Migrating LONG and LONG RAW to LOB Types.

The following simplified code is an example of creating an index cluster and the tables it will contain:

CREATE CLUSTER orders (order_id NUMBER) SIZE 512 K;
CREATE TABLE order_header( order_number NUMBER,
 customer_number NUMBER)
 CLUSTER orders (order_number);
CREATE TABLE order_detail(
 order_number NUMBER,
 detail_line VARCHAR2(100))
 CLUSTER orders (order_number);

A hash cluster covering the same tables would add the HASHKEYS clause, sized to the expected number of distinct order numbers:

CREATE CLUSTER orders_hash (order_id NUMBER)
    SIZE 512 K
    HASHKEYS 5000;
CREATE TABLE order_header( order_number NUMBER,
 customer_number NUMBER)
 CLUSTER orders_hash (order_number);
CREATE TABLE order_detail(
 order_number NUMBER,
 detail_line VARCHAR2(100))
 CLUSTER orders_hash (order_number);

The next lesson explains how to size a cluster properly.