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 sections.
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 sections provide details on the optimization phases.
Cost metrics for operations performed by the SQL engine's storage manager and query processor components
