What is a query optimizer?

Query optimization is of great importance for the performance of a relational database, especially for the execution of complex SQL statements. A query optimizer determines the best strategy for performing each query. The query optimizer chooses, for example:

• whether or not to use indexes for a given query
• which join techniques to use when joining multiple tables

These decisions have a tremendous effect on SQL performance, and query optimization is a key technology for every application, from operational systems to data warehouse and analysis systems to content-management systems. The query optimizer is entirely transparent to:

1. the application
2. the end-user

Extreme sophistication

Because applications may generate very complex SQL, query optimizers must be extremely sophisticated and robust to ensure good performance. For example, query optimizers transform SQL statements, so that these complex statements can be transformed into equivalent, but better performing, SQL statements.

Cost-Based

Query optimizers are typically 'cost-based'. In a cost-based optimization strategy:

1. multiple execution plans are generated for a given query
2. an estimated cost is computed for each plan

The query optimizer chooses the plan with the lowest estimated cost.