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/，如需轉載，請註明出處，否則將追究法律責任。