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.

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;

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