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

To get recommendations for indexes:

1. 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.

   Action

   Specifies whether the recommendation is to create, alter, or drop the index.

   Creator

   The schema of the index.

   Object Name

   The name of the index.

   Key Columns

   The columns that are in the recommended index.

   Include Columns

   The include columns that are 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.

   Old Key Columns

   The columns that are in the current version of the index.

   Old INCLUDE Columns

   The include columns that are in the current version of the index.

   Unique

   Indicates whether the recommendation is for a unique index.

   Estimated Disk Space

   The amount of disk space that the index will require.

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.
   Attention: Before running DDL statements to modify existing indexes, you must check whether other SQL statements use those indexes in their access plans and would be affected by the changes.
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.