SQL Engine - Query Optimizer (Query Optimization)

Oracle Database Sql Processing


Algebraic Optimization in a SQL Engine.

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.

Understanding the optimizer

Query Optimizer Process

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.

Influence the optimizer's choices

You can influence the optimizer's choices by



A CBO will try to minimize the expected cost of executing a query


A RBO (Rule based Optimizer) will apply a set of rules in order.

It's then sensitive to the order of the tables in the FROM clause and will use the order in which we enter them to choose a Nested Loop - Driving Table for the query if none of the predicates do so (in the event of a “tie”, the RBO will look at the order the developer typed in table names to pick what table to use first!).

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


Index Optimization

  • Data: If the data is an Unordered Set of tuples with 100 fields each.
  • Select Pattern: One specific attribute is always selected for a couple of specific rows identified by the 1st attribute
  • Optimization: Rewrite the data into a 99-Tuple of Maps with the key as the 1st attribute


Recommended Pages
Filter Early Algebraic Optimization
Algebraic Optimization

Same as: Algebraic Laws: (+) identity: x+0 = x (/) identity: x/1 = x () distributes: (nx+ny) = n(x+y) () commutes: xy = yx Apply rules 1,3,4,2 and we get Two operations instead of five,...
Bobj Designer User Interface
Business Object - Universe Designer

Universe Designer provides a connection wizard that allows you to connect to your database middleware. You can create multiple connections with Designer, but only one connection can be defined for each...
Card Puncher Data Processing
Calcite - Optimizer (RelOptCluster)

The optimizer is a program that takes a relational expression (query plan) and rewrites it with optimization rules. The output is still a relational expression and is generally called the physical plan....
Card Puncher Data Processing
Calcite - Planner (RelOptPlanner)

org/apache/calcite/plan/package-infoPlan provides an optimizer interface (ie Defines interfaces for constructing rule-based optimizers of relational expressions) Frameworks...
Card Puncher Data Processing
Calcite - Query Cost

in calcite. Calcite applies a Cost based optimizer by default that is called the Volcano planner. The cost is provided by the relational expression (relNode). See Cost is represented by org/apache/calcite/plan/RelOptCostRelOptCost...
Data System Architecture
Database management system (DBMS)

A Database_management_systemDBMS is a set of software programs that controls the organization, storage, management, and retrieval of data. A database is a collection of permanently stored data used by...
Relational Algebra Between Sql And Query Plan
Nested Loop - Adding More Tables

When a nested loops join uses four tables, the optimizer performs the following steps: Select a driving table. Perform a NESTED LOOPS join between the driving table and the second table. Perform...
Relational Algebra Between Sql And Query Plan
Nested Loop - Driving Table

The key to the performance of a NESTED LOOPS join is the order in which the tables are joined. The selection of the driving table, the first table in the join, is critical. The amount of repetition in...
Bi Server Architecture With Client
OBIEE - Hint

Database hints are instructions placed within a SQL statement that tell the database query optimizer the most efficient way to execute the statement. Hints override the optimizer’s execution plan,...
Bi Server Architecture
OBIEE - Query Compiler

The query compiler is responsible of the compilation of a logical sql in the query processing process of BI Server. The query compilation is composed of the following five phases: parsing, , navigation,...

Share this page:
Follow us:
Task Runner