SQL Optimizer for SAP ASE 3.9.1

Saving the abstract plan for a SQL statement preserves how the SQL statement is executed. Therefore, when changes occur in the database environment, the query plan used to execute the SQL statement remains constant.

One major advantage of saving the abstract plan is that you can optimize the SQL statement without altering the SQL text. This is an ideal solution for when you do not have the source code from a vendor but want to improve the performance of the database application. The saving of the abstract plan can be very useful when you are considering making changes to the configuration parameters or you are migrating to another version of Adaptive Server and you want to preserve the current performance of SQL statements that are critical to the performance of your applications.

These database environment changes may include:

Parallel degree
Table partitioning
Indexing
Database software upgrades
Changes in data columns

You can save an abstract plan from the following modules:

To save in...	See:
SQL Optimizer	Save Abstract Plan
SQL Scanner	Save Abstract Plan.
Index Impact Analyzer	Right Pane for SQL Statements
Configuration Analyzer	Right Pane for SQL Statement
Migration Analyzer	Right Pane for SQL Statement
SQL Repository	SQL Repository Window

Related Topics

Abstract Plan Manager Overview

Abstract Plan Group

Abstract Plan Compatibility with Original SQL

Abstract Plan Manager Window

Open the Abstract Plan Manager

Abstract Plan Group Functions

Export a Group to a Table

Import an Abstract Plan for each User

Abstract Plan ID Functions

Use Saved Abstract Plans

Abstract Plan Compatibility with Original SQL

Abstract Plan Manager > Abstract Plan Compatibility with Original SQL

Adaptive Server generates an abstract plan based on the SQL syntax itself. If a SQL statement is transformed to another syntax that the Adaptive Server optimizer cannot transform internally, then the abstract plans are not compatible. Therefore, even though two SQL statements may be semantically equivalent, their abstract plans are not compatible.

For example, take this SQL statement as your original SQL statement:

SELECT A CITY FROM A, B, C

WHERE A.CITY =B.CITY

AND B.CITY=C.CITY

And take this semantically equivalent alternative SQL:

SELECT A.CITY FROM A, B, C

WHERE A.CITY =B.CITY

AND B.CITY=C.CITY

AND A.CITY=C.CITY

The transforms adds the new condition, A.CITY=C.CITY, and if the Adaptive Server optimizer cannot generate A.CITY=C.CITY internally for your original SQL, then an index scan from A.CITY to C.CITY or from C.CITY to A.CITY is not possible for your original SQL. So, the new abstract plan is not compatible for the original SQL.

Also, if an OR condition is transformed to a UNION, the new abstract plan is so different from the original SQL that there is no way for the original SQL to be compatible with the new abstract plan.

Related Topics

Abstract Plan Manager Overview

Abstract Plan Group

Why Save the Abstract Plan?

Abstract Plan Manager Window

Open the Abstract Plan Manager

Abstract Plan Group Functions

Export a Group to a Table

Import an Abstract Plan for each User

Abstract Plan ID Functions

Use Saved Abstract Plans