SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)
Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
4-3
Understanding SQL/MX Table and File Structures
Each SQL/MX table and index, and each partition of a table or index, is implemented
as a pair of Guardian physical files representing:
A data fork, where the user data resides
A resource fork, where the runtime metadata resides
If a table or index is not partitioned, then it has just one data fork and one resource
fork.
Table and index files are managed by the SQL/MX file system and are accessed
implicitly through the DP2 disk process. When a table or index is dropped, both its data
fork and resource fork are also dropped.
The data you insert into tables and views is stored in these underlying files. Tables and
indexes are associated with their corresponding physical files through entries in the
system metadata. Views are associated with physical files through their underlying
tables.
When you create a table, you establish the characteristics of the underlying file by
specifying them as parameters in the CREATE TABLE statement. That is, you create
the file implicitly through this statement rather than directly through an explicit
file-creation statement.
Data transfers to and from the files are done in terms of logical records and key fields
within those records. Each file has a unique clustering key associated with it, which
contains a unique value used to order and identify records in the file.
The large block support feature enables you to create tables on physical disk blocks of
size 32768 bytes (32 KB) instead of the default size of 4096 bytes (4 KB). You can
specify the disk block size, while creating a table, using the CREATE TABLE
command. Increasing the disk block size allows packing more records in each disk
block and is convenient for reading multiple records sequentially. However, the large
block support feature might not be useful for random access, where only one or a few
records are accessed at a time. Therefore, you must select a disk block size
depending on the type of most frequently used queries to the database. By default, the
table is created with 4096 bytes (4 KB) disk block size.
SQL/MX tables use the key-sequenced file structure. Other table and file structure
types are not supported by NonStop SQL/MX.
Types of Keys Used in SQL/MX Tables and Indexes on page 4-4 describes the key
types used with SQL/MX tables and indexes. The Key-Sequenced File Structure on
page 4-5 discusses the structure of key-sequenced files. An understanding of the
key-sequenced file structure can help you plan the best use of disk storage space
when sizing your SQL/MX database, implementing economical table-access methods,
and analyzing various performance trade-offs. This understanding is also essential for
anyone using the FCHECK utility to operate on physical file structures.