Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.8 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

Index Advisor

Index Advisor Overview

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.


Related Topic

Index Advisor Window

Index Advisor > 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:




Database where index resides


Owner of the index


Indexed table


Indexed column(s)


Index name. All indexes generated by SQL Optimizer will be prefixed with QUEST_SX_IDX_.


Selectivity value. The selectivity is the percentage of rows in the table that the SQL statement selects. A SQL statement that selects a small percentage of the rows from a table has good selectivity, while a query that selects a large percentage of rows has poor selectivity. The maximum selectivity value is 1 and it corresponds to the best selectivity.

Note: A Sample Block of the actual table data is used to calculate the selectivity value therefore it is possible that the value may vary according to what Sample Block is used.


Index DDL for creating the index.

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 image\I_RedX.gif is placed in the far left column if for some reason the index is not created when the SQL statement is executed.

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.


Related Topics

SQL for Cursor Checkbox

Index Advisor > 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


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.


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.


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.


Specify that the rows are retrieved one row at a time. All update cursors are non-scrollable.


Related Topics

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating