SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

The Key-Sequenced File Structure
All SQL/MX tables are key-sequenced files, which store rows (records) that contain a clustering
key. New rows are stored in sequence by clustering key value. A user performing update operations
can update or delete rows and lengthen or shorten values in a varying-length column (VARCHAR,
NCHAR VARYING) when the column is not part of the clustering key.
Rows are stored in a key-sequenced file logically in ascending or descending order, according to
their clustering key values, as defined by the use of the ASCENDING or DESCENDING specification
in the CREATE TABLE statement.
For more information, see the SQL/MP Installation and Management Guide and the SQL/MP
Reference Manual.
Types of Key-Sequenced File Access
You can access key-sequenced files either sequentially or randomly. Sequential access is preferable,
for example, when generating a report of the quantity on hand of all parts in an inventory file.
Random access is preferable when you want to identify the vendor of a particular part. The SQL/MX
optimizer determines whether sequential or random access is performed.
When NonStop SQL/MX reads a key-sequenced file by its clustering key, each read operation
retrieves the record containing the next sequentially higher key value. When NonStop SQL/MX
reads the file through an alternate index, each operation randomly accesses the table.
If you do not use an index, access occurs by the clustering key. Access can begin with the first
record in the file or can be requested for only a specified range of records in the file.
Key-Sequenced Tree Structure
Key-sequenced files are physically organized as one or more bit-map blocks and a B-tree structure
of index blocks and data blocks. Bit-map blocks within a structured file organize the file’s free
space.
Figure 1 (page 32) illustrates a sample tree structure for a key-sequenced file.
The Key-Sequenced File Structure 31