Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - Release Notes

Optimization (2) (Optimization Tab)

SQL Rewrite (2)

ClosedView SQL Rewrite tab – SQL Rewrite (2) options

The SQL Rewrite (2) page under the SQL Rewrite tab on the Options window allows you to enable or disable certain specific optimization techniques and to define how duplicated access plans are eliminated.

Eliminate SQL alternative with

After SQL Rewrite has parsed the original SQL statement, it creates all the semantically equivalent SQL statements. It then eliminates alternative SQL statements based on one of two criteria: same access plans or same costs. Eliminating the SQL based on like access plans is more accurate, but can take longer because SQL Rewrite compares every operation in the access plans, instead of comparing only cost.

Eliminate SQL alternative with options

Description

Identical access plan (Default)

Eliminate optimized SQL statements with identical access plans.

Identical DB2 LUW cost

Eliminate optimized SQL statements that incur the same DB2 LUW cost amounts.

Advanced SQL transformation

Advanced SQL transformation option

Description

Enable transformation that adds COALESCE (Default = enabled)

Specify to apply the SQL syntax transformation rule that adds COALESCE to a column. When the data is retrieved, the COALESCE 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

Join Tables

Join Tables options

Description

Rewrite SQL using the same JOIN syntax as the original SQL (Default)

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, the optimization process rewrites the syntax using the Ansi-92 JOIN syntax. The outer join is not included 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 DB2 SQL Options are applied to the original SQL and the transformed SQL. So you can see SQL alternatives that use the JOIN syntax from the original SQL, but these SQL alternatives are simply the original SQL with a SQL Option 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 Ansi-92 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 DB2 SQL Options 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 a SQL Option 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 use the both types of SQL syntax for joining the tables. Each type of join syntax will result in a different alternative.

 

Related Topics

Related Documents