【转】高效率SQL语句必杀技

原文地址：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个以上的表连接查询，?那就需要选择交叉表（intersection?table）作为基础表，交叉表是指那个被其他表所引用的表。?????????????????
6. ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
7. 下面的例子使用最常见的scott或hr模式下的表进行演示????????????????????????????????
8. ???????????????????????????????????????????????????????????????????????????????????????????????
9. 表?EMP?有14条记录????????????????????????????????????????????????????????????????
10. 表?DEPT?有4条记录???????????????????????????????????????????????????
11. SELECT??/*+?rule?*/?COUNT(?*?)??FROM???emp,?dept;??????????--高效的写法?? ??
12. ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
13. [email protected]>?set?autotrace?traceonly?stat;?????????????????????????????????????????????????????
14. [email protected]>?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. [email protected]>?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. [email protected]>?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. [email protected]>?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,?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中的MIN和MAX函数???????????????????????????????????????????????????????????????????????????????????????????????
16. [email protected]>?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. [email protected]>?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. [email protected]>?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. [email protected]>?create?table?t6?as?select?object_id,owner,object_name?from?dba_objects?where?owner='SYS'?and?rownum<1001;???????????
120. ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
121. [email protected]>?insert?into?t6?select?object_id,owner,object_name?from?dba_objects?where?owner='SCOTT'?and?rownum<6;????????????????
122. ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
123. [email protected]>?create?index?i_t6_object_id?on?t6(object_id);??????????????????????????????????????????????????
124. ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
125. [email protected]>?create?index?i_t6_owner?on?t6(owner);????????????????????????????????????????????????
126. ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
127. [email protected]>?insert?into?t6?select?object_id,owner,object_name?from?dba_objects?where?owner='SYSTEM'?and?rownum<=300;????????????
128. ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
129. [email protected]>?commit;?????????????????????????????????????????????????????????????????????????????????????????????????????????????
130. ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
131. [email protected]>?exec?dbms_stats.gather_table_stats('SCOTT','T6',cascade=>true);????
132. ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
133. [email protected]>?select?owner,count(*)?from?t6?group?by?owner;????????
134. ??????????????????????????????????????????????????????????????
135. OWNER??????????????????COUNT(*)??????????????????????????????????????????????????
136. --------------------?----------?????????????????????????????????????????????????? ??
137. SCOTT?????????????????????????5?????????????????????????????????????????????????????????
138. SYSTEM??????????????????????300???????????????????????????????????????????????????????????
139. SYS????????????????????????1000??????????????????????????????????????????????????????????
140. ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
141. [email protected]>?select?*?from?t6?where?owner='SCOTT'?and?rownum<2;??????????????????????????????????????????????????????????????????
142. ????????????????????????????????????????????????????????????????????????????
143. ?OBJECT_ID?OWNER????????????????OBJECT_NAME?????????????????????????????????????????????????
144. ----------?--------------------?--------------------?????????????????????????????????????????? ??
145. ?????69450?SCOTT????????????????T_TEST?????????????????????????????????????????????????????????
146. ????????????????????????????????????????????????????????????????????????????????
147. [email protected]>?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. [email protected]>?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. [email protected]>?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?NULL和IS?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连接等)