Introduction to three types of Oracle optimizer

Oracle optimizer has three types, namely, RULE based on rules), COST Based on COST) and CHOOSE selection. We all know that the Oracle optimizer with default settings can be configured through init. various declarations of the OPTIMIZER_MODE parameter in the ora file.

For example, RULE, COST, CHOOSE, ALL_ROWS, and FIRST_ROWS. You certainly overwrite them at the SQL statement level or session level.

To use the Cost-Based Optimizer CBO, Cost-Based Optimizer), you must always run the analyze command to increase the object statistics in the database.

If the optimizer mode of the database is set to selective CHOOSE), the actual Oracle optimizer mode is related to whether the analyze command has been run. If the table has been analyze, the optimizer mode will automatically become CBO. Otherwise, the database will adopt the RULE form optimizer.

By default, Oracle uses the CHOOSE optimizer. To avoid unnecessary full table scan, you must avoid using the CHOOSE optimizer, instead, we directly use a rule-based or cost-based optimizer.

The above content introduces three types of Oracle optimizer. I hope you will get some benefits.