SQL Optimizer for SAP ASE 3.8 - User Guide

Advise Index Alternatives

To have the Index Advisor propose Index candidates for a SQL statement

1. Click .

2. 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.

3. Click to generate new Index candidates for the original SQL statement.  

4. 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.

Related Topics

Analyze Impact of New Indexes

Create Index(es)

Clear All Index Alternatives

Index Advisor Overview

Index Advisor Window

Index Advising Details

View Index Advising Details Window

The Index Advising Details window is optional and can be displayed after index candidates are generated. This window displays the number of index candidates generated.

The Index Advising Details window is shown every time the index generation is finished unless it is disabled. You can disable it by clearing the Show details on next index advising option in the Index Advising Details window.

To open the Index Advising Details window

Select View | Show Index Advise Details.

If no alternative index candidates are generated, is available for you to directly copy the original SQL statement from the Index Advisor to the SQL Optimizer for optimization. The optimization process will start automatically. It applies advanced SQL transformation technology to generate a list of semantically equivalent SQL statements that you can test in your environment to find the best performing SQL statement.

Related Topics

Advise Index Alternatives

Index Advisor Overview

Index Advisor Window

Add Index Alternatives

In addition to generating indexes from the Index Advisor window, you can also add your own Index candidates and combine them into index sets manually.

1. Click .

2. Enter the original SQL statement you want to analyze for new indexes that could be created to change SQL performance.

3. You can also copy the SQL statement directly from another window using the Send to Index Advisor function.
   
   It is recommended that you use the Advise Indexes function first to generate Index candidates before creating your own.

4. Click .

This window enables you to add new indexes or to construct index sets by combining two or more indexes together.

You need to first create your own index candidates and then group them into Index Sets. If you have generated Index candidates using the Advise Indexes function, notice that they are displayed in the User-Defined Index window and can be used to create Index sets.

Add user-defined indexes

If one or no index candidate exists, then the Add Index window displays automatically. Otherwise, click to the right of the list of indexes to add a new index.

The Add Index window enables you to construct your index using a graphical user interface without the need to construct the DDL for the index manually. Enter the index name, select the database, owner, table and columns on which you are defining the index and select the index type (Unique and Clustered and Non-clustered).

Create function-based indexes

Select the Function-based index checkbox to create the index as a function-base index. Computed columns and function-based indexes is a new feature in ASE 15.0. This feature allows developers to define a column as an expression and create indexes on expressions. Use to select the column to add the operation to it. Use to add the modified column to the selected columns for the function-based index. 

Create Index Sets

Three options are available for whether to include the new index in an Index Set:

• Add the index and add it to a new Index Set (Create new Index Set for this index)

• Add the index to an existing User-defined Index Set (Add to existing index set), or

• Add the index without adding it to any Index Set (Do not assign to any index set).

Click OK to add the index to the list of indexes on the left pane list [Create Index here]. If you choose to add a new Index Set, notice that the Index Set is added to right pane list [Create Index Set here]. You can add multiple User-defined Indexes and Index Sets.

Delete User-defined Indexes

Select the index you want to delete from the top left pane list [Create Index here] and click . You can only delete indexes that are not used by any Index Sets.

Add Index Sets

You can create new Index Sets by clicking from the top right pane [Create Index Set here]. In the Add Index Set window, enter a name for the Index Set and select the indexes you want in the Set. Click OK.

Delete user-defined Index Set

Select the Index Set you want to delete from the top right pane list [Create Index Set here] and click .

Add and remove index from Index Set

The bottom pane of the User-Defined Index window allows you to add and remove indexes from the Index Set by double-clicking the corresponding cell under the Index Set name.

Once you have finish creating your index(es) and Index Set(s), click OK. The new Index Sets are added and displayed on the tabs in the bottom left pane of the Index Advisor window.

Related Topics

Advise Index Alternatives

Index Advisor Overview

Index Advisor Window

Batch Show Plan

The index candidates are either generated with the Advise Index function or manually created by the User-Defined Index function. To evaluate the impact of the index candidates on the SQL statement, the query plan is needed. You can obtain the query plans for all the index candidates by using the Batch Show Plan.

Click .

Note: The indexes are physically created on the database, the query plan is retrieved, and then the indexes are dropped. This process may affect other SQL statements executing on the database during this period.

Related Topics

Index Advisor Overview

Index Advisor Window