Explain three different types of Oracle optimizer

Guidance:This article focuses onOracle optimizer3 different types of descriptions, and under what circumstances can different types of optimizer be used, and under what circumstances should we avoid using which optimizer, the following describes the specific content of the article.

A. RULE (based on Rules)

B. COST (COST-based)

C. CHOOSE (optional)

Set the default Oracle optimizer. various declarations of the OPTIMIZER_MODE parameter in the ora file, such as RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS. of course, you also overwrite SQL statements or sessions.

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

If the database optimizer mode is set to 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, the Oracle optimizer based on rules or costs is directly used.

The above content is an introduction to three types of Oracle optimizer. I hope you will find some gains.