SQL Engine - Query Optimizer (Query Optimization)

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > RDBMS - Sql Processing (Engine)

1 - About

Same as: Algebraic Optimization ??

A SQL statement can be executed in many different ways, such as:

  • full table scans,
  • index scans,
  • nested loops,
  • hash joins.

The query optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query.

The Query optimizer determines the most efficient way to execute a SQL statement after considering many factors including the Optimizer Goal.

This determination is an step in the processing of any SQL statement and can greatly affect execution time.

The optimizer might not make the same decisions from one version of Database to the next. In recent versions, the optimizer might make different decisions, because better information is available.

The output from the optimizer is an execution_plan that describes an optimum method of execution. The plans shows the combination of the steps Oracle Database uses to execute a SQL statement. Each step either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement.

In general the databases are going to correctly optimize queries. Queries are rarely incorrectly optimized when the statistics are good.

Advertising

3 - Understanding the optimizer

The query optimizer performs the following steps:

  • 1- The query transformer transform the original query (if it is advantageous)
  • 2- The optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.
  • 3- The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement.
  • 4- The optimizer compares the costs of the plans and chooses the one with the lowest cost.

4 - Influence the optimizer's choices

You can influence the optimizer's choices by

5 - Optimizer and Joins

Much work in database-systems has aimed at efficient implementation of joins, because relational systems commonly call for joins, yet face difficulties in optimising their efficient execution. The problem arises because inner joins operate both commutatively and associatively. In practice, this means that the user merely supplies the list of tables for joining and the join conditions to use, and the database system has the task of determining the most efficient way to perform the operation. A query optimizer determines how to execute a query containing joins.

Join algorithm Implementation

Advertising

6 - Reference

data/type/relation/engine/optimizer.txt · Last modified: 2017/09/13 16:16 by gerardnico