Oracle Optimizer )!

Before executing an SQL statement, Oracle needs to analyze the statement execution plan and then execute it according to the execution plan. The execution plan of the Analysis Statement is completed by the Optimizer. In different cases, an SQL statement may have multiple execution plans, but at a certain point in time, there must be only one execution plan that is optimal and takes the least time.
PS: Rule, Choose, First rows, All rows

1. Optimization Method of Optimizer
There are two Optimization Methods for Oracle optimizer: Rule-Based Optimization (RBO) and Cost-Based Optimization (Cost-Based Optimization, (hereinafter referred to as CBO ).

A. RBO mode:
When analyzing SQL statements, the optimizer follows Oracle's predefined rules. For example, a column in a where clause is indexed.

B. CBO mode:
According to the meaning of words, it is 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 are only available after you perform analyze. In many cases, the Optimizer may make an incorrect execution plan when the statistics expire, because we should update this information in a timely manner. In Oracle8 and later versions, CBO is recommended for Oracle columns.

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

2. Optermizer Mode)

The optimization modes include Rule, Choose, First rows, and All rows. I will explain it as follows:

Rule: a Rule-based approach.

Choolse: This is what we should note. By default, Oracle uses this method. 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, the rule-based approach is adopted.

3. How to set which optimization mode to choose

A. Instance level

In init. set OPTIMIZER_MODE = RULE, OPTIMIZER_MODE = CHOOSE, OPTIMIZER_MODE = FIRST_ROWS, and OPTIMIZER_MODE = ALL_ROWS in the ora file to CHOOSE the four methods mentioned in 3, if you do not set the OPTIMIZER_MODE parameter, Choose is used by default.

B. Sessions level

Use SQL> ALTER SESSION SET OPTIMIZER_MODE =.

C. Statement level

Hint is required, for example:

　　
SQL> SELECT/* + RULE */a. userid,
2 B. name,
3 B. depart_name
4 FROM tf_f_yhda,
5 tf_f_depart B
6 WHERE a. userid = B. userid;
 
4. Why is there a clearly indexed field in a table sometimes? How can this problem be solved?

A. There are several reasons why indexes are not taken away:

♀You are using the all_rows method at the Instance level;

♀The statistical information of your table (the most likely reason );

♀Your table is small. As mentioned above, the Oracle optimizer does not think it is worth indexing.

B. solution:

♀You can modify the OPTIMIZER_MODE parameter in init. ora to Rule or Choose to restart the database. You can also use the Hint mentioned in 4;

♀Delete statistics SQL> analyze table table_name delete statistics;

♀It is correct that the table is small and does not take indexes.

5. Other problems

A. How to check whether A table or index is statistical information?

　　
SQL> SELECT * FROM user_tables
2 WHERE table_name = <table_name>
3 AND num_rows is not null;
　　
SQL> SELECT * FROM user_indexes
2 WHERE table_name = <table_name>
3 AND num_rows is not null;
 

B. if we use the CBO method first, we should update the statistical information of tables and indexes in a timely manner, so as not to generate realistic execution plans.

　　
SQL> ANALYZE TABLE table_name COMPUTE STATISTICS;
SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;
 

For specific ANALYZE statements, see the refrence document of Oracle8i/9i.