Clustering Tables   «Prev  Next»

Lesson 5 Creating Oracle Cluster Key
ObjectiveCreating a cluster key

Creating Oracle Cluster Key

The third step in creating a cluster is creating a cluster index. A cluster index is an index structure that contains the values of the cluster key, the column or columns that link the tables in the cluster together. The cluster index is more than just a way to optimize retrieval. If you have a cluster, you must have a cluster index because of the way data is accessed through a cluster. The order for getting the data is to go to the cluster index, find the cluster value, and then go directly to the first data block on the disk where all the rows with that value reside. If there is no cluster index, this chain is broken. If you drop a cluster index, you effectively remove the data in the cluster from the database because users can not get to the data without an index. In addition, a cluster index differs from a standard index in that a cluster index contains a single entry for each value in the index, not for each row in the underlying table.

SQL Syntax for creating Cluster Index

The SQL syntax for creating a cluster index is:
  1. Required keywords.
  2. The name of the index.
  3. Required keywords.
  4. The name of a pre-existing cluster.
CREATE INDEX index_name 
ON CLUSTER cluster_name

Create Index Cluster
You do not have to list any of the columns in the cluster key, because they have already been identified in the CREATE CLUSTER command. You can have additional clauses on the CREATE INDEX statement for a cluster to specify additional attributes like the tablespace for the index, just as you would for a normal index. To create a cluster index for a cluster named coin_lot, you would use the following SQL statement: The next lesson introduces a special type of cluster called a hash cluster.

CREATE INDEX coin_lot_idx 
ON CLUSTER coin_lot;

Creating Cluster Key - Quiz

Click the Quiz link below to answer a few questions about clusters and cluster indexes.
Creating Cluster Key - Quiz