SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

For additional guidelines related to index creation, including performance-related considerations,
see the SQL/MX Reference Manual, “Determining When to Use Indexes” (page 37), “Defining an
Index” (page 39), and “Creating Indexes for SQL/MX Tables” (page 96).
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, see “Evaluating the Benefits of a New Index” (page 156)
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.
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, see “Performance Benefits of Indexes” (page 37).
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
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
156 Adding, Altering, and Dropping SQL/MX Database Objects