SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-40
Database Design Guidelines for Improving OLTP
Performance
°
Align UCS2 character types on two-byte boundaries.
°
Align numeric data types as follows:
°
Align smallint on two-byte boundaries.
°
Align int on four-byte boundaries.
°
Align largeint on eight-byte boundaries.
°
Use fillers as required to attain proper alignment:
°
Recommended: CREATE TABLE t1, where column a is SMALLINT NOT
NULL, c is SMALLINT NOT NULL, and b is INT NOT NULL.
°
Not recommended: CREATE TABLE t1, where column a is SMALLINT
NOT NULL, b is INT NOT NULL, and c is SMALLINT NOT NULL.
°
Unaligned fields cannot choose bulk move.
•
Avoid using signed numeric data types. Signed numbers are expensive to process
and expensive to encode for key access. Unsigned numbers do not need
encoding.
•
Avoid using DECIMAL data types when scale and precision are not needed. For
example, use INT instead of NUMERIC(8,0).
•
Use numbers instead of characters whenever possible. For example, an INT field
is better than a CHAR(8) field for numeric employee IDs.
•
Use VARCHARs carefully:
°
Length processing of VARCHARS is expensive.
°
Use fixed character data types if maximum data length is “small.”
°
Use VARCHARS if the variance of data size is large.
•
Do not put character fields between noncharacter fields.
°
The C/C++ preprocessor generates an extra null terminator.
°
Bulk move degradation results.
•
Use datetime fields carefully:
°
Datetime operations are expensive.
°
Use datetime fields if datetime arithmetic is needed in an SQL query (for
example, where date-col + interval '1' day > date '2001-09-
11').
°
Use LARGEINT or CHAR if datetime is needed for storage and retrieval only.
•
Where possible, avoid adding columns to a table:
°
Added columns turn off internal expression optimizations.
°
If you add a column, reload the table.