Introduction to NonStop SQL/MP

Physical Database Structure
NonStop SQL Architecture
3–4 113425 Tandem Computers Incorporated
Relative Tables
Relative table organization is useful when an application requires random access to the
table and the row number can function as the key to the table. For relative tables,
NonStop SQL/MP automatically defines a primary key based on the relative positions
of the rows.
Typically, the row number has no meaning for the end user and is managed by the
application. Access to a specific row is very fast if you know the row number.
Because rows are stored relative to the beginning of the table, gaps in row numbers
cause gaps to exist in the physical file. However, newly inserted rows can reuse these
gaps.
You might use a relative table to store reservation records if they are listed by
confirmation number. The row numbers (the key) can then have the same values as
the confirmation numbers. Clerks can access a reservation directly by using the
confirmation number. If a clerk deletes a reservation, the space in the file used by that
reservation can be reused if someone reuses the confirmation number.
Entry-Sequenced Tables
Tables created using entry-sequenced organization are best suited for sequential
processing. In an entry-sequenced table, NonStop SQL/MP stores rows in the order in
which they are entered into the system. (NonStop SQL/MP automatically generates
the primary key value for the row address.) New rows in an entry-sequenced table are
appended to the end of the table. Typically, you use entry-sequenced tables for
transaction logs in which entries are inserted based on the time they occurred.
Indexes You can usually find a value faster in a primary-key column than in another table
column. If you often need to look up table values in a sequence different from the
primary-key sequence, you can create an index table. (The table on which an index is
based is called the base table.)
(In NonStop SQL/MP, the primary-key column is part of the structure of the base
table and is the clustering column for the table. Thus, the primary key is functionally
equivalent to what is called the primary index in some other SQL environments. All
other indexes in NonStop SQL/MP are separate files.)
Suppose, for example, that you want an alphabetical list of employees from the
EMPLOYEE table. An index based on the LAST_NAME and FIRST_NAME columns
can speed up access by employee name.
You can always select rows in a sequence based on nonprimary-key columns, with or
without an index. For example, if there is no index on employee names,
NonStop SQL/MP sequentially scans the entire EMPLOYEE table and sorts the rows
to retrieve the names in the requested order. However, with an index,
NonStop SQL/MP can scan the index in much less time than it takes to scan the base
table and then retrieve the requested data by fetching the corresponding rows in the
base table.