高效SQL语句必杀技

  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. scott@CNMMBO> SELECT deptno, AVG( sal )                                                                      
  175.   2  FROM   emp                                                                                       
  176.   3  GROUP BY deptno                                                                                  
  177.   4  HAVING deptno= 20;                                                                                   
  178.                                                                                               
  179. Statistics                      
  180. ----------------------------------------------------------     
  181.           0  recursive calls                                                                           
  182.           0  db block gets                                                                 
  183.           7  consistent gets                                                               
  184.           0  physical reads                                                                
  185.           0  redo size                                                                       
  186.         583  bytes sent via SQL*Net to client                                                  
  187.         492  bytes received via SQL*Net from client                                                 
  188.           2  SQL*Net roundtrips to/from client                                                       
  189.           0  sorts (memory)                                                                      
  190.           0  sorts (disk)                                                                     
  191.           1  rows processed                                                                
  192. -->高效:                                                                
  193. SELECT deptno, AVG( sal )          
  194. FROM   emp                                                                                      
  195. WHERE  deptno = 20                                                                                  
  196. GROUP BY deptno;                                                                   
  197.                                                                                       
  198. scott@CNMMBO> SELECT deptno, AVG( sal )                  
  199.   2  FROM   emp                                                                             
  200.   3  WHERE  deptno = 20                                                                   
  201.   4  GROUP BY deptno;                                                                   
  202.                                                                                          
  203. Statistics                                          
  204. ----------------------------------------------------------        
  205.           0  recursive calls                                                              
  206.           0  db block gets                                                               
  207.           2  consistent gets                                                               
  208.           0  physical reads                                                            
  209.           0  redo size                                                                
  210.         583  bytes sent via SQL*Net to client                                         
  211.         492  bytes received via SQL*Net from client                                      
  212.           2  SQL*Net roundtrips to/from client                                               
  213.           0  sorts (memory)                                                                      
  214.           0  sorts (disk)                                                                          
  215.           1  rows processed                                                                     
  216.                                                                                         
  217. 11) 最小化表查询次数                                                                                                             
  218. -->在含有子查询的SQL语句中,要特别注意减少对表的查询                                                                             
  219. -->低效:                                                           
  220. SELECT *                                                                                      
  221. FROM   employees                                                                                   
  222. WHERE  department_id = (SELECT department_id                                                       
  223.                         FROM   departments                                                     
  224.                         WHERE  department_name = 'Marketing')                                   
  225.        AND manager_id = (SELECT manager_id                                                       
  226.                          FROM   departments                                                     
  227.                          WHERE  department_name = 'Marketing');                                  
  228. -->高效:                                                                
  229. SELECT *                                                                                    
  230. FROM   employees                                                                             
  231. WHERE  ( department_id, manager_id ) = (SELECT department_id, manager_id                                  
  232.                                         FROM   departments                                                   
  233.                                         WHERE  department_name = 'Marketing')                   
  234.                                                                                     
  235. -->类似更新多列的情形                
  236. -->低效:                     
  237. UPDATE employees                                                                                  
  238. SET    job_id = ( SELECT MAX( job_id ) FROM jobs ), salary = ( SELECT AVG( min_salary ) FROM jobs )           
  239. WHERE  department_id = 10;                                                                    
  240.                                                                                         
  241. -->高效:                  
  242. UPDATE employees           
  243. SET    ( job_id, salary ) = ( SELECT MAX( job_id ), AVG( min_salary ) FROM jobs )       
  244. WHERE  department_id = 10;                                                              
  245.                                                                           
  246. 12) 使用表别名                                                                        
  247. -->在多表查询时,为所返回列使用表别名作为前缀以减少解析时间以及那些相同列歧义引起的语法错误                                      
  248.                                                                                  
  249. 13) 用EXISTS替代IN                                                                                   
  250.     在一些基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOT EXISTS)通常        
  251. 将提高查询的效率.                                                                                                               
  252. -->低效:                                 
  253. SELECT *                                                              
  254. FROM   emp                                                       
  255. WHERE  sal > 1000                                                      
  256.        AND deptno IN (SELECT deptno                                         
  257.                       FROM   dept                                          
  258.                       WHERE  loc = 'DALLAS')                                     
  259.                                                                    
  260. -->高效:                                                                               
  261. SELECT *                                                                 
  262. FROM   emp                                                             
  263. WHERE  empno > 1000                                          
  264.        AND EXISTS                                                       
  265.               (SELECT 1                                       
  266.                FROM   dept                                     
  267.                WHERE  deptno = emp.deptno AND loc = 'DALLAS')                           
  268.                      
  269. 14) 用NOT EXISTS替代NOT IN     
  270.     在子查询中,NOT IN子句引起一个内部的排序与合并.因此,无论何时NOT IN子句都是最低效的,因为它对子查询中的表执行了一个全表        
  271. 遍历.为避免该情形,应当将其改写成外部连接(OUTTER JOIN)或适用NOT EXISTS                                    
  272. -->低效:                                                                      
  273. SELECT *                                                                                        
  274. FROM   emp                                                                        
  275. WHERE  deptno NOT IN (SELECT deptno                                          
  276.                        FROM   dept                                            
  277.                        WHERE  loc = 'DALLAS');                                             
  278.                                     
  279. -->高效:                                             
  280. SELECT e.*                                                                                      
  281. FROM   emp e                                                                                      
  282. WHERE  NOT EXISTS                                                                                     
  283.           (SELECT 1                                                                                    
  284.            FROM   dept                                                                                
  285.            WHERE  deptno = e.deptno AND loc = 'DALLAS');                                       
  286.                                                                       
  287. -->最高效(尽管下面的查询最高效,并不推荐使用,因为列loc使用了不等运算,当表dept数据量较大,且loc列存在索引的话,则此时索引失效)   
  288. SELECT e.*                                                                                     
  289. FROM   emp e LEFT JOIN dept d ON e.deptno = d.deptno                                                 
  290. WHERE  d.loc <> 'DALLAS'                                                                       
  291.                                                             
  292. 15) 使用表连接替换EXISTS                                                
  293. 一般情况下,使用表连接比EXISTS更高效                                                             
  294. -->低效:                                                    
  295. SELECT *                                                                                                       
  296. FROM   employees e                                                                                         
  297. WHERE  EXISTS                                                                                                      
  298.           (SELECT 1                                                                       
  299.            FROM   departments                                                              
  300.            WHERE  department_id = e.department_id AND department_name = 'IT');                                 
  301.                                                                                     
  302. -->高效:                  
  303. SELECT *              -->经测试此写法SQLplus下比上面的写法多一次逻辑读,而在Toad下两者结果一致               
  304. FROM   employees e INNER JOIN departments d ON d.department_id = e.department_id              
  305. WHERE  d.department_name = 'IT';                                                        
  306.                                                               
  307. 16) 用EXISTS替换DISTINCT          
  308. 对于一对多关系表信息查询时(如部门表和雇员表),应避免在select 子句中使用distinct,而使用exists来替换           
  309.                                                      
  310. -->低效:                                                                
  311. SELECT DISTINCT e.department_id, d.department_name                                              
  312. FROM   departments d INNER JOIN employees e ON d.department_id = e.department_id;                   
  313.                                   
  314. -->高效:                                                            
  315. SELECT d.department_id,department_name                                                         
  316. from departments d                                                                       
  317. WHERE  EXISTS                                                                              
  318.           (SELECT 1                                                                      
  319.            FROM   employees e                                                       
  320.            WHERE  d.department_id=e.department_id);                                              
  321.                                                                       
  322. EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果                                
  323. -->经测试此写法SQLplus下比上面的写法多一次逻辑读,而在Toad下两者结果一致                                  
  324.                                                               
  325. 17) 使用 UNION ALL 替换 UNION(如果有可能的话)                                                           
  326. 当SQL语句需要UNION两个查询结果集时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序。       
  327. 如果用UNION ALL替代UNION, 这样排序就不是必要了。 效率就会因此得到提高。                                                         
  328.                                                                 
  329. 注意:                       
  330. UNION ALL会输出所有的结果集,而UNION则过滤掉重复记录并对其进行排序.因此在使用时应考虑业务逻辑是否允许当前的结果集存在重复现象   
  331.                                                                          
  332. 寻找低效的SQL语句                                                            
  333. -->下面的语句主要适用于从视图v$sqlarea中获得当前运行下且耗用buffer_gets较多的SQL语句                   
  334. SELECT executions                                                                     
  335.      , disk_reads                                                                    
  336.      , buffer_gets                                                                  
  337.      , ROUND( ( buffer_gets         
  338.                - disk_reads )       
  339.              / buffer_gets, 2 )      
  340.           hit_ratio                                      
  341.      , ROUND( disk_reads / executions, 2 ) reads_per_run                   
  342.      , sql_text                                       
  343. FROM   v$sqlarea                                                               
  344. WHERE      executions > 0                                                   
  345.        AND buffer_gets > 0                                               
  346.        AND ( buffer_gets                                                    
  347.             - disk_reads )                                                  
  348.            / buffer_gets < 0.80                                                        
  349. ORDER BY 4 DESC;                                                   
  350.                               
  351. 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连接等)


©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页