Query execution plans

When a SELECT statement or other DML operation is submitted to the database server, the query execution optimizer designs a query execution plan. The query execution optimizer is often referenced as the query optimizer.

To design a query execution plan, and estimate the costs of candidate query plans, the query optimizer considers a wide range of information including:
  • Specifications that identify the database objects, predicates, filters, joins, and other operations in the SQL syntax that defines the query operation
  • System catalog information about indexes and constraints on the tables, views, and columns that are referenced or implied in the query
  • Data distribution statistics for the tables and indexes, or for their fragments, that are referenced or implied in the query
  • Optimizer directives that are specified inline or as external optimizer directives that favor or avoid subsets of the potential query plans
  • Information in the database server environment or in the session environment that affects the query execution optimizer