高效SQL语句必杀技

10 篇文章 0 订阅

转载于:http://blog.csdn.net/robinson_0612/article/details/7406672

 

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个以上的表连接查询, 那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。
  6. 下面的例子使用最常见的scott或hr模式下的表进行演示
  7. 表 EMP 有14条记录
  8. 表 DEPT 有4条记录
  9. SELECT /*+rule */ COUNT( * )FROM emp, dept; --高效的写法
  10. scott@CNMMBO> set autotrace traceonly stat;
  11. scott@CNMMBO> SELECT /*+rule */ COUNT( * )FROM emp, dept;
  12. Elapsed: 00:00:00.14
  13. Statistics
  14. ----------------------------------------------------------
  15. 1 recursive calls
  16. 0 db block gets
  17. 35 consistent gets
  18. 0 physical reads
  19. 0 redo size
  20. 515 bytes sent via SQL*Net to client
  21. 492 bytes received via SQL*Net from client
  22. 2 SQL*Net roundtrips to/from client
  23. 0 sorts (memory)
  24. 0 sorts (disk)
  25. 1 rows processed
  26. SELECT /*+rule */ COUNT( * )FROM dept, emp; --低效的写法
  27. scott@CNMMBO> SELECT /*+rule */ COUNT( * )FROM dept, emp;
  28. Elapsed: 00:00:00.02
  29. Statistics
  30. ----------------------------------------------------------
  31. 1 recursive calls
  32. 0 db block gets
  33. 105 consistent gets
  34. 0 physical reads
  35. 0 redo size
  36. 515 bytes sent via SQL*Net to client
  37. 492 bytes received via SQL*Netfrom client
  38. 2 SQL*Net roundtrips to/from client
  39. 0 sorts (memory)
  40. 0 sorts (disk)
  41. 1 rows processed
  42. 2) select 查询中避免使用'*'
  43. 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用'*' 是一个方便的方法.不幸的是,这是一个非常低效的方法.实际
  44. 上,ORACLE在解析的过程中, 会将 '*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
  45. 注:本文中的例子出于简化演示而使用了select * ,生产环境应避免使用.
  46. 3) 减少访问数据库的次数
  47. 每当执行一条SQL语句,Oracle 需要完成大量的内部操作,象解析SQL语句,估算索引的利用率,绑定变量, 读数据块等等.由此可
  48. 见,减少访问数据库的次数,实际上是降低了数据库系统开销
  49. -->下面通过3种方式来获得雇员编号为7788与7902的相关信息
  50. -->方式 1 (最低效):
  51. select ename,job,salfrom emp where empno=7788;
  52. select ename,job,salfrom emp where empno=7902;
  53. -->方式 2 (次低效):
  54. -->下面使用了参数游标来完成,每传递一次参数则需要对表emp访问一次,增加了I/O
  55. DECLARE
  56. CURSOR C1(E_NO NUMBER)IS
  57. SELECT ename, job, sal
  58. FROM emp
  59. WHERE empno = E_NO;
  60. BEGIN
  61. OPEN C1 (7788);
  62. FETCH C1INTO …, …, …;
  63. ..
  64. OPEN C1 (7902);
  65. FETCH C1INTO …, …, …;
  66. CLOSE C1;
  67. END;
  68. -->方式 3 (最高效)
  69. SELECT a.ename
  70. , a.job
  71. , a.sal
  72. , b.ename
  73. , b.job
  74. , b.sal
  75. FROM emp a, emp b
  76. WHERE a.empno = 7788AND b.empno = 7902;
  77. 注意:在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200.
  78. 4) 使用DECODE函数来减少处理时间
  79. -->使用decode函数可以避免重复扫描相同的行或重复连接相同的表
  80. selectcount(*),sum(sal)from emp where deptno=20and ename like'SMITH%';
  81. selectcount(*),sum(sal)from emp where deptno=30and ename like'SMITH%';
  82. -->通过使用decode函数一次扫描即可完成所有满足条件记录的处理
  83. SELECTCOUNT( DECODE( deptno, 20, 'x',NULL ) ) d20_count
  84. , COUNT( DECODE( deptno, 30,'x', NULL ) ) d30_count
  85. , SUM( DECODE( deptno, 20, sal,NULL ) ) d20_sal
  86. , SUM( DECODE( deptno, 30, sal,NULL ) ) d30_sal
  87. FROM emp
  88. WHERE enameLIKE 'SMITH%';
  89. 类似的,DECODE函数也可以运用于GROUPBYORDERBY子句中。
  90. 5) 整合简单,无关联的数据库访问
  91. -->如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中以提高性能(即使它们之间没有关系)
  92. -->整合前
  93. SELECTname
  94. FROM emp
  95. WHERE empno = 1234;
  96. SELECTname
  97. FROM dept
  98. WHERE deptno = 10;
  99. SELECTname
  100. FROM cat
  101. WHERE cat_type ='RD';
  102. -->整合后
  103. SELECT e.name, d.name, c.name
  104. FROM cat c
  105. , dpt d
  106. , emp e
  107. , dual x
  108. WHERE NVL('X', x.dummy ) = NVL( 'X', e.ROWID(+) )
  109. AND NVL('X', x.dummy ) = NVL( 'X', d.ROWID(+) )
  110. AND NVL('X', x.dummy ) = NVL( 'X', c.ROWID(+) )
  111. AND e.emp_no(+) = 1234
  112. AND d.dept_no(+) = 10
  113. AND c.cat_type(+) ='RD';
  114. -->从上面的SQL语句可以看出,尽管三条语句被整合为一条,性能得以提高,然可读性差,此时应权衡性能与代价
  115. 6) 删除重复记录
  116. -->通过使用rowid来作为过滤条件,性能高效
  117. DELETEFROM emp e
  118. WHERE e.ROWID > (SELECTMIN( x.ROWID )
  119. FROM emp x
  120. WHERE x.empno = e.empno);
  121. 7) 使用truncate 代替delete
  122. -->通常情况下,任意记录的删除需要在回滚段构造删除前镜像以实现回滚(rollback).对于未提交的数据在执行rollback之后,Oracle会生成
  123. -->等价SQL语句去恢复记录(如delete,则生成对应的insert语句;如insert则生成对应的delete;如update,则是同时生成delete和insert
  124. -->使用truncate命令则是执行DDL命令,不产生任何回滚信息,直接格式化并释放高水位线.故该语句性能高效.由于不能rollback,因此慎用.
  125. 8) 尽量多使用COMMIT(COMMIT应确保事务的完整性)
  126. -->只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少
  127. -->COMMIT所释放的资源:
  128. -->1.回滚段上用于恢复数据的信息
  129. -->2.释放语句处理期间所持有的锁
  130. -->3.释放redo log buffer占用的空间(commit将redo log buffer中的entries 写入到联机重做日志文件)
  131. -->4.ORACLE为管理上述3种资源中的内部开销
  132. 9) 计算记录条数
  133. -->一般的情况下,count(*)比count(1)稍快.如果可以通过索引检索,对索引列的计数是最快的,因为直接扫描索引即可,例如COUNT(EMPNO)
  134. -->实际情况是经测试上述三种情况并无明显差异.
  135. 10) 用Where子句替换HAVING子句
  136. -->尽可能的避免having子句,因为HAVING 子句是对检索出所有记录之后再对结果集进行过滤。这个处理需要排序,总计等操作
  137. -->通过WHERE子句则在分组之前即可过滤不必要的记录数目,从而减少聚合的开销
  138. -->低效:
  139. SELECT deptno,AVG( sal )
  140. FROM emp
  141. GROUPBY deptno
  142. HAVING deptno = 20;
  143. scott@CNMMBO> SELECT deptno,AVG( sal )
  144. 2 FROM emp
  145. 3 GROUPBY deptno
  146. 4 HAVING deptno= 20;
  147. Statistics
  148. ----------------------------------------------------------
  149. 0 recursive calls
  150. 0 db block gets
  151. 7 consistent gets
  152. 0 physical reads
  153. 0 redo size
  154. 583 bytes sent via SQL*Net to client
  155. 492 bytes received via SQL*Netfrom client
  156. 2 SQL*Net roundtrips to/from client
  157. 0 sorts (memory)
  158. 0 sorts (disk)
  159. 1 rows processed
  160. -->高效:
  161. SELECT deptno,AVG( sal )
  162. FROM emp
  163. WHERE deptno = 20
  164. GROUPBY deptno;
  165. scott@CNMMBO> SELECT deptno,AVG( sal )
  166. 2 FROM emp
  167. 3 WHERE deptno = 20
  168. 4 GROUPBY deptno;
  169. Statistics
  170. ----------------------------------------------------------
  171. 0 recursive calls
  172. 0 db block gets
  173. 2 consistent gets
  174. 0 physical reads
  175. 0 redo size
  176. 583 bytes sent via SQL*Net to client
  177. 492 bytes received via SQL*Netfrom client
  178. 2 SQL*Net roundtrips to/from client
  179. 0 sorts (memory)
  180. 0 sorts (disk)
  181. 1 rows processed
  182. 11) 最小化表查询次数
  183. -->在含有子查询的SQL语句中,要特别注意减少对表的查询
  184. -->低效:
  185. SELECT *
  186. FROM employees
  187. WHERE department_id = (SELECT department_id
  188. FROM departments
  189. WHERE department_name ='Marketing')
  190. AND manager_id = (SELECT manager_id
  191. FROM departments
  192. WHERE department_name ='Marketing');
  193. -->高效:
  194. SELECT *
  195. FROM employees
  196. WHERE ( department_id, manager_id ) = (SELECT department_id, manager_id
  197. FROM departments
  198. WHERE department_name ='Marketing')
  199. -->类似更新多列的情形
  200. -->低效:
  201. UPDATE employees
  202. SET job_id = (SELECT MAX( job_id )FROM jobs ), salary = ( SELECTAVG( min_salary ) FROM jobs )
  203. WHERE department_id = 10;
  204. -->高效:
  205. UPDATE employees
  206. SET ( job_id, salary ) = (SELECT MAX( job_id ),AVG( min_salary ) FROM jobs )
  207. WHERE department_id = 10;
  208. 12) 使用表别名
  209. -->在多表查询时,为所返回列使用表别名作为前缀以减少解析时间以及那些相同列歧义引起的语法错误
  210. 13) 用EXISTS替代IN
  211. 在一些基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOT EXISTS)通常
  212. 将提高查询的效率.
  213. -->低效:
  214. SELECT *
  215. FROM emp
  216. WHERE sal > 1000
  217. AND deptnoIN (SELECT deptno
  218. FROM dept
  219. WHERE loc ='DALLAS')
  220. -->高效:
  221. SELECT *
  222. FROM emp
  223. WHERE empno > 1000
  224. AND EXISTS
  225. (SELECT 1
  226. FROM dept
  227. WHERE deptno = emp.deptnoAND loc = 'DALLAS')
  228. 14) 用NOT EXISTS替代NOTIN
  229. 在子查询中,NOTIN子句引起一个内部的排序与合并.因此,无论何时NOTIN子句都是最低效的,因为它对子查询中的表执行了一个全表
  230. 遍历.为避免该情形,应当将其改写成外部连接(OUTTER JOIN)或适用NOT EXISTS
  231. -->低效:
  232. SELECT *
  233. FROM emp
  234. WHERE deptnoNOT IN (SELECT deptno
  235. FROM dept
  236. WHERE loc ='DALLAS');
  237. -->高效:
  238. SELECT e.*
  239. FROM emp e
  240. WHERENOT EXISTS
  241. (SELECT 1
  242. FROM dept
  243. WHERE deptno = e.deptnoAND loc = 'DALLAS');
  244. -->最高效(尽管下面的查询最高效,并不推荐使用,因为列loc使用了不等运算,当表dept数据量较大,且loc列存在索引的话,则此时索引失效)
  245. SELECT e.*
  246. FROM emp eLEFT JOIN dept dON e.deptno = d.deptno
  247. WHERE d.loc <>'DALLAS'
  248. 15) 使用表连接替换EXISTS
  249. 一般情况下,使用表连接比EXISTS更高效
  250. -->低效:
  251. SELECT *
  252. FROM employees e
  253. WHERE EXISTS
  254. (SELECT 1
  255. FROM departments
  256. WHERE department_id = e.department_idAND department_name = 'IT');
  257. -->高效:
  258. SELECT *-->经测试此写法SQLplus下比上面的写法多一次逻辑读,而在Toad下两者结果一致
  259. FROM employees eINNER JOIN departments dON d.department_id = e.department_id
  260. WHERE d.department_name ='IT';
  261. 16) 用EXISTS替换DISTINCT
  262. 对于一对多关系表信息查询时(如部门表和雇员表),应避免在select 子句中使用distinct,而使用exists来替换
  263. -->低效:
  264. SELECTDISTINCT e.department_id, department_name
  265. FROM departments dINNER JOIN employees eON d.department_id = e.department_id;
  266. -->高效:
  267. SELECT d.department_id,department_name
  268. from departments d
  269. WHERE EXISTS
  270. (SELECT 1
  271. FROM employees e
  272. WHERE d.department_id=e.department_id);
  273. EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果
  274. -->经测试此写法SQLplus下比上面的写法多一次逻辑读,而在Toad下两者结果一致
  275. 17) 使用 UNIONALL 替换 UNION(如果有可能的话)
  276. 当SQL语句需要UNION两个查询结果集时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序。
  277. 如果用UNIONALL替代UNION, 这样排序就不是必要了。 效率就会因此得到提高。
  278. 注意:
  279. UNIONALL会输出所有的结果集,而UNION则过滤掉重复记录并对其进行排序.因此在使用时应考虑业务逻辑是否允许当前的结果集存在重复现象
  280. 寻找低效的SQL语句
  281. -->下面的语句主要适用于从视图v$sqlarea中获得当前运行下且耗用buffer_gets较多的SQL语句
  282. SELECT executions
  283. , disk_reads
  284. , buffer_gets
  285. , ROUND( ( buffer_gets
  286. - disk_reads )
  287. / buffer_gets, 2 )
  288. hit_ratio
  289. , ROUND( disk_reads / executions, 2 ) reads_per_run
  290. , sql_text
  291. FROM v$sqlarea
  292. WHERE executions > 0
  293. AND buffer_gets > 0
  294. AND ( buffer_gets
  295. - disk_reads )
  296. / buffer_gets < 0.80
  297. ORDERBY 4 DESC;
  298. 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. SELECT employee_id, first_name
  5. FROM employees
  6. WHERE employee_id + 10 > 150;-->索引列上使用了计算,因此索引失效,走全表扫描方式
  7. -->高效:
  8. SELECT employee_id, first_name
  9. FROM employees
  10. WHERE employee_id > 160;-->走索引范围扫描方式
  11. 例外情形
  12. 上述规则不适用于SQL中的MINMAX函数
  13. hr@CNMMBO> SELECTMAX( employee_id ) max_id
  14. 2 FROM employees
  15. 3 WHERE employee_id
  16. 4 + 10 > 150;
  17. 1 row selected.
  18. Execution Plan
  19. ----------------------------------------------------------
  20. Plan hash value: 1481384439
  21. ---------------------------------------------------------------------------------------------
  22. | Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
  23. ---------------------------------------------------------------------------------------------
  24. | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
  25. | 1 | SORT AGGREGATE | | 1 | 4 | | |
  26. | 2 | FIRST ROW | | 5 | 20 | 1 (0)| 00:00:01 |
  27. |* 3 | INDEXFULL SCAN (MIN/MAX)| EMP_EMP_ID_PK | 5 | 20 | 1 (0)| 00:00:01 |
  28. ---------------------------------------------------------------------------------------------
  29. 2) 避免在索引列上使用NOT运算或不等于运算(<>,!=)
  30. 通常,我们要避免在索引列上使用NOT或<>,两者会产生在和在索引列上使用函数相同的影响。 当ORACLE遇到NOT或不等运算时,他就会停止
  31. 使用索引转而执行全表扫描。
  32. -->低效:
  33. SELECT *
  34. FROM emp
  35. WHERENOT ( deptno = 20 ); -->实际上NOT ( deptno = 20 )等同于deptno <> 20,即deptno <>同样会限制索引
  36. -->高效:
  37. SELECT *
  38. FROM emp
  39. WHERE deptno > 20OR deptno < 20;
  40. -->尽管此方式可以替换且实现上述结果,但依然走全表扫描,如果是单纯的 > 或 < 运算,则此时为索引范围扫描
  41. 需要注意的是,在某些时候, ORACLE优化器会自动将NOT转化成相对应的关系操作符
  42. 其次如果是下列运算符进行NOT运算,依然有可能选择走索引, 仅仅除了NOT = 之外,因为NOT = 等价于 <>
  43. NOT >”to <=
  44. NOT >=”to <
  45. NOT <”to >=
  46. NOT <=”to >
  47. 来看一个实际的例子
  48. hr@CNMMBO> SELECT *
  49. 2 FROM employees
  50. 3 wherenot employee_id<100; -->索引列上使用了not,但是该查询返回了所有的记录,即107条,因此此时选择走全表扫描
  51. 107 rows selected.
  52. Execution Plan
  53. ----------------------------------------------------------
  54. Plan hash value: 1445457117
  55. -------------------------------------------------------------------------------
  56. | Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
  57. -------------------------------------------------------------------------------
  58. | 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 |
  59. |* 1 | TABLE ACCESSFULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |-->执行计划中使用了走全表扫描方式
  60. -------------------------------------------------------------------------------
  61. Predicate Information (identified by operation id):
  62. ---------------------------------------------------
  63. 1 - filter("EMPLOYEE_ID">=100)-->查看这里的谓词信息被自动转换为 >= 运算符
  64. hr@CNMMBO> SELECT *
  65. 2 FROM employees
  66. 3 wherenot employee_id<140; -->此例与上面的语句相同,仅仅是查询范围不同返回67条记录,而此时选择了索引范围扫描
  67. 67 rows selected.
  68. Execution Plan
  69. ----------------------------------------------------------
  70. Plan hash value: 603312277
  71. ---------------------------------------------------------------------------------------------
  72. | Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
  73. ---------------------------------------------------------------------------------------------
  74. | 0 | SELECT STATEMENT | | 68 | 4624 | 3 (0)| 00:00:01 |
  75. | 1 | TABLE ACCESSBY INDEX ROWID| EMPLOYEES | 68 | 4624 | 3 (0)| 00:00:01 |
  76. |* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 68 | | 1 (0)| 00:00:01 |-->索引范围扫描方式
  77. ---------------------------------------------------------------------------------------------
  78. Predicate Information (identified by operation id):
  79. ---------------------------------------------------
  80. 2 - access("EMPLOYEE_ID">=140)
  81. 3) 用UNION 替换OR(适用于索引列)
  82. 通常情况下,使用UNION 替换WHERE子句中的OR将会起到较好的效果.基于索引列使用OR使得优化器倾向于使用全表扫描,而不是扫描索引.
  83. 注意,以上规则仅适用于多个索引列有效。 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。
  84. -->低效:
  85. SELECT deptno, dname
  86. FROM dept
  87. WHERE loc ='DALLAS' OR deptno = 20;
  88. -->高效:
  89. SELECT deptno, dname
  90. FROM dept
  91. WHERE loc ='DALLAS'
  92. UNION
  93. SELECT deptno, dname
  94. FROM dept
  95. WHERE deptno = 30
  96. -->经测试,由于数据量较少,此时where子句中的谓词上都存在索引列时,两者性能相当.
  97. -->假定where子句中存在两列
  98. scott@CNMMBO> createtable t6 asselect object_id,owner,object_name from dba_objects where owner='SYS'and rownum<1001;
  99. scott@CNMMBO> insertinto t6 select object_id,owner,object_namefrom dba_objects where owner='SCOTT'and rownum<6;
  100. scott@CNMMBO> createindex i_t6_object_id on t6(object_id);
  101. scott@CNMMBO> createindex i_t6_owner on t6(owner);
  102. scott@CNMMBO> insertinto t6 select object_id,owner,object_namefrom dba_objects where owner='SYSTEM'and rownum<=300;
  103. scott@CNMMBO> commit;
  104. scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T6',cascade=>true);
  105. scott@CNMMBO> select owner,count(*)from t6 groupby owner;
  106. OWNER COUNT(*)
  107. -------------------- ----------
  108. SCOTT 5
  109. SYSTEM 300
  110. SYS 1000
  111. scott@CNMMBO> select *from t6 where owner='SCOTT'and rownum<2;
  112. OBJECT_ID OWNER OBJECT_NAME
  113. ---------- -------------------- --------------------
  114. 69450 SCOTT T_TEST
  115. scott@CNMMBO> select *from t6 where object_id=69450or owner='SYSTEM';
  116. 301 rows selected.
  117. Execution Plan
  118. ----------------------------------------------------------
  119. Plan hash value: 238853296
  120. -----------------------------------------------------------------------------------------------
  121. | Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
  122. -----------------------------------------------------------------------------------------------
  123. | 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 |
  124. | 1 | CONCATENATION | | | | | |
  125. | 2 | TABLE ACCESSBY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
  126. |* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
  127. |* 4 | TABLE ACCESSBY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 |
  128. |* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
  129. -----------------------------------------------------------------------------------------------
  130. Predicate Information (identifiedby operation id):
  131. ---------------------------------------------------
  132. 3 - access("OBJECT_ID"=69450)
  133. 4 - filter(LNNVL("OBJECT_ID"=69450))
  134. 5 - access("OWNER"='SYSTEM')
  135. Statistics
  136. ----------------------------------------------------------
  137. 0 recursive calls
  138. 0 db block gets
  139. 46 consistent gets
  140. 0 physical reads
  141. 0 redo size
  142. 11383 bytes sent via SQL*Net to client
  143. 712 bytes received via SQL*Netfrom client
  144. 22 SQL*Net roundtrips to/from client
  145. 0 sorts (memory)
  146. 0 sorts (disk)
  147. 301 rows processed
  148. scott@CNMMBO> select *from t6 where owner='SYSTEM'or object_id=69450;
  149. 301 rows selected.
  150. Execution Plan
  151. ----------------------------------------------------------
  152. Plan hash value: 238853296
  153. -----------------------------------------------------------------------------------------------
  154. | Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
  155. -----------------------------------------------------------------------------------------------
  156. | 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 |
  157. | 1 | CONCATENATION | | | | | |
  158. | 2 | TABLE ACCESSBY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
  159. |* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
  160. |* 4 | TABLE ACCESSBY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 |
  161. |* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
  162. -----------------------------------------------------------------------------------------------
  163. Predicate Information (identifiedby operation id):
  164. ---------------------------------------------------
  165. 3 - access("OBJECT_ID"=69450)
  166. 4 - filter(LNNVL("OBJECT_ID"=69450))
  167. 5 - access("OWNER"='SYSTEM')
  168. Statistics
  169. ----------------------------------------------------------
  170. 1 recursive calls
  171. 0 db block gets
  172. 46 consistent gets
  173. 0 physical reads
  174. 0 redo size
  175. 11383 bytes sent via SQL*Net to client
  176. 712 bytes received via SQL*Netfrom client
  177. 22 SQL*Net roundtrips to/from client
  178. 0 sorts (memory)
  179. 0 sorts (disk)
  180. 301 rows processed
  181. scott@CNMMBO> select *from t6
  182. 2 where object_id=69450
  183. 3 union
  184. 4 select *from t6
  185. 5 where owner='SYSTEM';
  186. 301 rows selected.
  187. Execution Plan
  188. ----------------------------------------------------------
  189. Plan hash value: 370530636
  190. ------------------------------------------------------------------------------------------------
  191. | Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
  192. ------------------------------------------------------------------------------------------------
  193. | 0 | SELECT STATEMENT | | 301 | 7224 | 7 (72)| 00:00:01 |
  194. | 1 | SORT UNIQUE | | 301 | 7224 | 7 (72)| 00:00:01 |
  195. | 2 | UNION-ALL | | | | | |
  196. | 3 | TABLE ACCESSBY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
  197. |* 4 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
  198. | 5 | TABLE ACCESSBY INDEX ROWID| T6 | 300 | 7200 | 3 (0)| 00:00:01 |
  199. |* 6 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
  200. ------------------------------------------------------------------------------------------------
  201. Predicate Information (identified by operation id):
  202. ---------------------------------------------------
  203. 4 - access("OBJECT_ID"=69450)
  204. 6 - access("OWNER"='SYSTEM')
  205. Statistics
  206. ----------------------------------------------------------
  207. 1 recursive calls
  208. 0 db block gets
  209. 7 consistent gets
  210. 0 physical reads
  211. 0 redo size
  212. 11383 bytes sent via SQL*Net to client
  213. 712 bytes received via SQL*Netfrom client
  214. 22 SQL*Net roundtrips to/from client
  215. 1 sorts (memory)
  216. 0 sorts (disk)
  217. 301 rows processed
  218. -->从上面的统计信息可知,consistent gets由46下降为7,故当where子句中谓词上存在索引时,使用union替换or更高效
  219. -->即使当列object_id与owner上不存在索引时,使用union仍然比or更高效(在Oracle 10g R2与Oracle 11g R2测试)
  220. 4) 避免索引列上使用函数
  221. -->下面是一个来自实际生产环境的例子
  222. -->表acc_pos_int_tbl上business_date列存在索引,由于使用了SUBSTR函数,此时索引失效,使用全表扫描
  223. SELECT acc_num
  224. , curr_cd
  225. , DECODE( '20110728'
  226. , ( SELECT TO_CHAR( LAST_DAY( TO_DATE('20110728', 'YYYYMMDD' ) ),'YYYYMMDD' ) FROM dual ), 0
  227. , adj_credit_int_lv1_amt
  228. + adj_credit_int_lv2_amt
  229. - adj_debit_int_lv1_amt
  230. - adj_debit_int_lv2_amt )
  231. AS interest
  232. FROM acc_pos_int_tbl
  233. WHERE SUBSTR( business_date, 1, 6 ) = SUBSTR('20110728', 1, 6 ) AND business_date <='20110728';
  234. -->改进的办法
  235. SELECT acc_num
  236. , curr_cd
  237. , DECODE( '20110728'
  238. , ( SELECT TO_CHAR( LAST_DAY( TO_DATE('20110728', 'YYYYMMDD' ) ),'YYYYMMDD' ) FROM dual ), 0
  239. , adj_credit_int_lv1_amt
  240. + adj_credit_int_lv2_amt
  241. - adj_debit_int_lv1_amt
  242. - adj_debit_int_lv2_amt )
  243. AS interest
  244. FROM acc_pos_int_tbl acc_pos_int_tbl
  245. WHERE business_date >= TO_CHAR( LAST_DAY( ADD_MONTHS( TO_DATE('20110728', 'yyyymmdd' ), -1 ) )
  246. + 1, 'yyyymmdd' )
  247. AND business_date <='20110728';
  248. -->下面的例子虽然没有使用函数,但字符串连接同样导致索引失效
  249. -->低效:
  250. SELECT account_name, amount
  251. FROMtransaction
  252. WHERE account_name
  253. || account_type = 'AMEXA';
  254. -->高效:
  255. SELECT account_name, amount
  256. FROMtransaction
  257. WHERE account_name ='AMEX' AND account_type ='A';
  258. 5) 比较不匹配的数据类型
  259. -->下面的查询中business_date列上存在索引,且为字符型,这种
  260. -->低效:
  261. SELECT *
  262. FROM acc_pos_int_tbl
  263. WHERE business_date = 20090201;
  264. Execution Plan
  265. ----------------------------------------------------------
  266. Plan hash value: 2335235465
  267. -------------------------------------------------------------------------------------
  268. | Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
  269. -------------------------------------------------------------------------------------
  270. | 0 | SELECT STATEMENT | | 37516 | 2857K| 106K (1)| 00:21:17 |
  271. |* 1 | TABLE ACCESSFULL| ACC_POS_INT_TBL | 37516 | 2857K| 106K (1)| 00:21:17 |
  272. -------------------------------------------------------------------------------------
  273. Predicate Information (identified by operation id):
  274. ---------------------------------------------------
  275. 1 - filter(TO_NUMBER("BUSINESS_DATE")=20090201)-->这里可以看到产生了类型转换
  276. -->高效:
  277. SELECT *
  278. FROM acc_pos_int_tbl
  279. WHERE business_date ='20090201'
  280. 6) 索引列上使用 NULL
  281. ISNULLISNOT NULL会限制索引的使用,因为数据中没有值等于NULL值,即便是NULL值也不等于NULL值.且NULL值不存储在于索引之中
  282. 因此应尽可能避免在索引类上使用NULL
  283. SELECT acc_num
  284. , pl_cd
  285. , order_qty
  286. , trade_date
  287. FROM trade_client_tbl
  288. WHERE input_dateIS NOTNULL;
  289. Execution Plan
  290. ----------------------------------------------------------
  291. Plan hash value: 901462645
  292. --------------------------------------------------------------------------------------
  293. | Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
  294. --------------------------------------------------------------------------------------
  295. | 0 | SELECT STATEMENT | | 1 | 44 | 15 (0)| 00:00:01 |
  296. |* 1 | TABLE ACCESSFULL| TRADE_CLIENT_TBL | 1 | 44 | 15 (0)| 00:00:01 |
  297. --------------------------------------------------------------------------------------
  298. altertable trade_client_tbl modify (input_datenot null);
  299. 不推荐使用的查询方式
  300. SELECT *FROM table_name WHERE colIS NOTNULL
  301. SELECT *FROM table_name WHERE colIS NULL
  302. 推荐使用的方式
  303. SELECT *FROM table_name WHERE col >= 0--尽可能的使用 =, >=, <=, like 等运算符
  304. -->Author: Robinson Cheng
  305. -->Blog: http://blog.csdn.net/robinson_0612

三、总结
1、尽可能最小化基表数据以及中间结果集(通过过滤条件避免后续产生不必要的计算与聚合)
2、为where子句中的谓词信息提供最佳的访问路径(rowid访问,索引访问)
3、使用合理的SQL写法来避免过多的Oracle内部开销以提高性能
4、合理的使用提示以提高表之间的连接来提高连接效率(如避免迪卡尔集,将不合理的嵌套连接改为hash连接等)

四、更多参考

启用用户进程跟踪

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

PL/SQL 联合数组与嵌套表

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值