How does Oracle decide how to retrieve the necessary data whenever a valid SQL statement is processed?

Options
- Using Rule Based Optimizer
- Using Cost Based Optimizer
- Both a and b
- SQL-level tuning

CORRECT ANSWER : Both a and b

Discussion Board
Rule-Based Optimizer (RBO) and Cost-Based Optimizer (CBO)

Using Rule Based Optimizer and Cost Based Optimizer, Oracle decide how to retrieve the necessary data whenever a valid SQL statement is processed. When a valid SQL statement is sent to the server for the first time, Oracle produces an execution plan that describes how to retrieve the necessary data.

Rule-Based Optimizer (RBO): This was the original optimization method and as the name suggests, was essentially a list of rules Oracle should follow to generate an execution plan. Even after the cost-based optimizer was introduced, this method was used if the server had no internal statistics relating to the objects referenced by the statement, or if explicitly requested by a hint or instance/session parameter. This optimizer was made obsolete, then deprecated in later versions of the database.

Cost-Based Optimizer (CBO): The CBO uses database statistics to generate several execution plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation.

Prajakta Pandit 02-9-2017 06:20 AM

Write your comments


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)


Related Content

Advertisement
 
▲