Rule Based Optimizer (RBO) rule-based
Cost Based Optimizer (CBO) cost-based, or statistical information about
ORACLE provides CBO, RBO two kinds of SQL optimizer. CBO in ORACLE7 introduction, but in ORACLE8i in to mature. ORACLE has been explicitly stated in ORACLE9i later version (ORACLE 10G), RBO will no longer support. Therefore chosen to CBO is an inevitable trend.
CBO and RBO as a different SQL optimizer, SQL statement execution plan for a major impact, if the existing applications from RBO to CBO transplant, you must take full account of these effects, avoid sharp decline in performance of SQL statements; However, for new applications, consider the direct use CBO, the CBO mode SQL statement preparation, analysis, implementation plan, performance testing, etc., which requires the developer more familiar with the characteristics of the CBO. The following summary points to write SQL statements in the CBO Notes:
1, RBO from ORACLE 6 version has been adopted, with the use of a set of strict rules, as long as you follow it to write SQL statements, regardless of how the data contents of the table, it will not affect your "implementation plan" that is that the data is not "sensitive"; CBO calculating possible "implementation plan" and "price", ie cost, choose from the lowest cost solution, as the actual running program. The "implementation plan" is calculated according to cost, data-dependent statistical distribution of data in the table, ORACLE database itself is not clear that the statistical distribution, we must analyze the table and related indexes (using the ANALYZE command) in order to gather CBO the required data.
2, using the CBO, the write SQL statements, you do not consider the "FROM" clause of the table or view behind the order and "WHERE" clause of the conditions behind the order; ORACLE version 7 has been used since many new technologies are based on CBO , such as star-connected arrangement of queries, hash join queries, functions indexes, and parallel inquiries.
3, in general, CBO selected the "implementation plan" are no more than RBO's "implementation plan" poor, but relatively speaking, CBO did not request the programmer RBO so harsh, in order to save the programmers from multiple possible "implementation plan", select an optimal solution and the time spent debugging, but in some cases there will be problems. A more typical problem: sometimes, that there is an index built out, but apparently did not use the query process-related index, causing the query takes a long process, taking up huge resources, then you need to carefully analyze the execution plan, find out why. For example, you can see the connection sequence is allowed to use the relevant index. Assume table emp has an index on the deptno column, table dept is no index on the deptno column, WHERE statement has emp.deptno = dept.deptno conditions. In doing NL connection, emp as appearance, was first visited, the connection mechanism causes the appearance of the data access method is a full table scan, emp.deptno obviously do not have access on the index, the index up to do its full scan or index fast full scan.
4, if a statement execution plan using the RBO does better than the CBO, you can add "rule" prompt, mandatory use of RBO.
5, using the CBO, SQL statements "FROM" clause after the table, must all be analyzed using the ANALYZE command, if the "FROM" clause is the back view, this view of the underlying table, it must all be analyzed using the ANALYZE command over; otherwise, ORACLE will execute this SQL statement, you automatically analyze the ANALYZE command, which will cause the SQL statement is executed very very slow.
6, using the CBO, SQL statements "FROM" clause of the number of tables should not be behind too much, because the CBO in the choice of table join order, would "FROM" clause factorial operation behind the table, choose the best a connection order. If the "FROM" clause of the table after six, then the choice of join order is 6 * 5 * 4 * 3 * 2 * 1 = 720 kinds, CBO choose one, and if the "FROM" clause after 12 tables, then the choice of join order is 12 * 11 * 10 * 9 * 8 * 7 * 6 * 5 * 4 * 3 * 2 * 1 = 479,001,600 kinds, it is conceivable to choose one, will consume much CPU time? If you really want to access many of the table, it is best to use the ORDER hint to force use of "FROM" clause of the fixed table access order.
7, using the CBO, SQL statements can not refer to system data dictionary table or view, because the system data dictionary tables have not been analyzed, could lead to very poor "implementation plan." But do not do without analysis of the data dictionary table, or it may lead to a deadlock, or a serious decline in system performance.
8, using the CBO, we should pay attention to see what type of table using a connection. ORACLE total of Sort Merge Join (SMJ), Hash Join (HJ) and the Nested Loop Join (NL). CBO sometimes emphasis on SMJ and HJ, but in the OLTP system, NL is generally better, because it is efficient use of the index. In the two-table joins, and the target table columns in an index built on, only to effectively use Nested Loop to the index. SMJ built even if the relevant index on the column, can only exist because of the index, to avoid the data sorting process. HJ HASH operations due to be done, there is an index of the speed of data queries had little effect.
9, using the CBO must ensure that the relevant index for the table and collect adequate statistical data. Data often add, delete, change the table the best on a regular basis to analyze tables and indexes, SQL statements can "analyze table xxx compute statistics for all indexes;" ORACLE mastered fully reflect the actual statistics, made possible the right choice.
10, using the CBO, we should pay attention to the value of the indexed data in the field of distribution, will affect the SQL statement execution plans. For example: the table emp, a total of one million rows of data, but the emp.deptno column, only four different data values, such as 10,20,30,40. Although there are many emp rows, ORACLE found the default table column value is evenly distributed in all rows, that is 250,000 each deptno value of each corresponding data line. Assuming SQL search conditions DEPTNO = 10, use the index on the deptno column data search efficiency is often higher than full-table scan, ORACLE course of the index "blind", that the selectivity of the index is not high.
We consider another situation, if one million rows of data is not actually deptno value in four equally among which 99 million lines correspond to the line corresponding to the value of the value of 10,5000 20,3000 30,2000 row line corresponding to the value of the corresponding value of 40 . In such a data distribution pattern on the outside than the other deptno is 10 the value of search, no doubt, if the index can be applied, then the efficiency will be much higher. We can use a separate analysis of the index column, or columns with the analyze statements to establish the histogram, the column to collect enough statistical data to ORACLE in the search for higher value can be used selectively on the index.