Introduction to NonStop SQL/MP

Additional Features
Introduction to NonStop SQL/MP
113425 Tandem Computers Incorporated 1–17
Constraints Constraints are conditions stored in the data dictionary to control the data values
added to a database. You specify constraints to keep invalid data out of the database.
For example, you can ensure that a particular code in the database is limited to values
1 or 2. By defining constraints in the data dictionary, you can avoid duplicating
program code to check for invalid data, because the data is checked by
NonStop SQL/MP rather than by the program.
Performance Features for
DSS
A database used for decision support needs special features not required for online
transaction processing. Typically, users examine large amounts of data rather than
accessing a single record at a time, as in OLTP.
NonStop SQL/MP provides several types of query execution plans that speed up
scanning of large tables, take full advantage of the Tandem parallel architecture, and
efficiently calculate aggregate values such as SUM, MINIMUM, MAXIMUM, or
AVERAGE.
Often, DSS queries combine data from a very large table with one or more small tables.
For example, you may join data from a sales history table with a table containing store
names and addresses. NonStop SQL/MP provides three methods for joining tables:
nested-loop, sort-merge, and hash join. Hash joins are particularly efficient for large-
scale DSS queries for which sorting is inefficient. A hash join works by building a
hash table in memory to perform the join. Another feature, cross product join, prejoins
several small tables so you only need a single join operation with the big table.
In addition, DSS queries frequently request summarized data derived by grouping
and aggregating the data. In NonStop SQL/MP, aggregate values can be calculated
directly as data is being read from the disk or by using special hash algorithms to
eliminate the need to sort data.
In most decision support systems, you load data from another operational database
into a separate DSS database. NonStop SQL/MP provides a utility for loading data.
This utility uses special techniques to process large blocks of data rather than single
rows at a time. You can use the utility to load data into multiple partitions in parallel
to further improve the performance of the load operation.
Tools for Database
Administration
The database administrator (DBA) can use SQL Data Definition Language (DDL)
statements to create, alter, rename, and delete SQL objects. These statements can
create new indexes, add columns to tables, and change security.
In addition, NonStop SQL/MP provides database operations that can move data from
one disk volume to another while users are updating the database. These partition-
configuration operations help to optimize system performance and can be executed
online; they do not affect application availability.
NonStop SQL/MP and third-party software products also provide utilities that help to
convert data, manage a database in a distributed environment, reorganize a table or
database, and examine which programs use which tables in the database.
NonStop SQL/MP is integrated with system administration tools for the NonStop
Kernel. These tools measure performance and manage backup and recovery of
database files.