SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-9
Entry-Sequenced File Structure
Each data block contains a header, plus one or more data records, depending on the
record size and data-block size. For each data block, an entry in an index block
contains the value of the key field for the first record in the data block and the address
of that data block.
The position of a new record inserted into a key-sequenced file is determined by the
value of its primary-key field. If the data block is determined to be full when SQL/MP
attempts to insert a new record into it, a block split occurs: the disk process allocates a
new data block, moves part of the data from the old block into the new block if not at
the end of the file, and gives the index block a pointer to the new data block.
Key-sequenced files are also used to store indexes. When an index block fills up, it is
split in a similar manner: a new index block is allocated, and some of the pointers are
moved from the old index block to the new one. The first time a split occurs in a file, the
disk process must generate a new level of indexes. The disk process does this by
allocating a higher-level index block containing the low key and a pointer to the two or
more lower-level index blocks, which in turn point to many data blocks. The disk
process must do this again each time the highest-level block is split.
The disk process can perform a three-way block split, when appropriate, creating two
new blocks and distributing the original block’s data and pointers plus the new record
or pointer among all three.
In a key-sequenced file, data blocks are chained together with forward and backward
pointers stored in the header of each block. Data records within each block are stored
in key-value sequence. This storage arrangement enhances the sequential
performance for tables stored in key-sequenced files. Index blocks, however, are not
chained.
Uses of Key-Sequenced Tables
A good example of the use of key-sequenced files in an application environment is an
inventory file in which each record describes a part. The primary key field for that file
would probably be the part number, and the file would be ordered by part number. The
part numbers should be readily distinguishable from one another. Other fields in the
record would contain such information as vendor name, quantity on hand, and so forth,
and one or more of these fields could be used as index keys.
Another example of key-sequenced file use is the storage of indexes. Each index is
stored in a separate SQL index file and is named after the file. An index file has a
primary key that includes the indexed columns and, for nonunique indexes, the
columns of the primary key of the table being referenced.
Entry-Sequenced File Structure
Entry-sequenced files are designed for sequential access. They consist of variable-
length records. New records are always appended to the end of the file; as a result, the
records in the file are arranged physically in the order in which they were added to the
file. Users can update existing records but cannot delete them. Update operations,
however, cannot increase or decrease the lengths of existing records.