Oracle optimizer: RBO/CBO, rule/choose/first_rows/all_rows Glossary

Oracle optimizer has two optimization methods: Rule-Based Optimization (RBO) and cost-based optimization (cost-based optimization, (for short, CBO). In oracle8 and later versions, the CBO method is recommended for Oracle strong columns.
RBO mode: when analyzing SQL statements, the optimizer follows some predefined rules in Oracle. For example, we usually use indexes when a column in a where clause has an index.

CBO mode: it refers to the cost of the statement (COST). The cost here mainly refers to the CPU and memory. When determining whether this method is used, the optimizer mainly refers to the statistical information of tables and indexes. The statistical information shows the table size, the number of rows, and the length of each row. These statistics are not available in the database at first, but only after analyze. In many cases, the Optimizer may make an incorrect execution plan when the statistics expire, these information should be updated in a timely manner.

Note: indexing is not necessarily optimal. For example, if a table has only two rows of data, I/O can be used to retrieve the entire table. At this time, I/O is required twice for indexing, full table scan is the best

The optimization modes include rule, choose, first rows, and all rows:

Rule: rule-based.

Choolse: Oracle uses this method by default. When a table or index has statistical information, it adopts the CBO method. If the table or index does not have statistical information, the table is not particularly small, in addition, when the corresponding column has an index, the index is adopted and the RBO method is adopted.

First rows: It is similar to the choose method. The difference is that when a table has statistics, it returns the first few rows of the query in the fastest way, the overall response time is reduced.

All rows: This is what we call the cost method. When a table has statistics, it returns all rows of the table in the fastest way, improves the query throughput in general. If no statistical information is available, RBO is used.

Set which optimization mode to choose:

A. At the instance level, we can set optimizer_mode = rule/choose/first_rows/all_rows in the initsid. ora file. If the optimizer_mode parameter is not set, the choose method is used by default.
B. Set the sessions level through alter session set optimizer_mode = rule/choose/first_rows/all_rows.
C. Use hint (/* +... */) to set the statement level.

Why does a field in the table clearly have an index, but the execution plan does not go through the index?

1. The optimization mode is all_rows.
2. the table has been analyzed and has statistical information.
3. The table is small. As mentioned above, the Oracle optimizer does not think it is worth indexing.