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

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Installation and Management Guide—544536-007
4-6
Types of Key-Sequenced File Access
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 information about the file structures of SQL/MP tables, 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 4-1 illustrates a sample tree structure for a key-sequenced file.