Optimization methods, i.e. cost-based approach and role-based optimization

Describe optimization methods, i.e. cost-based approach and role-based optimization.

Oracle decides how to retrieve the necessary data whenever a valid SQL statement is processed.

This decision can be made using one of two methods:

Rule Based Optimizer

If the server has no internal statistics relating to the objects referenced by the statement then the RBO method is used. This method will be deprecated in the future releases of oracle.

Cost Based Optimizer

The CBO method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost based on the system resources.

Explain the rule-based optimizer and cost-based optimizer.

Rule-based optimizer - In a Rule-based optimizer, based on the access paths and their ranks, an execution plan is chosen. Ranking of the access path is heuristic and operation with the lower rank is used if there is more than one way to execute a SQL statement.

Cost-based optimizer - The most advised approach and uses an SQL statement and tries to weigh different ways (plan) to execute it. It also assigns a cost to each plan and chooses the plan with the smallest cost. CBO uses some statistics to process the cost across different plans.