There are many things worth learning about in Oracle. Here we will mainly introduce the Oracle documentation, including the RBO optimizer. In the Oracle document, for RBO, select the driver table from right to left in the from clause, that is, the rightmost table is the first driver table: all things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT.
However, in my tests, I have never verified that this statement is correct. In my opinion, even in RBO, there is also a set of rules to decide which connection type and which table to use as the driving table. In the selection, the current index will definitely be taken into account, the restriction condition in the where clause may also be considered, but it must be irrelevant to the location of the restriction condition in the where clause.
If I create three tables:
- create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
- create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
- create table C(col2 number(4,0),col3 number(4,0), name_c char(30));
- create index inx_col12A on a(col1,col2);
Run the query:
- select A.col4
- from B, A, C
- where B.col3 = 10
- and A.col1 = B.col1
- and A.col2 = C.col2
- and C.col3 = 5;
- Execution Plan
Using the above examples, I am skeptical about the "All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT" statement in the Oracle document. At this point, I cannot use hints to force the optimizer to use the nested loop. If hints is used, the CBO optimizer is automatically used instead of the RBO optimizer.
- How to Understand the execution plan generated by Oracle
- Oracle table join
- Analysis of four categories of Oracle index Scanning
- Master talk about Oracle index Scanning
- Quick introduction to Oracle hash connections