SQL Optimizer for SAP ASE 3.9.1 - Installation Guide

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