and A.col2 = C.col2; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (FULL) OF 'B' 5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A' 6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE) 7 1 SORT (JOIN) 8 7 TABLE ACCESS (FULL) OF 'C'
将A表上的索引inx_col12A删除后： select A.col4 from B, A, C where A.col1 = B.col1 and A.col2 = C.col2; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 MERGE JOIN 4 3 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'C' 6 3 SORT (JOIN) 7 6 TABLE ACCESS (FULL) OF 'A' 8 1 SORT (JOIN) 9 8 TABLE ACCESS (FULL) OF 'B'
通过上面的这些例子，使我对oracle文档上的” All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT”这句话持怀疑态度。此时，我也不能使用hints来强制优化器使用nested loop，如果使用了hints，这样就自动使用CBO优化器，而不是RBO优化器了。
精品推荐