NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
I-9
Index Keys
•
The following directive specifies version 310 for all SQLCA structures declared
later in the compilation unit, but version 315 for all SQLDA and SQLSA structures
declared later in the compilation unit:
EXEC SQL INCLUDE STRUCTURES SQLCA VERSION 310
SQLDA VERSION 315
SQLSA VERSION 315;
Index Keys
An index is stored in a key-sequenced file. Each row in an index contains:
•
A two-byte column called the “keytag” column
•
The columns specified in the CREATE INDEX statement
•
The primary key of the underlying table (the user-defined primary key, the
SYSKEY, or combination of the clustering key and the SYSKEY)
For a unique index, the primary key of the index is composed of the first two of these
items. The primary key of the index cannot exceed 255 bytes, but the entire row
(including the primary key of the index) can contain up to 510 bytes.
For a nonunique index, the primary key of the index is composed of all three items. The
primary key cannot exceed 255 bytes. Because the primary key includes all the columns
in the table, each row is also limited to 255 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.)
The keytag value must be unique among indexes for the table; you can specify it when
you create the index with the CREATE INDEX statement, or you can allow the system
to generate it for you. (System-generated keytags are sequential numbers, beginning
with one. User-specified keytag values can be either two bytes of character data or a
SMALLINT UNSIGNED value in the range 1 through 65535. The keytag value for the
primary key is 0.)
There is always a one-to-one correspondence between index rows and base table rows.
You should typically use random access to access index rows. Sequential access is less
efficient for large subsets of rows unless SQL can use index-only access. For more
information, see the NonStop SQL/MP Query Guide.