SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-7
Adding Partitions to Tables and Indexes
Steps for Adding an Index
To add an index, 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 for which you want to add the index.
3. Optionally, determine which programs depend on this table by using the DISPLAY
USE OF command. These programs will be invalidated unless you use one of the
options discussed previously.
4. Optionally, prevent the use of the table for the duration of the CREATE INDEX
operation to eliminate conflicts in access to the table; this operation requires
exclusive use of the table during the final phase of the creation process.
5. For an index column, you can specify a different collation than the collation used
by the corresponding base table column, provided the shifting rules of both
collations are the same. Make sure that collations exist before you refer to them in
column definitions. If a collation to be used by a column does not exist, create the
collation as explained under Adding Collations on page 7-13.
6. Enter the CREATE INDEX statement. To allow an online dump during index
creation, use the WITH SHARED ACCESS option.
7. Enter the UPDATE STATISTICS statement for the underlying table.
8. SQL compile the invalidated programs to enable the compiler to determine the best
access strategy.
9. For audited indexes, make a new TMF online dump.
10. Restart use of the table if you stopped its use.
To maximize concurrent access during the index creation operation, use the WITH
SHARED ACCESS option. For more information, see Understanding the Implications
of Concurrency on page 14-1.
Adding Partitions to Tables and Indexes
To add a new partition to a key-sequenced table, use the ALTER TABLE statement
with the PARTONLY MOVE specification. To add a new partition to an index, use the
ALTER INDEX statement. Alternatively, you can split partitions, merge partitions, or
move row boundaries within existing partitions.
You can also use the ADD PARTITION option with the ALTER TABLE or ALTER
INDEX statement to add a new partition to a table or index. The ADD PARTITION
option is equivalent to the one-way split form of the PARTONLY MOVE option.
However, to use enhanced features such as the WITH SHARED ACCESS option, you
must use the PARTONLY MOVE option.