Pathmaker Programming Guide

Using Clustering Keys
Defining Data for a NonStop SQL Pathmaker Application
067868 Tandem Computers Incorporated C–9
Using Clustering Keys A clustering key is a group of columns that forms a nonunique key for a key-
sequenced table. To define a clustering key, you specify one or more columns in the
CLUSTERING KEY clause of the CREATE TABLE statement and, optionally, a sort
order for each column. You use a clustering key instead of using a primary key for a
key-sequenced table.
Clustering keys are used for performance reasons:
Clustering keys determine the physical ordering of rows in a table.
Tables that have a clustering key can be partitioned by the clustering key value.
The primary index for a table with a clustering key consists of the columns of the
clustering key followed by a SYSKEY. In effect, the clustering key allows you to
specify that the rows of a table will be physically arranged on the disk sorted by a list
of columns that form part of, but are not entirely, the primary key of the table. Queries
to select a range of rows from a table based on the clustering key’s columns will
perform efficiently.
You can create NonStop SQL tables that contain clustering keys and use them in a
NonStop SQL Pathmaker application. Pathmaker DB requesters will:
Treat the columns of the clustering key plus the SYSKEY as the primary key of the
table.
Treat the columns of the clustering key plus the SYSKEY as the primary key for a
protection view. For a Pathmaker DB requester, protection views must contain
every column of the clustering index plus the SYSKEY.
Treat the SYSKEY column as an inefficient unique index for base tables and
protection views that have clustering keys. (You can read a table along the
SYSKEY; however, this read will not be an efficient operation because the rows are
physically ordered by clustering keys, not by the SYSKEY.)
Mark the first column of a clustering key with an asterisk.