SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-5
Primary Keys
For example, keys defined with these columns are level 2 (SIGNED is the default):
KEY-1 INTEGER
KEY-2 CHARACTER (30) DESCENDING
KEY-3 CHARACTER(2), NUMERIC(8), DECIMAL(4), VARCHAR(20)
KEY-4 INTEGER, DATETIME, INTERVAL
•
Level 3 keys include other possible keys not included in levels 1 or 2. Level 3 keys
have the poorest performance because the overhead for handling these keys by
the primary disk process is approximately 10 to 20 percent greater than that of
level 2 keys. (Note, however, that overall transaction overhead, or response time,
does not increase by 10 to 20 percent.) Level 3 keys have these column
characteristics:
°
Noncontiguous columns
°
A leading VARCHAR column, which causes a nonfixed initial offset
°
Multiple VARCHAR columns
°
Columns with ASCII data but with mixed ascending and descending orders
°
Columns that use collations
For example, keys defined with these columns are level 3:
KEY-1 DECIMAL (8) ASCENDING, DECIMAL (8) DESCENDING
KEY-2 VARCHAR (20)
KEY-3 Column A, D, F <--Indicates that the columns
are not contiguous in the table
The performance of level 3 keys is the poorest because the overhead for handling
these keys by the primary disk process is approximately 10 to 20 percent greater
than that of level 2 keys. (Note that overall transaction overhead, or response time,
does not increase by 10 to 20 percent.)
In a key-sequenced table, the data types of the columns in the user-defined primary
key or clustering key (a subset of the primary key; part of a combination key) define the
key level. Different key levels require increasing system overhead for processing;
therefore, the keys affect performance.
The key levels and performance implications also apply to indexes, and indexes can
be created on tables of all three organizations. After being created, the index is like a
Note. As an exception to the preceding key-level guidelines, a key column that can be
defined with either numeric or character data type should typically be defined as numeric.
This approach is especially important if the column is used in a predicate. The numeric
data type helps achieve optimum performance, because SQL/MP computes the selectivity
for numeric columns more efficiently than it does for character columns.
As an example, suppose that you want to use a key column that contains a telephone
number. Because this column is not used in calculations, you can create it as either a
numeric or character column; for best performance, you should choose a numeric column.