Generating and acting on recommendations for indexes for improving the performance of single SQL statements

The Index Advisor recommends indexes that you might create to improve the performance of the query, and enables you to create the indexes directly from the workflow assistant, if you have the appropriate authorities on the data server. Otherwise, you can copy the CREATE INDEX statements and send them to someone who has the authority to create the indexes.

Before you begin

About this task

Indexes are important for query performance because a good set of indexes can provide more efficient access to tables and speed up query processing. Indexes can improve performance by providing local filtering, making join processing more efficient, avoiding the need for sort processing, providing index-only access, enforcing referential integrity, and more.

Procedure

To get recommendations for indexes:

  1. After running the RUNSTATS commands that were recommended by the Statistics Advisor, return to the Run Single-Query Advisors and Tools page of the Invoke section in the workflow assistant. Click the Invoke tab on the left side of the workflow assistant. If the Run Single-Query Advisors and Tools page is not open, click Run Advisors and Analysis Tools on the left side of the workflow assistant to open the page.
  2. Optional: Modify values for the options for the Index Advisor. Click Set Advisor Options on the left side of the Invoke section. Then, click the Indexes tab to display the options that you can modify. For information about the options, click the Help icon. After you are finished, on the left side of the Invoke section click Run Advisors and Tools to return to the Run Single-Query Advisors and Analysis Tools page.
  3. If you are running the Index Advisor after running the RUNSTATS commands that the Statistics Advisor recommended, ensure that the Re-explain the query check box is selected. Now that the statistics for the SQL statement are accurate, the workflow assistant must update the EXPLAIN information for it.
  4. Click the Select What to Run button.
  5. In the Select Activities window, select the Indexes check box. Then, click OK. After the Index Advisor runs, the Review Advisor Recommendations page of the Review section opens. The Summary section shows whether there are any recommendations for new indexes.
  6. In the Review Single-Query Advisor Recommendations page, double-click the Indexes row in the Summary table.
  7. Review the estimates of the performance improvement and disk space required if the recommended indexes are created.
  8. Review the list of candidate indexes. Here are descriptions of the columns of the table in which the indexes are listed.
    Indexes by Table
    Lists candidate indexes by the tables that the SQL statement references.
    Creator
    The schema of the index.
    Object Name
    The name of the index.
    Index Columns
    The columns in the index.
    Include Columns
    The include columns in the index. Include columns are appended to the set of index key columns. These columns are not used to enforce uniqueness. These columns might improve the performance of some queries through index only access. The columns must be distinct from the columns used to enforce uniqueness.
    Estimated Disk Space
    The amount of disk space that the index will require.
    Created By
    The origin of the candidate index. This column helps you to distinguish between the indexes that the Index Advisor recommends and the indexes that you propose.
  9. Click the Show DDL icon to see the DDL statements for creating the indexes that are listed in the table in the Candidate indexes section. You can save these statements to a file and run them outside of the workflow assistant.
  10. Optional: Test the candidate indexes that the Index Advisor recommends and any that you propose. For help with this feature, see Virtually testing recommended indexes and indexes that you propose.

What to do next

If you generated an access plan graph before running the Index Advisor, you can generate another access plan graph after creating the recommended indexes. Then, you can compare the two access plan graphs side by side in the Compare section of the workflow assistant.

You can also run the Access Path Advisor to get recommendations that can improve the access path for the current SQL statement, and you can run the Query Advisor to get recommendations for revising the text of the statement.


Feedback