优化规则总结：
1.通常不应对where语句检索条件的表列做任何处理。处理包括加函数 ,计算等。例外情况DBA会提前公布。
2.在业务逻辑及工具允许的条件下，采用正确的SQL嵌套方式，且把运算处理放在最外层。
3.详细的分析业务逻辑和数据结构，避免不必要的计算。如排重distinct， nvl检测等。
4.避免数据列类型和输入值类型不一致。
5.SQL语句中使用绑定变量。
6.理清业务逻辑，选择中间结果集最少为目标，减少SQL内部的操作运算。
7.减少数据库的调用次数。
8.正确高效的使用模糊匹配查询---右百分号。
9.分页排序时正确的使用rownum
10.避免使用耗费资源的操作--UNION,MINUS,INTERSECT，DISTINCT
优化规则举例说明：
规则1.通常不应对where语句检索条件的表列做任何处理。处理包括加函数,计算等 。例外情况DBA会提前公布。
举例(函数)：
Error:
select count(*) as col_0_0_
from B_LOG blog0_
where :"SYS_B_0" =:"SYS_B_1"
and to_char(blog0_.OP_DATE,:"SYS_B_3") >= :"SYS_B_4"
正确改法：
select count(*) as col_0_0_
from B_LOG blog0_
where :"SYS_B_0" =:"SYS_B_1"
and blog0_.OP_DATE >= To_date('2007-01-0600:00:00', 'yyyy-mm-dd hh24:mi:ss')
and blog0_.OP_DATE <= to_date('2007-01-0823:59:59', 'yyyy-mm-dd hh24:mi:ss')
举例(计算):
Error:
SELECT ENAME
FROM EMP
WHERE DEPTNO + 1 = 10 /*DEPTNO上的索引将失效*/
AND EMP_TYPE ||’$’ = ‘A' /*EMP_TYPE上的索引将失效*/
正确改法：
SELECT ENAME
FROM EMP
WHERE DEPTNO = 10-1 /*DEPTNO上的索引将失效*/
AND EMP_TYPE = ‘A' ||‘$’
规则2.在业务逻辑及工具允许的条件下，采用正确的SQL嵌套方式，且把运算处理放在最外层。
on_busin_chance a与Busin_long_info 关系上一一对应。
改成 嵌套方式， 最里层不要关联Busin_long_info， 用结果的20条 (rownum < :"SYS_B_11") 去关联Busin_long_info 。
Error
-----------------------------PrjIframe.exe--- begin -------------------------------
Select title,
:"SYS_B_00" || username || :"SYS_B_01" || bc_id || :"SYS_B_02" as linku,
.......
price,
trustvalue
from (Select a.title as title,
a.bc_id as bc_id,
.......
a.pricerange as price,
:"SYS_B_04" as trustvalue
from on_busin_chance a, Busin_long_info b
where a.bc_id = b.bc_id
and a.pubdate > sysdate - :"SYS_B_05"
.......
and a.SortTag = :"SYS_B_10"
order by a.pubdate desc)
where rownum < :"SYS_B_11"
-----------------------------PrjIframe.exe--- end -------------------------------
错误的sql:
select A.bc_id,
A.title,
A.Pubdate ,
/*to_char(A.Pubdate, :"SYS_B_1") as NEWPubdate,*\*/
A.picpath,
A.supcatid asCLSID,
A.username,
A.providerid,
C.NAME ascorname,
C.Memtypeid,
b.areaid
from (select bc_id,
title,
.....
providerid
frombusin_list
wheresorttag = '1'
order bypubdate desc) A,
(selectsupcatid, areaid
fromnewhc.new_iframe_class
where areaid= '005'
and clsid= '003034') B,
provider_listC
where b.supcatid =A.supcatid
and C.providerid =A.providerid
and rownum < 20
正确的sql
select bb.to_char(A.Pubdate, :"SYS_B_1") asNEWPubdate , xxxx,xxxx,
from (select aa.*
from(select A.*
from busin_list A, newhc.new_iframe_class B
where A.sorttag = '1'
and B.areaid = '005'
and B.clsid = '003034'
and B.supcatid = A.supcatid
orderby A.pubdate desc) aa
whererownum < 20) bb,
provider_listC
where c.providerid= bb.providerid
规则3.详细的分析业务逻辑和数据结构，避免不必要的计算。 如排重distinct，nvl检测等。
同时从设计方面提高数据的内在质量(约束，匹配，数据类型转换)，,避免SQL过多关联检测计算，多次类型转换。
select distinct bidinproce0_.NAME as col_0_0_,
bidsetting1_.ID as col_1_0_,
bidsetting1_.ID as ID409_,
.......
bidsetting1_.POSTENDDATE as POSTENDD7_409_,
bidsetting1_.STATE as STATE409_
from keyword.BID_IN_PROCESS bidinproce0_,
keyword.BID_SETTING bidsetting1_,
keyword.ON_COR_TABLE oncortable2_
where bidinproce0_.BIDSETTINGID =bidsetting1_.ID
and bidinproce0_.CURBID = '1'
and bidinproce0_.VIPPURCHASE = '0'
updateon_busin_chance
setoperstate ='0',
searchstate ='0',
yrepeated ='1',
REPEATTIMES =nvl(REPEATTIMES,0) +1,
pubdate =sysdate,
enddate =sysdate+ validdate
wherebc_id = P_id;
检查发现REPEATTIMES列上有非空约束。
updateon_busin_chance
setoperstate ='0',
searchstate ='0',
yrepeated ='1',
REPEATTIMES =REPEATTIMES +1,
pubdate =sysdate,
enddate =sysdate+ validdate
wherebc_id = P_id;
------------改前-------------------------------------
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- --------------------
886 267 96,174 0.0 1.5 8k49v07aa7dhw
BEGIN RepeateInfo(:1,:2,:3); END;
nvl去掉后的效果
------------改后--------------11-14-----------------------
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- --------------------
396 227 108,756 0.0 1.7 8k49v07aa7dhw
BEGIN RepeateInfo(:1,:2,:3); END;
null数据检测/转换
从数据进入源头，数据底层强制约束。
如重发中的nvl检测, nvl(B.SORTTAG,:"SYS_B_0000")
规则4.避免数据列类型和输入值类型不一致。
举例
列为字符型varchar2 。
where var_col='36837053' 可以使用index
where var_col= 36837053 ; 不能用上其列index
规则5: SQL语句中使用绑定变量。
为将sql分析减少到最小，在SQL语句中使用绑定变量的方法实现,。这样，所有的用户都可以使用相同的SQL语句。
规则6:理清业务逻辑，选择中间结果集最少为目标，减少SQL内部的操作运算。
select*
from(selectrownumrowno, t.*
from(selectbd_id,
bd_state,
bd_title,
.......
bd_user,
fromt_bbs_data
wherebd_parent =0
orderbybd_lastdatedesc) t
whererownum<=20)
whererowno >=1
正确改法：
bd_lastdate加数据的检索条件时间范围限制。
规则7:减少数据库的调用次数。
举例：
减少对表的查询 （ 在含有子查询的SQL语句中,要特别注意减少对表的查询.）
SELECTTAB_NAME
FROMTABLES
WHERETAB_NAME = (SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION =604)
AND DB_VER = (SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION =604)
正确改法：
SELECTTAB_NAME
FROMTABLES
WHERE(TAB_NAME, DB_VER) =
(SELECTTAB_NAME, DB_VER)FROMTAB_COLUMNSWHEREVERSION =604)
规则8.正确高效的使用模糊匹配查询---右百分号。
一. 用户输入的查询关键字长度不能过短。Like ‘H%’
二． 判断结果集的数量是否属合理范围，否则要求用户的再次确认或取消此查询。
三． 前端避免用户输入的特殊关键字( %xxxxx, _)。 恶意匹配， 域名查询要求。
考虑： 此需求的实现 的确要对email的信息拆分。翻转index测试
规则9.分页排序时正确的使用rownum
下面的SQL多耗费了5倍的CPU,并会随数据量的增长而成增长
原sql
SELECT A.*
FROM(SELECT X.*, rownum rn
FROM (SELECT rowid as rowidx
From List_Provider_sup_1 X
WHERE X.L3CurCatID = '011'
AND X.L6CurCatID = '011002'
ORDER BY SORTVALUE ASC) X ) A
WHEREA.rn <=20
ANDA.rn > 0
应改写如下:
select * from (
selectA.*, rownum rn
from(SELECT rowid as rowidx
From List_Provider_sup_1 X
WHERE X.L3CurCatID = '011'
AND X.L6CurCatID = '011002'
ORDER BY SORTVALUE ASC) A
whererownum < 20 ) B
where B.rn > 0
规则10.避免使用耗费资源的操作----
UNION,MINUS,INTERSECT，DISTINCT,
会启动SQL引擎执行耗费资源的排序(SORT)功能.。
DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.
例如,一个UNION查询,其中每个查询都带有GROUP BY子句,GROUP BY会触发嵌入排序(NESTED SORT) ;这样,每个查询需要执行一次排序,然后在执行UNION时,又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行.嵌入的排序的深度会大大影响查询的效率.通常,带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写。
建议:学会分析SQL语句执行计划。
建议对所有的SQL语句执行EXPLAIN_PLAN，并查看输出结果，然后调整相应的语句。如：
随着数据库的版本升级，有些书介绍的一些规则在新版中已不在适用。
如：表名顺序，OR的使用等。
选择TAB2作为基础表(最好的方法)
select count(*) from tab1,tab2执行时间0.96秒
选择TAB2作为基础表(不佳的方法)
select count(*) from tab2,tab1执行时间26.09秒
SQL> set autotrace on explain;
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
---------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'