SQL Engine - Query Optimizer Goal

Oracle Database Sql Processing


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

Choosing a goal

Optimizer Goal Description
best throughput

best response time
The optimizer chooses the least amount of resources necessary to process all rows

the first row
accessed by a SQL statement

By default, the goal of the query optimizer is the best throughput.

Choose a goal for the query optimizer based on the needs of your application:

  • For system where the last result is most important, optimize for best throughput.

Example application performed in batch, such as data access tool applications. Response time is less important, because the user does not examine the results of individual statements while the application is running.

  • For OLTP system where the user is waiting to see the first row such such as ERP, optimize for best response time.

Usually, response time is important in interactive applications, because the interactive user is waiting to see the first row or first few rows accessed by the statement.

Discover More
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
Oracle Database - Optimizer Goal (CBO/RBO)

in Oracle In Oracle 9i and before the query plan generated by the Oracle database were determined by certain rules In 10g and above, it is based on The OPTIMIZER_MODE initialization parameter...
Query Optimizer Process
SQL Engine - Query Optimizer (Query 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...

Share this page:
Follow us:
Task Runner