Oracle Database - Query optimizer

A SQL statement can be executed in many different ways, such as full table scans, index scans, nested loops, and 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. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.

The output from the optimizer is an Oracle Database - Execution/Query 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.

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.

You can influence the optimizer's choices by

• setting the Oracle Database - Optimizer Goal (CBO/RBO) (either throughput or response time)
• gathering representative Oracle Database - Optimizer Statistics for the schema objects (tables or indexes)
• using hints to instruct the optimizer about how a statement should be executed.
• changing some initialization parameters

• The Query Optimizer