SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-25
Adding and Dropping Partitions
Adding and Dropping Partitions
For performance improvement, consider partitioning a table or index to enable them to
span multiple volumes or multiple nodes. For more information, see Adding Partitions
to Tables and Indexes on page 7-7. As the number of rows in the table or index
increases, consider redistributing rows across partitions to balance the distribution of
rows. You can use the ALTER TABLE and ALTER INDEX statements to split partitions,
move partitions, and move row boundaries.
When all information in a partition becomes obsolete, or when a database design
deficiency leaves a partition continually empty, a reference to a table or index defined
across this partition results in an unnecessary message to the partition. This message,
in turn, results in a correspondingly longer access time to the table or index. In such
circumstances, you might want to drop this partition while leaving the others defined for
the object intact. For directions on dropping partitions, see Dropping Partitions of
Tables and Indexes on page 7-32.
Avoiding Automatic Recompilations
Automatic recompilation can become a significant performance concern. In most
cases, you should attempt to be running valid programs at all times to ensure the best
possible performance.
Automatic recompilation makes it possible for application programs to continue to
perform when invalidating events occur or when access paths are unavailable. The
time required to perform the recompilation, however, can noticeably add to the initial
response time of the application program that contains the SQL statements.
For more information about automatic recompilation, see SQL Compilation and
Recompilation on page 10-6.
Matching Block Split Operation to Table Usage
In a table with key-sequenced organization, when an INSERT operation causes a data
block to overflow, the disk process makes room for the new row by splitting the block
and transferring some of its contents to a newly allocated block.
The disk process can use one of two methods to split a block:
•
Split the block in the middle.
•
Split the block at the insertion point when rows are being inserted in sequence and
the user has specified the SEQUENTIAL BLOCKSPLIT ON option of the
CONTROL TABLE statement.
The SEQUENTIAL BLOCKSPLIT ON option can increase the average number of rows
stored per block in certain applications where the disk process cannot detect
sequential insertion of rows.