SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-4
Primary Keys
nonunique clustering key. Using these nonunique key values, a user can scan the file
more efficiently and can also create partitions.
The combined length of the clustering key, not including the appended SYSKEY
column, cannot exceed 247 bytes. Columns in the clustering key definition cannot be
updated.
Clustering keys have the same performance implications as primary keys, as
described next under Key Levels.
Key Levels
SQL/MP keys (including index keys) can be classified in three levels. The three levels
are associated with three different levels of performance related to the overhead
associated with the use of the keys, as follows:
•
Level 1 keys have the best performance because these keys have the least
overhead. Level 1 keys have these column characteristics:
°
Columns that are contiguous, have ascending values belonging to the ASCII
collating sequence, and are stored at fixed offsets
°
Combined columns defined with numeric or character data types
°
Columns with an UNSIGNED numeric data type (DECIMAL, NUMERIC,
SMALLINT, INTEGER, PIC 9 COMPUTATIONAL, PIC 9 DISPLAY, DATE,
TIME, or DATETIME)
°
Columns defined as having an ASCII data type (CHARACTER, PIC X,
DECIMAL, or PIC 9 DISPLAY)
For example, keys defined with these columns are level 1 (ASCENDING is the
default):
KEY-1 NUMERIC (4.2) UNSIGNED, SMALLINT UNSIGNED
KEY-2 CHARACTER (8), DECIMAL(8) UNSIGNED
KEY-3 SMALLINT UNSIGNED, CHARACTER (24)
KEY-4 CHARACTER (24), DATETIME
•
Level 2 keys have performance comparable to those of level 1, but with a small
amount of additional overhead. Level 2 keys have these column characteristics:
°
Columns that are contiguous, either all ascending or all descending, and with a
fixed offset
°
Columns with a SIGNED numeric data type (DECIMAL, NUMERIC, INTEGER,
SMALLINT, LARGEINT, PIC S9 COMPUTATIONAL, PIC S9 DISPLAY,
DATETIME, INTERVAL, or FLOAT)
°
Contiguous columns that are defined as numeric data but with mixed signed
and unsigned columns
°
Combined columns that include at most one VARCHAR column, which cannot
be the first column of the key