The 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 influence execution time.
A SQL statement can be executed in many different ways, including the following:
- Full table scans
- Index scans
- Nested loops
- Hash joins
The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides the (CBO) cost-based and (RBO) rule-based optimization. In general, use the cost-based approach.
