Introduction to the three major types of Oracle Optimizer

There are three types of Oracle optimizer, rule (based on rules), cost (based on costs), and choose (selectivity), which we all know to set the default associated Oracle optimizer, which can be passed to the Init.ora file Optimizer_ Various declarations of the mode parameter.

such as Rule,cost,choose,all_rows,first_rows. You can certainly overwrite it at the SQL sentence level or at the session level.

In order to use the cost-based optimizer (CBO, cost-based Optimizer), you must frequently run the Analyze command to increase the accuracy of the object statistics (objects statistics) in the database.

If the optimizer mode of the database is set to selective (CHOOSE), then the actual Oracle Optimizer mode will be related to whether the Analyze command has been run. If the table has been analyze, the optimizer mode will automatically become the CBO, whereas the database will use the rule-form optimizer.

By default, Oracle uses the Choose Optimizer, and to avoid unnecessary full table scans, you must try to avoid using the Choose Optimizer directly, using either a rule-based or cost-based optimizer.