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

　　 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