Explain rule-based optimizer and cost-based optimizer

Explain rule-based optimizer and cost-based optimizer.

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.
Oracle database objects
Oracle database objects - Tables, indexes, views, synonyms, sequences, partitions, clusters, stored procedure and packages, user-defined data types, table spaces, constraints......
Oracle correlated sub-queries
Oracle correlated sub-queries - A query which uses values from the outer query is called as a correlated sub query. The subquery is executed once and uses the results for all the evaluations in the outer query.......
SQL-level tuning
Tuning disk and network I/O subsystem to optimize the I/O time, network packet size and dispatching frequency is called the server kernel optimization.....
Post your comment