Oracle Optimizer (Optimizer)

Before Oracle executes a SQL, it first analyzes the execution plan of the statement and then executes the execution plan. The execution plan for parsing statements is performed by the optimizer (Optimizer). In different cases, a single SQL may have multiple execution plans, but at some point there must be only one execution plan that is optimal and spends the least time. I believe you will use Pl/sql Developer, Toad and other tools to see the execution plan of a statement, but you may have questions about rule, Choose, first rows, and all rows, because I was like this, At that time I also wondered why the different items were chosen, and the execution plan changed.

1. Optimization method of Optimizer

Oracle's optimizer has a total of two optimization methods, i.e. rule-based optimization (rule-based optimization, short rbo) and cost based optimization (cost-based optimization, referred to as CBO).

A, Rbo Way: 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.

B, CBO way: according to the meaning of the word, it is to look at the cost of the statement, where the cost of mainly refers to the 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 in the library at first, it is you do analyze after the appearance, a lot of time and the expiration of statistics will make the optimizer made a wrong execution plan, because we should update this information in a timely manner. In Oracle8 and later versions, the Oracle column recommends a CBO approach.

We want to understand, not necessarily go index is 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, then the table to do a full table scan (scan) is the best.

2, optimizer optimization mode (Optermizer mode)

The optimization model includes the four Rule,choose,first rows,all rows, which we mentioned above. I would like to explain the following:

Rule: Needless to say, it's a rule-based approach.

Choolse: That's what we should be watching, and that's the way Oracle uses it 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 to go the rule-based approach.