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.
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:
- 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.
- 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.
- 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.
- Click the Select What
to Run button.
- 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.
- In the Review Single-Query Advisor Recommendations page,
double-click the Indexes row in the Summary table.
- Review the estimates of the performance improvement and
disk space required if the recommended indexes are created.
- 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.
- 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.
- 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.