Query Optimizer

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 loop join). The search space can become quite large depending on the complexity of the SQL query.

Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs. However, some database engines allow guiding the query optimizer with hints.

Read more about Query OptimizerImplementation

Other articles related to "query optimizer, query, optimizers":

Explain Plan - Query Tuning
... The query optimizer will not always choose the best query plan for a given query ... In some databases the query plan can be reviewed, problems found, and then the query optimizer given hints on how to improve it ... In other databases alternatives to express the same query (other queries that return the same results) can be tried ...
Query Optimizer - Implementation - Cost Estimation
... One of the hardest problems in query optimization is to accurately estimate the costs of alternative query plans ... Optimizers cost query plans using a mathematical model of query execution costs that relies heavily on estimates of the cardinality, or number of tuples, flowing through each edge in a query plan ... in turn depends on estimates of the selection factor of predicates in the query ...

Famous quotes containing the word query:

    Such condition of suspended judgment indeed, in its more genial development and under felicitous culture, is but the expectation, the receptivity, of the faithful scholar, determined not to foreclose what is still a question—the “philosophic temper,” in short, for which a survival of query will be still the salt of truth, even in the most absolutely ascertained knowledge.
    Walter Pater (1839–1894)