Query optimization and tuning
The query
optimizer is the component of a database management system that attempts to
determine the most efficient way to execute a query. The optimizer considers
the possible query plans for a given input query, and attempts to determine
which of those plans will be the most efficient. Cost-based query optimizers
assign an estimated "cost" to each possible query plan, and choose
the plan with the smallest cost. Costs are used to estimate the runtime cost
of evaluating the query, in terms of the number of I/O operations required,
the CPU requirements, and other factors determined from the data dictionary.
The set of query plans examined is formed by examining the possible access
paths (e.g. index scan, sequential scan) and join algorithms (e.g. sort-merge
join, hash join, nested loops). The search space can become quite large depending
on the complexity of the SQL query.
The query optimizer cannot be accessed directly by users. Instead, once queries
are submitted to database server, and parsed by the parser, they are then
passed to the query optimizer where optimization occurs.