Automatic Transformation of SQL
Statements 
Use
The database system tries to transform SQL statements so that the resulting statement is semantically identical but faster to process. The transformations are purely rule-based and do not take account of statistical information. The main purpose of transformations is to avoid intermediate result sets.
In some cases, it may not be possible to represent the statement that results from the transformation as a syntactically correct SQL statement.
Integration
After an SQL statement has been transformed, the SQL Optimizer determines the most cost-effective search strategy for accessing the data.
See also:
Features
The database system can transform the following types of SQL statements:
● SELECT
● CREATE CURSOR FOR SELECT
● EXPLAIN SELECT
Activities
You configure automatic transformation of SQL statements with the special database parameter OPTIMIZE_QUERYREWRITE.
When the database system has transformed an SQL statement, the result of the EXPLAIN Statement contains one of the following:
● QUERY REWRITTEN ON SQLSTATEMENT LEVEL
● QUERY REWRITTEN ON OPERATOR LEVEL
Example
The database system transforms the following SQL statement:
SELECT * FROM v WHERE a=1, where v is defined as CREATE VIEW v (a) as SELECT b FROM t1 UNION ALL SELECT c FROM t2
The result of the first transformation is a statement that has a smaller intermediate result set:
select b as a from (select b from t1 where b=1 union all select c from t2 where c=1)
After another transformation, the resulting statement no longer has an outer SELECT.
select b from t1 where b=1 union all select c from t2 where c=1
See also: