Gauri Shiv Enterprises, Committed to share knowledge

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 sql statement after considering many factors related to the objects referenced and the conditions specified in the query. By default, the goal of the query optimizer is the throughput, this means that it choose the least amount of resource necessary to process all rows accessed by the statement. oracle can also optimize a statement with the goal of best response.

Query optimizer mode

Normal Mode :- The optimizer compiles the sql and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of sql statement.

Tuning mode :- The optimizer performs additional analysis to check the execution plan produced under normal mode can be further improved .The output of the query optimizer is not an execution plan but it series of action.