The Workload Index Advisor recommends new indexes and changes to existing
indexes on tables that are referenced by SQL statements in query workloads.
About this task
The Workload Index
Advisor can give one of the following reasons for recommending an
index:- The index supports referential integrity.
- The index supports local predicates.
- The index supports join predicates.
- The index prevents sorting.
- The index supports multi-column, foreign-key
index access.
- The index supports index screening.
- The index supports one-fetch index access.
After the Workload Index Advisor generates recommendations for new
indexes and changes to existing indexes, you can review those recommendations in detail. If you have
the appropriate authorities on the database, you can run the DDL statements to put into effect the
recommendations that you select.
Procedure
To generate and act on recommendations for indexes:
- In the Manage section of the workflow assistant, open the
Manage Workloads page.
- Select the query
workload and click the Invoke Advisors icon, which is on the left end of the
toolbar above the list of query workloads. The Run Workload Advisors page of
the Invoke section
opens.
- Optional: Modify values of the options for the Workload Index
Advisor. Click Set Advisor
Options under Workload on the left side of the
Invoke section. Then, click the Indexes tab to display
the options that you can modify. For help with the options, click the ? icon. After you are finished
modifying the values for options, click Run Workload Advisors on the left
side of the Review section.
- Ensure that the Re-explain the
query workload check box is selected, if either or both
of the following conditions are true:
- You are running this advisor after implementing recommendations
from another advisor.
- You want to collect EXPLAIN information that you can compare with
EXPLAIN information taken at a different time for this query workload.
- Click the Select What
to Run button.
- In the Select Activities window, select the
Indexes check box and click OK.
The Review Workload Advisor Recommendations page of
the Review section opens. The Summary section on this page shows whether
there are recommendations for indexes.
- In the Review
Workload Advisor Recommendations page, click the Indexes tab if
there are new recommendations for indexes.
- Review the list of tables that the Workload Index Advisor has
recommendations for. This information appears for each
table.
- Cardinality
- The number of rows that are in the table. The value is -1 if the
cardinality is unknown.
- References to Table
- The number of SQL statements in the query workload that reference
the table.
- Cumulative Total Cost
- The cumulative cost to execute all of the SQL statements that
reference the table.
- Recommended Indexes
- The number of indexes that the Workload Index Advisor recommends
for the table.
- IUDM Statements
- The number of INSERT, UPDATE, DELETE, and MERGE statements that
reference the table.
- Select the check box next to each table that you want
to review the recommendations for.
- In the
Recommendations section, review the new and changed indexes that the advisor
recommends. This information appears in this
section.
- Index
- The name of the recommended index. You can change the name.
- Table
- The name of the table that the index is recommended for.
- Action
- The action that is recommended.
- Create
- It is recommended to create the index.
- Drop
- It is recommended to drop the existing index.
Attention: Recommendations
to drop indexes are based on the current query workload. Before implementing
such recommendations, ensure that the indexes are not used by any
statements that are outside of the current query workload.
- Alter
- It is recommended to alter the existing index.
- Key Columns
- The key columns in the recommended index.
- Include Columns
- The columns that are appended to the key columns and that can
allow queries to use index-only access when accessing data. These
columns are not used to enforce uniqueness, but they can be appended
only to unique indexes. Include columns are distinct from key columns.
- Old Key Columns
- If the recommendation alters the current index: The key columns
in the current index.
- Old Include Columns
- If the recommendation alters the current index: The columns that
are appended to the key columns and that can allow queries to use
index-only access when accessing data. These columns are not used
to enforce uniqueness, but they can be appended only to unique indexes.
Include columns are distinct from key columns.
- Estimated Performance Gain
- The percentage by which the recommended index can improve the
speed of the execution of statements against the table.
- Estimated Disk Space
- The amount of space that is required to create the recommended
index.
- Times Used In Workload
- The sum of the execution counts of all SQL statements in the query
workload that reference the table.
- Unique
- Indicates whether or not the index is unique.
- Reason Recommended
- The reason that the advisor is making the recommendation.
- In the Existing Indexes
section, you can find out whether the DB2 optimizer is using existing indexes and whether the
optimizer would continue to use existing indexes after you followed the recommendations from the
advisor. This information appears in this
section.
- Index
- The name of the index.
- Table
- The name of the corresponding table.
- Creator
- The qualifier of the index.
- Key Columns
- The key columns in the existing index.
- Include Columns
- The columns that are appended to the key columns and that can
allow queries to use index-only access when accessing data. These
columns are not used to enforce uniqueness, but they can be appended
only to unique indexes. Include columns are distinct from key columns.
- Used Before
- Indicates whether the index is used in the current access plans
for the statements that reference the corresponding table.
- Used After
- Indicates whether the index would be used if the recommended indexes
were created.
- Foreign Key Index
- Indicates whether or not the index is on a foreign key in the
corresponding table.
- Unique
- Indicates whether or not the index is unique.
- Virtually Drop
- Indicates whether the index was virtually dropped before a test
was run from the Workload Test Candidate Indexes page.
- Time Last Used
- Shows when the index was last used in an access plan.
- In the Index Chosen by Optimizer but Not
Recommended section, you can review a list of indexes that the advisor considered
recommending and that the DB2 optimizer would have used, but that the advisor did not recommend
because they violated constraints that you set. If you want to see the constraints, click the
Constraints tab. This information appears in this
section.
- Index
- The name of the index.
- Table
- The name of the table that the index was evaluated for.
- Key Columns
- The key columns in the index.
- Estimated Disk Space
- The amount of disk space that the index would require.
- Reason Not Recommended
- The constraint that the index violates.
- If you want to see
which SQL statements would make use of the recommended indexes, in the
Recommendations section select the check box next to each index that you are
interested in and click the Show SQL Affected by the Selected Indexes icon.
- Optional: Change one or two constraints on
the Workload Index Advisor and generate a new set of recommendations. In the Recommendations section, click the Change
Constraints icon. You can change the amount of disk space
to allocate for new indexes and the number of indexes that are allowed
per table.
- If you want to run or save the DDL statements for the
recommendations, click the Run DDL icon. The Run DDL for Selected
Indexes window lets you run the statements or save them to a file. You can also modify
the statements before running or saving
them.
What to do next
After running the DDL, re-run the Workload Statistics Advisor.