高效SQL語句必殺技

Allen-Li發表於2014-11-05

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

一、編寫高效SQL語句

[sql]view
plaincopyprint?

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;                                                                                          

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;                                                                   

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操作使用索引上存在碎片而失去高度均衡,因此定期的重構索引是有必要的.

[sql]view
plaincopyprint?

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連線等)

本文轉載至：http://blog.csdn.net/leshami/article/details/7406672

高效sql必殺技
2012-12-04
SQL
必知必會——SQL語句基本語法整理
2024-03-14
SQL
高效率Oracle SQL語句
2009-12-10
OracleSQL
如何寫出高效能SQL語句
2012-07-15
SQL
編寫高效 SQL 語句的最佳實踐
2010-11-30
SQL
SQL語句優化技術分析
2008-04-29
SQL優化
[Oracle]高效的SQL語句之分析函式（三）
2008-12-17
OracleSQL函式
優化SQL查詢：如何寫出高效能SQL語句
2016-12-14
優化SQL
SQL語句最佳化技術分析
2007-04-19
SQL
ORACLE SQL語句優化技術分析
2011-12-01
OracleSQL優化
SQL語句
2021-01-03
SQL
[Oracle]高效的SQL語句之分析函式（一）（二）
2008-11-12
OracleSQL函式
ORACLE優化SQL語句，提高效率(1)
2009-11-19
Oracle優化SQL
ORACLE優化SQL語句，提高效率(2)
2009-11-19
Oracle優化SQL
ORACLE優化SQL語句，提高效率(3)
2009-11-19
Oracle優化SQL
Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？
2023-03-07
JavaSQL
Oracle SQL語句最佳化技術分析
2015-09-22
OracleSQL
ORACLE SQL語句優化技術分析(zt)
2005-01-24
OracleSQL優化
ORACLE SQL語句優化技術分析（轉）
2011-12-02
OracleSQL優化
sql語句大全
2016-09-29
SQL
共享SQL語句
2014-01-14
SQL
SQL語句整理
2012-10-22
SQL
SQL基本語句
2009-02-18
SQL
精妙Sql語句
2009-01-04
SQL
SQL語句集合
2008-07-10
SQL
oracle sql語句
2012-12-11
OracleSQL
sql語句求救！
2004-09-27
SQL
SQL精妙語句
2009-08-12
SQL
SQL語句收集
2009-08-12
SQL
常用SQL語句
2007-08-23
SQL
sql常用語句
2024-02-28
SQL
【SQL】10 SQL UPDATE 語句
2018-07-26
SQL
【SQL】11 SQL DELETE 語句
2018-07-26
SQLdelete
【SQL】Oracle 表新增列提高效率語句參考
2021-07-16
SQLOracle
[Oracle]高效的SQL語句之分析函式(四)--lag()/lead()
2008-12-27
OracleSQL函式
Mysql常用SQL語句集錦 | 掘金技術徵文
2016-12-12
MySql
談談SQL 語句的優化技術 (2)
2011-04-21
SQL優化
談談SQL 語句的優化技術 (1)
2011-04-21
SQL優化

高效SQL語句必殺技

相關文章