Index Advisor
Index Advisor Overview
The Index Advisor provides the index candidates by analyzing the structure of a SQL statement and identifying all the related tables and indexes currently used by the SQL statement. By looking into the search arguments and table join conditions of the SQL statement, different index candidates are generated for you to evaluate the effect new indexes may have on database performance. You can further investigate index possibilities by creating your own candidates and grouping the candidates into Index Sets.
Index Advisor Window
View Index Advisor Window
The Index Advisor window is divided into the following sections:
Top Pane
SQL Editor tab
Used to enter the SQL statement you wish to analyze for index recommendations.
Database Objects tab
Displays information about the tables used in the SQL statement.
Left Bottom Pane
Displays the table names accessed by the SQL Statement
Right Bottom Pane
Displays specific information about the selected table on different tabs: Definition, Columns, Indexes, Constraints/Keys, and Data.
Index List tab
Displays a list of the individual index candidates proposed by the Index Advisor. These are the indexes used in the Index alternatives displayed in the bottom left pane. The following information displays on this page:
Time tab
Displays the run time information for the SQL statement using the alternative indexes. The values are filled in after the Show Plan, Batch Run, Run for First Record, or Run for All Records functions are executed.
Statistics tab
Displays the run time statistics for the SQL statement using the alternative indexes. These values are filled in after the Batch Run, the Run for First Record, or Run for All Record functions are executed. An 
Chart tab
Charts the run time statistics for the SQL statement using the alternative indexes.
Bottom Left Pane
Displays the Used Index which is the index(es) that are currently used by the SQL statement, the Index candidates which are generated by the Index Advisor, or the Index Sets that are created using the User-Defined Index window.
Bottom Right Pane - SQL Information Pane
Displays information about the SQL statement when it is using a specific index.
SQL for Cursor Checkbox
Adaptive Server uses a different query plan for a SQL statement that is embedded in a cursor declaration from the query plan when the SQL statement is not embedded in a cursor. This needs to be taken into account when retrieving the query plan or run time.
Therefore, if the SQL statement comes from or will be embedded in a cursor declaration then you need to select SQL for Cursor in the SQL Editor pane of Index Advisor window. This enables cursor simulation when retrieving the query plan and run time information.
This checkbox is automatically selected in the SQL Editor pane when you use the Send to Index Advisor function from the SQL Scanner if the SQL was extracted from within a cursor declaration.
In Adaptive Server 15 or later, the Declare Cursor Setting window is also available to select specific cursor settings.
Cursor Arguments
The cursor arguments should match the settings used for the SQL statement in your application code.
| Item | Description |
|
SEMI-SENSITIVE |
Specify that the worktable which holds the result set is populated only as the rows are fetched. Therefore changes to the data that occur while the cursor is opened may be visible in the result set. |
|
INSENSITIVE |
Specify that the data is copied to a worktable when the cursor is open which makes the data insensitive to changes in the data that may occur while the cursor is opened. |
|
SCROLL |
Specify that the cursor is scrollable meaning that you can position the cursor anywhere in the cursor result set for as long as the cursor is open. All scrollable cursors are read only. |
|
NO SCROLL |
Specify that the rows are retrieved one row at a time. All update cursors are non-scrollable. |