Speaking of Oracle's Optimizer (Optimizer)

The purpose of this article: 1, say some of Oracle's Optimizer and some of its related knowledge. 2. Answer why some fields of a table clearly have an index, when observing some SQL execution plans, find the problem that you don't pay indexes. 3, if you have doubts about first_rows, all_rows can also look at this article.

let's start:

Oracle first analyzes the execution plan of the statement before executing a SQL, and then press the execution plan. The operation plan of the analysis statement is done by an Optimizer. Different situations, a SQL may have a variety of implementation plans, but at a certain point, there must be only one implementation plan is optimal, and it takes time. I believe that you will use PL / SQL Developer, Toad and other tools to see a statement execution plan, but you may have questions about Rule, Choose, First Rows, All Rows, because I was the same, at the time Doubtful why the above different items have been selected, and the implementation plan has changed?

1, optimizer optimization

Oracle's optimizer has two optimization methods, namely rule-based optimization methods (RBO) and cost-based optimization, referred to as CBOs. A, RBO mode: The optimizer is analyzed when analyzing the SQL statement. Some rules within the Oracle internal predetermined rules. For example, we are common when a column in a WHERE clause has an index. B, CBO method: Idi-Id, it is known that it is the price of the statement (COST), and the cost here mainly refers to CPU and memory. When it is determined whether or not this method is used, the statistics are mainly referred to with reference to the statistics of the tables and indexes. Statistics give the size of the table, there are information on the length of each line, each line. These statistics have not been in the library. It is that you appear after you do Analyze, and a lot of time expiration statistics will make the optimizer make a wrong execution plan, which should update this information in time. In Oracle8 and later versions, the Oracle column is recommended to use CBO.

We must make a clear, not to take an index is excellent, such as a table only two lines of data, once I can complete the full table search, and when I take the index, I need two IO, which is a full table for this table. Full Table Scan is the best.

2. Optimization mode of the optimizer (OpterMizer mode)

Optimization mode includes four ways of Rule, Choose, First Rows, All Rows, that is, what we mentioned above. Let me explain the following:

Rule: Don't say more, just rushed-based approach.

Choolse: This is what we should follow, the default is that Oracle is in this way. It means that when a table or or index has statistics, walk the way, if the table or index is not statistical, the table is not particularly small, and the corresponding column has an index, then take index, walk RBO the way.

First Rows: It is similar to the Choose mode, which is different when a table has statistics, which will be the first one of the first few lines of queries in the fastest way, reducing the response time.

All rows: The way we call Cost, when a table has statistics, it will return all the rows of the table in the fastest way, and improve the throughput of the query. There is no statistical information to rule.

3, how to set which optimization mode selected

A, Instance level

We can use Optimizer_Mode = Rule, Optimizer_Mode = number = rule, optimizer_mode = number = rule, optimizer_mode = number = rule, optimizer_mode = number_rows, Optimizer_Mode = All_Rows, Optimizer_Mode = all_rows, if you do not set an Optimizer_Mode parameter default This is a choose.

B, sessions level

Set by SQL> ALTER Session Set Optimizer_Mode = ; C, statement level

These needs to use Hint, such as: SQL> SELECT / * Rule * / A.Userid, 2 B.Name, 3 B.DEPART_NAME 4 from TF_F_YHDA A, 5 TF_F_DEPART B 6 Where a.userid = B.UserID; 4, Why is there a field of a table clearly and have an index, when observing the execution plan of some words does not take index? How to solve it?

A. If you don't take an index, you have the following reasons. You are all on all_rows in the instance level. The optimizer thinks that it is not worth walking. B. Solution ♀ You can modify this parameter in INIT . Od, change it to Rule or Choose, and restart the database. You can also use the Hint in 4 in the 4.

5, other related

A, how to see if a table or index is statistics

SQL> Select * from user_tables 2 where table_name = 3 and num_rows is not null;

SQL> Select * from user_indexes 2 where table_name = 3 and num_rows is not null;

b, if we use CBO's way, we should update the statistics of the tables and indexes in time to avoid the implementation plan of life. SQL> Analyze Table Table_name Compute Statistics; SQL> Analyze Index Index_name Estimate Statistics

For specific Analyze statements, please refer to the REFRENCE document of Oracle8i / 9i.

(Full text)