通过分析SQL语句的执行计划优化SQL（15）

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);

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
-----------------------------------------
   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"

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       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"

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"