Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.8 - Installation Guide

Optimization Engine

During optimization, the SQL Optimizer employs a unique optimization engine that uses multiple SQL syntax transformation rules to produce a list of semantically equivalent SQL statements. This engine first transforms the original SQL statement, hence producing a group of optimized SQL statements. It then goes on rewriting each optimized SQL statement to produce another group of alternatives. The engine continues rewriting until the SQL statements cannot be rewritten any further or a set of user-defined quotas are reached.

One of the searching rules is illustrated in the following example:

SELECT *

FROM table_a  

WHERE table_a.key in (SELECT table_b.key  

  FROM table_b)                       

If table_b.key is an indexed column, the following transformation is executed:

SELECT *

FROM table_a  

WHERE EXISTS (SELECT 'x'  

FROM table_b                 

WHERE table_b.key = table_a.key)                

 

Although the above two SQL statements produce the same result, the database may evolve two different query plans. It is difficult to decide which SQL statement will run faster without taking into account the database structure, indexes, and data volume, so testing the SQL alternatives in your database environment in important to the process of selecting the best SQL alternative.

 

Related Topic

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating