`
yuxuan1215
  • 浏览: 14017 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

高效SQL语句必杀技

 
阅读更多

No SQL,No cost. SQL语句是造成数据库开销最大的部分。而不良SQL写法直接导致数据库系统性能下降的情形比比皆是。那么如何才能称得
上高效的SQL语句呢？一是查询优化器为当前的SQL语句生成最佳的执行计划，保证数据读写使用最佳路径；二是设置合理的物理存储结构，如表
的类型，字段的顺序，字段的数据类型等。本文主要描述如何编写高效的SQL语句并给出示例。下面的描述主要分为三个部分，一是编写高效SQL
语句，二是使用索引提高查询性能的部分，三是总结部分。

一、编写高效SQL语句

[sql]view plaincopyprint?
  1. 1)选择最有效的表名顺序(仅适用于RBO模式)
  2. ORACLE的解析器总是按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中最后的一个表将作为驱动表被优先处理。当FROM子句
  3. 存在多个表的时候，应当考虑将表上记录最少的那个表置于FROM的最右端作为基表。Oracle会首先扫描基表(FROM子句中最后的那个表)并对
  4. 记录进行排序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。如
  5. 果有3个以上的表连接查询，那就需要选择交叉表（intersectiontable）作为基础表，交叉表是指那个被其他表所引用的表。
  7. 下面的例子使用最常见的scott或hr模式下的表进行演示
  9. 表EMP有14条记录
  10. 表DEPT有4条记录
  11. SELECT/*+rule*/COUNT(*)FROMemp,dept;--高效的写法
  13. scott@CNMMBO>setautotracetraceonlystat;
  14. scott@CNMMBO>SELECT/*+rule*/COUNT(*)FROMemp,dept;
  16. Elapsed:00:00:00.14
  18. Statistics
  19. ----------------------------------------------------------
  20. 1recursivecalls
  21. 0dbblockgets
  22. 35consistentgets
  23. 0physicalreads
  24. 0redosize
  25. 515bytessentviaSQL*Nettoclient
  26. 492bytesreceivedviaSQL*Netfromclient
  27. 2SQL*Netroundtripsto/fromclient
  28. 0sorts(memory)
  29. 0sorts(disk)
  30. 1rowsprocessed
  32. SELECT/*+rule*/COUNT(*)FROMdept,emp;--低效的写法
  33. scott@CNMMBO>SELECT/*+rule*/COUNT(*)FROMdept,emp;
  35. Elapsed:00:00:00.02
  37. Statistics
  38. ----------------------------------------------------------
  39. 1recursivecalls
  40. 0dbblockgets
  41. 105consistentgets
  42. 0physicalreads
  43. 0redosize
  44. 515bytessentviaSQL*Nettoclient
  45. 492bytesreceivedviaSQL*Netfromclient
  46. 2SQL*Netroundtripsto/fromclient
  47. 0sorts(memory)
  48. 0sorts(disk)
  49. 1rowsprocessed
  51. 2)select查询中避免使用'*'
  52. 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用'*'是一个方便的方法.不幸的是,这是一个非常低效的方法.实际
  53. 上，ORACLE在解析的过程中，会将'*'依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
  54. 注：本文中的例子出于简化演示而使用了select*,生产环境应避免使用.
  56. 3)减少访问数据库的次数
  57. 每当执行一条SQL语句,Oracle需要完成大量的内部操作,象解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可
  58. 见，减少访问数据库的次数，实际上是降低了数据库系统开销
  59. -->下面通过3种方式来获得雇员编号为7788与7902的相关信息
  61. -->方式1(最低效):
  62. selectename,job,salfromempwhereempno=7788;
  64. selectename,job,salfromempwhereempno=7902;
  66. -->方式2(次低效):
  67. -->下面使用了参数游标来完成，每传递一次参数则需要对表emp访问一次，增加了I/O
  68. DECLARE
  69. CURSORC1(E_NONUMBER)IS
  70. SELECTename,job,sal
  71. FROMemp
  72. WHEREempno=E_NO;
  73. BEGIN
  74. OPENC1(7788);
  75. FETCHC1INTO…,…,…;
  76. ..
  77. OPENC1(7902);
  78. FETCHC1INTO…,…,…;
  79. CLOSEC1;
  80. END;
  82. -->方式3(最高效)
  83. SELECTa.ename
  84. ,a.job
  85. ,a.sal
  86. ,b.ename
  87. ,b.job
  88. ,b.sal
  89. FROMempa,empb
  90. WHEREa.empno=7788ORb.empno=7902;
  92. 注意：在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200.
  94. 4)使用DECODE函数来减少处理时间
  95. -->使用decode函数可以避免重复扫描相同的行或重复连接相同的表
  96. selectcount(*),sum(sal)fromempwheredeptno=20andenamelike'SMITH%';
  98. selectcount(*),sum(sal)fromempwheredeptno=30andenamelike'SMITH%';
  100. -->通过使用decode函数一次扫描即可完成所有满足条件记录的处理
  101. SELECTCOUNT(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. FROMemp
  106. WHEREenameLIKE'SMITH%';
  108. 类似的，DECODE函数也可以运用于GROUPBY和ORDERBY子句中。
  110. 5)整合简单，无关联的数据库访问
  111. -->如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中以提高性能(即使它们之间没有关系)
  112. -->整合前
  113. SELECTname
  114. FROMemp
  115. WHEREempno=1234;
  117. SELECTname
  118. FROMdept
  119. WHEREdeptno=10;
  121. SELECTname
  122. FROMcat
  123. WHEREcat_type='RD';
  125. -->整合后
  126. SELECTe.name,d.name,c.name
  127. FROMcatc
  128. ,dptd
  129. ,empe
  130. ,dualx
  131. WHERENVL('X',x.dummy)=NVL('X',e.ROWID(+))
  132. ANDNVL('X',x.dummy)=NVL('X',d.ROWID(+))
  133. ANDNVL('X',x.dummy)=NVL('X',c.ROWID(+))
  134. ANDe.emp_no(+)=1234
  135. ANDd.dept_no(+)=10
  136. ANDc.cat_type(+)='RD';
  138. -->从上面的SQL语句可以看出，尽管三条语句被整合为一条，性能得以提高，然可读性差，此时应权衡性能与代价
  140. 6)删除重复记录
  141. -->通过使用rowid来作为过滤条件，性能高效
  142. DELETEFROMempe
  143. WHEREe.ROWID>(SELECTMIN(x.ROWID)
  144. FROMempx
  145. WHEREx.empno=e.empno);
  147. 7)使用truncate代替delete
  148. -->通常情况下,任意记录的删除需要在回滚段构造删除前镜像以实现回滚(rollback).对于未提交的数据在执行rollback之后,Oracle会生成
  149. -->等价SQL语句去恢复记录(如delete,则生成对应的insert语句;如insert则生成对应的delete;如update,则是同时生成delete和insert
  150. -->使用truncate命令则是执行DDL命令,不产生任何回滚信息,直接格式化并释放高水位线.故该语句性能高效.由于不能rollback,因此慎用.
  152. 8)尽量多使用COMMIT(COMMIT应确保事务的完整性)
  153. -->只要有可能，在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少
  154. -->COMMIT所释放的资源：
  155. -->1.回滚段上用于恢复数据的信息
  156. -->2.释放语句处理期间所持有的锁
  157. -->3.释放redologbuffer占用的空间(commit将redologbuffer中的entries写入到联机重做日志文件)
  158. -->4.ORACLE为管理上述3种资源中的内部开销
  160. 9)计算记录条数
  161. -->一般的情况下,count(*)比count(1)稍快.如果可以通过索引检索,对索引列的计数是最快的,因为直接扫描索引即可,例如COUNT(EMPNO)
  162. -->实际情况是经测试上述三种情况并无明显差异.
  164. 10)用Where子句替换HAVING子句
  165. -->尽可能的避免having子句，因为HAVING子句是对检索出所有记录之后再对结果集进行过滤。这个处理需要排序，总计等操作
  166. -->通过WHERE子句则在分组之前即可过滤不必要的记录数目，从而减少聚合的开销
  168. -->低效:
  169. SELECTdeptno,AVG(sal)
  170. FROMemp
  171. GROUPBYdeptno
  172. HAVINGdeptno=20;
  174. scott@CNMMBO>SELECTdeptno,AVG(sal)
  175. 2FROMemp
  176. 3GROUPBYdeptno
  177. 4HAVINGdeptno=20;
  179. Statistics
  180. ----------------------------------------------------------
  181. 0recursivecalls
  182. 0dbblockgets
  183. 7consistentgets
  184. 0physicalreads
  185. 0redosize
  186. 583bytessentviaSQL*Nettoclient
  187. 492bytesreceivedviaSQL*Netfromclient
  188. 2SQL*Netroundtripsto/fromclient
  189. 0sorts(memory)
  190. 0sorts(disk)
  191. 1rowsprocessed
  192. -->高效:
  193. SELECTdeptno,AVG(sal)
  194. FROMemp
  195. WHEREdeptno=20
  196. GROUPBYdeptno;
  198. scott@CNMMBO>SELECTdeptno,AVG(sal)
  199. 2FROMemp
  200. 3WHEREdeptno=20
  201. 4GROUPBYdeptno;
  203. Statistics
  204. ----------------------------------------------------------
  205. 0recursivecalls
  206. 0dbblockgets
  207. 2consistentgets
  208. 0physicalreads
  209. 0redosize
  210. 583bytessentviaSQL*Nettoclient
  211. 492bytesreceivedviaSQL*Netfromclient
  212. 2SQL*Netroundtripsto/fromclient
  213. 0sorts(memory)
  214. 0sorts(disk)
  215. 1rowsprocessed
  217. 11)最小化表查询次数
  218. -->在含有子查询的SQL语句中，要特别注意减少对表的查询
  219. -->低效:
  220. SELECT*
  221. FROMemployees
  222. WHEREdepartment_id=(SELECTdepartment_id
  223. FROMdepartments
  224. WHEREdepartment_name='Marketing')
  225. ANDmanager_id=(SELECTmanager_id
  226. FROMdepartments
  227. WHEREdepartment_name='Marketing');
  228. -->高效:
  229. SELECT*
  230. FROMemployees
  231. WHERE(department_id,manager_id)=(SELECTdepartment_id,manager_id
  232. FROMdepartments
  233. WHEREdepartment_name='Marketing')
  235. -->类似更新多列的情形
  236. -->低效:
  237. UPDATEemployees
  238. SETjob_id=(SELECTMAX(job_id)FROMjobs),salary=(SELECTAVG(min_salary)FROMjobs)
  239. WHEREdepartment_id=10;
  241. -->高效:
  242. UPDATEemployees
  243. SET(job_id,salary)=(SELECTMAX(job_id),AVG(min_salary)FROMjobs)
  244. WHEREdepartment_id=10;
  246. 12)使用表别名
  247. -->在多表查询时，为所返回列使用表别名作为前缀以减少解析时间以及那些相同列歧义引起的语法错误
  249. 13)用EXISTS替代IN
  250. 在一些基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常
  251. 将提高查询的效率.
  252. -->低效:
  253. SELECT*
  254. FROMemp
  255. WHEREsal>1000
  256. ANDdeptnoIN(SELECTdeptno
  257. FROMdept
  258. WHEREloc='DALLAS')
  260. -->高效:
  261. SELECT*
  262. FROMemp
  263. WHEREempno>1000
  264. ANDEXISTS
  265. (SELECT1
  266. FROMdept
  267. WHEREdeptno=emp.deptnoANDloc='DALLAS')
  269. 14)用NOTEXISTS替代NOTIN
  270. 在子查询中,NOTIN子句引起一个内部的排序与合并.因此,无论何时NOTIN子句都是最低效的,因为它对子查询中的表执行了一个全表
  271. 遍历.为避免该情形,应当将其改写成外部连接(OUTTERJOIN)或适用NOTEXISTS
  272. -->低效:
  273. SELECT*
  274. FROMemp
  275. WHEREdeptnoNOTIN(SELECTdeptno
  276. FROMdept
  277. WHEREloc='DALLAS');
  279. -->高效:
  280. SELECTe.*
  281. FROMempe
  282. WHERENOTEXISTS
  283. (SELECT1
  284. FROMdept
  285. WHEREdeptno=e.deptnoANDloc='DALLAS');
  287. -->最高效(尽管下面的查询最高效,并不推荐使用,因为列loc使用了不等运算,当表dept数据量较大,且loc列存在索引的话,则此时索引失效)
  288. SELECTe.*
  289. FROMempeLEFTJOINdeptdONe.deptno=d.deptno
  290. WHEREd.loc<>'DALLAS'
  292. 15)使用表连接替换EXISTS
  293. 一般情况下，使用表连接比EXISTS更高效
  294. -->低效:
  295. SELECT*
  296. FROMemployeese
  297. WHEREEXISTS
  298. (SELECT1
  299. FROMdepartments
  300. WHEREdepartment_id=e.department_idANDdepartment_name='IT');
  302. -->高效:
  303. SELECT*-->经测试此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果一致
  304. FROMemployeeseINNERJOINdepartmentsdONd.department_id=e.department_id
  305. WHEREd.department_name='IT';
  307. 16)用EXISTS替换DISTINCT
  308. 对于一对多关系表信息查询时(如部门表和雇员表)，应避免在select子句中使用distinct,而使用exists来替换
  310. -->低效:
  311. SELECTDISTINCTe.department_id,d.department_name
  312. FROMdepartmentsdINNERJOINemployeeseONd.department_id=e.department_id;
  314. -->高效:
  315. SELECTd.department_id,department_name
  316. fromdepartmentsd
  317. WHEREEXISTS
  318. (SELECT1
  319. FROMemployeese
  320. WHEREd.department_id=e.department_id);
  322. EXISTS使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果
  323. -->经测试此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果一致
  325. 17)使用UNIONALL替换UNION(如果有可能的话)
  326. 当SQL语句需要UNION两个查询结果集时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。
  327. 如果用UNIONALL替代UNION，这样排序就不是必要了。效率就会因此得到提高。
  329. 注意：
  330. UNIONALL会输出所有的结果集,而UNION则过滤掉重复记录并对其进行排序.因此在使用时应考虑业务逻辑是否允许当前的结果集存在重复现象
  332. 寻找低效的SQL语句
  333. -->下面的语句主要适用于从视图v$sqlarea中获得当前运行下且耗用buffer_gets较多的SQL语句
  334. SELECTexecutions
  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. FROMv$sqlarea
  344. WHEREexecutions>0
  345. ANDbuffer_gets>0
  346. AND(buffer_gets
  347. -disk_reads)
  348. /buffer_gets<0.80
  349. ORDERBY4DESC;
  351. 18)尽可能避免使用函数，函数会导致更多的recursivecalls

二、合理使用索引以提高性能
 索引依赖于表而存在，是真实表的一个缩影，类似于一本书的目录，通过目录以更快获得所需的结果。Oracle使用了一个复杂的自平衡
B数据结构。即任意记录的DML操作将打破索引的平衡，而定期重构索引使得索引重新获得平衡。通常，通过索引查找数据比全表扫描更高效。
任意的DQL或DML操作，SQL优化引擎优先使用索引来计算当前操作的成本以生成最佳的执行计划。一旦使用索引操出参数optimizer_index_cost_adj
设定的值才使用全表扫描。同样对于多表连接使用索引也可以提高效率。同时索引也提供主键（primary key）的唯一性验证。

除了那些LONG或LONG RAW数据类型,你可以索引几乎所有的列.通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索
引同样能提高效率。

虽然使用索引能得到查询效率的提高,但是索引需要空间来存储,需要定期维护.尤其是在有大量DML操作的表上，任意的DML操作都将引起索
引的变更这意味着每条记录的INSERT ， DELETE ， UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理，
那些不必要的索引反而会使查询反应时间变慢。

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

[sql]view plaincopyprint?
  1. 1)避免基于索引列的计算
  2. where子句中的谓词上存在索引，而此时基于该列的计算将使得索引失效
  4. -->低效:
  5. SELECTemployee_id,first_name
  6. FROMemployees
  7. WHEREemployee_id+10>150;-->索引列上使用了计算，因此索引失效，走全表扫描方式
  9. -->高效:
  10. SELECTemployee_id,first_name
  11. FROMemployees
  12. WHEREemployee_id>160;-->走索引范围扫描方式
  14. 例外情形
  15. 上述规则不适用于SQL中的MIN和MAX函数
  16. hr@CNMMBO>SELECTMAX(employee_id)max_id
  17. 2FROMemployees
  18. 3WHEREemployee_id
  19. 4+10>150;
  21. 1rowselected.
  23. ExecutionPlan
  24. ----------------------------------------------------------
  25. Planhashvalue:1481384439
  26. ---------------------------------------------------------------------------------------------
  27. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
  28. ---------------------------------------------------------------------------------------------
  29. |0|SELECTSTATEMENT||1|4|1(0)|00:00:01|
  30. |1|SORTAGGREGATE||1|4|||
  31. |2|FIRSTROW||5|20|1(0)|00:00:01|
  32. |*3|INDEXFULLSCAN(MIN/MAX)|EMP_EMP_ID_PK|5|20|1(0)|00:00:01|
  33. ---------------------------------------------------------------------------------------------
  35. 2)避免在索引列上使用NOT运算或不等于运算(<>,!=)
  36. 通常，我们要避免在索引列上使用NOT或<>,两者会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT或不等运算时，他就会停止
  37. 使用索引转而执行全表扫描。
  39. -->低效:
  40. SELECT*
  41. FROMemp
  42. WHERENOT(deptno=20);-->实际上NOT(deptno=20)等同于deptno<>20,即deptno<>同样会限制索引
  44. -->高效:
  45. SELECT*
  46. FROMemp
  47. WHEREdeptno>20ORdeptno<20;
  48. -->尽管此方式可以替换且实现上述结果，但依然走全表扫描，如果是单纯的>或<运算，则此时为索引范围扫描
  50. 需要注意的是，在某些时候，ORACLE优化器会自动将NOT转化成相对应的关系操作符
  51. 其次如果是下列运算符进行NOT运算,依然有可能选择走索引,仅仅除了NOT=之外,因为NOT=等价于<>
  53. “NOT>”to<=
  54. “NOT>=”to<
  55. “NOT<”to>=
  56. “NOT<=”to>
  58. 来看一个实际的例子
  59. hr@CNMMBO>SELECT*
  60. 2FROMemployees
  61. 3wherenotemployee_id<100;-->索引列上使用了not，但是该查询返回了所有的记录,即107条，因此此时选择走全表扫描
  63. 107rowsselected.
  65. ExecutionPlan
  66. ----------------------------------------------------------
  67. Planhashvalue:1445457117
  68. -------------------------------------------------------------------------------
  69. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
  70. -------------------------------------------------------------------------------
  71. |0|SELECTSTATEMENT||107|7276|3(0)|00:00:01|
  72. |*1|TABLEACCESSFULL|EMPLOYEES|107|7276|3(0)|00:00:01|-->执行计划中使用了走全表扫描方式
  73. -------------------------------------------------------------------------------
  74. PredicateInformation(identifiedbyoperationid):
  75. ---------------------------------------------------
  77. 1-filter("EMPLOYEE_ID">=100)-->查看这里的谓词信息被自动转换为>=运算符
  79. hr@CNMMBO>SELECT*
  80. 2FROMemployees
  81. 3wherenotemployee_id<140;-->此例与上面的语句相同,仅仅是查询范围不同返回67条记录,而此时选择了索引范围扫描
  83. 67rowsselected.
  85. ExecutionPlan
  86. ----------------------------------------------------------
  87. Planhashvalue:603312277
  89. ---------------------------------------------------------------------------------------------
  90. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
  91. ---------------------------------------------------------------------------------------------
  92. |0|SELECTSTATEMENT||68|4624|3(0)|00:00:01|
  93. |1|TABLEACCESSBYINDEXROWID|EMPLOYEES|68|4624|3(0)|00:00:01|
  94. |*2|INDEXRANGESCAN|EMP_EMP_ID_PK|68||1(0)|00:00:01|-->索引范围扫描方式
  95. ---------------------------------------------------------------------------------------------
  96. PredicateInformation(identifiedbyoperationid):
  97. ---------------------------------------------------
  98. 2-access("EMPLOYEE_ID">=140)
  100. 3)用UNION替换OR(适用于索引列)
  101. 通常情况下,使用UNION替换WHERE子句中的OR将会起到较好的效果.基于索引列使用OR使得优化器倾向于使用全表扫描，而不是扫描索引.
  102. 注意,以上规则仅适用于多个索引列有效。如果有column没有被索引，查询效率可能会因为你没有选择OR而降低。
  103. -->低效:
  104. SELECTdeptno,dname
  105. FROMdept
  106. WHEREloc='DALLAS'ORdeptno=20;
  108. -->高效:
  109. SELECTdeptno,dname
  110. FROMdept
  111. WHEREloc='DALLAS'
  112. UNION
  113. SELECTdeptno,dname
  114. FROMdept
  115. WHEREdeptno=30
  117. -->经测试,由于数据量较少，此时where子句中的谓词上都存在索引列时，两者性能相当.
  118. -->假定where子句中存在两列
  119. scott@CNMMBO>createtablet6asselectobject_id,owner,object_namefromdba_objectswhereowner='SYS'andrownum<1001;
  121. scott@CNMMBO>insertintot6selectobject_id,owner,object_namefromdba_objectswhereowner='SCOTT'andrownum<6;
  123. scott@CNMMBO>createindexi_t6_object_idont6(object_id);
  125. scott@CNMMBO>createindexi_t6_owneront6(owner);
  127. scott@CNMMBO>insertintot6selectobject_id,owner,object_namefromdba_objectswhereowner='SYSTEM'andrownum<=300;
  129. scott@CNMMBO>commit;
  131. scott@CNMMBO>execdbms_stats.gather_table_stats('SCOTT','T6',cascade=>true);
  133. scott@CNMMBO>selectowner,count(*)fromt6groupbyowner;
  135. OWNERCOUNT(*)
  136. ------------------------------
  137. SCOTT5
  138. SYSTEM300
  139. SYS1000
  141. scott@CNMMBO>select*fromt6whereowner='SCOTT'andrownum<2;
  143. OBJECT_IDOWNEROBJECT_NAME
  144. --------------------------------------------------
  145. 69450SCOTTT_TEST
  147. scott@CNMMBO>select*fromt6whereobject_id=69450orowner='SYSTEM';
  149. 301rowsselected.
  151. ExecutionPlan
  152. ----------------------------------------------------------
  153. Planhashvalue:238853296
  154. -----------------------------------------------------------------------------------------------
  155. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
  156. -----------------------------------------------------------------------------------------------
  157. |0|SELECTSTATEMENT||300|7200|5(0)|00:00:01|
  158. |1|CONCATENATION||||||
  159. |2|TABLEACCESSBYINDEXROWID|T6|1|24|2(0)|00:00:01|
  160. |*3|INDEXRANGESCAN|I_T6_OBJECT_ID|1||1(0)|00:00:01|
  161. |*4|TABLEACCESSBYINDEXROWID|T6|299|7176|3(0)|00:00:01|
  162. |*5|INDEXRANGESCAN|I_T6_OWNER|300||1(0)|00:00:01|
  163. -----------------------------------------------------------------------------------------------
  165. PredicateInformation(identifiedbyoperationid):
  166. ---------------------------------------------------
  167. 3-access("OBJECT_ID"=69450)
  168. 4-filter(LNNVL("OBJECT_ID"=69450))
  169. 5-access("OWNER"='SYSTEM')
  171. Statistics
  172. ----------------------------------------------------------
  173. 0recursivecalls
  174. 0dbblockgets
  175. 46consistentgets
  176. 0physicalreads
  177. 0redosize
  178. 11383bytessentviaSQL*Nettoclient
  179. 712bytesreceivedviaSQL*Netfromclient
  180. 22SQL*Netroundtripsto/fromclient
  181. 0sorts(memory)
  182. 0sorts(disk)
  183. 301rowsprocessed
  185. scott@CNMMBO>select*fromt6whereowner='SYSTEM'orobject_id=69450;
  187. 301rowsselected.
  189. ExecutionPlan
  190. ----------------------------------------------------------
  191. Planhashvalue:238853296
  192. -----------------------------------------------------------------------------------------------
  193. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
  194. -----------------------------------------------------------------------------------------------
  195. |0|SELECTSTATEMENT||300|7200|5(0)|00:00:01|
  196. |1|CONCATENATION||||||
  197. |2|TABLEACCESSBYINDEXROWID|T6|1|24|2(0)|00:00:01|
  198. |*3|INDEXRANGESCAN|I_T6_OBJECT_ID|1||1(0)|00:00:01|
  199. |*4|TABLEACCESSBYINDEXROWID|T6|299|7176|3(0)|00:00:01|
  200. |*5|INDEXRANGESCAN|I_T6_OWNER|300||1(0)|00:00:01|
  201. -----------------------------------------------------------------------------------------------
  203. PredicateInformation(identifiedbyoperationid):
  204. ---------------------------------------------------
  205. 3-access("OBJECT_ID"=69450)
  206. 4-filter(LNNVL("OBJECT_ID"=69450))
  207. 5-access("OWNER"='SYSTEM')
  209. Statistics
  210. ----------------------------------------------------------
  211. 1recursivecalls
  212. 0dbblockgets
  213. 46consistentgets
  214. 0physicalreads
  215. 0redosize
  216. 11383bytessentviaSQL*Nettoclient
  217. 712bytesreceivedviaSQL*Netfromclient
  218. 22SQL*Netroundtripsto/fromclient
  219. 0sorts(memory)
  220. 0sorts(disk)
  221. 301rowsprocessed
  223. scott@CNMMBO>select*fromt6
  224. 2whereobject_id=69450
  225. 3union
  226. 4select*fromt6
  227. 5whereowner='SYSTEM';
  229. 301rowsselected.
  231. ExecutionPlan
  232. ----------------------------------------------------------
  233. Planhashvalue:370530636
  234. ------------------------------------------------------------------------------------------------
  235. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
  236. ------------------------------------------------------------------------------------------------
  237. |0|SELECTSTATEMENT||301|7224|7(72)|00:00:01|
  238. |1|SORTUNIQUE||301|7224|7(72)|00:00:01|
  239. |2|UNION-ALL||||||
  240. |3|TABLEACCESSBYINDEXROWID|T6|1|24|2(0)|00:00:01|
  241. |*4|INDEXRANGESCAN|I_T6_OBJECT_ID|1||1(0)|00:00:01|
  242. |5|TABLEACCESSBYINDEXROWID|T6|300|7200|3(0)|00:00:01|
  243. |*6|INDEXRANGESCAN|I_T6_OWNER|300||1(0)|00:00:01|
  244. ------------------------------------------------------------------------------------------------
  246. PredicateInformation(identifiedbyoperationid):
  247. ---------------------------------------------------
  248. 4-access("OBJECT_ID"=69450)
  249. 6-access("OWNER"='SYSTEM')
  251. Statistics
  252. ----------------------------------------------------------
  253. 1recursivecalls
  254. 0dbblockgets
  255. 7consistentgets
  256. 0physicalreads
  257. 0redosize
  258. 11383bytessentviaSQL*Nettoclient
  259. 712bytesreceivedviaSQL*Netfromclient
  260. 22SQL*Netroundtripsto/fromclient
  261. 1sorts(memory)
  262. 0sorts(disk)
  263. 301rowsprocessed
  265. -->从上面的统计信息可知，consistentgets由46下降为7，故当where子句中谓词上存在索引时，使用union替换or更高效
  266. -->即使当列object_id与owner上不存在索引时，使用union仍然比or更高效(在Oracle10gR2与Oracle11gR2测试)
  268. 4)避免索引列上使用函数
  269. -->下面是一个来自实际生产环境的例子
  270. -->表acc_pos_int_tbl上business_date列存在索引,由于使用了SUBSTR函数，此时索引失效，使用全表扫描
  271. SELECTacc_num
  272. ,curr_cd
  273. ,DECODE('20110728'
  274. ,(SELECTTO_CHAR(LAST_DAY(TO_DATE('20110728','YYYYMMDD')),'YYYYMMDD')FROMdual),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. ASinterest
  280. FROMacc_pos_int_tbl
  281. WHERESUBSTR(business_date,1,6)=SUBSTR('20110728',1,6)ANDbusiness_date<='20110728';
  283. -->改进的办法
  284. SELECTacc_num
  285. ,curr_cd
  286. ,DECODE('20110728'
  287. ,(SELECTTO_CHAR(LAST_DAY(TO_DATE('20110728','YYYYMMDD')),'YYYYMMDD')FROMdual),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. ASinterest
  293. FROMacc_pos_int_tblacc_pos_int_tbl
  294. WHEREbusiness_date>=TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE('20110728','yyyymmdd'),-1))
  295. +1,'yyyymmdd')
  296. ANDbusiness_date<='20110728';
  298. -->下面的例子虽然没有使用函数，但字符串连接同样导致索引失效
  299. -->低效:
  300. SELECTaccount_name,amount
  301. FROMtransaction
  302. WHEREaccount_name
  303. ||account_type='AMEXA';
  305. -->高效:
  306. SELECTaccount_name,amount
  307. FROMtransaction
  308. WHEREaccount_name='AMEX'ANDaccount_type='A';
  310. 5)比较不匹配的数据类型
  311. -->下面的查询中business_date列上存在索引,且为字符型,这种
  312. -->低效:
  313. SELECT*
  314. FROMacc_pos_int_tbl
  315. WHEREbusiness_date=20090201；
  317. ExecutionPlan
  318. ----------------------------------------------------------
  319. Planhashvalue:2335235465
  321. -------------------------------------------------------------------------------------
  322. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
  323. -------------------------------------------------------------------------------------
  324. |0|SELECTSTATEMENT||37516|2857K|106K(1)|00:21:17|
  325. |*1|TABLEACCESSFULL|ACC_POS_INT_TBL|37516|2857K|106K(1)|00:21:17|
  326. -------------------------------------------------------------------------------------
  328. PredicateInformation(identifiedbyoperationid):
  329. ---------------------------------------------------
  330. 1-filter(TO_NUMBER("BUSINESS_DATE")=20090201)-->这里可以看到产生了类型转换
  332. -->高效:
  333. SELECT*
  334. FROMacc_pos_int_tbl
  335. WHEREbusiness_date='20090201'
  337. 6)索引列上使用NULL值
  338. ISNULL和ISNOTNULL会限制索引的使用,因为数据中没有值等于NULL值,即便是NULL值也不等于NULL值.且NULL值不存储在于索引之中
  339. 因此应尽可能避免在索引类上使用NULL值
  341. SELECTacc_num
  342. ,pl_cd
  343. ,order_qty
  344. ,trade_date
  345. FROMtrade_client_tbl
  346. WHEREinput_dateISNOTNULL;
  348. ExecutionPlan
  349. ----------------------------------------------------------
  350. Planhashvalue:901462645
  351. --------------------------------------------------------------------------------------
  352. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
  353. --------------------------------------------------------------------------------------
  354. |0|SELECTSTATEMENT||1|44|15(0)|00:00:01|
  355. |*1|TABLEACCESSFULL|TRADE_CLIENT_TBL|1|44|15(0)|00:00:01|
  356. --------------------------------------------------------------------------------------
  358. altertabletrade_client_tblmodify(input_datenotnull);
  360. 不推荐使用的查询方式
  361. SELECT*FROMtable_nameWHEREcolISNOTNULL
  363. SELECT*FROMtable_nameWHEREcolISNULL
  365. 推荐使用的方式
  366. SELECT*FROMtable_nameWHEREcol>=0--尽可能的使用=,>=,<=,like等运算符
  367. -->Author:RobinsonCheng
  368. -->Blog:http://blog.csdn.net/robinson_0612

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

四、更多参考

Oracle SQL tuning 步骤

启用用户进程跟踪

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

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

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

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

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

PL/SQL 联合数组与嵌套表

分享到：
| Date、String、Calendar三个类型之间的转化
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics