You can test recommended indexes and indexes that you propose
without first creating them. The results of each test show you whether
the indexes improve the performance of the current SQL statement,
the estimated disk space that would be required for the indexes, and
whether the DB2® optimizer would
use the indexes in the access path for the current SQL statement.
Procedure
To test virtually recommended indexes and indexes that
you propose:
- Capture the SQL statement that you want
to tune. See Locations from which you can capture
an SQL statement for single-query tuning.
- Select one of the captured statements
and click the Invoke Advisors and Tools button.
The Run Single-Query Advisors and Analysis Tools page
in the Invoke section opens.
- Add the indexes that you want to test:
- If you want to test only indexes that you propose: On the
left side of the workflow assistant, expand Advanced under Single
Query. Select Test Candidate Indexes.
The Test Candidate Indexes page opens.
- If you want to test both indexes that the Index Advisor recommends
and indexes that you propose:
- Run the Index Advisor to generate recommendations for new indexes.
- If the Index Advisor generates recommendations, double-click the Indexes row
in the Summary table in the Review
Single-Query Advisor Recommendations page of the Review section.
The Index section opens.
Two tables appear:- Candidate indexes table
- If you ran the Index Advisor, recommended new indexes are listed
in this table. You can edit these index definitions or leave them
as is.
- You can add your own indexes to the table by clicking the Add
Index icon.
- Existing indexes table
- For your reference, this table lists all of the existing indexes
on the tables that the current SQL statement references.
- After adding and editing the indexes that you want to test
virtually, click the Test Candidate Indexes button.
- In the Test Candidate Indexes window,
customize the statistics for the indexes, or leave the default statistics.
Then, click OK to run the test. The Review
Results of Testing Candidate Indexes page lists the indexes
that you tested and shows whether the access plan used each index.
- If you decide that you want to create one or more of the
indexes that you tested, right-click an index and select Show
DDL. Copy or save the DDL, so that you can
run it outside of the workflow assistant. Repeat this step for each
index that you want to create.