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-9
Adding Indexes
There is no restriction on the number of indexes per table, and there is no restriction on
the number of partitions an index supports, but HP recommends a maximum of 512
partitions.
Consider these guidelines when adding an index to an existing table:
•
Index creation can be quite a long operation, depending on the size of the table
and the load on the system. Therefore, the default locking strategy acquires a
shared table lock on the underlying table. The shared table lock ensures that no
users can modify rows during the creation of the index. This lock will prohibit
access to the table by other users that make write requests during the index
creation process.
•
Make a TMF online dump of the index immediately after creating in preparation for
potential file recovery (which could be faster than rebuilding the index).
For additional guidelines related to index creation, including performance-related
considerations, see the SQL/MX Reference Manual, Determining When to Use
Indexes on page 4-12, Defining an Index on page 4-15, and Creating Indexes for
SQL/MX Tables on page 7-31.
Evaluating the Benefits of a New Index
Use indexes to improve the performance of your database and to implement
constraints. Indexes can improve performance by eliminating the need for the disk
process to access the underlying table. Knowing when to add an index to improve
performance requires a detailed understanding and analysis of your application. Ways
of collecting performance data are:
•
Analyze the programs and ad hoc queries for the columns used in the DISTINCT,
GROUP BY, ORDER BY, and WHERE clauses, and in join operations.
•
Run the SQL compiler with the EXPLAIN option to obtain a report on the access
paths the compiler chooses for the programs.
•
Analyze Measure statistics on SQL statements.
For more information about evaluating the benefits of using indexes, see Evaluating
the Benefits of a New Index on page 4-14
Testing the Performance Benefits of a New Index
If you need to determine whether an index can benefit performance, you could test the
performance before implementing the index in the production system:
1. Test a sample set of queries against the production tables by using the DISPLAY
STATISTICS command to obtain the statistical information.
2. Duplicate the table or tables involved to a test location.
3. Create the new index.