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

Advise Index Alternatives

Index Advisor > Advise Index Alternatives

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

  1. Click image\B_IndexAdvisor.gif.

  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 image\B_AdviseIndexes.gif 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 image\b_AbortAdviseIndexes.gif.

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, Index2IndexN. 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

Index Advising Details

Index Advisor > Index Advising Details

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

Add Index Alternatives

Index Advisor > 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 image\B_IndexAdvisor.gif.

  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 image\B_UserDefinedIndex.gif.

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 image\B_AddIndex.gif 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 image\B_SelectColumn.gif to select the column to add the operation to it. Use image\B_AddToFunctionBasedIndex.gif 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 image\B_RemoveIndex.gif. You can only delete indexes that are not used by any Index Sets.

Add Index Sets

You can create new Index Sets by clicking image\B_AddIndex.gif 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 image\B_RemoveIndex.gif.

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

Batch Show Plan

Index Advisor > 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 image\B_BatchShowPlan.gif.

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

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating