How does an optimizer SQL

This is how the optimizer works

The role of the optimizer is to find an efficient way to execute SQL statements. To do this, the optimizer must define an execution plan for a query. This plan includes decisions about the access order for tables referenced in the query, the join operators and access methods for each table, and whether materialized views not referenced in the query are used to compute parts of the query can. The optimizer attempts to choose the best plan for running the query during the join enumeration phase, which is where it generates possible access plans for a query and calculates the cost. The best access plan is the one that the optimizer believes will deliver the desired set of results in the shortest time and at the lowest cost. The optimizer determines the cost of each strategy found in the enumeration by estimating the number of reads and writes required.

In Interactive SQL, you can view the best access plan for running a query by clicking Tools ┬╗Plan Viewer. See Reading Graphic Plans and Reading Execution Plans.

Minimize the cost of returning the first line
Use semantically equivalent syntax
Reduce the cost of optimization queries

Optimizer estimates and column statistics
Optimize performance automatically
Basic assumptions of the optimizer
Use predicates in queries
Cost-based optimization with MIN and MAX functions
Plan caching