SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—523723-004
9-12
Adding Partitions to Tables and Indexes
•
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.
•
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. Even for join queries, which do not require partitioning
of the objects involved, parallel processing operates best when a table or index is
partitioned.
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.
For more information about the benefits of new partitions, see Performance Benefits of
Partitioning on page 7-16.
Steps for Adding a Partition
1. Start an MXCI session. Enter a LOG command to initiate a log file for 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 range partitioned tables or indexes, determine the location and starting key of
the new partition. For hash partitioned tables or indexes, determine the location of
the new partition.
4. Ensure ample disk space is available for the new partition. For information about
space requirements, see the SQL/MX Reference Manual.
5. Use the DISPLAY USE OF command to identify which user modules are
associated with this object. See the similarity check criteria in the SQL/MX
Programming Manual for C and COBOL and the SQL/MX Programming Manual for
Java to determine if your changes are likely to cause similarly check to fail and
force automatic recompilation. If they will, you should SQL compile these modules
after making the changes to avoid expensive automatic recompilations at run time.
SQL applications that are running while you make these changes will still undergo
automatic recompilation.
For information about explicit and automatic recompilation, see the SQL/MX
Programming Manual for C and COBOL and the SQL/MX Programming Manual for
Java. For information about using DISPLAY USE OF, see Checking Module
Dependencies With DISPLAY USE OF on page 11-20 and the SQL/MX Reference
Manual.
6. Use the MODIFY utility to add the partition. (See Using MODIFY to Manage Table
and Index Partitions on page 10-9.)
7. Revise the application source code as needed to reflect your changes to the
database. Process and compile the updated source file. For more information, see