ORACLE数据库SQL优化总结_培训

ORACLE数据库SQL优化总结一、优化规则1.正确建立索引，避免大表FULLTABLESCAN：1)比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的”唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高，在特定情况下,使用索引也许会比全表扫描慢,但这是同一个数量级上的区别.而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!2)如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引（执行查询时条件中使用到唯一性索引列时）；3)过程中涉及的数据量大表或试图，对于使用频繁地关联字段需要建立索引，如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引，这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引,组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片；4)避免在索引列上使用函数，WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．例如：条件中TRIM，TO_CHAR等函数的使用，因其对每条记录做处理所以非常耗时；如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。5)避免在索引列上使用计算，优化器将不使用索引而使用全表扫描6)避免在索引列上使用NOT,NOT会产生在和在索引列上使用函数相同的影响；7)避免使用前置通配符（%），WHERE子句中,如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始,索引将不被采用；8)避免在索引列上使用ISNULL和ISNOTNULL，因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引；9)避免出现索引列自动转换，当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换.，因为内部发生的类型转换,这个索引将不会被用到；10)任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度；11)对于有联接（“||”）的列，即使最后的联接值为一个静态值，优化器是不会使用索引的；A.JH||CXHNOTIN(SELECTJH||SJCXHFROMADCN442WHERERQ=P_RQ12)用=替代，通过使用=、=等，避免使用NOT命令；13)合理使用索引,一张表索引不要太多虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的Insert,Delete,Update将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的：AlterINDEXINDEXNAMEREBUILDTABLESPACENAME2.查询尽量用确定的列名，少用*号；3.避免复杂的多表关联；4.在查询时尽量少用格式转换；5.使用DECODE来减少处理时间；6.使用表的别名(Alias)：当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。7.ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：select*fromempe,deptdwhered.deptno10ande.deptno=30;8.避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.WHEREGROUPHAVING；低效:SelectJOB,AVG(SAL)FROMEMPGROUPJOBHAVINGJOB=‘PRESIDENT'orJOB=‘MANAGER'高效:SelectJOB,AVG(SAL)FROMEMPWhereJOB=‘PRESIDENT'orJOB=‘MANAGER'GROUPJOB9.用EXISTS代替IN（全表扫描）、用NOTEXISTS替代NOTIN（全表扫描）两种情况都不会使用索引，但NOTEXISTS效率较高SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN(SELECTdeptnoFROMemp);SELECTdname,deptnoFROMdeptWHERENOTEXISTS(SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno);IN写法相似10.用innerjoin..ON代替表的直接连接SELECTA.WELL_COMMON_NAMEFROMCY_DB_DAILY_YTLA,CY_DB_DAILY_YTLBWHEREA.OPC_ID=B.OPC_ID；SELECTA.WELL_COMMON_NAMEFROMCY_DB_DAILY_YTLAinnerjoinCY_DB_DAILY_YTLBONA.OPC_ID=B.OPC_ID11.COUNT(索引列)count(*)count(1)效率最高的为COUNT(索引列)；12.避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.,通常,带有UNION（联集）,MINUS（差集）,INTERSECT（交集）的SQL语句都可以用其他方式重写;用EXISTS替换DISTINCT（注意只能在多表关联查询时使用），EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果低效:SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效:SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X’FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);用UNION-ALL替换UNION13.最高效的删除重复记录方法DeleteFROMEMPEWhereE.ROWID(SelectMIN(X.ROWID)FROMEMPXWhereX.EMP_NO=E.EMP_NO);SQL调整的目标1.去掉不必要的大型表的全表扫描。2.缓存小型表的全表扫描。3.校验优化索引的使用。4.检验优化的连接技术。以上目标任务将占据SQL调整90%以上的工作。通过F5查看到的执行计划，其实是pl/sqldeveloper工具内部执行查询plan_table表然后格式化的结果。select*fromplan_tablewherestatement_id＝'...'。其中Description列描述当前的数据库操作，Objectowner列表示对象所属用户，Objectname表示操作的对象，Cost列表示当前操作的代价（消耗），这个列基本上就是评价SQL语句的优劣，Cardinality列表示操作影响的行数，Bytes列表示字节数二、应用实例实例一MERGEINTO多行合并CYCJXZY_SCK.KSP_DDCD14优化前语句：UPDATEDDCD14ASETZCYL=(SELECTZBJEFROMDDCD16WHEREXMMS='正常压裂'ANDCS_ID=A.CS_IDANDDW=A.DW),DXYL=(SELECTZBJEFROMDDCD16WHEREXMMS='大型压裂'ANDCS_ID=A.CS_IDANDDW=A.DW),FCYL=(SELECTZBJEFROMDDCD16WHEREDW=A.DWANDCS_ID=A.CS_IDANDXMMS='分层压裂'),QTYL=(SELECTZBJEFROMDDCD16WHEREDW=A.DWANDCS_ID=A.CS_IDANDXMMS='其它压裂'),优化后语句：MERGEINTOCYCJXZY_SCK.DDCD14AUSING(SELECTDW,CS_ID,sum(decode(XMMS,'正常压裂',ZBJE))asZCYL,sum(decode(XMMS,'大型压裂',ZBJE))asDXYL,sum(decode(XMMS,'设计费',ZBJE))asSJF,sum(decode(XMMS,'压裂液',ZBJE))asYLY,sum(decode(XMMS,'石英砂',ZBJE))asSYC,sum(decode(XMMS,'其他',decode(JSXM,'其他',ZBJE)))asQT,FROM(SELECTZBJE,XMMS,CS_ID,DWFROMCYCJXZY_SCK.DDCD16)GROUPBYDW,CS_ID)BON(A.DW=B.DWANDA.CS_ID=B.CS_ID)WHENMATCHEDTHENUPDATESETA.ZCYL=B.ZCYL,A.DXYL=B.DXYL,A.SJF=B.SJF,A.YLY=B.YLY,A.SY=B.SYC,A.QT=B.QT;实例二插入加速，不查找空闲块，直接从表最后记录后插入CYCCYGC.SJTJ_GCDJ_CYC--插入基础数据INSERT/*+APPEND*/INTOWZTING.TEMP_GCDJYB(NY,QKDM,QK,CWDM)SELECTP_NY,A.QKDM,A.QK,A.CWDMFROMCYCCYGC.DGC03AWHERENY=P_NY;实例三分析函数替代普通函数CYCCYGC.SJTJ_GCDJ_CYC优化前：UPDATEWZTING.TEMP_GCDJYBASETGGXH=(SELECTDISTINCTTRIM(GGXH)FROM(SELECTA.JHDM,A.QKDM,A.NY,GGXHFROMCYCCYGC.DGB01A,(SELECTMAX(NY)NY,QKDM,JHDMFROMCYCCYGC.DGB01GROUPBYQKDM,JHDM)BWHEREA.JHDM=B.JHDMANDA.NY=B.NYANDA.QKDM=B.QKDM)BWHEREA.JHDM=B.JHDMANDA.QKDM=B.QKDM)WHEREA.NY=P_NY;优化后：MERGEINTOWZTING.TEMP_GCDJYBAUSING(SELECTGGXH,JHDM,MAXNY,QKDMFROM(SELECTROW_NUMBER()OVER(PARTITIONBYJHDMORDERBYNYDESC)MAXNY,T.*FROMCYCCYGC.DGB01T)WHEREMAXNY=1)BON(A.JHDM=B.JHDMANDA.QKDM=B.QKDMANDA.NY=P_NY)WHENMATCHEDTHENUPDATESETGGXH=B.GGXH;实例四分析函数求最大值替换||；查询空值放最后NULLSLASTCYCCYGC.SJTJ_GCDJ_CYCUPDATEWZTING.TEMP_GCDJYBASETBMC=(SELECTBMCFROMDGB02BWHEREA.JHDM=B.JHDMANDB.NY=P_NYANDB.SBZK='1'ANDB.JHDM|