Introduction to NonStop SQL/MP

Physical Database Structure
NonStop SQL Architecture
113425 Tandem Computers Incorporated 3–3
Moreover, the data dictionary keeps track of all programs that use the database objects
described in the catalog and file labels. The catalog is sometimes called the compile-
time component of the dictionary because, when programs are SQL-compiled, their
relationship to the database objects they access is recorded in the catalog. The file
labels are called the run-time component of the dictionary because, when the
programs execute, NonStop SQL/MP can access the file labels to find the current
database information required to execute the SQL access plans. If the logical or
physical structure is changed, NonStop SQL/MP can, if requested, recompile the SQL
access plans to execute efficiently in the altered environment.
Table Organization When you create a table in NonStop SQL/MP, you specify both logical and physical
attributes. Logical attributes include column names and data types. Physical
attributes include block size, maximum extents, and the physical organization of the
file.
The physical organization determines the structure of the table’s underlying files. The
physical files can be organized in one of three ways: key-sequenced, relative, or entry-
sequenced. The organization determines how rows are stored in the physical file.
Each row must be identified by a unique value, called its primary key.
Your requirements for accessing and updating data determine which organization is
best for a particular table.
Key-Sequenced Tables
Key-sequenced is the most commonly used table organization (and the default). When
you create a key-sequenced table, you define the primary key as one or more columns
in the table; the values in the key columns determine the order in which rows are
stored. A typical key might be the employee number, social security number, or job
code. You can easily access a row if you know the value of the key. You also can
easily retrieve data in the same order as the key.
Key-sequenced organization is best suited for random-access processing, in which the
end-user or application accesses rows based on external criteria, not on the row order.
This organization type is also efficient for sequential access. The EMPLOYEE table
(shown in Figure 2-1) would probably use key-sequenced organization because each
employee has a unique employee ID, and users typically access the rows in random
order.
If the information you want to organize in a table does not have a unique key value,
you can create a key-sequenced table with a clustering key. A clustering key is one or
more columns (such as LAST_NAME, FIRST_NAME) whose values determine the row
sequence but do not uniquely identify rows. Clustering permits related rows of a table
to be stored physically close to one another, which allows the system to retrieve them
quickly. To identify each row, NonStop SQL/MP appends a unique eight-byte value
to the end of the clustering key.