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.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
