Optimizer Operations while executing SQL statement

Whenever you write an sql query, the query can be executed in many different ways, such as full table scans, index scans, nested loops, and hash joins. The query optimizer checks the various ways and 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 a plan that describes an optimum method of execution. The Oracle server provides query optimization.

Let's have a look at the operations that optimizer performs while processing SQL operations.

1)Evaluation of expressions and conditions: The optimizer first evaluates expressions and conditions containing constants as fully as possible.

2)Statement transformation: For complex statements involving, for example, correlated subqueries or views, the optimizer might transform the original statement into an equivalent join statement.

3)Choice of optimizer goals: The optimizer determines the goal of optimization. This topic is discussed later.

4)Choice of access paths: For each table accessed by the statement, the optimizer chooses one or more of the available access paths to obtain table data.

5)Choice of join orders: For a join statement that joins more than two tables, the optimizer chooses which pair of tables is joined first, and then which table is joined to the result, and so on.

Besides optimizer determination, the application designer can use hints in SQL statements to instruct the optimizer about how a statement should be executed.

Note: Before oracle 10g there was available CBO and RBO in case of optimizer optimization.

CBO-Cost Based Optimizer: Execution plan is calculated by taking into account the distribution of data. Starting from 10g optimizer only use CBO while optimization.

RBO-Rule-based optimizer: Chooses an execution plan for SQL statements based on the access paths available and the ranks of these access paths. If there is more than one way, then the RBO uses the operation with the lowest rank. This feature has been desupported.