SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-32
Creating Table Partitions
Creating Table Partitions
To promote parallel processing of queries and parallel index maintenance, you should
partition data across available disk volumes. For a very large table or a table used at
different geographical sites, partitions can make the data more accessible and can
reduce the time required for table scans by a factor almost equal to the number of
partitions.
Partitions are allowed for tables of all three organization types. For key-sequenced
tables, however, the table must have a user-defined primary key to have partitions.
A partition of a table or index holds all the rows within a range of key values. Partitions
can reside on one system or across many systems in a network.
You can partition a table or index upon creation. You can also split an existing table or
index into partitions, or you can add or drop a partition by using the ALTER TABLE or
ALTER INDEX statement. There are special rules for adding, moving, and splitting
partitions based on file type (key-sequenced, entry-sequenced, or relative). For more
information, see Adding Partitions to Tables and Indexes on page 7-7 and Altering
Partition Attributes on page 7-19.
Performance Benefits
The benefits of using partitioned tables and indexes are:
•
Partitions are independent of one another for access. Only the accessed partition
must be available.
•
Partitions improve transaction throughput by allowing simultaneous disk access to
different partitions of the same table.
•
Partitions require no special access procedures. SQL manages partition access for
you automatically.
•
Partitions enable SQL to more readily process queries in parallel.
•
Partitions allow you to have tables larger than the size of a single disk volume.
Partitioning the indexes of a table enables SQL to take maximum advantage of parallel
index updates. Indexes should reside on separate volumes and should be configured
on separate processors.
Creating Partitions on a System That Uses SMF
If you create tables and indexes with a large number of partitions—for example, tables
supporting a DSS application—you can use the SMF product to manage the
distribution of partitions across physical disk volumes. When you create a table or
index, you can specify partition names using virtual volumes as the volume portion of
the volume.subvolume.file name; SMF automatically distributes the partitions
across the physical volumes assigned to the storage pool (or pools). You can also
place nonpartitioned tables and indexes on virtual volumes managed by SMF.