高效sql必殺技

pengfoo發表於2012-12-04

原作者:robbinson_ 0612

原文地址:http://blog.csdn.net/robinson_0612/article/details/7406672

No SQL,No cost. SQL語句是造成資料庫開銷最大的部分。而不良SQL寫法直接導致資料庫系統效能下降的情形比比皆是。那麼如何才能稱得
上高效的SQL語句呢?一是查詢優化器為當前的SQL語句生成最佳的執行計劃,保證資料讀寫使用最佳路徑;二是設定合理的物理儲存結構,如表
的型別,欄位的順序,欄位的資料型別等。本文主要描述如何編寫高效的SQL語句並給出示例。下面的描述主要分為三個部分,一是編寫高效SQL
語句,二是使用索引提高查詢效能的部分,三是總結部分。

一、編寫高效SQL語句

1) 選擇最有效的表名順序(僅適用於RBO模式)                                                                            
    ORACLE的解析器總是按照從右到左的順序處理FROM子句中的表名,因此FROM子句中最後的一個表將作為驅動表被優先處理。當FROM子句     
存在多個表的時候,應當考慮將表上記錄最少的那個表置於FROM的最右端作為基表。Oracle會首先掃描基表(FROM子句中最後的那個表)並對     
記錄進行排序,然後掃描第二個表(FROM子句中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合併。如     
果有3個以上的表連線查詢, 那就需要選擇交叉表(intersection table)作為基礎表,交叉表是指那個被其他表所引用的表。               
                                                                                                                               
下面的例子使用最常見的scott或hr模式下的表進行演示                              
                                                                                             
表 EMP 有14條記錄                                                              
表 DEPT 有4條記錄                                                 
SELECT  /*+ rule */ COUNT( * )  FROM   emp, dept;          --高效的寫法  
                                                                                                                               
scott@CNMMBO> set autotrace traceonly stat;                                                   
scott@CNMMBO> SELECT  /*+ rule */ COUNT( * )  FROM   emp, dept;                      
                                                                           
Elapsed: 00:00:00.14                                                       
                                                                                   
Statistics                                                                       
----------------------------------------------------------                              
          1  recursive calls                                                          
          0  db block gets                                                           
         35  consistent gets                                                      
          0  physical reads                                               
          0  redo size                                                             
        515  bytes sent via SQL*Net to client                                  
        492  bytes received via SQL*Net from client                           
          2  SQL*Net roundtrips to/from client                          
          0  sorts (memory)                                                     
          0  sorts (disk)                                                           
          1  rows processed                                                                 
                                                                                           
SELECT  /*+ rule */ COUNT( * )  FROM   dept, emp;         --低效的寫法          
scott@CNMMBO> SELECT  /*+ rule */ COUNT( * )  FROM   dept, emp;                           
                                                                                        
Elapsed: 00:00:00.02                                                                         
                                        
Statistics                                                                                 
----------------------------------------------------------                              
          1  recursive calls                                                       
          0  db block gets                                                              
        105  consistent gets                                                            
          0  physical reads                                                            
          0  redo size                                                               
        515  bytes sent via SQL*Net to client                                  
        492  bytes received via SQL*Net from client                                  
          2  SQL*Net roundtrips to/from client                                         
          0  sorts (memory)                                                         
          0  sorts (disk)                                                         
          1  rows processed                                                               
                                                                                            
2) select 查詢中避免使用'*'                                                             
   當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 '*' 是一個方便的方法.不幸的是,這是一個非常低效的方法.實際     
上,ORACLE在解析的過程中, 會將 '*' 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間。       
注:本文中的例子出於簡化演示而使用了select * ,生產環境應避免使用.                              
                                                                                
3) 減少訪問資料庫的次數                                                 
    每當執行一條SQL語句,Oracle 需要完成大量的內部操作,象解析SQL語句,估算索引的利用率,繫結變數, 讀資料塊等等.由此可     
見,減少訪問資料庫的次數,實際上是降低了資料庫系統開銷                            
-->下面通過3種方式來獲得僱員編號為7788與7902的相關資訊                         
                                                                           
-->方式 1 (最低效):                                                     
select ename,job,sal from emp where empno=7788;                          
                                                                           
select ename,job,sal from emp where empno=7902;                                  
                                                                     
-->方式 2 (次低效):                                                            
-->下面使用了引數遊標來完成,每傳遞一次引數則需要對錶emp訪問一次,增加了I/O                         
  DECLARE                                                                            
    CURSOR C1(E_NO NUMBER)  IS                                                   
    SELECT ename, job, sal                                                        
    FROM emp                                                                  
    WHERE empno = E_NO;                                                    
  BEGIN                                                                      
    OPEN C1 (7788);                                                         
    FETCH C1 INTO …, …, …;                                                    
    ..                                                                   
    OPEN C1 (7902);                                                   
    FETCH C1 INTO …, …, …;                                                 
    CLOSE C1;                                                              
  END;                                                                         
                                                                                
-->方式 3 (最高效)                                           
SELECT a.ename                                                            
     , a.job                                                   
     , a.sal                                                      
     , b.ename                                                      
     , b.job                                                         
     , b.sal                                                      
FROM   emp a, emp b                                                    
WHERE  a.empno = 7788 OR b.empno = 7902;                                    
                                                      
注意:在SQL*Plus,SQL*Forms和Pro*C中重新設定ARRAYSIZE引數,可以增加每次資料庫訪問的檢索資料量,建議值為200.     
                                                               
4) 使用DECODE函式來減少處理時間                              
-->使用decode函式可以避免重複掃描相同的行或重複連線相同的表                 
select count(*),sum(sal) from emp where deptno=20 and ename like 'SMITH%';            
                                                                                        
select count(*),sum(sal) from emp where deptno=30 and ename like 'SMITH%';              
                                                                                            
-->通過使用decode函式一次掃描即可完成所有滿足條件記錄的處理                                      
SELECT COUNT( DECODE( deptno, 20, 'x', NULL ) ) d20_count                                       
     , COUNT( DECODE( deptno, 30, 'x', NULL ) ) d30_count                                    
     , SUM( DECODE( deptno, 20, sal, NULL ) ) d20_sal                                        
     , SUM( DECODE( deptno, 30, sal, NULL ) ) d30_sal                                          
FROM   emp                                                                
WHERE  ename LIKE 'SMITH%';                                              
                                                                        
類似的,DECODE函式也可以運用於GROUP BY 和ORDER BY子句中。                         
                                                                               
5) 整合簡單,無關聯的資料庫訪問                                      
-->如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中以提高效能(即使它們之間沒有關係)        
-->整合前                                                                         
SELECT name                                                                          
FROM   emp                                                                        
WHERE  empno = 1234;                                                            
                                                                             
SELECT name                                                                   
FROM   dept                                                        
WHERE  deptno = 10;                                                      
                                                                                          
SELECT name                                                               
FROM   cat                                                             
WHERE  cat_type = 'RD';                                                                 
                                                                                        
-->整合後                                                                     
SELECT e.name, d.name, c.name                                                                   
FROM   cat c                                                                                       
     , dpt d                                                                                      
     , emp e                                                                                      
     , dual x                                                                                   
WHERE      NVL( 'X', x.dummy ) = NVL( 'X', e.ROWID(+) )                        
       AND NVL( 'X', x.dummy ) = NVL( 'X', d.ROWID(+) )         
       AND NVL( 'X', x.dummy ) = NVL( 'X', c.ROWID(+) )          
       AND e.emp_no(+) = 1234                                                                   
       AND d.dept_no(+) = 10                                                                   
       AND c.cat_type(+) = 'RD';                                                                 
                                                                                       
-->從上面的SQL語句可以看出,儘管三條語句被整合為一條,效能得以提高,然可讀性差,此時應權衡效能與代價            
                                                                                                                               
6) 刪除重複記錄                                                                                 
-->通過使用rowid來作為過濾條件,效能高效                                             
DELETE FROM emp e                                                                   
WHERE  e.ROWID > (SELECT MIN( x.ROWID )   
                  FROM   emp x                                                     
                  WHERE  x.empno = e.empno);                                                 
                                                                                  
7) 使用truncate 代替 delete                                                             
-->通常情況下,任意記錄的刪除需要在回滾段構造刪除前映象以實現回滾(rollback).對於未提交的資料在執行rollback之後,Oracle會生成   
-->等價SQL語句去恢復記錄(如delete,則生成對應的insert語句;如insert則生成對應的delete;如update,則是同時生成delete和insert
-->使用truncate命令則是執行DDL命令,不產生任何回滾資訊,直接格式化並釋放高水位線.故該語句效能高效.由於不能rollback,因此慎用. 
                                                                                               
8) 儘量多使用COMMIT(COMMIT應確保事務的完整性)           
-->只要有可能,在程式中儘量多使用COMMIT,這樣程式的效能得到提高,需求也會因為COMMIT所釋放的資源而減少            
-->COMMIT所釋放的資源:                                                                                                        
-->1.回滾段上用於恢復資料的資訊                                                                                                
-->2.釋放語句處理期間所持有的鎖                                                                                                
-->3.釋放redo log buffer佔用的空間(commit將redo log buffer中的entries 寫入到聯機重做日誌檔案)                        
-->4.ORACLE為管理上述3種資源中的內部開銷                                                                                       
                                                                                                                               
9) 計算記錄條數                                                                                                                
-->一般的情況下,count(*)比count(1)稍快.如果可以通過索引檢索,對索引列的計數是最快的,因為直接掃描索引即可,例如COUNT(EMPNO) 
-->實際情況是經測試上述三種情況並無明顯差異.                                                 
                                                                                              
10) 用Where子句替換HAVING子句                                                                                                  
-->儘可能的避免having子句,因為HAVING 子句是對檢索出所有記錄之後再對結果集進行過濾。這個處理需要排序,總計等操作               
-->通過WHERE子句則在分組之前即可過濾不必要的記錄數目,從而減少聚合的開銷                                                       
                                                                                                                               
-->低效:                                                                              
SELECT deptno, AVG( sal )                                                               
FROM   emp                                                                                            
GROUP BY deptno                                                                                        
HAVING deptno = 20;                                                                                        
                                                                                          
scott@CNMMBO> SELECT deptno, AVG( sal )                                                                    
  2  FROM   emp                                                                                     
  3  GROUP BY deptno                                                                                
  4  HAVING deptno= 20;                                                                                 
                                                                                            
Statistics                    
----------------------------------------------------------   
          0  recursive calls                                                                         
          0  db block gets                                                               
          7  consistent gets                                                             
          0  physical reads                                                              
          0  redo size                                                                     
        583  bytes sent via SQL*Net to client                                                
        492  bytes received via SQL*Net from client                                               
          2  SQL*Net roundtrips to/from client                                                     
          0  sorts (memory)                                                                    
          0  sorts (disk)                                                                   
          1  rows processed                                                              
-->高效:                                                              
SELECT deptno, AVG( sal )        
FROM   emp                                                                                    
WHERE  deptno = 20                                                                                
GROUP BY deptno;                                                                 
                                                                                    
scott@CNMMBO> SELECT deptno, AVG( sal )                
  2  FROM   emp                                                                           
  3  WHERE  deptno = 20                                                                 
  4  GROUP BY deptno;                                                                 
                                                                                       
Statistics                                        
----------------------------------------------------------      
          0  recursive calls                                                            
          0  db block gets                                                             
          2  consistent gets                                                             
          0  physical reads                                                          
          0  redo size                                                              
        583  bytes sent via SQL*Net to client                                       
        492  bytes received via SQL*Net from client                                    
          2  SQL*Net roundtrips to/from client                                             
          0  sorts (memory)                                                                    
          0  sorts (disk)                                                                        
          1  rows processed                                                                   
                                                                                      
11) 最小化表查詢次數                                                                                                           
-->在含有子查詢的SQL語句中,要特別注意減少對錶的查詢                                                                           
-->低效:                                                         
SELECT *                                                                                    
FROM   employees                                                                                 
WHERE  department_id = (SELECT department_id                                                     
                        FROM   departments                                                   
                        WHERE  department_name = 'Marketing')                                 
       AND manager_id = (SELECT manager_id                                                     
                         FROM   departments                                                   
                         WHERE  department_name = 'Marketing');                                
-->高效:                                                              
SELECT *                                                                                  
FROM   employees                                                                           
WHERE  ( department_id, manager_id ) = (SELECT department_id, manager_id                                
                                        FROM   departments                                                 
                                        WHERE  department_name = 'Marketing')                 
                                                                                  
-->類似更新多列的情形              
-->低效:                   
UPDATE employees                                                                                
SET    job_id = ( SELECT MAX( job_id ) FROM jobs ), salary = ( SELECT AVG( min_salary ) FROM jobs )         
WHERE  department_id = 10;                                                                  
                                                                                      
-->高效:                
UPDATE employees         
SET    ( job_id, salary ) = ( SELECT MAX( job_id ), AVG( min_salary ) FROM jobs )     
WHERE  department_id = 10;                                                            
                                                                        
12) 使用表別名                                                                      
-->在多表查詢時,為所返回列使用表別名作為字首以減少解析時間以及那些相同列歧義引起的語法錯誤                                    
                                                                               
13) 用EXISTS替代IN                                                                                 
    在一些基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下,使用EXISTS(或NOT EXISTS)通常      
將提高查詢的效率.                                                                                                             
-->低效:                               
SELECT *                                                            
FROM   emp                                                     
WHERE  sal > 1000                                                    
       AND deptno IN (SELECT deptno                                       
                      FROM   dept                                        
                      WHERE  loc = 'DALLAS')                                   
                                                                 
-->高效:                                                                             
SELECT *                                                               
FROM   emp                                                           
WHERE  empno > 1000                                        
       AND EXISTS                                                     
              (SELECT 1                                     
               FROM   dept                                   
               WHERE  deptno = emp.deptno AND loc = 'DALLAS')                         
                   
14) 用NOT EXISTS替代NOT IN   
    在子查詢中,NOT IN子句引起一個內部的排序與合併.因此,無論何時NOT IN子句都是最低效的,因為它對子查詢中的表執行了一個全表      
遍歷.為避免該情形,應當將其改寫成外部連線(OUTTER JOIN)或適用NOT EXISTS                                  
-->低效:                                                                    
SELECT *                                                                                      
FROM   emp                                                                      
WHERE  deptno NOT IN (SELECT deptno                                        
                       FROM   dept                                          
                       WHERE  loc = 'DALLAS');                                           
                                  
-->高效:                                           
SELECT e.*                                                                                    
FROM   emp e                                                                                    
WHERE  NOT EXISTS                                                                                   
          (SELECT 1                                                                                  
           FROM   dept                                                                              
           WHERE  deptno = e.deptno AND loc = 'DALLAS');                                     
                                                                    
-->最高效(儘管下面的查詢最高效,並不推薦使用,因為列loc使用了不等運算,當表dept資料量較大,且loc列存在索引的話,則此時索引失效) 
SELECT e.*                                                                                   
FROM   emp e LEFT JOIN dept d ON e.deptno = d.deptno                                               
WHERE  d.loc <> 'DALLAS'                                                                     
                                                          
15) 使用表連線替換EXISTS                                              
一般情況下,使用表連線比EXISTS更高效                                                           
-->低效:                                                  
SELECT *                                                                                                     
FROM   employees e                                                                                       
WHERE  EXISTS                                                                                                    
          (SELECT 1                                                                     
           FROM   departments                                                            
           WHERE  department_id = e.department_id AND department_name = 'IT');                               
                                                                                  
-->高效:                
SELECT *              -->經測試此寫法SQLplus下比上面的寫法多一次邏輯讀,而在Toad下兩者結果一致             
FROM   employees e INNER JOIN departments d ON d.department_id = e.department_id            
WHERE  d.department_name = 'IT';                                                      
                                                            
16) 用EXISTS替換DISTINCT        
對於一對多關係表資訊查詢時(如部門表和僱員表),應避免在select 子句中使用distinct,而使用exists來替換         
                                                   
-->低效:                                                              
SELECT DISTINCT e.department_id, d.department_name                                            
FROM   departments d INNER JOIN employees e ON d.department_id = e.department_id;                 
                                
-->高效:                                                          
SELECT d.department_id,department_name                                                       
from departments d                                                                     
WHERE  EXISTS                                                                            
          (SELECT 1                                                                    
           FROM   employees e                                                     
           WHERE  d.department_id=e.department_id);                                            
                                                                    
EXISTS 使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足後,立刻返回結果                              
-->經測試此寫法SQLplus下比上面的寫法多一次邏輯讀,而在Toad下兩者結果一致                                
                                                            
17) 使用 UNION ALL 替換 UNION(如果有可能的話)                                                         
當SQL語句需要UNION兩個查詢結果集時,這兩個結果集合會以UNION-ALL的方式被合併, 然後在輸出最終結果前進行排序。     
如果用UNION ALL替代UNION, 這樣排序就不是必要了。 效率就會因此得到提高。                                                       
                                                              
注意:                     
UNION ALL會輸出所有的結果集,而UNION則過濾掉重複記錄並對其進行排序.因此在使用時應考慮業務邏輯是否允許當前的結果集存在重複現象 
                                                                       
尋找低效的SQL語句                                                          
-->下面的語句主要適用於從檢視v$sqlarea中獲得當前執行下且耗用buffer_gets較多的SQL語句                 
SELECT executions                                                                   
     , disk_reads                                                                  
     , buffer_gets                                                                
     , ROUND( ( buffer_gets       
               - disk_reads )     
             / buffer_gets, 2 )    
          hit_ratio                                    
     , ROUND( disk_reads / executions, 2 ) reads_per_run                 
     , sql_text                                     
FROM   v$sqlarea                                                             
WHERE      executions > 0                                                 
       AND buffer_gets > 0                                             
       AND ( buffer_gets                                                  
            - disk_reads )                                                
           / buffer_gets < 0.80                                                      
ORDER BY 4 DESC;                                                 
                            
18) 儘可能避免使用函式,函式會導致更多的 recursive calls      


 

二、合理使用索引以提高效能
       索引依賴於表而存在,是真實表的一個縮影,類似於一本書的目錄,通過目錄以更快獲得所需的結果。Oracle使用了一個複雜的自平衡
B資料結構。即任意記錄的DML操作將打破索引的平衡,而定期重構索引使得索引重新獲得平衡。通常,通過索引查詢資料比全表掃描更高效。
任意的DQL或DML操作,SQL優化引擎優先使用索引來計算當前操作的成本以生成最佳的執行計劃。一旦使用索引操出引數optimizer_index_cost_adj
設定的值才使用全表掃描。同樣對於多表連線使用索引也可以提高效率。同時索引也提供主鍵(primary key)的唯一性驗證。

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

       雖然使用索引能得到查詢效率的提高,但是索引需要空間來儲存,需要定期維護.尤其是在有大量DML操作的表上,任意的DML操作都將引起索
引的變更這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的儲存空間和處理,
那些不必要的索引反而會使查詢反應時間變慢。

DML操作使用索引上存在碎片而失去高度均衡,因此定期的重構索引是有必要的.

1) 避免基於索引列的計算                                                                                                         
where 子句中的謂詞上存在索引,而此時基於該列的計算將使得索引失效                                                                
                                                                                                                                
-->低效:                    
SELECT employee_id, first_name                                                                                                  
FROM   employees                                                                                                                
WHERE  employee_id + 10 > 150;        -->索引列上使用了計算,因此索引失效,走全表掃描方式                                       
                                                                                                                                
-->高效:                            
SELECT employee_id, first_name                                                                                                  
FROM   employees                                                                                                                
WHERE  employee_id > 160;    -->走索引範圍掃描方式                                                                              
                       
例外情形    
上述規則不適用於SQL中的MIN和MAX函式                                                                                             
hr@CNMMBO> SELECT MAX( employee_id ) max_id                                                                                     
  2  FROM   employees                                                                                                           
  3  WHERE  employee_id                                                                                                         
  4         + 10 > 150;                                                                                                         
                                                                                                                                
1 row selected.                                                                                                                 
                                                                                                                                
Execution Plan                                                                                                                  
----------------------------------------------------------       
Plan hash value: 1481384439                               
---------------------------------------------------------------------------------------------              
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                
---------------------------------------------------------------------------------------------               
|   0 | SELECT STATEMENT            |               |     1 |     4 |     1   (0)| 00:00:01 |                
|   1 |  SORT AGGREGATE             |               |     1 |     4 |            |          |                 
|   2 |   FIRST ROW                 |               |     5 |    20 |     1   (0)| 00:00:01 |         
|*  3 |    INDEX FULL SCAN (MIN/MAX)| EMP_EMP_ID_PK |     5 |    20 |     1   (0)| 00:00:01 |        
---------------------------------------------------------------------------------------------            
                                                                                                                                
2) 避免在索引列上使用NOT運算或不等於運算(<>,!=)                                                                                 
通常,我們要避免在索引列上使用NOT或<>,兩者會產生在和在索引列上使用函式相同的影響。 當ORACLE遇到NOT或不等運算時,他就會停止      
使用索引轉而執行全表掃描。                                                                                                      
                                                                                                                                
-->低效:                                                                               
SELECT *                                                                                                                        
FROM   emp                                                                                                                      
WHERE  NOT ( deptno = 20 );   -->實際上NOT ( deptno = 20 )等同於deptno <> 20,即deptno <>同樣會限制索引                          
                                                                                                                                
-->高效:                                                                         
SELECT *                                                                                                                        
FROM   emp                                                                                                                      
WHERE  deptno > 20 OR deptno < 20;                                                                                              
-->儘管此方式可以替換且實現上述結果,但依然走全表掃描,如果是單純的 > 或 < 運算,則此時為索引範圍掃描                           
                                                                                                                                
需要注意的是,在某些時候, ORACLE優化器會自動將NOT轉化成相對應的關係操作符                                                      
其次如果是下列運算子進行NOT運算,依然有可能選擇走索引, 僅僅除了NOT = 之外,因為 NOT = 等價於 <>                                   
                                                                                                                                
“NOT >”   to <=                                                                                                               
“NOT >=”  to <                                                                                                                
“NOT <”   to >=                                                                                                               
“NOT <=”  to >                                                                                                                
                                                                                                                                
來看一個實際的例子                                                                                                              
hr@CNMMBO> SELECT *                                                                                                             
  2  FROM   employees                                                                                                           
  3  where not employee_id<100; -->索引列上使用了not,但是該查詢返回了所有的記錄,即107條,因此此時選擇走全表掃描                
                                                                                                                                
107 rows selected.                                                                                                              
                                                                                                                                
Execution Plan                                                                                                                  
----------------------------------------------------------  
Plan hash value: 1445457117                                                                                                     
-------------------------------------------------------------------------------           
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |        
-------------------------------------------------------------------------------     
|   0 | SELECT STATEMENT  |           |   107 |  7276 |     3   (0)| 00:00:01 |  
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7276 |     3   (0)| 00:00:01 | -->執行計劃中使用了走全表掃描方式      
-------------------------------------------------------------------------------                                          
Predicate Information (identified by operation id):                                              
---------------------------------------------------     
     
   1 - filter("EMPLOYEE_ID">=100)           -->檢視這裡的謂詞資訊被自動轉換為 >= 運算子                  
                                                                                                                                
hr@CNMMBO> SELECT *                                                                                                             
  2  FROM   employees                                                                                                           
  3  where not employee_id<140; -->此例與上面的語句相同,僅僅是查詢範圍不同返回67條記錄,而此時選擇了索引範圍掃描                 
                                                                                                                                
67 rows selected.                                                                                                               
                                                                                                                                
Execution Plan                                                                                                                  
----------------------------------------------------------        
Plan hash value: 603312277                                                                                                      
                                                                                                                                
---------------------------------------------------------------------------------------------             
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |           
---------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT            |               |    68 |  4624 |     3   (0)| 00:00:01 |         
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |    68 |  4624 |     3   (0)| 00:00:01 |             
|*  2 |   INDEX RANGE SCAN          | EMP_EMP_ID_PK |    68 |       |     1   (0)| 00:00:01 | -->索引範圍掃描方式  
---------------------------------------------------------------------------------------------                         
Predicate Information (identified by operation id):                                              
---------------------------------------------------                              
    2 - access("EMPLOYEE_ID">=140)                                      
                                                                                                                                
3) 用UNION 替換OR(適用於索引列)                                                                                                 
    通常情況下,使用UNION 替換WHERE子句中的OR將會起到較好的效果.基於索引列使用OR使得優化器傾向於使用全表掃描,而不是掃描索引.    
    注意,以上規則僅適用於多個索引列有效。 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低。                       
-->低效:                           
SELECT deptno, dname                                                                                                            
FROM   dept                                                                                                                     
WHERE  loc = 'DALLAS' OR deptno = 20;                                                                                           
                                                                                                                                
-->高效:                                     
SELECT deptno, dname                                                                                                            
FROM   dept                                                                                                                     
WHERE  loc = 'DALLAS'                                                                                                           
UNION                                                                                                                           
SELECT deptno, dname                                                                                                            
FROM   dept                                                                                                                     
WHERE  deptno = 30                                                                                                              
                                                                                                                                
-->經測試,由於資料量較少,此時where子句中的謂詞上都存在索引列時,兩者效能相當.                                                  
-->假定where子句中存在兩列    
scott@CNMMBO> create table t6 as select object_id,owner,object_name from dba_objects where owner='SYS' and rownum<1001;         
                                                                                                                                
scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SCOTT' and rownum<6;              
                                                                                                                                
scott@CNMMBO> create index i_t6_object_id on t6(object_id);                                                
                                                                                                                                
scott@CNMMBO> create index i_t6_owner on t6(owner);                                              
                                                                                                                                
scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SYSTEM' and rownum<=300;          
                                                                                                                                
scott@CNMMBO> commit;                                                                                                           
                                                                                                                                
scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T6',cascade=>true);  
                                                                                                                                
scott@CNMMBO> select owner,count(*) from t6 group by owner;      
                                                            
OWNER                  COUNT(*)                                                
-------------------- ----------                                                  
SCOTT                         5                                                       
SYSTEM                      300                                                         
SYS                        1000                                                        
                                                                                                                                
scott@CNMMBO> select * from t6 where owner='SCOTT' and rownum<2;                                                                
                                                                          
 OBJECT_ID OWNER                OBJECT_NAME                                               
---------- -------------------- --------------------                                          
     69450 SCOTT                T_TEST                                                       
                                                                              
scott@CNMMBO> select * from t6 where object_id=69450 or owner='SYSTEM';                                                         
                                                                                                                                
301 rows selected.                                                                                                              
                                                                                                                                
Execution Plan                                                                                                                  
----------------------------------------------------------     
Plan hash value: 238853296                                                                                                     
-----------------------------------------------------------------------------------------------          
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |         
-----------------------------------------------------------------------------------------------      
|   0 | SELECT STATEMENT             |                |   300 |  7200 |     5   (0)| 00:00:01 |       
|   1 |  CONCATENATION               |                |       |       |            |          |    
|   2 |   TABLE ACCESS BY INDEX ROWID| T6             |     1 |    24 |     2   (0)| 00:00:01 |            
|*  3 |    INDEX RANGE SCAN          | I_T6_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |           
|*  4 |   TABLE ACCESS BY INDEX ROWID| T6             |   299 |  7176 |     3   (0)| 00:00:01 |           
|*  5 |    INDEX RANGE SCAN          | I_T6_OWNER     |   300 |       |     1   (0)| 00:00:01 |              
-----------------------------------------------------------------------------------------------              
                                                                                                                                
Predicate Information (identified by operation id):                                                                             
---------------------------------------------------                               
   3 - access("OBJECT_ID"=69450)                  
   4 - filter(LNNVL("OBJECT_ID"=69450))           
   5 - access("OWNER"='SYSTEM')                
                                                                                                                                
Statistics                                                                                                                      
----------------------------------------------------------  
          0  recursive calls                                                                                                    
          0  db block gets                                                                                                      
         46  consistent gets                                                                                                    
          0  physical reads                                                                                                     
          0  redo size                                                                                                          
      11383  bytes sent via SQL*Net to client                                                                                   
        712  bytes received via SQL*Net from client                                                                             
         22  SQL*Net roundtrips to/from client                                                                                  
          0  sorts (memory)                                                                                                     
          0  sorts (disk)                                                                                                       
        301  rows processed                                                                                                     
                                                                                                                                
scott@CNMMBO> select * from t6 where owner='SYSTEM' or object_id=69450;                                                         
                                                                                                                                
301 rows selected.                                                                                                              
                                                                                                                                
Execution Plan                                                                                                                  
----------------------------------------------------------  
Plan hash value: 238853296                                                                                                      
-----------------------------------------------------------------------------------------------            
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |             
-----------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT             |                |   300 |  7200 |     5   (0)| 00:00:01 |            
|   1 |  CONCATENATION               |                |       |       |            |          |              
|   2 |   TABLE ACCESS BY INDEX ROWID| T6             |     1 |    24 |     2   (0)| 00:00:01 |              
|*  3 |    INDEX RANGE SCAN          | I_T6_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |                
|*  4 |   TABLE ACCESS BY INDEX ROWID| T6             |   299 |  7176 |     3   (0)| 00:00:01 |        
|*  5 |    INDEX RANGE SCAN          | I_T6_OWNER     |   300 |       |     1   (0)| 00:00:01 |        
-----------------------------------------------------------------------------------------------            
                                                                                                                                
Predicate Information (identified by operation id):                                                                             
---------------------------------------------------    
   3 - access("OBJECT_ID"=69450)                                     
   4 - filter(LNNVL("OBJECT_ID"=69450))                                 
   5 - access("OWNER"='SYSTEM')                                   
                                                                                                                                
Statistics                                                            
----------------------------------------------------------                 
          1  recursive calls                                                                                                    
          0  db block gets                                                                                                      
         46  consistent gets                                                                                                    
          0  physical reads                                                                                                     
          0  redo size                                                                                                          
      11383  bytes sent via SQL*Net to client                                                                                   
        712  bytes received via SQL*Net from client                                                                             
         22  SQL*Net roundtrips to/from client                                                                                  
          0  sorts (memory)                                                                                                     
          0  sorts (disk)                                                                                                       
        301  rows processed                                                                                                     
                                                                                                                                
scott@CNMMBO> select * from t6                                                                                                  
  2  where object_id=69450                                                                                                      
  3  union                                                                                                                      
  4  select * from t6                                                                                                           
  5  where owner='SYSTEM';                                                                                                      
                                                                                                                                
301 rows selected.                                                                                                              
                                                                                                                                
Execution Plan                                                                                                                  
----------------------------------------------------------  
Plan hash value: 370530636                                                                                                      
------------------------------------------------------------------------------------------------         
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |         
------------------------------------------------------------------------------------------------        
|   0 | SELECT STATEMENT              |                |   301 |  7224 |     7  (72)| 00:00:01 |         
|   1 |  SORT UNIQUE                  |                |   301 |  7224 |     7  (72)| 00:00:01 |       
|   2 |   UNION-ALL                   |                |       |       |            |          |       
|   3 |    TABLE ACCESS BY INDEX ROWID| T6             |     1 |    24 |     2   (0)| 00:00:01 |        
|*  4 |     INDEX RANGE SCAN          | I_T6_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |    
|   5 |    TABLE ACCESS BY INDEX ROWID| T6             |   300 |  7200 |     3   (0)| 00:00:01 |       
|*  6 |     INDEX RANGE SCAN          | I_T6_OWNER     |   300 |       |     1   (0)| 00:00:01 |    
------------------------------------------------------------------------------------------------              
                                                                                                                                
Predicate Information (identified by operation id):                                                                             
---------------------------------------------------                                                                             
   4 - access("OBJECT_ID"=69450)                       
   6 - access("OWNER"='SYSTEM')                                    
                                                                                                                                
Statistics                                                                                                                      
----------------------------------------------------------                                                                      
          1  recursive calls                                                                                                    
          0  db block gets                                                                                                      
          7  consistent gets                                                                                                    
          0  physical reads                                                                                                     
          0  redo size                                                                                                          
      11383  bytes sent via SQL*Net to client                                                                                   
        712  bytes received via SQL*Net from client                                                                             
         22  SQL*Net roundtrips to/from client                                                                                  
          1  sorts (memory)                                                                                                     
          0  sorts (disk)                                                                                                       
        301  rows processed                                                                                                     
                                                                                                                                
-->從上面的統計資訊可知,consistent gets由46下降為7,故當where子句中謂詞上存在索引時,使用union替換or更高效                     
-->即使當列object_id與owner上不存在索引時,使用union仍然比or更高效(在Oracle 10g R2與Oracle 11g R2測試)                          
                                                                                                                                
4) 避免索引列上使用函式                                                                                                         
-->下面是一個來自實際生產環境的例子                                                                                             
-->表acc_pos_int_tbl上business_date列存在索引,由於使用了SUBSTR函式,此時索引失效,使用全表掃描                                  
SELECT acc_num                                                                                                                  
     , curr_cd                                                                                                                  
     , DECODE( '20110728'                                              
             , ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728', 'YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0     
             ,   adj_credit_int_lv1_amt                    
               + adj_credit_int_lv2_amt                          
               - adj_debit_int_lv1_amt                             
               - adj_debit_int_lv2_amt )                                
          AS interest                                             
FROM   acc_pos_int_tbl                                        
WHERE  SUBSTR( business_date, 1, 6 ) = SUBSTR( '20110728', 1, 6 ) AND business_date <= '20110728';     
                                                                                                                                
-->改進的辦法           
SELECT acc_num                                           
     , curr_cd                                         
     , DECODE( '20110728'                                
             , ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728', 'YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0  
             ,   adj_credit_int_lv1_amt                    
               + adj_credit_int_lv2_amt                       
               - adj_debit_int_lv1_amt                          
               - adj_debit_int_lv2_amt )                           
          AS interest                                     
FROM   acc_pos_int_tbl acc_pos_int_tbl                                             
WHERE  business_date >= TO_CHAR( LAST_DAY( ADD_MONTHS( TO_DATE( '20110728', 'yyyymmdd' ), -1 ) )  
                                + 1, 'yyyymmdd' )                      
       AND business_date <= '20110728';                 
                                                                                                                                
-->下面的例子雖然沒有使用函式,但字串連線同樣導致索引失效                                                                     
-->低效:                    
SELECT account_name, amount                                                                                                     
FROM   transaction                                                                                                              
WHERE  account_name                                                                                                             
       || account_type = 'AMEXA';                                                                                               
                                                                                                                                
-->高效:                        
SELECT account_name, amount                                                                                                     
FROM   transaction                                                                                                              
WHERE  account_name = 'AMEX' AND account_type = 'A';                                                                            
                                                                                                                                
5) 比較不匹配的資料型別                                                                                                         
-->下面的查詢中business_date列上存在索引,且為字元型,這種                                                                        
-->低效:                                  
SELECT *                                                                                                                        
FROM   acc_pos_int_tbl                                                                                                          
WHERE  business_date = 20090201;                                                                                               
                                                                                                                                
Execution Plan                                                                                                                  
----------------------------------------------------------      
Plan hash value: 2335235465                    
                                               
-------------------------------------------------------------------------------------               
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |                       
-------------------------------------------------------------------------------------                      
|   0 | SELECT STATEMENT  |                 | 37516 |  2857K|   106K  (1)| 00:21:17 |                    
|*  1 |  TABLE ACCESS FULL| ACC_POS_INT_TBL | 37516 |  2857K|   106K  (1)| 00:21:17 |               
-------------------------------------------------------------------------------------                  
                                                                                                                                
Predicate Information (identified by operation id):                        
---------------------------------------------------       
     1 - filter(TO_NUMBER("BUSINESS_DATE")=20090201)    -->這裡可以看到產生了型別轉換           
                                                                                                                                
-->高效:                                      
SELECT *                                                                                                                        
FROM   acc_pos_int_tbl                                                                                                          
WHERE  business_date = '20090201'                                                                                               
                                                                                                                                
6) 索引列上使用 NULL 值           
    IS NULL和IS NOT NULL會限制索引的使用,因為資料中沒有值等於NULL值,即便是NULL值也不等於NULL值.且NULL值不儲存在於索引之中  
因此應儘可能避免在索引類上使用NULL值                                                                                            
                                                                                                                                
SELECT acc_num                                                                                                                  
     , pl_cd                                                                                                                    
     , order_qty                                                                                                                
     , trade_date                                                                                                               
FROM   trade_client_tbl                                                                                                         
WHERE  input_date IS NOT NULL;                                                                                                  
                                                                                                                                
Execution Plan                                            
----------------------------------------------------------                        
Plan hash value: 901462645                                      
--------------------------------------------------------------------------------------                 
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |                   
--------------------------------------------------------------------------------------                 
|   0 | SELECT STATEMENT  |                  |     1 |    44 |    15   (0)| 00:00:01 |              
|*  1 |  TABLE ACCESS FULL| TRADE_CLIENT_TBL |     1 |    44 |    15   (0)| 00:00:01 |                   
--------------------------------------------------------------------------------------                 
                                          
alter table trade_client_tbl modify (input_date not null);          
                                                                   
不推薦使用的查詢方式                                      
SELECT * FROM table_name WHERE col IS NOT NULL                      
                                                                
SELECT * FROM table_name WHERE col IS NULL                               
                                                                                                                                
推薦使用的方式                      
SELECT * FROM table_name WHERE col >= 0 --儘可能的使用 =, >=, <=, like 等運算子     
-->Author: Robinson Cheng              
-->Blog: http://blog.csdn.net/robinson_0612               


 

三、總結
1、儘可能最小化基表資料以及中間結果集(通過過濾條件避免後續產生不必要的計算與聚合)
2、為where子句中的謂詞資訊提供最佳的訪問路徑(rowid訪問,索引訪問)
3、使用合理的SQL寫法來避免過多的Oracle內部開銷以提高效能
4、合理的使用提示以提高表之間的連線來提高連線效率(如避免迪卡爾集,將不合理的巢狀連線改為hash連線等)

四、更多參考

Oracle SQL tuning 步驟

啟用使用者程式跟蹤

父遊標、子游標及共享遊標

繫結變數及其優缺點

dbms_xplan之display_cursor函式的使用

dbms_xplan之display函式的使用

執行計劃中各欄位各模組描述

使用 EXPLAIN PLAN 獲取SQL語句執行計劃

啟用 AUTOTRACE 功能

函式使得索引列失效

Oracle 繫結變數窺探

PL/SQL 聯合陣列與巢狀表

 

相關文章