The optimization process is divided into several phases. Some phases deal with internal infrastructure, such as minimizing data handling or temp-table usage. Others deal with significant cost factors and are straightforward to understand. Each phase addresses a specific type of optimization, as explained in the following topics.

The optimizer follows a cost-based model. In each stage, whenever multiple alternatives are available, the optimizer estimates the cost for each and selects the cheapest. The cost computation takes into account:

  • Cost metrics for operations performed by the SQL engine's storage manager and query processor components
  • Index definitions
  • Properties of join algorithms
  • Column selectivity
  • Filter factors
  • Table cardinality

The following topics provide details on the optimization phases.