高效SQL語句必殺技

Allen-Li發表於2014-11-05

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

 

一、編寫高效SQL語句

  1. 1) 選擇最有效的表名順序(僅適用於RBO模式)                                                                              
  2.     ORACLE的解析器總是按照從右到左的順序處理FROM子句中的表名,因此FROM子句中最後的一個表將作為驅動表被優先處理。當FROM子句       
  3. 存在多個表的時候,應當考慮將表上記錄最少的那個表置於FROM最右端作為基表。Oracle會首先掃描基表(FROM子句中最後的那個表)並對       
  4. 記錄進行排序,然後掃描第二個表(FROM子句中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合併。如       
  5. 果有3個以上的表連線查詢, 那就需要選擇交叉表(intersection table)作為基礎表,交叉表是指那個被其他表所引用的表。                 
  6.                                                                                                                                  
  7. 下面的例子使用最常見的scott或hr模式下的表進行演示                                
  8.                                                                                                
  9. 表 EMP 有14條記錄                                                                
  10. 表 DEPT 有4條記錄                                                   
  11. SELECT  /*+ rule */ COUNT(*)  FROM  emp, dept;          --高效的寫法    
  12.                                                                                                                                  
  13. scott@CNMMBO> set autotrace traceonly stat;                                                     
  14. scott@CNMMBO> SELECT  /*+ rule */ COUNT( * )  FROM   emp, dept;                        
  15.                                                                              
  16. Elapsed: 00:00:00.14                                                         
  17.                                                                                      
  18. Statistics                                                                         
  19. ----------------------------------------------------------                                
  20.           1  recursive calls                                                            
  21.           0  db block gets                                                             
  22.          35  consistent gets                                                        
  23.           0  physical reads                                                 
  24.           0  redo size                                                               
  25.         515  bytes sent via SQL*Net to client                                    
  26.         492  bytes received via SQL*Net from client                             
  27.           2  SQL*Net roundtrips to/from client                            
  28.           0  sorts (memory)                                                       
  29.           0  sorts (disk)                                                             
  30.           1  rows processed                                                                   
  31.                                                                                              
  32. SELECT  /*+ rule */ COUNT( * )  FROM   dept, emp;         --低效的寫法            
  33. scott@CNMMBO> SELECT  /*+ rule */ COUNT( * )  FROM   dept, emp;                             
  34.                                                                                           
  35. Elapsed: 00:00:00.02                                                                           
  36.                                           
  37. Statistics                                                                                   
  38. ----------------------------------------------------------                                
  39.           1  recursive calls                                                         
  40.           0  db block gets                                                                
  41.         105  consistent gets                                                              
  42.           0  physical reads                                                              
  43.           0  redo size                                                                 
  44.         515  bytes sent via SQL*Net to client                                    
  45.         492  bytes received via SQL*Net from client                                    
  46.           2  SQL*Net roundtrips to/from client                                           
  47.           0  sorts (memory)                                                           
  48.           0  sorts (disk)                                                           
  49.           1  rows processed                                                                 
  50.                                                                                               
  51. 2) select 查詢中避免使用'*'                                                               
  52.    當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 '*' 是一個方便的方法.不幸的是,這是一個非常低效的方法.實際       
  53. 上,ORACLE在解析的過程中, 會將 '*' 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間。         
  54. 注:本文中的例子出於簡化演示而使用了select * ,生產環境應避免使用.                                
  55.                                                                                   
  56. 3) 減少訪問資料庫的次數                                                   
  57.     每當執行一條SQL語句,Oracle 需要完成大量的內部操作,像解析SQL語句,估算索引的利用率,繫結變數, 讀資料塊等等.由此可       
  58. 見,減少訪問資料庫的次數,實際上是降低了資料庫系統開銷                              
  59. -->下面通過3種方式來獲得僱員編號為7788與7902的相關資訊                           
  60.                                                                              
  61. -->方式 1 (最低效):                                                       
  62. select ename,job,sal from emp where empno=7788;                            
  63.                                                                              
  64. select ename,job,sal from emp where empno=7902;                                    
  65.                                                                        
  66. -->方式 2 (次低效):                                                              
  67. -->下面使用了引數遊標來完成,每傳遞一次引數則需要對錶emp訪問一次,增加了I/O                           
  68.   DECLARE                                                                              
  69.     CURSOR C1(E_NO NUMBER)  IS                                                     
  70.     SELECT ename, job, sal                                                          
  71.     FROM emp                                                                    
  72.     WHERE empno = E_NO;                                                      
  73.   BEGIN                                                                        
  74.     OPEN C1 (7788);                                                           
  75.     FETCH C1 INTO …, …, …;                                                      
  76.     ..                                                                     
  77.     OPEN C1 (7902);                                                     
  78.     FETCH C1 INTO …, …, …;                                                   
  79.     CLOSE C1;                                                                
  80.   END;                                                                           
  81.                                                                                   
  82. -->方式 3 (最高效)                                             
  83. SELECT a.ename                                                              
  84.      , a.job                                                     
  85.      , a.sal                                                        
  86.      , b.ename                                                        
  87.      , b.job                                                           
  88.      , b.sal                                                        
  89. FROM   emp a, emp b                                                      
  90. WHERE  a.empno = 7788 OR b.empno = 7902;                                      
  91.                                                         
  92. 注意:在SQL*Plus,SQL*Forms和Pro*C中重新設定ARRAYSIZE引數,可以增加每次資料庫訪問的檢索資料量,建議值為200.       
  93.                                                                  
  94. 4) 使用DECODE函式來減少處理時間                                
  95. -->使用decode函式可以避免重複掃描相同的行或重複連線相同的表                   
  96. select count(*),sum(sal) from emp where deptno=20 and ename like 'SMITH%';              
  97.                                                                                           
  98. select count(*),sum(sal) from emp where deptno=30 and ename like 'SMITH%';                
  99.                                                                                               
  100. -->通過使用decode函式一次掃描即可完成所有滿足條件記錄的處理                                        
  101. SELECT COUNT( DECODE( deptno, 20, 'x'NULL ) ) d20_count                                         
  102.      , COUNT( DECODE( deptno, 30, 'x'NULL ) ) d30_count                                      
  103.      , SUM( DECODE( deptno, 20, sal, NULL ) ) d20_sal                                          
  104.      , SUM( DECODE( deptno, 30, sal, NULL ) ) d30_sal                                            
  105. FROM   emp                                                                  
  106. WHERE  ename LIKE 'SMITH%';                                                
  107.                                                                           
  108. 類似的,DECODE函式也可以運用於GROUP BY 和ORDER BY子句中。                           
  109.                                                                                  
  110. 5) 整合簡單,無關聯的資料庫訪問                                        
  111. -->如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中以提高效能(即使它們之間沒有關係)          
  112. -->整合前                                                                           
  113. SELECT name                                                                            
  114. FROM   emp                                                                          
  115. WHERE  empno = 1234;                                                              
  116.                                                                                
  117. SELECT name                                                                     
  118. FROM   dept                                                          
  119. WHERE  deptno = 10;                                                        
  120.                                                                                             
  121. SELECT name                                                                 
  122. FROM   cat                                                               
  123. WHERE  cat_type = 'RD';                                                                   
  124.                                                                                           
  125. -->整合後                                                                       
  126. SELECT e.name, d.name, c.name                                                                     
  127. FROM   cat c                                                                                         
  128.      , dpt d                                                                                        
  129.      , emp e                                                                                        
  130.      , dual x                                                                                     
  131. WHERE      NVL( 'X', x.dummy ) = NVL( 'X', e.ROWID(+) )                          
  132.        AND NVL( 'X', x.dummy ) = NVL( 'X', d.ROWID(+) )           
  133.        AND NVL( 'X', x.dummy ) = NVL( 'X', c.ROWID(+) )            
  134.        AND e.emp_no(+) = 1234                                                                     
  135.        AND d.dept_no(+) = 10                                                                     
  136.        AND c.cat_type(+) = 'RD';                                                                   
  137.                                                                                          
  138. -->從上面的SQL語句可以看出,儘管三條語句被整合為一條,效能得以提高,然可讀性差,此時應權衡效能與代價              
  139.                                                                                                                                  
  140. 6) 刪除重複記錄                                                                                   
  141. -->通過使用rowid來作為過濾條件,效能高效                                               
  142. DELETE FROM emp e                                                                     
  143. WHERE  e.ROWID > (SELECT MIN( x.ROWID )     
  144.                   FROM   emp x                                                       
  145.                   WHERE  x.empno = e.empno);                                                   
  146.                                                                                     
  147. 7) 使用truncate 代替 delete                                                               
  148. -->通常情況下,任意記錄的刪除需要在回滾段構造刪除前映象以實現回滾(rollback).對於未提交的資料在執行rollback之後,Oracle會生成     
  149. -->等價SQL語句去恢復記錄(如delete,則生成對應的insert語句;如insert則生成對應的delete;如update,則是同時生成delete和insert  
  150. -->使用truncate命令則是執行DDL命令,不產生任何回滾資訊,直接格式化並釋放高水位線.故該語句效能高效.由於不能rollback,因此慎用.   
  151.                                                                                                  
  152. 8) 儘量多使用COMMIT(COMMIT應確保事務的完整性)             
  153. -->只要有可能,在程式中儘量多使用COMMIT,這樣程式的效能得到提高,需求也會因為COMMIT所釋放的資源而減少              
  154. -->COMMIT所釋放的資源:                                                                                                          
  155. -->1.回滾段上用於恢復資料的資訊                                                                                                  
  156. -->2.釋放語句處理期間所持有的鎖                                                                                                  
  157. -->3.釋放redo log buffer佔用的空間(commit將redo log buffer中的entries 寫入到聯機重做日誌檔案)                          
  158. -->4.ORACLE為管理上述3種資源中的內部開銷                                                                                         
  159.                                                                                                                                  
  160. 9) 計算記錄條數                                                                                                                  
  161. -->一般的情況下,count(*)比count(1)稍快.如果可以通過索引檢索,對索引列的計數是最快的,因為直接掃描索引即可,例如COUNT(EMPNO)   
  162. -->實際情況是經測試上述三種情況並無明顯差異.                                                   
  163.                                                                                                 
  164. 10) 用Where子句替換HAVING子句                                                                                                    
  165. -->儘可能的避免having子句,因為HAVING 子句是對檢索出所有記錄之後再對結果集進行過濾。這個處理需要排序,總計等操作                 
  166. -->通過WHERE子句則在分組之前即可過濾不必要的記錄數目,從而減少聚合的開銷                                                         
  167.                                                                                                                                  
  168. -->低效:                                                                                
  169. SELECT deptno, AVG( sal )                                                                 
  170. FROM   emp                                                                                              
  171. GROUP BY deptno                                                                                          
  172. HAVING deptno = 20;                                                                                          
  173.                                                                                             
  174.                                                                                                                       
  175. Statistics                      
  176. ----------------------------------------------------------     
  177.           0  recursive calls                                                                           
  178.           0  db block gets                                                                 
  179.           7  consistent gets                                                               
  180.           0  physical reads                                                                
  181.           0  redo size                                                                       
  182.         583  bytes sent via SQL*Net to client                                                  
  183.         492  bytes received via SQL*Net from client                                                 
  184.           2  SQL*Net roundtrips to/from client                                                       
  185.           0  sorts (memory)                                                                      
  186.           0  sorts (disk)                                                                     
  187.           1  rows processed                                                                
  188. -->高效:                                                                
  189. SELECT deptno, AVG( sal )          
  190. FROM   emp                                                                                      
  191. WHERE  deptno = 20                                                                                  
  192. GROUP BY deptno;                                                                   
  193.                                                                                                        
  194.                                                                                          
  195. Statistics                                          
  196. ----------------------------------------------------------        
  197.           0  recursive calls                                                              
  198.           0  db block gets                                                               
  199.           2  consistent gets                                                               
  200.           0  physical reads                                                            
  201.           0  redo size                                                                
  202.         583  bytes sent via SQL*Net to client                                         
  203.         492  bytes received via SQL*Net from client                                      
  204.           2  SQL*Net roundtrips to/from client                                               
  205.           0  sorts (memory)                                                                      
  206.           0  sorts (disk)                                                                          
  207.           1  rows processed                                                                     
  208.                                                                                         
  209. 11) 最小化表查詢次數                                                                                                             
  210. -->在含有子查詢的SQL語句中,要特別注意減少對錶的查詢                                                                             
  211. -->低效:                                                           
  212. SELECT *                                                                                      
  213. FROM   employees                                                                                   
  214. WHERE  department_id = (SELECT department_id                                                       
  215.                         FROM   departments                                                     
  216.                         WHERE  department_name = 'Marketing')                                   
  217.        AND manager_id = (SELECT manager_id                                                       
  218.                          FROM   departments                                                     
  219.                          WHERE  department_name = 'Marketing');                                  
  220. -->高效:                                                                
  221. SELECT *                                                                                    
  222. FROM   employees                                                                             
  223. WHERE  ( department_id, manager_id ) = (SELECT department_id, manager_id                                  
  224.                                         FROM   departments                                                   
  225.                                         WHERE  department_name = 'Marketing')                   
  226.                                                                                     
  227. -->類似更新多列的情形                
  228. -->低效:                     
  229. UPDATE employees                                                                                  
  230. SET    job_id = ( SELECT MAX( job_id ) FROM jobs ), salary = ( SELECT AVG( min_salary ) FROM jobs )           
  231. WHERE  department_id = 10;                                                                    
  232.                                                                                         
  233. -->高效:                  
  234. UPDATE employees           
  235. SET    ( job_id, salary ) = ( SELECT MAX( job_id ), AVG( min_salary ) FROM jobs )       
  236. WHERE  department_id = 10;                                                              
  237.                                                                           
  238. 12) 使用表別名                                                                        
  239. -->在多表查詢時,為所返回列使用表別名作為字首以減少解析時間以及那些相同列歧義引起的語法錯誤                                      
  240.                                                                                  
  241. 13) 用EXISTS替代IN                                                                                   
  242.     在一些基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下,使用EXISTS(或NOT EXISTS)通常        
  243. 將提高查詢的效率.                                                                                                               
  244. -->低效:                                 
  245. SELECT *                                                              
  246. FROM   emp                                                       
  247. WHERE  sal > 1000                                                      
  248.        AND deptno IN (SELECT deptno                                         
  249.                       FROM   dept                                          
  250.                       WHERE  loc = 'DALLAS')                                     
  251.                                                                    
  252. -->高效:                                                                               
  253. SELECT *                                                                 
  254. FROM   emp                                                             
  255. WHERE  empno > 1000                                          
  256.        AND EXISTS                                                       
  257.               (SELECT 1                                       
  258.                FROM   dept                                     
  259.                WHERE  deptno = emp.deptno AND loc = 'DALLAS')                           
  260.                      
  261. 14) 用NOT EXISTS替代NOT IN     
  262.     在子查詢中,NOT IN子句引起一個內部的排序與合併.因此,無論何時NOT IN子句都是最低效的,因為它對子查詢中的表執行了一個全表        
  263. 遍歷.為避免該情形,應當將其改寫成外部連線(OUTTER JOIN)或適用NOT EXISTS                                    
  264. -->低效:                                                                      
  265. SELECT *                                                                                        
  266. FROM   emp                                                                        
  267. WHERE  deptno NOT IN (SELECT deptno                                          
  268.                        FROM   dept                                            
  269.                        WHERE  loc = 'DALLAS');                                             
  270.                                     
  271. -->高效:                                             
  272. SELECT e.*                                                                                      
  273. FROM   emp e                                                                                      
  274. WHERE  NOT EXISTS                                                                                     
  275.           (SELECT 1                                                                                    
  276.            FROM   dept                                                                                
  277.            WHERE  deptno = e.deptno AND loc = 'DALLAS');                                       
  278.                                                                       
  279. -->最高效(儘管下面的查詢最高效,並不推薦使用,因為列loc使用了不等運算,當表dept資料量較大,且loc列存在索引的話,則此時索引失效)   
  280. SELECT e.*                                                                                     
  281. FROM   emp e LEFT JOIN dept d ON e.deptno = d.deptno                                                 
  282. WHERE  d.loc <> 'DALLAS'                                                                       
  283.                                                             
  284. 15) 使用表連線替換EXISTS                                                
  285. 一般情況下,使用表連線比EXISTS更高效                                                             
  286. -->低效:                                                    
  287. SELECT *                                                                                                       
  288. FROM   employees e                                                                                         
  289. WHERE  EXISTS                                                                                                      
  290.           (SELECT 1                                                                       
  291.            FROM   departments                                                              
  292.            WHERE  department_id = e.department_id AND department_name = 'IT');                                 
  293.                                                                                     
  294. -->高效:                  
  295. SELECT *              -->經測試此寫法SQLplus下比上面的寫法多一次邏輯讀,而在Toad下兩者結果一致               
  296. FROM   employees e INNER JOIN departments d ON d.department_id = e.department_id              
  297. WHERE  d.department_name = 'IT';                                                        
  298.                                                               
  299. 16) 用EXISTS替換DISTINCT          
  300. 對於一對多關係表資訊查詢時(如部門表和僱員表),應避免在select 子句中使用distinct,而使用exists來替換           
  301.                                                      
  302. -->低效:                                                                
  303. SELECT DISTINCT e.department_id, d.department_name                                              
  304. FROM   departments d INNER JOIN employees e ON d.department_id = e.department_id;                   
  305.                                   
  306. -->高效:                                                            
  307. SELECT d.department_id,department_name                                                         
  308. from departments d                                                                       
  309. WHERE  EXISTS                                                                              
  310.           (SELECT 1                                                                      
  311.            FROM   employees e                                                       
  312.            WHERE  d.department_id=e.department_id);                                              
  313.                                                                       
  314. EXISTS 使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足後,立刻返回結果                                
  315. -->經測試此寫法SQLplus下比上面的寫法多一次邏輯讀,而在Toad下兩者結果一致                                  
  316.       
  317.                                                         
  318. 17) 使用 UNION ALL 替換 UNION(如果有可能的話)                                                           
  319. 當SQL語句需要UNION兩個查詢結果集時,這兩個結果集合會以UNION-ALL的方式被合併, 然後在輸出最終結果前進行排序。       
  320. 如果用UNION ALL替代UNION, 這樣排序就不是必要了。 效率就會因此得到提高。                                                         
  321.                                                                 
  322. 注意:                       
  323. UNION ALL會輸出所有的結果集,而UNION則過濾掉重複記錄並對其進行排序.因此在使用時應考慮業務邏輯是否允許當前的結果集存在重複現象   
  324.                                                                          
  325. 尋找低效的SQL語句                                                            
  326. -->下面的語句主要適用於從檢視v$sqlarea中獲得當前執行下且耗用buffer_gets較多的SQL語句                   
  327. SELECT executions                                                                     
  328.      , disk_reads                                                                    
  329.      , buffer_gets                                                                  
  330.      , ROUND( ( buffer_gets         
  331.                - disk_reads )       
  332.              / buffer_gets, 2 )      
  333.           hit_ratio                                      
  334.      , ROUND( disk_reads / executions, 2 ) reads_per_run                   
  335.      , sql_text                                       
  336. FROM   v$sqlarea                                                               
  337. WHERE      executions > 0                                                   
  338.        AND buffer_gets > 0                                               
  339.        AND ( buffer_gets                                                    
  340.             - disk_reads )                                                  
  341.            / buffer_gets < 0.80                                                        
  342. ORDER BY 4 DESC;                                                   
  343.                               
  344. 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. 1) 避免基於索引列的計算                                                                                                           
  2. where 子句中的謂詞上存在索引,而此時基於該列的計算將使得索引失效                                                                  
  3.                                                                                                                                   
  4. -->低效:                      
  5. SELECT employee_id, first_name                                                                                                    
  6. FROM   employees                                                                                                                  
  7. WHERE  employee_id + 10 > 150;        -->索引列上使用了計算,因此索引失效,走全表掃描方式                                         
  8.                                                                                                                                   
  9. -->高效:                              
  10. SELECT employee_id, first_name                                                                                                    
  11. FROM   employees                                                                                                                  
  12. WHERE  employee_id > 160;    -->走索引範圍掃描方式                                                                                
  13.                          
  14. 例外情形      
  15. 上述規則不適用於SQL中的MINMAX函式                                                                                               
  16. hr@CNMMBO> SELECT MAX( employee_id ) max_id                                                                                       
  17.   2  FROM   employees                                                                                                             
  18.   3  WHERE  employee_id                                                                                                           
  19.   4         + 10 > 150;                                                                                                           
  20.                                                                                                                                   
  21. 1 row selected.                                                                                                                   
  22.                                                                                                                                   
  23. Execution Plan                                                                                                                    
  24. ----------------------------------------------------------         
  25. Plan hash value: 1481384439                                 
  26. ---------------------------------------------------------------------------------------------                
  27. | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                  
  28. ---------------------------------------------------------------------------------------------                 
  29. |   0 | SELECT STATEMENT            |               |     1 |     4 |     1   (0)| 00:00:01 |                  
  30. |   1 |  SORT AGGREGATE             |               |     1 |     4 |            |          |                   
  31. |   2 |   FIRST ROW                 |               |     5 |    20 |     1   (0)| 00:00:01 |           
  32. |*  3 |    INDEX FULL SCAN (MIN/MAX)| EMP_EMP_ID_PK |     5 |    20 |     1   (0)| 00:00:01 |          
  33. ---------------------------------------------------------------------------------------------              
  34.                                                                                                                                   
  35. 2) 避免在索引列上使用NOT運算或不等於運算(<>,!=)                                                                                   
  36. 通常,我們要避免在索引列上使用NOT或<>,兩者會產生在和在索引列上使用函式相同的影響。 當ORACLE遇到NOT或不等運算時,他就會停止        
  37. 使用索引轉而執行全表掃描。                                                                                                        
  38.                                                                                                                                   
  39. -->低效:                                                                                 
  40. SELECT *                                                                                                                          
  41. FROM   emp                                                                                                                        
  42. WHERE  NOT ( deptno = 20 );   -->實際上NOT ( deptno = 20 )等同於deptno <> 20,即deptno <>同樣會限制索引                            
  43.                                                                                                                                   
  44. -->高效:                                                                           
  45. SELECT *                                                                                                                          
  46. FROM   emp                                                                                                                        
  47. WHERE  deptno > 20 OR deptno < 20;                                                                                                
  48. -->儘管此方式可以替換且實現上述結果,但依然走全表掃描,如果是單純的 > 或 < 運算,則此時為索引範圍掃描                             
  49.                                                                                                                                   
  50. 需要注意的是,在某些時候, ORACLE優化器會自動將NOT轉化成相對應的關係操作符                                                        
  51. 其次如果是下列運算子進行NOT運算,依然有可能選擇走索引, 僅僅除了NOT = 之外,因為 NOT = 等價於 <>                                     
  52.                                                                                                                                   
  53. NOT >”   to <=                                                                                                                 
  54. NOT >=”  to <                                                                                                                  
  55. NOT <”   to >=                                                                                                                 
  56. NOT <=”  to >                                                                                                                  
  57.                                                                                                                                   
  58. 來看一個實際的例子                                                                                                                
  59. hr@CNMMBO> SELECT *                                                                                                               
  60.   2  FROM   employees                                                                                                             
  61.   3  where not employee_id<100; -->索引列上使用了not,但是該查詢返回了所有的記錄,即107條,因此此時選擇走全表掃描                  
  62.                                                                                                                                   
  63. 107 rows selected.                                                                                                                
  64.                                                                                                                                   
  65. Execution Plan                                                                                                                    
  66. ----------------------------------------------------------    
  67. Plan hash value: 1445457117                                                                                                       
  68. -------------------------------------------------------------------------------             
  69. | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |          
  70. -------------------------------------------------------------------------------       
  71. |   0 | SELECT STATEMENT  |           |   107 |  7276 |     3   (0)| 00:00:01 |    
  72. |*  1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7276 |     3   (0)| 00:00:01 | -->執行計劃中使用了走全表掃描方式        
  73. -------------------------------------------------------------------------------                                            
  74. Predicate Information (identified by operation id):                                                
  75. ---------------------------------------------------       
  76.        
  77.    1 - filter("EMPLOYEE_ID">=100)           -->檢視這裡的謂詞資訊被自動轉換為 >= 運算子                    
  78.                                                                                                                                   
  79. hr@CNMMBO> SELECT *                                                                                                               
  80.   2  FROM   employees                                                                                                             
  81.   3  where not employee_id<140; -->此例與上面的語句相同,僅僅是查詢範圍不同返回67條記錄,而此時選擇了索引範圍掃描                   
  82.                                                                                                                                   
  83. 67 rows selected.                                                                                                                 
  84.                                                                                                                                   
  85. Execution Plan                                                                                                                    
  86. ----------------------------------------------------------          
  87. Plan hash value: 603312277                                                                                                        
  88.                                                                                                                                   
  89. ---------------------------------------------------------------------------------------------               
  90. | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |             
  91. ---------------------------------------------------------------------------------------------            
  92. |   0 | SELECT STATEMENT            |               |    68 |  4624 |     3   (0)| 00:00:01 |           
  93. |   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |    68 |  4624 |     3   (0)| 00:00:01 |               
  94. |*  2 |   INDEX RANGE SCAN          | EMP_EMP_ID_PK |    68 |       |     1   (0)| 00:00:01 | -->索引範圍掃描方式    
  95. ---------------------------------------------------------------------------------------------                           
  96. Predicate Information (identified by operation id):                                                
  97. ---------------------------------------------------                                
  98.     2 - access("EMPLOYEE_ID">=140)                                        
  99.                                                                                                                                   
  100. 3) 用UNION 替換OR(適用於索引列)                                                                                                   
  101.     通常情況下,使用UNION 替換WHERE子句中的OR將會起到較好的效果.基於索引列使用OR使得優化器傾向於使用全表掃描,而不是掃描索引.      
  102.     注意,以上規則僅適用於多個索引列有效。 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低。                         
  103. -->低效:                             
  104. SELECT deptno, dname                                                                                                              
  105. FROM   dept                                                                                                                       
  106. WHERE  loc = 'DALLAS' OR deptno = 20;                                                                                             
  107.                                                                                                                                   
  108. -->高效:                                       
  109. SELECT deptno, dname                                                                                                              
  110. FROM   dept                                                                                                                       
  111. WHERE  loc = 'DALLAS'                                                                                                             
  112. UNION                                                                                                                             
  113. SELECT deptno, dname                                                                                                              
  114. FROM   dept                                                                                                                       
  115. WHERE  deptno = 30                                                                                                                
  116.                                                                                                                                   
  117. -->經測試,由於資料量較少,此時where子句中的謂詞上都存在索引列時,兩者效能相當.                                                    
  118. -->假定where子句中存在兩列      
  119. scott@CNMMBO> create table t6 as select object_id,owner,object_name from dba_objects where owner='SYS' and rownum<1001;           
  120.                                                                                                                                   
  121. scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SCOTT' and rownum<6;                
  122.                                                                                                                                   
  123. scott@CNMMBO> create index i_t6_object_id on t6(object_id);                                                  
  124.                                                                                                                                   
  125. scott@CNMMBO> create index i_t6_owner on t6(owner);                                                
  126.                                                                                                                                   
  127. scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SYSTEM' and rownum<=300;            
  128.                                                                                                                                   
  129. scott@CNMMBO> commit;                                                                                                             
  130.                                                                                                                                   
  131. scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T6',cascade=>true);    
  132.                                                                                                                                   
  133. scott@CNMMBO> select owner,count(*) from t6 group by owner;        
  134.                                                               
  135. OWNER                  COUNT(*)                                                  
  136. -------------------- ----------                                                    
  137. SCOTT                         5                                                         
  138. SYSTEM                      300                                                           
  139. SYS                        1000                                                          
  140.                                                                                                                                   
  141. scott@CNMMBO> select * from t6 where owner='SCOTT' and rownum<2;                                                                  
  142.                                                                             
  143.  OBJECT_ID OWNER                OBJECT_NAME                                                 
  144. ---------- -------------------- --------------------                                            
  145.      69450 SCOTT                T_TEST                                                         
  146.                                                                                 
  147. scott@CNMMBO> select * from t6 where object_id=69450 or owner='SYSTEM';                                                           
  148.                                                                                                                                   
  149. 301 rows selected.                                                                                                                
  150.                                                                                                                                   
  151. Execution Plan                                                                                                                    
  152. ----------------------------------------------------------       
  153. Plan hash value: 238853296                                                                                                       
  154. -----------------------------------------------------------------------------------------------            
  155. | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |           
  156. -----------------------------------------------------------------------------------------------        
  157. |   0 | SELECT STATEMENT             |                |   300 |  7200 |     5   (0)| 00:00:01 |         
  158. |   1 |  CONCATENATION               |                |       |       |            |          |      
  159. |   2 |   TABLE ACCESS BY INDEX ROWID| T6             |     1 |    24 |     2   (0)| 00:00:01 |              
  160. |*  3 |    INDEX RANGE SCAN          | I_T6_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |             
  161. |*  4 |   TABLE ACCESS BY INDEX ROWID| T6             |   299 |  7176 |     3   (0)| 00:00:01 |             
  162. |*  5 |    INDEX RANGE SCAN          | I_T6_OWNER     |   300 |       |     1   (0)| 00:00:01 |                
  163. -----------------------------------------------------------------------------------------------                
  164.                                                                                                                                   
  165. Predicate Information (identified by operation id):                                                                               
  166. ---------------------------------------------------                                 
  167.    3 - access("OBJECT_ID"=69450)                    
  168.    4 - filter(LNNVL("OBJECT_ID"=69450))             
  169.    5 - access("OWNER"='SYSTEM')                  
  170.                                                                                                                                   
  171. Statistics                                                                                                                        
  172. ----------------------------------------------------------    
  173.           0  recursive calls                                                                                                      
  174.           0  db block gets                                                                                                        
  175.          46  consistent gets                                                                                                      
  176.           0  physical reads                                                                                                       
  177.           0  redo size                                                                                                            
  178.       11383  bytes sent via SQL*Net to client                                                                                     
  179.         712  bytes received via SQL*Net from client                                                                               
  180.          22  SQL*Net roundtrips to/from client                                                                                    
  181.           0  sorts (memory)                                                                                                       
  182.           0  sorts (disk)                                                                                                         
  183.         301  rows processed                                                                                                       
  184.                                                                                                                                   
  185. scott@CNMMBO> select * from t6 where owner='SYSTEM' or object_id=69450;                                                           
  186.                                                                                                                                   
  187. 301 rows selected.                                                                                                                
  188.                                                                                                                                   
  189. Execution Plan                                                                                                                    
  190. ----------------------------------------------------------    
  191. Plan hash value: 238853296                                                                                                        
  192. -----------------------------------------------------------------------------------------------              
  193. | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |               
  194. -----------------------------------------------------------------------------------------------            
  195. |   0 | SELECT STATEMENT             |                |   300 |  7200 |     5   (0)| 00:00:01 |              
  196. |   1 |  CONCATENATION               |                |       |       |            |          |                
  197. |   2 |   TABLE ACCESS BY INDEX ROWID| T6             |     1 |    24 |     2   (0)| 00:00:01 |                
  198. |*  3 |    INDEX RANGE SCAN          | I_T6_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |                  
  199. |*  4 |   TABLE ACCESS BY INDEX ROWID| T6             |   299 |  7176 |     3   (0)| 00:00:01 |          
  200. |*  5 |    INDEX RANGE SCAN          | I_T6_OWNER     |   300 |       |     1   (0)| 00:00:01 |          
  201. -----------------------------------------------------------------------------------------------              
  202.                                                                                                                                   
  203. Predicate Information (identified by operation id):                                                                               
  204. ---------------------------------------------------      
  205.    3 - access("OBJECT_ID"=69450)                                       
  206.    4 - filter(LNNVL("OBJECT_ID"=69450))                                   
  207.    5 - access("OWNER"='SYSTEM')                                     
  208.                                                                                                                                   
  209. Statistics                                                              
  210. ----------------------------------------------------------                   
  211.           1  recursive calls                                                                                                      
  212.           0  db block gets                                                                                                        
  213.          46  consistent gets                                                                                                      
  214.           0  physical reads                                                                                                       
  215.           0  redo size                                                                                                            
  216.       11383  bytes sent via SQL*Net to client                                                                                     
  217.         712  bytes received via SQL*Net from client                                                                               
  218.          22  SQL*Net roundtrips to/from client                                                                                    
  219.           0  sorts (memory)                                                                                                       
  220.           0  sorts (disk)                                                                                                         
  221.         301  rows processed                                                                                                       
  222.                                                                                                                                   
  223. scott@CNMMBO> select * from t6                                                                                                    
  224.   2  where object_id=69450                                                                                                        
  225.   3  union                                                                                                                        
  226.   4  select * from t6                                                                                                             
  227.   5  where owner='SYSTEM';                                                                                                        
  228.                                                                                                                                   
  229. 301 rows selected.                                                                                                                
  230.                                                                                                                                   
  231. Execution Plan                                                                                                                    
  232. ----------------------------------------------------------    
  233. Plan hash value: 370530636                                                                                                        
  234. ------------------------------------------------------------------------------------------------           
  235. | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |           
  236. ------------------------------------------------------------------------------------------------          
  237. |   0 | SELECT STATEMENT              |                |   301 |  7224 |     7  (72)| 00:00:01 |           
  238. |   1 |  SORT UNIQUE                  |                |   301 |  7224 |     7  (72)| 00:00:01 |         
  239. |   2 |   UNION-ALL                   |                |       |       |            |          |         
  240. |   3 |    TABLE ACCESS BY INDEX ROWID| T6             |     1 |    24 |     2   (0)| 00:00:01 |          
  241. |*  4 |     INDEX RANGE SCAN          | I_T6_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |      
  242. |   5 |    TABLE ACCESS BY INDEX ROWID| T6             |   300 |  7200 |     3   (0)| 00:00:01 |         
  243. |*  6 |     INDEX RANGE SCAN          | I_T6_OWNER     |   300 |       |     1   (0)| 00:00:01 |      
  244. ------------------------------------------------------------------------------------------------                
  245.                                                                                                                                   
  246. Predicate Information (identified by operation id):                                                                               
  247. ---------------------------------------------------                                                                               
  248.    4 - access("OBJECT_ID"=69450)                         
  249.    6 - access("OWNER"='SYSTEM')                                      
  250.                                                                                                                                   
  251. Statistics                                                                                                                        
  252. ----------------------------------------------------------                                                                        
  253.           1  recursive calls                                                                                                      
  254.           0  db block gets                                                                                                        
  255.           7  consistent gets                                                                                                      
  256.           0  physical reads                                                                                                       
  257.           0  redo size                                                                                                            
  258.       11383  bytes sent via SQL*Net to client                                                                                     
  259.         712  bytes received via SQL*Net from client                                                                               
  260.          22  SQL*Net roundtrips to/from client                                                                                    
  261.           1  sorts (memory)                                                                                                       
  262.           0  sorts (disk)                                                                                                         
  263.         301  rows processed                                                                                                       
  264.                                                                                                                                   
  265. -->從上面的統計資訊可知,consistent gets由46下降為7,故當where子句中謂詞上存在索引時,使用union替換or更高效                       
  266. -->即使當列object_id與owner上不存在索引時,使用union仍然比or更高效(在Oracle 10g R2與Oracle 11g R2測試)                            
  267.                                                                                                                                   
  268. 4) 避免索引列上使用函式                                                                                                           
  269. -->下面是一個來自實際生產環境的例子                                                                                               
  270. -->表acc_pos_int_tbl上business_date列存在索引,由於使用了SUBSTR函式,此時索引失效,使用全表掃描                                    
  271. SELECT acc_num                                                                                                                    
  272.      , curr_cd                                                                                                                    
  273.      , DECODE( '20110728'                                                
  274.              , ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728''YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0       
  275.              ,   adj_credit_int_lv1_amt                      
  276.                + adj_credit_int_lv2_amt                            
  277.                - adj_debit_int_lv1_amt                               
  278.                - adj_debit_int_lv2_amt )                                  
  279.           AS interest                                               
  280. FROM   acc_pos_int_tbl                                          
  281. WHERE  SUBSTR( business_date, 1, 6 ) = SUBSTR( '20110728', 1, 6 ) AND business_date <= '20110728';       
  282.                                                                                                                                   
  283. -->改進的辦法             
  284. SELECT acc_num                                             
  285.      , curr_cd                                           
  286.      , DECODE( '20110728'                                  
  287.              , ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728''YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0    
  288.              ,   adj_credit_int_lv1_amt                      
  289.                + adj_credit_int_lv2_amt                         
  290.                - adj_debit_int_lv1_amt                            
  291.                - adj_debit_int_lv2_amt )                             
  292.           AS interest                                       
  293. FROM   acc_pos_int_tbl acc_pos_int_tbl                                               
  294. WHERE  business_date >= TO_CHAR( LAST_DAY( ADD_MONTHS( TO_DATE( '20110728''yyyymmdd' ), -1 ) )    
  295.                                 + 1, 'yyyymmdd' )                        
  296.        AND business_date <= '20110728';                   
  297.                                                                                                                                   
  298. -->下面的例子雖然沒有使用函式,但字串連線同樣導致索引失效                                                                       
  299. -->低效:                      
  300. SELECT account_name, amount                                                                                                       
  301. FROM   transaction                                                                                                                
  302. WHERE  account_name                                                                                                               
  303.        || account_type = 'AMEXA';                                                                                                 
  304.                                                                                                                                   
  305. -->高效:                          
  306. SELECT account_name, amount                                                                                                       
  307. FROM   transaction                                                                                                                
  308. WHERE  account_name = 'AMEX' AND account_type = 'A';                                                                              
  309.                                                                                                                                   
  310. 5) 比較不匹配的資料型別                                                                                                           
  311. -->下面的查詢中business_date列上存在索引,且為字元型,這種                                                                          
  312. -->低效:                                    
  313. SELECT *                                                                                                                          
  314. FROM   acc_pos_int_tbl                                                                                                            
  315. WHERE  business_date = 20090201;                                                                                                 
  316.                                                                                                                                   
  317. Execution Plan                                                                                                                    
  318. ----------------------------------------------------------        
  319. Plan hash value: 2335235465                      
  320.                                                  
  321. -------------------------------------------------------------------------------------                 
  322. | Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |                         
  323. -------------------------------------------------------------------------------------                        
  324. |   0 | SELECT STATEMENT  |                 | 37516 |  2857K|   106K  (1)| 00:21:17 |                      
  325. |*  1 |  TABLE ACCESS FULL| ACC_POS_INT_TBL | 37516 |  2857K|   106K  (1)| 00:21:17 |                 
  326. -------------------------------------------------------------------------------------                    
  327.                                                                                                                                   
  328. Predicate Information (identified by operation id):                          
  329. ---------------------------------------------------         
  330.      1 - filter(TO_NUMBER("BUSINESS_DATE")=20090201)    -->這裡可以看到產生了型別轉換             
  331.                                                                                                                                   
  332. -->高效:                                        
  333. SELECT *                                                                                                                          
  334. FROM   acc_pos_int_tbl                                                                                                            
  335. WHERE  business_date = '20090201'                                                                                                 
  336.                                                                                                                                   
  337. 6) 索引列上使用 NULL 值             
  338.     IS NULLIS NOT NULL會限制索引的使用,因為資料中沒有值等於NULL值,即便是NULL值也不等於NULL值.且NULL值不儲存在於索引之中    
  339. 因此應儘可能避免在索引類上使用NULL值                                                                                              
  340.                                                                                                                                   
  341. SELECT acc_num                                                                                                                    
  342.      , pl_cd                                                                                                                      
  343.      , order_qty                                                                                                                  
  344.      , trade_date                                                                                                                 
  345. FROM   trade_client_tbl                                                                                                           
  346. WHERE  input_date IS NOT NULL;                                                                                                    
  347.                                                                                                                                   
  348. Execution Plan                                              
  349. ----------------------------------------------------------                          
  350. Plan hash value: 901462645                                        
  351. --------------------------------------------------------------------------------------                   
  352. | Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |                     
  353. --------------------------------------------------------------------------------------                   
  354. |   0 | SELECT STATEMENT  |                  |     1 |    44 |    15   (0)| 00:00:01 |                
  355. |*  1 |  TABLE ACCESS FULL| TRADE_CLIENT_TBL |     1 |    44 |    15   (0)| 00:00:01 |                     
  356. --------------------------------------------------------------------------------------                   
  357.                                             
  358. alter table trade_client_tbl modify (input_date not null);            
  359.                                                                      
  360. 不推薦使用的查詢方式                                        
  361. SELECT * FROM table_name WHERE col IS NOT NULL                        
  362.                                                                   
  363. SELECT * FROM table_name WHERE col IS NULL                                 
  364.                                                                                                                                   
  365. 推薦使用的方式                        
  366. SELECT * FROM table_name WHERE col >= 0 --儘可能的使用 =, >=, <=, like 等運算子       
  367. -->Author: Robinson Cheng                
  368. -->Blog: http://blog.csdn.net/robinson_0612                 

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


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


 

相關文章