Index analysis is started when you either generate cases with Perform detail analysis selected on the Overview tab, or when you click the Analysis tab. If any columns referenced in the WHERE clause of the tuning candidate are not the first column of an index, tuning will recommend that you create an index on that column.
The color-coded Index Analysis feature highlights missing indexes as well as shows which indexes are used and which are not used in the default execution path. The Index Analysis feature highlights issues where the database optimizer might not be using the preferred indexes. DB Optimizer also lists indexes on the tables that do not have fields in the WHERE clause helping the designer to see if adding an additional predicate in the WHERE clause might make use of an existing index.
The layout of the Analysis tab shows the SQL text and Visual SQL Tuning (VST) diagram on the top and the indexes on the tables in the query below.
The Analysis tab has five important components as depicted in the previous illustration:
- Statement selector, if there are multiple statements in the tuning set. Here you can select the statement and the generated case you want to analyze.
- Statement text for selected statement.
- Graphical diagram of the SQL statement.
- Index analysis, statistics, and settings relating to the SQL statement and referenced elements.
- Description of the selected index, including the reasoning behind DB Optimizer recommendations.
Tabs are platform-specific. For example, against Oracle data sources, Table Statistics, Column Statistics And Histograms, and Outlines tabs are available. For more information, see Using platform-specific features.
The text, diagram, and analysis sections can be resized or expanded to take up the whole page.
The Analysis tab suggests missing indexes, indicates which indexes are used in the execution path and lists all indexes that exist on all the tables in the query. Indexes on the table are listed on the Analysis tab and color coded as follows:
| Text Color | Interpretation |
|---|---|
| Index is used in the query |
| Index is usable but not used in the current execution path. |
| This index is missing. DB Optimizer recommends that you create this index. |
| This index exists on the table but not usable in this query as it is written. |
In the Collect and Create Indexes table, orange-highlighted entries indicate missing indexes that DB Optimizer recommends be created to improve performance. Clicking on that index, displays text to the right outlining the rationale behind this recommendation.
For more information on using the Analysis tab, see Using the Analysis tab.