RBO下，oracle如何选择驱动表

--目的：弄清楚RBO下，oracle如何选择驱动表
--连接到部门测试机器10.188.239.68进行测试操作

CONN CRM1/CRM1@TEST

--********************************************************
--1测试环境说明
--********************************************************
--a 表记录数量
--XSTDDXX1 6663; XSTDDXX2 81467; KHVKHDA0 4438
--b 表索引
--xstddxx1:dhao00,khdm00,ywrq00,ywry00,ywzgry
--xstddxx2:(dhao00,jydm00)
--c 优化器模式
SQL> show parameter optimizer_mode
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      CHOOSE

--d 数据库版本
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

--****************************************************************
--2、将优化器模式切换为rule
--****************************************************************
SQL> alter session set optimizer_mode=rule;

会话已更改。

--****************************************************************
--3、对于2个表xstddxx1,xstddxx2的查询
--使用同样的sql语句中，仅调换from字句中，2个表的顺序
--****************************************************************

--**************************语句1的执行计划
select a.dhao00,a.khdm00,a.ywrq00,b.jydm00,b.sl0000
from xstddxx1 a,xstddxx2 b
where a.dhao00=b.dhao00;

已选择81467行。

已用时间:  00: 00: 02.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'XSTDDXX2'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'XSTDDXX1'
   4    3       INDEX (UNIQUE SCAN) OF 'XSTDDXX1_DHAO00_PK' (UNIQUE)
  
Statistics
----------------------------------------------------------
          0  recursive calls   :递归调用。一般原因：dictionary cache未命中；动态存储扩展；PL/SQL语句
          0  db block gets    :bufer中读取的block数量，用于insert,update,delete,select for update
     174530  consistent gets   :bufer中读取的用于查询（除掉select for update）的block数量。 db blocks gets+consistent gets= logical read
          0  physical reads    :从磁盘上读取的block数量
          0  redo size      :bytes，写到redo logs的数据量
    3546854  bytes sent via SQL*Net to client
      60244  bytes received via SQL*Net from client
       5433  SQL*Net roundtrips to/from client
          0  sorts (memory)    :内存排序次数
          0  sorts (disk)     :磁盘排序次数；与sort_area_size有关
      81467  rows processed
--以xstddxx2为驱动表，与xstddxx1产生嵌套循环连接

--**************************语句2的执行计划
select a.dhao00,a.khdm00,a.ywrq00,b.jydm00,b.sl0000
from xstddxx2 b,xstddxx1 a
where a.dhao00=b.dhao00;

已用时间:  00: 00: 02.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'XSTDDXX2'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'XSTDDXX1'
   4    2       INDEX (RANGE SCAN) OF 'XSTDDXX2_DH_JY_PK' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      39350  consistent gets
          0  physical reads
          0  redo size
    3544332  bytes sent via SQL*Net to client
      60244  bytes received via SQL*Net from client
       5433  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      81467  rows processed
--以xstddxx1为驱动表，与xstddxx2产生嵌套循环连接

--***************************************************************************
--4、初层次理解：1、RBO下，对于2个表的操作，FROM子句中，RBO选择最右的表作为驱动表
--               2、对于NESTED LOOPS、HASH JOIN、SORT MERGE JOIN方式，驱动表选择较小的表，速度会更快
--               3、存在主、外键关系的表，主键由oracle自动建立索引，外键上最好也建索引，以避免全表扫描
--***************************************************************************

--***************************************************************************
--5、下面看一下3个表的情况下，RBO如何选择驱动表
--***************************************************************************

--********************************************************************语句1
select a.dhao00,a.khdm00,c.khmc00,a.ywrq00,b.jydm00,b.sl0000
from xstddxx1 a,xstddxx2 b,khvkhda0 c
where a.dhao00=b.dhao00 and a.khdm00=c.khdm00;

已选择81583行。

已用时间:  00: 00: 02.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'XSTDDXX2'
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'KHVKHDA0'
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'XSTDDXX1'
   6    5           INDEX (RANGE SCAN) OF 'XSTDDXX1_KHDM_INDEX' (NON-UNIQUE)
   7    2       INDEX (RANGE SCAN) OF 'XSTDDXX2_DH_JY_PK' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      57029  consistent gets
          0  physical reads
         68  redo size
    3621039  bytes sent via SQL*Net to client
      60321  bytes received via SQL*Net from client
       5440  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      81583  rows processed
--以khvkhda0为驱动表，与xstddxx1做嵌套循环，产生的结果集于xstddxx2再做嵌套循环

--****************************************************************语句2
--将khvkhda0从最后调到最前
select a.dhao00,a.khdm00,c.khmc00,a.ywrq00,b.jydm00,b.sl0000
from khvkhda0 c,xstddxx1 a,xstddxx2 b
where a.dhao00=b.dhao00 and a.khdm00=c.khdm00;

已选择81583行。

已用时间:  00: 00: 03.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'KHVKHDA0'
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'XSTDDXX2'
   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'XSTDDXX1'
   6    5           INDEX (UNIQUE SCAN) OF 'XSTDDXX1_DHAO00_PK' (UNIQUE)
   7    2       INDEX (RANGE SCAN) OF 'KHVKHDA0_KHDM_INDEX' (NON-UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     279973  consistent gets
          0  physical reads
         68  redo size
    3713767  bytes sent via SQL*Net to client
      60321  bytes received via SQL*Net from client
       5440  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      81583  rows processed
--以xstddxx2为驱动表，与xstddxx1做嵌套循环，产生的结果集与khvkhda0再做嵌套循环

--***************************************************************************
--6、扩展理解：1、RBO下的多表连接，对于FROM子句，RBO以从右到左的顺序处理表连接
--***************************************************************************

--***************************************************************************
--7、问题：1、RBO下，where子句中，条件的顺序对于执行计划有何影响
--***************************************************************************