Add Index Window
The Add Index window is used to create the DDL for creating of the indexes for the Index Impact Analysis.
|
Item |
Description |
|---|---|
|
Index name |
Enter a name for the index. By default, the prefix of the index name is taken from the Index Expert settings in the Options window and then a random number is generated for the rest of the index name. Note: It is best not to change the prefix for the index. The indexes are generated as virtual indexes. If the Impact Analysis process terminates abnormally and a virtual index is left on the database, it can be easily identified as a virtual index which was created by SQL Optimizer by the prefix. |
|
Database |
Select the database where the index is to be created from the database list. |
|
Owner |
Select the owner of the index from the drop-down list. |
|
Table |
Select the table where the index is to be created from the drop-down list. |
|
Table columns |
List of all the columns from the selected table. |
|
Index columns |
Move the columns to be indexed from the Table columns list by highlighting the column and clicking |
|
Unique |
Specify to create a unique index. |
|
Clustered/Nonclustered |
Select to create either a clustered or a nonclustered index. |
.
View Index Impact Analysis Details
The Index Impact Analysis Details window displays information about the creation of the indexes and the retrieval of the access plans. The Index Impact Analysis Details window appears after the Index Impact Analysis process is completed unless the Show details on next generation checkbox in the Index Impact Analysis Details window is unchecked.
To view the Index Impact Analysis Details window
After an Index Impact Analysis is complete, select View | Last Index Impact Analysis Details when the Index Impact Analyzer window is active.
Review this information to see if an error occurred during the retrieval of the access plan. Errors may occur if the selected SQL statements are from one schema and you have set another schema in the Schema list at the bottom left of the main window.
Add SQL to an Index Impact Analysis
After creating an Index Impact Analysis, you can add more SQL statements from the SQL Repository to it. This process will re-execute the Scenario.
To add SQL statements
-
From the left pane, select the Analyzer in which you want to add the SQL.
-
Right-click and select Add SQL.
-
In the Add SQL window under the Select SQL to be added pane, check the SQL statements you want added.
-
Under the Select Scenario to include SQL pane, check the Scenarios you want the SQL added to.
Add Scenario
After creating an Index Impact Analysis, you can add more Scenarios to it. A Scenario can be added in two ways:
To add a Scenario to an Analyzer
Method One
-
In the left pane, right-click the Analyzer and select Add Scenario.
-
Click the Index page and specify the information for the proposed new indexes.
Method Two
-
In the left pane, right-click an Analyzer and select New Analysis.
-
Select the Continuing an existing Analyzer using the Analyzer’s selected SQL option.
-
From the Select Analyzer box, click the Analyzer name.
-
Click the Index page and specify the information for the proposed new indexes.