RBO上的驱动表选择

有两个表 cceu，ac01，现在ac01在aac001，acc002上有唯一索引，cceu在aac001、BCE176有普通索引，在BCC843上有唯一索引。两表在其余列上均无索引

1.只有表连接的时候，from顺序对执行计划有影响，选择from最右边表为驱动表

select /*+rule*/ * from AC01 b,CCEU a where a.AAC001=b.AAC001

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'CCEU'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'AC01'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_AC01' (UNIQUE)

select /*+rule*/ * from CCEU a,AC01 b where a.AAC001=b.AAC001

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CCEU'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'AC01'
   4    2       INDEX (RANGE SCAN) OF 'IDX_CCEU_AAC001' (NON-UNIQUE)

2.如果where有其余的查询条件，若条件上有索引，先执行该条件，该表做驱动表；若条件上无索引，以from最右边表做驱动表

select /*+rule*/ * from CCEU a,AC01 b where  a.AAC001=b.AAC001 and a.BCE176 = 'str2'

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'CCEU'
   3    2       INDEX (RANGE SCAN) OF 'IDX_CCEU_BCE176' (NON-UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'AC01'
   5    4       INDEX (UNIQUE SCAN) OF 'PK_AC01' (UNIQUE)

select /*+rule*/ * from CCEU a,AC01 b where  a.AAC001=b.AAC001 and a.bcc110 = 'str2'

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CCEU'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'AC01'
   4    2       INDEX (RANGE SCAN) OF 'IDX_CCEU_AAC001' (NON-UNIQUE)

 

select /*+rule*/ * from CCEU a,AC01 b where  a.AAC001=b.AAC001  and b.aac005='str1'

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CCEU'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'AC01'
   4    2       INDEX (RANGE SCAN) OF 'IDX_CCEU_AAC001' (NON-UNIQUE)

select /*+rule*/ * from  AC01 b,CCEU a where  a.AAC001=b.AAC001  and b.aac005='str1'

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'CCEU'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'AC01'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_AC01' (UNIQUE)

3.若两个表上均有查询条件，且查询条件上均有索引且索引类别相同，以from最右边的表为驱动表；若查询条件均无索引，以from最后边的表的表为驱动表

select /*+rule*/ * from AC01 b,CCEU a where a.AAC001=b.AAC001 and a.bcc843='str2' and b.aac002='str1'

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'CCEU'
   3    2       INDEX (UNIQUE SCAN) OF 'PK_CCEU' (UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'AC01'
   5    4       INDEX (UNIQUE SCAN) OF 'IDX_UQ_AC01_AAC002' (UNIQUE)

select /*+rule*/ * from CCEU a,AC01 b where a.AAC001=b.AAC001 and b.aac002='str1' and a.bcc843='str2'、

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'AC01'
   3    2       INDEX (UNIQUE SCAN) OF 'IDX_UQ_AC01_AAC002' (UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'CCEU'
   5    4       INDEX (UNIQUE SCAN) OF 'PK_CCEU' (UNIQUE)

若两表上的索引不同，以索引级别高的表作为驱动表，与from顺序与where顺序无影响

select /*+rule*/ * from CCEU a,AC01 b where a.AAC001=b.AAC001 and b.aac002='str1' and a.bce176='str2'

select /*+rule*/ * from CCEU a,AC01 b where a.AAC001=b.AAC001 and a.bce176='str2' and b.aac002='str1'

select /*+rule*/ * from AC01 b,CCEU a where a.AAC001=b.AAC001 and b.aac002='str1' and a.bce176= 'str2'

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'AC01'
   3    2       INDEX (UNIQUE SCAN) OF 'IDX_UQ_AC01_AAC002' (UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'CCEU'
   5    4       AND-EQUAL
   6    5         INDEX (RANGE SCAN) OF 'IDX_CCEU_AAC001' (NON-UNIQUE)
   7    5         INDEX (RANGE SCAN) OF 'IDX_CCEU_BCE176' (NON-UNIQUE)

若where上有多个索引列，仍以最高等级的索引为驱动表，若最高等级相同，以from右边表为驱动表，与索引数量无关。

select /*+rule*/ * from CCEU a,AC01 b where a.AAC001=b.AAC001 and b.aac002='str1' and a.bce176= 'str2' and a.bcc843='str3'

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'AC01'
   3    2       INDEX (UNIQUE SCAN) OF 'IDX_UQ_AC01_AAC002' (UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'CCEU'
   5    4       INDEX (UNIQUE SCAN) OF 'PK_CCEU' (UNIQUE)

 

但是如果调整表连接的位置，会改变索引执行顺序，持续了解中。。。

select /*+rule*/ * from CCEU a,AC01 b where   a.bce176='str2' and b.aac002='str1' and a.AAC001=b.AAC001

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'AC01'
   3    2       INDEX (UNIQUE SCAN) OF 'IDX_UQ_AC01_AAC002' (UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'CCEU'
   5    4       AND-EQUAL
   6    5         INDEX (RANGE SCAN) OF 'IDX_CCEU_BCE176' (NON-UNIQUE)
   7    5         INDEX (RANGE SCAN) OF 'IDX_CCEU_AAC001' (NON-UNIQUE)