Virtually testing recommended indexes and indexes that you propose

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.

Before you begin

Procedure

To test virtually recommended indexes and indexes that you propose:

  1. Capture the SQL statement that you want to tune. See Locations from which you can capture an SQL statement for single-query tuning.
  2. 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.
  3. 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:
      1. Run the Index Advisor to generate recommendations for new indexes.
      2. 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.
  4. After adding and editing the indexes that you want to test virtually, click the Test Candidate Indexes button.
  5. 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.
  6. 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.

Feedback