Difference between Optimizer=ALL_ROWS and Optimizer=CHOOSE

Choose - Choose either Rule based or Cost based depend on the
availability of statistics. If statistics is available on the table it uses
CBO and if not it uses RBO.

       ALL_ROWS - Choose based optimization, statistics is needed. U could
pass as hint in ur query.

But in the majority of cases with optimizer_mode=choose ( and with a good
statistics ) the CBO will be able to find a good execution plan for
yoursqueries .
  • Interview Candidate
  • 1
  • 8490

Showing Answers 1 - 1 of 1 Answers

Manoj Seemar

  • Nov 2nd, 2007
 

The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance.


CHOOSE

The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.

  • If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
  • If the data dictionary contains only some statistics, then the cost-based approach is still used, but the optimizer must guess the statistics for the subjects without any statistics. This can result in suboptimal execution plans.
  • If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.

ALL_ROWS

The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions