熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle語句優化規則匯總(6)

2022-06-13   來源: Oracle 
基礎表的選擇

  基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問) 根據優化器的不同 SQL語句中基礎表的選擇是不一樣的

  如果你使用的是CBO (COST BASED OPTIMIZER)優化器會檢查SQL語句中的每個表的物理大小索引的狀態然後選用花費最低的執行路徑

  如果你用RBO (RULE BASED OPTIMIZER) 並且所有的連接條件都有索引對應 在這種情況下 基礎表就是FROM 子句中列在最後的那個表

  舉例

     SELECT ANAME  BMANAGER
  FROM WORKER A
  LODGING B
  WHERE ALODGING = BLODING;

  由於LODGING表的LODING列上有一個索引 而且WORKER表中沒有相比較的索引 WORKER表將被作為查詢中的基礎表

   多個平等的索引

  當SQL語句的執行路徑可以使用分布在多個表上的多個索引時 ORACLE會同時使用多個索引並在運行時對它們的記錄進行合並 檢索出僅對全部索引有效的記錄

  在ORACLE選擇執行路徑時唯一性索引的等級高於非唯一性索引 然而這個規則只有當WHERE子句中索引列和常量比較才有效如果索引列和其他表的索引類相比較 這種子句在優化器中的等級是非常低的

  如果不同表中兩個想同等級的索引將被引用 FROM子句中表的順序將決定哪個會被率先使用 FROM子句中最後的表的索引將有最高的優先級

  如果相同表中兩個想同等級的索引將被引用 WHERE子句中最先被引用的索引將有最高的優先級

  舉例

  DEPTNO上有一個非唯一性索引EMP_CAT也有一個非唯一性索引

     SELECT ENAME
  FROM EMP
  WHERE DEPT_NO = 
  AND EMP_CAT = A;

  這裡DEPTNO索引將被最先檢索然後同EMP_CAT索引檢索出的記錄進行合並 執行路徑如下

     TABLE ACCESS BY ROWID ON EMP
  ANDEQUAL
  INDEX RANGE SCAN ON DEPT_IDX
  INDEX RANGE SCAN ON CAT_IDX

   等式比較和范圍比較

  當WHERE子句中有索引列 ORACLE不能合並它們ORACLE將用范圍比較

  舉例

  DEPTNO上有一個非唯一性索引EMP_CAT也有一個非唯一性索引

     SELECT ENAME
  FROM EMP
  WHERE DEPTNO > 
  AND EMP_CAT = A;

  這裡只有EMP_CAT索引被用到然後所有的記錄將逐條與DEPTNO條件進行比較 執行路徑如下

     TABLE ACCESS BY ROWID ON EMP
  INDEX RANGE SCAN ON CAT_IDX

   不明確的索引等級

  當ORACLE無法判斷索引的等級高低差別優化器將只使用一個索引它就是在WHERE子句中被列在最前面的

  舉例

  DEPTNO上有一個非唯一性索引EMP_CAT也有一個非唯一性索引

     SELECT ENAME
  FROM EMP
  WHERE DEPTNO > 
  AND EMP_CAT > A;

  這裡 ORACLE只用到了DEPT_NO索引 執行路徑如下

     TABLE ACCESS BY ROWID ON EMP
  INDEX RANGE SCAN ON DEPT_IDX

  譯者按我們來試一下以下這種情況

    SQL> select index_name uniqueness from user_indexes where table_name = EMP
    INDEX_NAME                     UNIQUENES
 
    EMPNO                          UNIQUE
 EMPTYPE                        NONUNIQUE
    SQL> select * from emp where empno >= and emp_type = A
    no rows selected
    Execution Plan
 
          SELECT STATEMENT Optimizer=CHOOSE
        TABLE ACCESS (BY INDEX ROWID) OF EMP
          INDEX (RANGE SCAN) OF EMPTYPE (NONUNIQUE

  雖然EMPNO是唯一性索引但是由於它所做的是范圍比較 等級要比非唯一性索引的等式比較低!

   強制索引失效

  如果兩個或以上索引具有相同的等級你可以強制命令ORACLE優化器使用其中的一個(通過它檢索出的記錄數量少)

  舉例

     SELECT ENAME
  FROM EMP
  WHERE EMPNO = 
  AND DEPTNO +  =  /*DEPTNO上的索引將失效*/
  AND EMP_TYPE ||  = A /*EMP_TYPE上的索引將失效*/

  這是一種相當直接的提高查詢效率的辦法 但是你必須謹慎考慮這種策略一般來說只有在你希望單獨優化幾個SQL時才能采用它

  這裡有一個例子關於何時采用這種策略

  假設在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引

     SELECT ENAME
  FROM EMP
  WHERE EMP_TYPE = A
  AND EMP_CLASS = X;

  優化器會注意到EMP_TYPE上的索引並使用它 這是目前唯一的選擇 如果一段時間以後 另一個非唯一性建立在EMP_CLASS上優化器必須對兩個索引進行選擇在通常情況下優化器將使用兩個索引並在他們的結果集合上執行排序及合並 然而如果其中一個索引(EMP_TYPE)接近於唯一性而另一個索引(EMP_CLASS)上有幾千個重復的值 排序及合並就會成為一種不必要的負擔 在這種情況下你希望使優化器屏蔽掉EMP_CLASS索引

  用下面的方案就可以解決問題

     SELECT ENAME
  FROM EMP
  WHERE EMP_TYPE = A
  AND EMP_CLASS|| = X;

   


From:http://tw.wingwit.com/Article/program/Oracle/201311/17299.html
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.