【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“SQL的优化技巧”一文，供大家参考学习
SQL使用技巧
1高效的sql语句
1.1Sql优化方法
RBO(rule-basedoptimizer)
CBO1.1.1驱动表1）2张行数不一致的表连接表TAB1行数：16,384行表TAB2行数：1行×SELECTCOUNT(*)FROMTAB2,TAB1;○SELECTCOUNT(*)FROMTAB1,TAB2;2）3张表连接×SELECT*FROMEMPE,LOCL,CATCWHEREE.emp_noANDE.cat_noANDE.locn
BETWEEN1000AND2000=C.cat_no=L.locn;
○SELECT*FROMLOCL,CATC,EMPEWHEREE.emp_noBETWEEN1000AND2000ANDE.cat_no=C.cat_noANDE.locn=L.locn;1.1.2Where语句顺序的效率1)使用索引引起的where语句效率使用AND语句时行数多的放在前面
1
×SELECT*FROMempEWHEREemp_sal>50000ANDemp_type='MANAGER'AND25<(SELECTCOUNT(*)FROMEMPWHEREemp_mgr=E.emp_no);○SELECT*FROMempEWHERE25<(SELECTCOUNT(*)FROMEMPWHEREemp_mgr=E.emp_no)ANDemp_sal>50000ANDemp_type='MANAGER';使用OR语句时，行数多的放在后面×SELECT*FROMempEWHERE25<(SELECTCOUNT(*)FROMEMPWHEREemp_mgr=E.emp_no)OR(emp_sal>50000ANDemp_type='MANAGER');○SELECTFROMWHEREAND*empE(emp_sal>50000emp_type='MANAGER')OR25<(SELECTCOUNT(*)FROMEMPWHEREemp_mgr=E.emp_no);
2）ROWID的使用使用ROWID的WHERE语句效率最高。SELECTROWID,...INTO:emp_rowid,...FROMempWHEREemp.emp_no=56722FORUPDATE;UPDATEempSETemp.name=...,WHEREROWID=:emp_rowid;3）减少访问次数×SELECTemp_name,sal,grade
2
FROMempWHEREemp_no=0342;SELECTemp_name,sal,gradeFROMempWHEREemp_no=0291;○SELECTA.emp_name,A.sal,A.grade,B.emp_name,B.sal,B.gradeFROMempA,empBWHEREA.emp_no=0342ANDB.emp_no=0291;4）Where语句的索引的使用（1）SUBSTR×SELECTacc_name,trans_date,amountFROMtransactionWHERESUBSTR(account_name,1,7)='CAPITAL';○SELECTacc_name,trans_date,amountFROMtransactionWHEREaccount_nameLIKE'CAPITAL%';（2）！=×SELECTacc_name,trans_date,amountFROMtransactionWHEREamount!=0;○SELECTacc_name,trans_date,amountFROMtransactionWHEREamount>0;
（3）||×SELECTacc_name,trans_date,amountFROMtransactionWHEREacc_name||acc_type='AMEXA';○SELECTacc_name,trans_date,amountFROMtransactionWHEREacc_name='AMEX'ANDacc_type='A';（4）运算
3
×SELECTacc_name,trans_date,amountFROMtransactionWHEREamount+3000<5000;○SELECTacc_name,trans_date,amountFROMtransactionWHEREamount<5000+3000;
1.2SQL命令的使用秘诀
1.2.1DECODE的使用×SELECTCOUNT(*),SUM(salary)FROMempWHEREdept_no=0020ANDemp_nameLIKE'SMITH%';SELECTCOUNT(*),SUM(salary)FROMempWHEREdept_no=0030ANDemp_nameLIKE'SMITH%';○SELECTCOUNT（DECODE(dept_no,0020,COUNT(DECODE(dept_no,0030,SUM(DECODE(dept,0020,SUM(DECODE(dept,0030,FROMempWHEREemp_nameLIKE1.2.2表的别名的使用×SELECTE.emp_no,name,tax_no,Cp_code,comp_nameFROMcompanyC,empEWHEREEp_co
de=Cp_code;○SELECTE.emp_no,E.name,E.tax_no,Cp_code,Cp_nameFROMcompanyC,empEWHEREEp_code=Cp_code;1.2.3去掉重复行DELETEFROMempEWHEREE.rowid>(SELECTMIN(X.rowid)FROMempXWHEREX.emp_no=E.emp_no);'X',NULL))D0020_count,'X',NULL))D0030_count,salary,NULL))D0020_sal,salary,NULL))D0030_sal'SMITH%';
4
1.2.4表的行计数SELECTCOUNT(有索引的列)FROMTRANS;SELECTCOUNT(*)FROMTRANS;SELECTCOUNT(1)FROMTRANS;1.2.5用WHERE语句替换HAVING语句的使用×SELECTregion,AVG(loc_size)FROMlocationGROUPBYregionHAVINGregion!='SYDNEY'ANDregion!='PERTH';○SELECTregion,AVG(loc_size)FROMlocationWHEREregion!='SYDNEY'ANDregion!='PERTH';GROUPBYregion1.2.6使用表连接替代EXISTS使用×SELECTemp_nameFROMempWHERE(emp_cat,sal_range)=(SELECTMAX(category),MAX(sal_range)FROMemp_categories)ANDemp_dept=0020;○SELECTemp_nameFROMempWHEREemp_cat=(SELECTMAX(category)FROMemp_categories)ANDsal_range=(SELECTMAX(sal_range)FROMemp_categories)ANDemp_dept=0020;1.2.7使用EXISTS替代表连接○SELECT...FROMempEWHEREEXISTS(SELECT'X'FROMdeptWHEREdept_no=E.dept_noANDdept_cat='A')ANDE.emp_type='MANAGER';1.2.8使用EXISTS代替DISTINCT语句×SELECTDISTINCTdept_no,dept_nameFROMdeptD,empEWHERED.dept_no=E.dept_no;
5
○SELECTdept_no,dept_nameFROMdeptDWHEREEXISTS(SELECT'X'FROMempEWHEREE.dept_no=D.dept_no);1.2.9使用NOTEXISTS代替NOTIN语句×SELECT...FROMempWHEREdept_noNOTIN(SELECTdept_noFROMdeptWHEREdept_cat='A');○SELECT...FROMempEWHERENOTEXISTS(SELECT'X'FROMdeptWHEREdept_no=E.dept_noANDdept_cat='A');1.2.10使用unionall代替union语句UnionUnionAll--------进行排序--------不排序
1.2.11使用Union和IN代替OR语句Loc_no,region上有索引×SELECT...FROMlocationWHEREloc_id=10ORregion='MELBOURNE';○SELECT...FROMlocationWHEREloc_id=10UNIONALLSELECT...FROMlocationWHEREregion='MELBOURNE';×SELECT...FROMlocationWHEREloc_id=10ORloc_id=20ORloc_id=30;
6
○SELECT...FROMlocationWHEREloc_idIN(10,20,30);
2高效索引
2.1索引的使用
使用索引时，要考虑以下因素：1）索引列的计算2）索引列的增加3）索引列不要用NOT4）索引中空值的使用ISNULL,ISNOTNULL5）索引列的数据类型的变换EMP_TYPE为varchar2类型，下列语句使用索引SELECT...FROMempWHEREemp_type=123;
SELECT...FROMempWHERETO_NUMBER(emp_type)=123;SELECT...FROMempWHEREemp_type='123';
2.2增加索引
增加索引要考虑多种因素，要考虑对更新、插入的影响等。1）频繁查询不超过大表1%~15%的场合。2）列的不同值3）列4）列5）同表
7
