Neoview SQL Reference Manual (R2.4 SP2)

Keys
Neoview SQL supports these types of keys:
• “Clustering Keys”
• “Hash Partition Keys” (page 260)
• “Index Keys”
• “Primary Keys”
Clustering Keys
Every table has a clustering key, which is the set of columns that determine the order of the rows
on disk. Neoview SQL organizes records of a table or index by using a b-tree based on this
clustering key. Therefore, the values of the clustering key act as logical row-ids.
Hash Partition Keys
Partitioned tables have a partitioning key (PARTITION BY clause), chosen from the columns of
the clustering key which Neoview SQL uses to distribute rows to different disks. The list of
columns defined in the partitioning key is used to determine the hash partition key.
The hash partition key has the following characteristics:
• Multiple columns can be chosen for the hash partition key (composite key)
• The values of these columns are used to determine placement.
• The columns are part of the clustering key.
• Neoview SQL recommends that you use the minimal number of columns for the hash
partition key.
Index Keys
There is always a one-to-one correspondence between index rows and base table rows.
Each row in a Neoview SQL index contains:
• The columns specified in the CREATE INDEX statement
• The clustering (primary) key of the underlying table (the user-defined clustering key)
For a unique index, the clustering key of the index is composed of the first of these items. The
clustering key of the index cannot exceed 2048 bytes, but the entire row (including the clustering
key of the index) can contain up to 4K bytes.
For a nonunique index, the clustering key of the index is composed of both items. The clustering
key cannot exceed 2048 bytes. Because the clustering key includes all the columns in the table,
each row is also limited to 2048 bytes.
For varying-length character columns, the length referred to in these byte limits is the defined
column length, not the stored length. (The stored length is the expanded length, which includes
two extra bytes for storing the data length of the item.)
See “CREATE INDEX Statement” (page 60).
Primary Keys
A primary key is the column or set of columns that define the uniqueness constraint for a table.
The columns cannot contain nulls, and there is only one primary key constraint on a table.
260 SQL Language Elements