1 SQL Statement Transformation

The SQL Optimizer consists of “Query Transformer”, “Cost Estimator” and “Explain Plan Generator”. Most of the SQL statements will be transformed more or less by the optimizer before generating the execution plan for the purpose of getting the best performance.
Here are some examples of query transformation…

(1) sales_qty > 1200 / 12 
(2) sales_qty > 100 
(3) sales_qty * 12 > 1200

The predicate (1) will be transformed into (2), but the (3) will not. This is because SQL optimizer will not “move” the condition across the “operator” (>).

Another example, the IN list will be transformed using “OR” operator.

(1) job IN ('MANAGER', 'CLERK') 
(2) job = 'CLERK' OR job = 'MANAGER'

“BETWEEN” will be transformed using “>=” and “<=” operators.

(1) sales_qty BETWEEN 100 AND 200 
(2) sales_qty >= 100 AND sales_qty <=200

“ANY” operator will be transformed using “OR” operators in some cases…

(1) sales_qty > ANY ( 100, 200) 
(2) sales_qty > 100 OR sales_qty > 200