Creating a Cluster Key in Oracle

In Oracle, tables can be organized into clusters so that rows from different tables that share a common key value are stored together in the same data blocks. This lesson explains how to create the cluster key by building a cluster index, which is a required step for indexed clusters.

The cluster key is the column (or columns) that logically binds the tables in a cluster. Oracle uses this key to locate the physical data blocks that contain all rows associated with a given cluster value.

Role of the Cluster Index

The third step in creating an indexed cluster is creating the cluster index. A cluster index is an index structure that contains the values of the cluster key. Unlike a standard table index, a cluster index does not point to individual rows. Instead, it points to the first data block where all rows for a given cluster key value reside.

Because Oracle relies on the cluster index to locate clustered data, an indexed cluster must have a cluster index. If the cluster index is dropped, the data in the cluster remains physically present but becomes inaccessible to users because Oracle can no longer resolve the cluster key to a data block.

Another important distinction is that a cluster index contains exactly one entry per cluster key value, not one entry per row. This design is what enables multiple related rows to be retrieved efficiently with a single index lookup.

SQL Syntax for Creating a Cluster Index

CREATE INDEX index_name
ON CLUSTER cluster_name;

Creating Indexes After Loading Data

In many database environments, data is bulk-loaded into tables using tools such as SQL*Loader or Oracle Data Pump. For performance reasons, it is generally more efficient to create indexes after the data has been loaded.

If indexes exist before loading data, Oracle must update each index entry as every row is inserted, which significantly slows the load process. When an index is created after data insertion, Oracle performs a single sort operation to build the index structure.

Index creation requires sort space. Oracle allocates this space from memory and, when necessary, from temporary segments in the user’s temporary tablespace. In older releases, the SORT_AREA_SIZE parameter influenced memory allocation, but in modern Oracle releases this behavior is managed automatically through work area sizing.

Under certain conditions, Oracle supports direct-path loads where index creation can occur as data is loaded, but this is an advanced optimization scenario.

Indexes (Conceptual Review)

An index is a data structure that speeds up access to rows in a table. Indexes store column values in a compact, ordered structure that allows Oracle to locate rows with fewer I/O operations than a full table scan.

The basic syntax for creating a standard table index is shown below:

CREATE INDEX emp_idx1 ON emp (ename, job);

In this example, emp_idx1 is the index name, emp is the table, and ename and job are the indexed columns. Oracle automatically maintains index entries when table data changes.

Indexes store the ROWID for each indexed row, allowing Oracle to retrieve the corresponding table row in the most efficient way possible. Indexes can be unique or non-unique, and rows containing only NULL values in indexed columns are not included.

In Oracle terminology, an index refers to the physical structure, while a key refers to the logical value stored in that structure. This distinction becomes especially important when discussing cluster keys.

Creating the Cluster Key

When creating a cluster index, you do not specify the cluster key columns again. The cluster key was already defined when the cluster itself was created using the CREATE CLUSTER statement.

You may, however, include additional clauses on the CREATE INDEX statement, such as specifying a tablespace or storage attributes, just as you would for a standard index.

The following example creates a cluster index for a cluster named coin_lot:

CREATE INDEX coin_lot_idx
ON CLUSTER coin_lot;

Once this statement executes successfully, the cluster key becomes usable, and Oracle can efficiently retrieve all rows in the cluster that share the same key value.

The next lesson introduces a special type of cluster known as a hash cluster, which uses a different access strategy.

Creating Cluster Key – Quiz