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

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

2013-11-13 15:32:23  來源: Oracle 

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

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

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

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

   用EXPLAIN PLAN 分析SQL語句

  EXPLAIN PLAN 是一個很好的分析SQL語句的工具它甚至可以在不執行SQL的情況下分析語句 通過分析我們就可以知道ORACLE是怎麼樣連接表使用什麼方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱

  你需要按照從裡到外從上到下的次序解讀分析的結果 EXPLAIN PLAN分析的結果是用縮進的格式排列的 最內部的操作將被最先解讀 如果兩個操作處於同一層中帶有最小操作號的將被首先執行

  NESTED LOOP是少數不按照上述規則處理的操作 正確的執行路徑是檢查對NESTED LOOP提供數據的操作其中操作號最小的將被最先處理

  通過實踐 感到還是用SQLPLUS中的SET TRACE 功能比較方便

  舉例

   SQL> list
SELECT *
FROM dept emp
  * WHERE empdeptno = deptdeptno
SQL> set autotrace traceonly /*traceonly 可以不顯示執行結果*/
SQL> /
rows selected
Execution Plan

         SELECT STATEMENT Optimizer=CHOOSE
          NESTED LOOPS
            TABLE ACCESS (FULL) OF EMP
            TABLE ACCESS (BY INDEX ROWID) OF DEPT
              INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)

Statistics

          recursive calls
          db block gets
          consistent gets
          physical reads
          redo size
       bytes sent via SQL*Net to client
         bytes received via SQL*Net from client
          SQL*Net roundtrips to/from client
          sorts (memory)
          sorts (disk)
          rows processed

  通過以上分析可以得出實際的執行步驟是

   TABLE ACCESS (FULL) OF EMP

   INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)

   TABLE ACCESS (BY INDEX ROWID) OF DEPT

   NESTED LOOPS (JOINING AND )

 

  注 目前許多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了極其方便的EXPLAIN PLAN工具也許喜歡圖形化界面的朋友們可以選用它們

   用索引提高效率

  索引是表的一個概念部分用來提高檢索數據的效率 實際上ORACLE使用了一個復雜的自平衡Btree結構 通常通過索引查詢數據比全表掃描要快 當ORACLE找出執行查詢和Update語句的最佳路徑時 ORACLE優化器將使用索引 同樣在聯結多個表時使用索引也可以提高效率 另一個使用索引的好處是它提供了主鍵(primary key)的唯一性驗證

  除了那些LONG或LONG RAW數據類型 你可以索引幾乎所有的列 通常 在大型表中使用索引特別有效 當然你也會發現 在掃描小表時使用索引同樣能提高效率

  雖然使用索引能得到查詢效率的提高但是我們也必須注意到它的代價 索引需要空間來存儲也需要定期維護 每當有記錄在表中增減或索引列被修改時 索引本身也會被修改 這意味著每條記錄的INSERT DELETE UPDATE將為此多付出 次的磁盤I/O 因為索引需要額外的存儲空間和處理那些不必要的索引反而會使查詢反應時間變慢

  定期的重構索引是有必要的

  ALTER INDEX REBUILD

   索引的操作

  ORACLE對索引有兩種訪問模式

  索引唯一掃描 ( INDEX UNIQUE SCAN)

  大多數情況下 優化器通過WHERE子句訪問INDEX

  例如

  表LODGING有兩個索引 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER

     SELECT *
  FROM LODGING
  WHERE LODGING = ROSE HILL;

  在內部 上述SQL將被分成兩步執行 首先 LODGING_PK 索引將通過索引唯一掃描的方式被訪問 獲得相對應的ROWID 通過ROWID訪問表的方式執行下一步檢索

  如果被檢索返回的列包括在INDEX列中ORACLE將不執行第二步的處理(通過ROWID訪問表) 因為檢索數據保存在索引中 單單訪問索引就可以完全滿足查詢結果

  下面SQL只需要INDEX UNIQUE SCAN 操作

     SELECT LODGING
  FROM LODGING
  WHERE LODGING = ROSE HILL;

  索引范圍查詢(INDEX RANGE SCAN)

  適用於兩種情況

   基於一個范圍的檢索

   基於非唯一性索引的檢索

  例

  SELECT LODGING FROM LODGING WHERE LODGING LIKE M%;

  WHERE子句條件包括一系列值 ORACLE將通過索引范圍查詢的方式查詢LODGING_PK 由於索引范圍查詢將返回一組值 它的效率就要比索引唯一掃描低一些

  例

     SELECT LODGING
  FROM LODGING
  WHERE MANAGER = BILL GATES;

  這個SQL的執行分兩步 LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過ROWID訪問表得到LODGING列的值 由於LODGING$MANAGER是一個非唯一性的索引數據庫不能對它執行索引唯一掃描

  由於SQL返回LODGING列而它並不存在於LODGING$MANAGER索引中 所以在索引范圍查詢後會執行一個通過ROWID訪問表的操作

  WHERE子句中 如果索引列所對應的值的第一個字符由通配符(WILDCARD)開始 索引將不被采用在這種情況下ORACLE將使用全表掃描

     SELECT LODGING
  FROM LODGING
  WHERE MANAGER LIKE %HANMAN;


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