12.9 Specifying a cluster key
A cluster key is a column that is specified as the key for storing rows in ascending or descending order of the specified column values. If a cluster key is specified for one or more columns in a table, the table rows can be stored in ascending or descending order of the values in the cluster key column(s).
When a cluster key is specified for a table, an index is created automatically for the specified column(s).
- Organization of this section
- (1) Effects of specifying a cluster key
- (2) Criteria
- (3) Specification
- (4) Design considerations
- (5) Notes
(1) Effects of specifying a cluster key
The effects of specifying a cluster key for a table are discussed as follows.
- Improved performance
- Input/output time can be saved when retrieving, updating, or deleting rows with a range specified or when retrieving or updating rows on the basis of the cluster key values.
- Improved operability
- If you define a cluster key with UNIQUE specified, the uniqueness and NOT NULL constraints apply to the cluster key. In this case, when rows are inserted, no duplicated value is allowed in any row in the cluster key column. Note that you cannot define a cluster key with UNIQUE specified for a table partitioned by flexible hash partitioning.
- If you define a cluster key with PRIMARY specified, the uniqueness and NOT NULL constraints apply to the cluster key. In this case, when rows are inserted, no duplicated value is allowed in any row in the cluster key column. Additionally, no null value can be stored in any of the columns that constitute the cluster key. Note that you cannot define a cluster key with PRIMARY specified for a table partitioned by flexible hash partitioning.
- When creating a table, you can use the database load utility (pdload) to determine whether the input data is arranged in ascending or descending order of the cluster key values.
- When reorganizing a table, you can use the database reorganization utility (pdrorg) to determine whether the unloaded cluster key matches the cluster key to be reloaded.
(2) Criteria
The cluster key should be specified in the following cases:
- Many applications accumulate and access data in ascending or descending order of the key values.
- Then table's keys will not be changed.
- The table has fixed-length rows.
(3) Specification
To define a cluster key for a table, specify the CLUSTER KEY option in the CREATE TABLE definition SQL statement.
(4) Design considerations
To improve retrieval efficiency after data is added, some unused space should be set in the pages containing the table. For details about how to set space in the pages containing the table, see 14.3 Pages.
(5) Notes
- Explanation:
- Data with C in the cluster key column is added.
- There is overhead involved in searching for the key values on either side of the C column.