SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-8
Adding Partitions to Tables and Indexes
For a relative or entry-sequenced table, the only way to add a new partition is to add
an empty partition to the end of a table with the ADD PARTITION option. You cannot
use the PARTONLY MOVE option with a relative or entry-sequenced table.
Evaluating the Benefit of a New Partition
Partitioning might increase performance in these cases:
•
If disk accesses are queued in the disk process, partitioning the table across
multiple volumes might increase performance.
•
If a partition of a table or index is full, the partition can be split into two partitions.
•
If a certain subset of a remote table’s data is accessed more frequently at the local
node than from a remote node, partitioning the table so that the frequently
accessed portion of the data resides on the local node can increase local
performance.
•
If a distributed table residing on a remote node might be frequently unavailable,
partitioning the table so that the local partition can be accessed regardless of the
remote table’s availability can increase local performance.
•
If an application program that disables row lock escalation to table locks receives
an error from the disk process because the disk process has used up the control
block space for locks, the table can be partitioned to allow more locks. Partitioning
allows more locks to be placed on a table because the disk process lock limit
functions on a partition basis.
•
If queries are processed in parallel, partitioning a table or index is often required.
Partitioning is necessary, for example, for parallel execution of a SELECT
statement on a single table. Also for join queries, which do not require partitioning
of the objects involved, parallel processing operates best when a table or index is
partitioned.
You can use the Measure product to obtain statistics concerning disk message levels,
queuing, and other measurements on various volumes or file partitions to identify the
levels of use.
Steps for Adding a Partition
You can add partitions to tables and indexes within the guidelines listed in the SQL/MP
Reference Manual in the descriptions of the ALTER TABLE and ALTER INDEX
statements.
To add a partition, follow these steps:
1. Start an SQLCI session. Enter a LOG command to initiate a log file for the
statements and commands entered in this session. Keep the log for your records.
2. Determine the name of the table or index to which you want to add the partition.
3. For a key-sequenced table or index, determine the starting key of the new partition.