Introduction to NonStop SQL/MP

Physical Database Structure
NonStop SQL Architecture
113425 Tandem Computers Incorporated 3–7
You can add and delete indexes as needed without having to change or redefine the
base table. If you determine that a new index would improve performance, you can
add the index and receive performance benefits immediately.
Partitions Partitions can make data more accessible in a large table or a table used at different
geographical locations. A partition of a table or index holds all the rows within a
range of key values.
Partitions provide the following benefits:
Partitions improve transaction throughput (for small queries) and query execution
(for large queries) by allowing simultaneous, parallel disk access to different
partitions of the same table.
Partitions also enable other system resources (such as disk cache and memory for
hashing, grouping, aggregation, and sorting) to operate simultaneously on a
query, further improving performance.
Partitions allow you to store the data close to where it is used in a geographically
distributed database.
Partitions are independent of one another for access. Only the accessed partition
needs to be available.
Partitions require no special access procedures. NonStop SQL/MP manages
partition access for you automatically. All partitions of a table or index
automatically receive the same security settings.
If a table is too large to fit on a single disk volume, you can partition the table over
two or more volumes.
Partitions provide an efficient method for performing database management
functions, which can be run against single partitions, thus reducing the impact of
database management on application availability.
Even if a large table fits on a single volume, it is sometimes more efficient to partition
the table across many disks to spread the I/O requests over many data access manager
processes. Partitioning the table allows you to take advantage of parallel execution for
some queries. (See “Parallel Execution” later in this section.)
Suppose your company stores parts at warehouses in New York, Los Angeles, and
Montreal, and you want to maintain the parts information at the three sites. You can
create a parts description table with three partitions, one for each location.
Figure 3-3 shows a table containing the location and quantity on hand of each part.
The table, PARTLOC, is partitioned by its primary key: the location code and part
number.