Introduction to NonStop SQL/MP

Other Architectural Features
NonStop SQL Architecture
3–18 113425 Tandem Computers Incorporated
If you choose to use the parallel execution option, the optimizer examines both parallel
and nonparallel execution plans and chooses the one that will use the fewest resources,
even if it is a nonparallel plan. Partitioning a table and its indexes increases the
likelihood that the optimizer will choose a parallel execution plan. The partitions
might reside on one system or on many nodes in a network.
Parallel Index Maintenance Indexes can improve the speed of data access. As you add indexes to a table, you
increase the number of quick paths to the data. A table with many indexes allows you
to write a variety of queries that meet requirements for high performance. However,
each time a user inserts or updates a row in the base table, the system must also
update the corresponding rows in all the indexes on the affected columns.
To achieve high performance for update operations, NonStop SQL/MP updates
multiple indexes on a table in parallel. After completing a change in the base table, the
file system sends asynchronous update requests to each data access manager serving
an index. Thus, NonStop SQL/MP can modify multiple indexes in approximately the
same elapsed time it would take to modify a single index.
To take advantage of parallel index maintenance, the DBA should define the indexes
for a given table on separate disk volumes and configure them on separate processors.
This strategy allows a separate data access manager to access each index, eliminating
contention during parallel operations.
You do not have to specify a statement or directive to achieve parallel index
maintenance. The system updates indexes automatically whenever a row is inserted
or deleted or values change in indexed columns.
Other Architectural
Features
NonStop SQL/MP provides additional architectural features that enhance query
performance by allowing data-access operations to execute at the lowest levels of the
operating system, as close as possible to the I/O subsystems that physically retrieve
the data. The integration of SQL operations with low-level operating system processes
(such as the data access managers) reduces the number of messages passed to higher-
level SQL processes (such as the executor) and reduces the overall path length of the
operations. Thus, low-level integration improves the efficiency and performance of
data-access operations.
The Data Access Manager
(DP2 Disk Process)
The SQL executor communicates with the Tandem data access manager (DP2 disk
process), a component of the NonStop Kernel operating system, through the file
system. A group of data access manager processes performs data access to a single
disk volume.
The data access manager handles disk space, access paths, and a main-memory buffer
pool of recently used blocks called the cache. It implements locking of rows,
partitions, or tables on the disk volume. The data access manager also records
database updates in the audit trail (log) used by NonStop TM/MP to roll back or
recover transactions if a failure occurs.
When the file system sends an OPEN request, the data access manager authorizes the
application process to access the table. The data access manager also enforces table
constraints specified for columns of the table being updated.