Optimization
The Optimization page on the Optimization tab of the Preferences window allows you to decide which SQL syntax to use for the table joins, whether to apply some the advanced SQL transformation rules, and if you would like the SQL Optimizer to generate alternatives that use temporary tables.
Temp table
Temp table generation
If selected, alternative SQL statements that use temporary tables to obtain the exact same results may be generated during the optimization process.
Apply selected forces to temp table SQL
If selected, forces are applied to the SQL statement that create and use the temporary table. This option is only available if the Temp table generation checkbox is selected.
Join tables (for ASE 15 or later)
Rewrite SQL using the same JOIN syntax as the original SQL
Specify that the alternative SQL statements join the tables in the FROM clause using the same SQL syntax that is used in the original SQL statement. If the original SQL statement contains both syntax types, then the optimization process will rewrite the syntax using the Ansi-92 JOIN syntax. The outer join is not including in this conversion.
Rewrite SQL using the Ansi-92 JOIN syntax
Specify to use the JOIN clause from the Ansi-92 SQL standard when generating the SQL alternatives. During the optimization, the SQL statement is converted to the Ansi-92 SQL standard and then SQL syntax transformation rules are applied to rewrite the converted SQL statement. Next, the ASE optimization forces, goals, and criteria are applied to the original SQL and the transformed SQL. So you may see SQL alternatives that use the JOIN syntax from the original SQL and not the Ansi-92 JOIN syntax, but these SQL alternatives are simply the original SQL with the ASE optimization options applied.
The outer join is not including in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the outer join using the (+) operator. So to avoid producing the wrong result set, the conversion of the outer join syntax cannot be applied.
For example:
SELECT DPT_ID
FROM EMPLOYEE
INNER JOIN DEPARTMENT
ON EMP_DEPT = DPT_ID
Rewrite SQL without using the Ansi-92 JOIN syntax
Specify to join tables in the FROM clause without the JOIN syntax or using comma. The join analysis occurs in the WHERE clause which specifies the column in one table that is compared to a column in another table. During the optimization, the SQL statement is converted from the Ansi-92 SQL standard and then SQL syntax transformation rules are applied to rewrite the converted SQL. Next, the ASE optimization forces, goals, and criteria are applied to the original SQL and the transformed SQL. So you may see SQL alternatives that use the JOIN syntax from the original SQL, but these SQL alternatives are simply the original SQL with the ASE optimization options applied.
The outer join is not including in this conversion because Ansi-92 OUTER JOIN syntax does not always retrieve the same result set as the outer join using the (+) operator. So to avoid producing the wrong result set, the conversion of the outer join syntax cannot be applied.
For example:
SELECT DPT_ID
FROM EMPLOYEE,
DEPARTMENT
WHERE DPT_ID = EMP_DEPT
Rewrite SQL with and without using the ANSI-92 JOIN syntax
Specify to join tables using either one of the JOIN syntax methods. The OUTER JOIN is not including in this conversion because Ansi-92 JOIN syntax is needed to define an OUTER JOIN.
Advanced SQL transformation
Enable transformation that adds COALESCE.
Specify to apply the SQL syntax transformation rule that adds COALESCE to a column. When the data is retrieved, the COALSECE function, which in this case is not actually doing anything to change the value of the column, causes a full table scan or the database to pick another index to use. For example:
SELECT *
FROM EMPLOYEE,
DEPARTMENT
WHERE COALESCE(DPT_ID, DPT_ID) = EMP_DEPT
Forces (Pre-ASE 15)
The Forces page on the Optimization for Pre-ASE 15 tab of the Preferences window allows you to decide if you would like to have the Adaptive Server forces applied to alternative SQL statements. By adding an Adaptive Server force, you can force the Adaptive Server’s internal optimizer to choose a particular query plan.
Set
Specify whether to enforce the tables to be joined in the order specified in the FROM clause.
{Adaptive Server 15 or later}
Specify whether to consider using merge joins. If selected, the parameter will toggle against the server sort_merge setting.
{Adaptive Server 15 or later}
Specify whether to use join transitive closure. If selected, the parameter will toggle against the server JTC setting.
{Adaptive Server 15 or later}
Specify the number of tables that the optimizer considers at one time while determining table join order.
Parallel force
PARALLEL
Specify whether to enforce the use of a particular parallelism degree to access the table if parallel query is enabled in the database server.
Index force
INDEX
Specify whether to enforce the use of a particular index to access the table.
Note: The forces that are marked with an asterisk are counted in the Number of forces selected on the Quota Preferences page.
Quota (Pre-ASE 15)
View Optimization for Pre-ASE 15 - Quota
The Quota page on the Optimization tab of the Preferences window allows you to restrict the number of SQL transformations produced during optimization process. The optimization quotas can only be changed when the optimizer intelligence level is set to custom.
Syntax Transformation Quota (Range = 1 to 99,999)
The maximum number of SQL statements generated by applying rules in the Feedback-Searching engine. The default quota of 100 is normally sufficient for most of the complicated SQL statements. However you can increase the quota to tackle exceptionally complicated SQL statements with very high levels of table joins or multiple levels of nested sub-queries.
This value controls the number of SQL statements that are generated during the process of rewriting the syntax of the SQL statement.
Parallel Quota (Range = 10 to 99,999)
The maximum number of SQL statements generated by applying the degree of parallelism in the Feedback-Searching engine. Only applicable if the MAXDOP checkbox is selected.
Forces Quota Ratio (Range = 1% to 100%)
The percentage used to calculate the maximum number of SQL statements to apply forces.
This value determines the number of SQL alternatives that are generated by applying the Forces to the original SQL and the SQL alternatives that were created by transforming the SQL syntax.
Number of forces selected (forces with * only)
A read only field indicating the number of forces selected. Only the forces that have an * after the name on the Forces page are counted. This figure is used to calculate the maximum number of SQL statements generated by applying forces, ((Syntax Transformation Quota + Parallel Quota )* Forces Quota Ratio%)*Number of Forces Selected = Forces Quota.
Total Quota
The maximum number of SQL statements generated during optimization, this figure consists of: Syntax Transformation Quota + Parallel Quota + Forces Quota.
Table Join Permutation Quota (Range = 50 to 999,999)
The maximum number of table joins rearrangements to generate a new table join access path during optimization.
Note: You should bear in mind that the higher the quota, the longer it might take to optimize a complicated SQL statement.
Abstract Plan
The Abstract Plan page on the Optimization tab of the Preferences window allows you to include the retrieval of the abstract plan for the SQL statements. This requires Adaptive Server 15 or later.
Dump abstract plan
Specify whether to retrieve the abstract plan for the SQL statement whenever the query plan is retrieved. The abstract plan displays in the SQL Optimizer window. The abstract plan is not saved on the database until you deliberately save it.
When this checkbox is selected, the selected abstract plan icon 
Note: For third-party package applications, you are able to optimize SQL statements without changing the source code by saving the abstract plan.
Group name
Specify the abstract plan group name where the abstract plans are saved. The default group names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.
ap_stdout is used by default to capture an abstract plan.
ap_stdin is used by default to retrieve the abstract plan associated with a SQL statement during the execution of the SQL statement.
Abstract Plan Manager button
Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.
Check compatibility with Original SQL
Specify whether you want to check the compatibility of the optimized SQL statements with the original SQL. When this option is selected, the AP Compatibility column displays on the SQL Run Time pane of the SQL Optimizer window and in the Batch Run Criteria window.
Note: A SQL statement may have many different "semantically equivalent" SQL statements. Each statement has an abstract plan. Even though SQL statements are "semantically equivalent", the abstract plans may not be compatible with the original SQL statement. When you check this option, the alternative abstract plans that are compatible with your original SQL are identified.