What are cost based and rule based approaches and the difference




Cost based and rule based approaches are the optimization techniques which are used in related to
databases, where we need to optimize a sql query.
Basically Oracle provides Two types of Optimizers (indeed 3 but we use only these two techniques., bcz
the third has some disadvantages.)
When ever you process any sql query in Oracle, what oracle engine internally does is, it reads the query and decides which will the best possible way for executing the query. So in this process, Oracle follows these optimization techniques.
1. cost based Optimizer(CBO): If a sql query can be executed in 2 different ways ( like may have path 1 and path2 for same query),then What CBO does is, it basically calculates the cost of each path and the analyses for which path the cost of execution is less and then executes that path so that it can optimize the quey execution.
2. Rule base optimizer(RBO): this basically follows the rules which are needed for executing a query. So
depending on the number of rules which are to be applied, the optimzer runs the query.
Use:
If the table you are trying to query is already analysed, then oracle will go with CBO.
If the table is not analysed , the Oracle follows RBO.
For the first time, if table is not analysed, Oracle will go with full table scan.

No comments:

Post a Comment