Advise Index Alternatives
To have the Index Advisor propose Index candidates for a SQL statement
-
Click
.
-
Enter the original SQL statement for which you want to analyze for new indexes that could be created to change SQL performance. You can also copy the SQL statement directly from another window using the Send to Index Advisor function.
-
Click
to generate new Index candidates for the original SQL statement.
-
In the Select tables window, select the tables for which you want the index candidates generated in the Selected column.
The Sampling Size (Rows) column displays the number of rows that will be used in the data sample that is used to determine the selectivity of the data. These values are calculated using the settings on the Index Advisor tab in the Preferences window. You can adjust these values to change the size of the sample.
The Index Advisor identifies columns as index candidates after performing an analysis of the SQL syntax, relation between tables, and selectivity of the data. It displays the DDL for the index candidates in the bottom left pane of the Index Advisor window.
To terminate the index generation process
Click 
It may take a few seconds to terminate all processes.
Once the advising process is completed, the Index Advising Details window displays detailing the indexes proposed. The Index candidates are displayed on the bottom left pane on the tabs labeled Index1, Index2 …IndexN. The Used Index tab displays the DDL for the index(es) currently used by the original SQL statement. The corresponding query plan, abstract plan, SQL classification, trace on and Sort Resource information are displayed in the SQL Information Pane at the bottom right.
Summarized index information for the entire index candidates is shown on the Index List tab on the top pane of the Index Advisor window.