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-10
Adding Indexes
4. Enter an UPDATE STATISTICS statement to update the statistical information
stored in the catalog and get statistics on the new index.
5. SQL compile (recompile) any SQL applications that use the table with the
EXPLAIN option to determine whether the index is the chosen path.
6. Test the same queries against the tables by using DISPLAY STATISTICS to obtain
the new statistical information.
7. Determine any improvement in performance.
8. If the query execution plans include using the new index and if you determine that
the performance improvement is sufficiently advantageous over the increased
system overhead of maintaining the index, add the index to the production
database.
9. If you add the index, recompile programs that use the table.
For more information about the performance benefits of using indexes, see
Performance Benefits of Indexes on page 4-13.
Steps for Adding an Index
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 to which you want to add the index.
3. Query the OBJECTS table to ensure the new index name is unique among existing
index names in the schema.
4. Query the table data to ensure it is consistent with the index. If you are creating a
unique index, query the data to ensure there are no duplicate entries.
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. Enter the CREATE INDEX statement.
Note. If the base table is populated, specify the NO POPULATE option for this statement, and
then run POPULATE INDEX utility. For more information, see the SQL/MX Reference Manual.