Oracle's optimizer has two optimization methods, namely, rule-based optimization approach (Rule-Based Optimization, referred to as RBO) and cost-based optimization method (Cost-Based Optimization, referred to as CBO), in Oracle8 and later, Oracle strongly recommends using CBO's way
RBO mode: optimizer in the analysis of SQL statements, followed by a predetermined number of the Oracle internal rules. For example, our common, when a where clause in an index to go with the index.
CBO way: it is to look at the cost of the statement (Cost), where the cost mainly refers to the Cpu and memory. Optimizer to determine whether this way, the main reference was to the table and index statistics. Statistics given the size of the table, a small line, the length of each line and other information. At first, these statistics are not in the database, do analyze after the emergence of many of Hou-date statistics will make the optimizer execution plan to make a mistake, because these should be promptly update this information.
Note: take the index is not necessarily superior, such as a table with only two rows of data, one can complete the whole IO table to retrieve, index at a time when you need to twice take the IO, then a full table scan (full table scan) is the best
Optimization models, including Rule, Choose, First rows, All rows in four ways:
Rule: rule-based approach.
Choolse: By default Oracle is used in this way. Means that when there is a table or index or statistics, the CBO way to go, if not the table or index statistics, tables are not particularly small, and the corresponding columns are indexed, then left the index, take the RBO's way.
First Rows: Choose ways it is similar, the difference is that when a table has statistics, it is the fastest way to return to the query the first few lines, in general, reduce the response time.
All Rows: Cost is the way we said, when a table with statistical information, it is the fastest way to return to the table all the rows, the query from the overall increase in throughput. RBO no statistical information is the way to go.
Choice of which set optimization mode:
A, Instance-level file can be set by initSID.ora OPTIMIZER_MODE = RULE / CHOOSE / FIRST_ROWS / ALL_ROWS If you do not set the parameters OPTIMIZER_MODE Choose the default method is used.
B, Sessions level by ALTER SESSION SET OPTIMIZER_MODE = RULE / CHOOSE / FIRST_ROWS / ALL_ROWS set.
C, the statement level with Hint (/ * + ... * /) to set
Why there is obviously a field table index, but the implementation plan did not take the index?
1, the optimization model is the way all_rows
2, the table for over analyze, there are statistics
3, the table is small, the above mentioned, Oracle's optimizer that is not worth taking the index.