Detailed Oracle RBO/CBO Optimizer ____oracle

Oracle's optimizer has two optimizations, namely, rule-based optimization (rule-based optimization, short rbo) and cost based optimization (cost-based optimization, referred to as CBO), In Oracle8 and later versions, the Oracle Strong column recommends the CBO approach

Rbo: When the optimizer analyzes SQL statements, it follows some rules that are predetermined within Oracle. For example, we usually go to the index when a column in a WHERE clause has an index.

CBO Way: It is to see the cost of the statement, where the cost is mainly CPU and memory. When the optimizer determines whether to use this method, the main reference is the statistics of tables and indexes. Statistics give information about the size of the table, the number of rows, the length of each line, and so on. These statistics are not at first in the library, is done after the analyze, many of the time delay statistics will cause the optimizer to make a wrong execution plan, because some should update the information in a timely manner.

Note: Indexing is not necessarily excellent, such as a table with only two rows of data, one IO can complete the whole table retrieval, and then go to the index when the need for two times Io, when the full table scan (scan) is the best

Optimization modes include rule, Choose, first rows, all rows four ways:

rule: a rule-based approach.

Choolse: This is the way Oracle is used by default. Refers to when a table or index has statistics, the way of the CBO, if the table or index does not have statistical information, the table is not particularly small, and the corresponding columns indexed, then go to the index, walk Rbo way.

First rows: It's similar to the Choose approach, and the difference is that when a table has statistics, it will be the quickest way to return the initial rows of the query, reducing the response time overall.

All rows: That is what we mean by cost, when a table has statistics, it returns all the rows of the table in the quickest way, increasing the throughput of the query as a whole. There is no statistical information on the way to go rbo.

set which optimization mode to choose:

A, instance level we can set in the Initsid.ora file optimizer_mode=rule/choose/first_rows/all_rows if not set Optimizer_ The mode parameter defaults to the Choose method.
B, the sessions level is set by alter session set Optimizer_mode=rule/choose/first_rows/all_rows.
C, the statement level with hint (/*+ ... *) to set

Why a field in a table is indexed, but the execution plan does not go to the index.

1, the optimization mode is all_rows way
2, the table has been analyze, has the statistical information
3, the table is very small, as mentioned above, the Oracle optimizer does not consider it worthwhile to go to the index.