Oracle Optimizer -RBO (理解Rule-based 优化器)

/*
理解Rule-based 优化器 
     RBO 优先使用预定义方法去计算使用的某条路径访问数据库资料.
     RDBMS 使用下列条件则优先选择RBO的优化器.包括:
在init.ora 文件中设定OPTIMIZER_MODE = RULE
在init.ora 文件中设定OPTIMIZER_MODE = choose,并且在SQL 中所包含的Table 没有统计资料.
在Session 中使用 alter session set optimizer_mode=rule
在Session 中使用 Alter session set optomizer_mode=Choose 命令,并且SQL 中的任何table 都没有统计资料
 Rule hint (例如 select /*+RULE*/....) 在SQL 中强制使用RBO
*/
 
 ---RBO rule-based optimizer
--(EMP_NO,EMP_NAME,EMP_CLASS,DEPT_NO,EMP_CATEGORY,COST_CENTER)

--Case 1: (In fact, only the two-column index is used;
       -- the single-column index is not used. While Oracle will merge two single-column indexes,
       -- it will not merge a multi-column index with another index. )

   ALTER session SET optimizer_mode=rule ;

   CREATE INDEX idx_1 ON emp(dept_no) ;
   CREATE INDEX idx_2 ON emp(emp_no,emp_name);
  
 SELECT emp_no,DEPT_NO,COST_CENTER
 FROM emp
 WHERE  emp_no = 2 
  AND emp_name = 'PJWANG'
      AND dept_no = '12'
  
   ---未Create Index 时
    -- Execution Plan
----------------------------------------------------------
   -- 0      SELECT STATEMENT Optimizer=RULE
   -- 1    0   TABLE ACCESS (FULL) OF 'EMP'

-- Create index idx_1 (dept_no) 
  --( 上面SQL where subclause 怎么组合都会用到 Idx_1)
  CREATE INDEX idx_1 ON emp(dept_no) ;
 
 
--Execution Plan
----------------------------------------------------------
  --  0      SELECT STATEMENT Optimizer=RULE
  --  1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
  --   2    1     INDEX (RANGE SCAN) OF 'IDX_1' (NON-UNIQUE)

   CREATE INDEX idx_2 ON emp(emp_no,emp_name);
   --(上面的SQL 语句会用到idx_2,因为Idx_2 的columns 比较多且接近Where 后面的 栏位)
 --Execution Plan
----------------------------------------------------------
--   0      SELECT STATEMENT Optimizer=RULE
--   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
--   2    1     INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE)

--Case 2:
---(如果index不是所有Column 在where 子句中,sql 则选择一些Column 在同一个Index 中的Index)
 --( Lis: 如果index 中的所有栏位在SQL 上,那肯定选择此index,如果此index 包含其他栏位,而这些栏位没有在
       --  SQL 中.则SQL 选择Where 条件后尽可能多的栏位在同一个Index's index)
   CREATE INDEX idx_3 ON emp(emp_no,emp_name,dept_no,cost_center);
   /*
   SELECT emp_no,DEPT_NO,COST_CENTER
 FROM emp
 WHERE  emp_no = 2 
  AND emp_name = 'PJWANG'
      AND dept_no = '12'  --AND cost_center='RD Center'
   */
  
--  Execution Plan
----------------------------------------------------------
--   0      SELECT STATEMENT Optimizer=RULE
--   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
--   2    1     INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE)  --Idx_3

--Case 3: (多个Index 都包含 Where 相同个数的Column,则SQL 选择 最后一个创建的index)
  -- CREATE INDEX idx_2 ON emp(emp_no,emp_name);
  --drop index idx_3.
  CREATE INDEX idx_4 ON emp(dept_no,cost_center);
  --(idx_2,idx_4 的栏位都在SQL 中,且个数相同,且不会因为Where 后面Column 顺序改变而选择不同的Index)
 
--  Execution Plan
----------------------------------------------------------
--   0      SELECT STATEMENT Optimizer=RULE
--   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
--   2    1     INDEX (RANGE SCAN) OF 'IDX_4' (NON-UNIQUE)

 
 
---Case 4: (如果Where 子句中的多个Column 在 不同的 Index 中, "=" 操作的Index 将替换 如Like,between 之类的Index)
 CREATE INDEX idx_3 ON emp(emp_no,emp_name,dept_no,cost_center);
 
  SELECT emp_no,DEPT_NO,COST_CENTER
 FROM emp
 WHERE   emp_no = 2
  AND emp_name LIKE 'PJWAN%' 
     AND dept_no = '12'
  AND cost_center='RD Center'
--
--Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'IDX_4' (NON-UNIQUE)

----如果是Between 则
   SELECT emp_no,DEPT_NO,COST_CENTER
  FROM emp
  WHERE   emp_no = 2
   AND emp_name = 'PJWANG' 
      AND dept_no BETWEEN '12' AND '20'
   AND cost_center='RD Center'
--Execution Plan
----------------------------------------------------------
--   0      SELECT STATEMENT Optimizer=RULE
--   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
--   2    1     INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE)

----如果全部是"="
     SELECT emp_no,DEPT_NO,COST_CENTER
  FROM emp
  WHERE   emp_no = 2
   AND emp_name = 'PJWANG' 
      AND dept_no = '12'
   AND cost_center='RD Center'
--Execution Plan
----------------------------------------------------------
--   0      SELECT STATEMENT Optimizer=RULE
--   1    0   INDEX (RANGE SCAN) OF 'IDX_3' (NON-UNIQUE)

--Case 5:( where 子句的多个栏位在不同的index 中,则Where 子句中Column 在Index 占Index 所有column 的百分率高的会被采用)
    --( 所有Index 中的栏位都没有全部被Where 子句包含)
      -- drop index idx_2;
      --drop index idx_1;
      CREATE INDEX idx_5 ON emp(emp_no,emp_category);
     
      --   CREATE INDEX idx_3 ON emp(emp_no,emp_name,dept_no,cost_center);
 
   SELECT emp_no,DEPT_NO,COST_CENTER
  FROM emp
  WHERE   emp_no = 2
   AND emp_name = 'PJWANG' 
   AND dept_no='12'
   AND emp_class='IE'

--百分率不同: idx_3=(emp_no+emp_name+dept_no)/4=75%
     --       idx_5=(emp_no)/2=50%
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'IDX_3' (NON-UNIQUE)
   
 

--- 百分率相同 则选最后一个创建的: idx_3=(emp_no+emp_name)/4=50%
         --     idx_5=(emp_no)/2=50%

  SELECT emp_no,DEPT_NO,COST_CENTER
  FROM emp
  WHERE   emp_no = 2
   AND emp_name = 'PJWANG' 
      AND emp_category='HI1'    
 
 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'IDX_5' (NON-UNIQUE)
  
 
--Case 6: (如果两个Index 包含相同的Column ,SQL在使用时会使用和Where 后面第一个Column 相同并且是index第一个column的 index)
   --    CREATE INDEX idx_5 ON emp(emp_no,emp_category);
   CREATE INDEX idx_6 ON emp( emp_category,emp_no);
   
   SELECT emp_no,DEPT_NO,COST_CENTER
  FROM emp
  WHERE   emp_no = 2

--此SQL 的第一个栏位在Idx_5 中是第一个,则选中idx_5
--Execution Plan
----------------------------------------------------------
--   0      SELECT STATEMENT Optimizer=RULE
--   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
--   2    1     INDEX (RANGE SCAN) OF 'IDX_5' (NON-UNIQUE)

     SELECT emp_no,DEPT_NO,COST_CENTER
  FROM emp
  WHERE emp_category='HI1' 

--此SQL 的第一个栏位在Idx_6 中是第一个,则选中idx_6
--Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'IDX_6' (NON-UNIQUE)
    

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/34596/viewspace-797998/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/34596/viewspace-797998/