Optimization Settings for ASE 15
The Optimization for ASE 15 tab on the Preferences window consists of six buttons that allow users to select the level of optimization intelligence and options for optimization while using the SQL Optimizer module when connected to Adaptive Server 15.
Intelligence (ASE 15)
Intelligence (Optimization for ASE 15 Tab)
View Optimization for ASE 15--Intelligence page
The Intelligence page on the Optimization tab of the Preferences window allows you to select your optimization intelligence settings.
Optimization intelligence settings enables you to either choose the settings used during optimization or allows you to select the predefined "Intelligence Level" settings.
Custom
Customize the settings on the Optimization, Forces/Goal, Criteria, and Quota pages.
Predefined
Use the predefined optimization intelligence levels. The items selected on the Forces and Quota pages and the Temp table generation option on the Optimization page change according to the level selected. Levels range from 0 to 10. The higher the level the more intelligent the SQL Optimizer is and the more likely of finding a better SQL alternative.
The Abstract Plan settings on the Abstract Plan page and the Join table and Advanced SQL transformation options on the Optimization page is adjusted independent of the optimization intelligence level.
Optimization
Optimization (Optimization Tab)
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/Goal (ASE 15)
The Forces/Goal page on the Optimization for 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
Set Forceplan On
Specify whether to enforce the tables to be joined in the order specified in the FROM clause.
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.
Optimization Goal
allrows_mix
Specify to set the optimization goal for a database environment that is a mixture of online transactions and database warehouse transactions.
allrows_dss
Specify to set the optimization goal for a database environment that is purely for database warehouse transactions.
allrows_oltp
Specify to set the optimization goal for a database environment that is purely for oltp (online transaction processing) transactions.
fastfirstrow
Specify to set the optimization goal to retrieve the first row as fast as possible.
Note: The forces that are marked with an asterisk are counted in the Number of forces selected on the Quota Preferences page.