2023年-优化sql语句提高oracle执行效率34种方法

选择最有效率的表名顺序只在基于规则的优化器中有效1的解析器按照从右到左的顺序处理子句中的表名，子句中写在最后的表基Oracle FROMFROM础表将被最先处理，在driving table子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表如果有个以FROM3上的表连接查询，那就需要选择交叉表作为基础表，交叉表是指那个被其他表所引用的表intersection table子句中的连接顺序2WHERE采用自下而上的顺序解析子句，根据这个原理,表之间的连接必须写在其他Oracle WHERE条件之前，那些可以过滤掉最大数量记录WHERE的条件必须写在子句的末尾WHERE子句中避免使用*3SELECT在解析的过程中，会将依次转换成所有的列名，这个工作是通过查询数据字典完成的，Oracle这意味着将耗费更多的时间减少访问数据库的次数4在内部执行了许多工作解析语句，估算索引的利用率，绑定变量，读数据块等Oracle SQL在和中重新设置参数，可以增加每次数据库访5SQL*Plus,SQL*Forms Pro*C ARRAYSIZE问的检索数据量，建议值为200使用函数来减少处理时间6DECODE使用函数可以避免重复扫描相同记录或重复连接相同的表DECODE整合简单，无关联的数据库访问7如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中即使它们之间没有关系删除重复记录8最高效的删除重复记录方法因为使用了例子ROWIDDELETE FROM EMP EWHERE E.ROWIDSELECT MINX.ROWID注意当字符和数值比较时,会优先转换数值类型到字符类型Oracle需要当心的子句31WHERE某些语句中的子句不使用索引这里有一些例子SELECT WHERE将不使用索引记住，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表1’!=’中『是字符连接函数就象其他函数那样，停用了索引2’4是数学函数就象其他数学函数那样，停用了索引3相同的索引列不能互相比较，这将会启用全表扫描4如果检索数据量超过的表中记录数.使用索引将没有显著的效率提高在特定情32a.30%b.况下，使用索引也许会比全表扫描慢，但这是同一个数量级上的区别而通常情况下，使用索引比全表扫描要块几倍乃至几千倍！避免使用耗费资源的操作33带有的语句会启动引擎执行耗DISTINCT,UNION,MINUS,INTERSECT,ORDER BYSQL SQL费资源的排序功能需要一次排序SORT DISTINCT操作，而其他的至少需要执行两次排序.通常，带有的语句UNION,MINUS,INTERSECT SQL都可以用其他方式重写.如果你的数据库的调配得好，使用也是可以考虑的，毕竟它SORT_AREA_SIZE UNION,MINUS,INTERSECT们的可读性很强优化34GROUP BY:提高语句的效率，可以通过将不需要的记录在之前过滤掉下面两个GROUP BYGROUP BY查询返回相同结果但第二个明显就快了许多低效SELECT JOB,AVGSALFROM EMPGROUPJOBHAVING JOB=PRESIDENTOR JOB=MANAGER高效SELECT JOB,AVGSALFROM EMPWHEREJOB=PRESIDENTOR JOB=MANAGERGROUP JOBFROM EMP XWHERE X.EMP_NO=E.EMP_NO;用替代9TRUNCATE DELETE当删除表中的记录时，在通常情况下，回滚段用来存放可以被恢复的信息.rollback segments如果你没有事务会COMMIT QRACLE将数据恢复到删除之前的状态准确地说是恢复到执行删除命令之前的状况而当运用时，回滚段不再存放任何可被恢复的TRUNCATE信息当命令运行后，数据不能被恢复.因此很少的资源被调用，执行时间也会很短只在册除全表适用,是不TRUNCATE UTRUNCATE DDL是DML尽量多使用10COMMIT只要有可能，在程序中尽量多使用这样程序的性能得到提高,需求也会因为COMMIT,所释放的资源而减少，所释放的资COMMIT COMMIT源回滚段上用于恢复数据的信息a.被程序语句获得的锁b.中的空间c.redo logbuffer为管理上述种资源中的内部花费d.Oracle3学习-SQL用子句替换子句11Where HAVING避免使用子句，只会在检索出所有记录之后才对结果集进行过滤这个处HAVING HAVING理需要排序，总计等操作.如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销非中、这三个都可oracle on where having以加条件的子句中，是最先执行，on次之，最后，因为是先把不符合条件的记录过滤后才进行统计，它就可以减少where havingon中间运算要处理的数据，按理说应该速度是最快的，也应该比快点的，因为它过where having滤数据后才进行在两个表联接时才用的，所以在一个表的时候，就剩下sum,on跟比较了在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字where having段，那它们的结果是一样的，只是可以使用技术，而就不能，在速度上where rushmorehaving后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，的作用时间是在计算之前就完成的，而就是在计where having算后才起作用的，所以在这种情况下，两者的结果会不同在多表联接查询时，比更早起作用系统首先根据各个表之间的onwhere联接条件，把多个表合成一个临时表后，再由进行过滤，然后再计算，计算完后再由进行过滤由此可见，要想过滤条where having件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里回复2009-7-2909:49视学中国位粉丝6楼2减少对表的查询12在含有子查询的语句中，要特别注意减少对表的查询例子SQLSELECT TAB_NAME FROMTABLES WHERETAB_NAME,DB_VER=SELECTTAB_NAME,DB_VER FROMTAB_COLUMNS WHEREVERSION=604通过内部函数提高效率13SQL复杂的往往牺牲了执行效率能够掌握上面的运用函数解决问题的方法在实际工作中是非SQL常有意义的使用表的别名14Alias当在语句中连接多个表时，请使用表的别名并把别名前缀于每个上这样一来,就SQL Column可以减少解析的时间并减少那些由歧义引起的语法错误Column用替代、用替代15EXISTS INNOT EXISTS NOT IN在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接在这种情况下，使用或通常将提EXISTSNOTEXISTS高查询的效率在子查询中,子句将执行一个内部的排序和合并无论在哪种情况NOT IN下,都是最低效的因为它对子查询中NOTIN的表执行了一个全表遍历为了避免使用我们可以把它改写成外连接或NOT IN,Outer JoinsNOTEXISTSo例子高效基础表SELECT*FROM EMPWHERE EMPNO0AND EXISTSSELECT XFROM DEPTWHERE DEPT.DEPTNO=EMP.DEPTNO ANDLOC=MELB■氐效基石出表SELECT*FROM EMPWHERE EMPNO0AND DEPTNOINSELECT DEPTNOFROM DEPT WHERE LOC=MELB识别低效执行’的语句16SQL虽然目前各种关于优化的图形化工具层出不穷，但是写出自己的工具来解决问题始终SQL SQL是一个最好的方法SELECT EXECUTIONS,DISK_READS,BUFFER_GETS,ROUNDBUFFER_GETS-DISK_READS/BUFFER_GETS,2Hit_radio,ROUNDDISK_READS/EXECUTIONS,2Reads_per_run,SQL_TEXTFROM V$SQLAREAWHERE EXECUTIONS0AND BUFFER_GETS0AND BUFFER_GETS-DISK_READS/BUFFER_GETS

0.8ORDER BY4DESC;用索引提高效率17索引是表的一个概念部分，用来提高检索数据的效率使用了一个复杂的自平衡结,Oracle B-tree构通常，通过索引查询数据比全表扫描要快当找出执行查询和语句的最佳路径时,优化器将使用索引同Oracle UpdateOracle样在联结多个表时使用索引也可以提高效率另一个使用索引的好处是,它提供了主键的唯一，性验证那些或primary keyLONG数据类型，你可以索引几乎所有的LONG RAW列通常，在大型表中使用索引特别有效.当然,你也会发现，在扫描小表时，使用索引同样能提高效率虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改这意味着每条记录的将为此多付出次的INSERT,DELETE,UPDATE4,5磁盘因为索引需要额外的存储空间I/O o和处理，那些不必要的索引反而会使查询反应时间变慢定期的重构索引是有必要的ALTER INDEXINDEXNAME REBUILDTABLESPACENAME回复2009-7-2909:49视学中国位粉丝6楼3用替换18EXISTS DISTINCT当提交一个包含一对多表信息、比如部门表和雇员表的查询时,避免在子句中使用SELECT一般可以考虑用替换,DISTINCTo EXIST使查询更为迅速，因为核心模块将在子查询的条件一旦满足后，立刻返回结果EXISTS RDBMS例子低效SELECT DISTINCTDEPT_NO,DEPT_NAME FROM DEPT D,EMP EWHERED.DEPT_NO=E.DEPT_NO高效SELECT DEPT_NO,DEPT_NAME FROM DEPT DWHERE EXISTSSELECT XFROM EMP EWHEREE.DEPT_NO=D.DEPT_NO;语句用大写的；因为总是先解析语句，把小写的字母转换成大写的再执19SQL Oracle SQL行在代码中尽量少用连接符+”连接字符串20Java避免在索引列上使用通常，我们要避免在索引列上使用会产生在和在索21NOT NOT,NOT引列上使用函数相同的影响当Oracle遇到”他就会停止使用索引转而执行全表扫描NOT,避免在索引列上使用计算子句中，如果索引列是函数的一部分优化器将不使22WHERE用索引而使用全表扫描举例低效SELECT...FROMDEPTWHERE SAL*1225000;高效SELECT...FROMDEPTWHERE SAL25000/12;用〉=替代〉23高效SELECT*FROM EMPWHERE DEPTNO=4低效SELECT*FROMEMPWHERE DEPTNO3两者的区别在于，前者将直接跳到第一个等于的记录而后者将首先定位到DBMS DEPT4的记录并且向前扫描到第一个大于DEPTNO=3DEPT3的记录用替换适用于索引列24UNION OR通常情况下，用替换子句中的将会起到较好的效果对索引列使用将UNION WHEREOR OR造成全表扫描注意，以上规则只针对多个索引列有效.如果有没有被索引，查询效率可能会因为你没有选择而降低在下面的例column OR子中,」和上都建有索引LOC DREGION高效SELECT LOCJD,LOC_DESC,REGIONFROM LOCATION」WHERE LOCD=10UNIONSELECT LOCJD,LOC_DESC,REGIONFROM LOCATIONWHEREREGION=MELBOURNE”低效SELECT LOCJD,LOC_DESC,REGIONFROM LOCATIONWHERE LOCJD=10OR REGION=MELBOURNE”用来替换25IN OR这是一条简单易记的规则，但是实际的执行效果还须检验，在下，两者的执行路径似Oracle8i乎是相同的低效SELECT....FROM LOCATIONWHERE LOCJD=10OR LOCJD=20OR LOCJD=30高效SELECT...FROM LOCATIONWHERELOC_IN IN10,20,30;避免在索引列上使用和26IS NULLIS NOT NULL避免在索引中使用任何可以为空的列，将无法使用该索引对于单列索引，如果列包含Oracle空值，索引中将不存在此记录对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中举例如果唯一性索引建立在表的列和列上，并且表中存在一条记录的值为将不接受下A BA,B123,null,Oracle一条具有相同值的记A,B123,null录插入然而如果所有的索引列都为空，将认为整个键值为空而空不等于空因此你Oracle可以插入条具有相同键值的记录1000，当然它们都是空！因为空值不存在于索引列中，所以子句中对索引列进行空值比较将WHERE使停用该索引ORACLE回复2009-7-2909:49视学中国位粉丝6楼4低效:索引失效SELECT...FROM DEPARTMENTWHERE DEPTCODE ISNOTNULL;高效:索引有效SELECT...FROM DEPARTMENTWHEREDEPTCODE=0;总是使用索引的第一个列27如果索引是建立在多个列上，只有在它的第一个列被子句引用时，优化器leading columnwhere才会选择使用该索引这也是一条简单而重要的规则，当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引用替换如果有可能的话:28UNION-ALL UNION当语句需要两个查询结果集合时，这两个结果集合会以的方式被合SQL UNIONUNION-ALL并，然后在输出最终结果前进行排序如果用替代这样排序就不是必要了效率就会因此得到提高需要注意的是，UNION ALLUNION,将重UNIONALL复输出两个结果集合中相同记录因此各位还是要从业务需求分析使用的可行UNION ALLft.将对结果集合排序,这个操作会使UNION用到这块内存对于这块内存的优化也是相当重要的下面的可以用SORT_AREA_SIZE SQL来查询排序的消耗量低效SELECT ACCTNUM,BALANCE AMTFROM DEBIT_TRANSACTIONS WHERETRAN_DATE=31-DEC-951UNIONSELECT ACCT_NUM,BALANCE.AMTFROM DEBIT_TRANSACTIONS WHERE高效TRAN_DATE=31-DEC-951SELECT ACCT_NUM,BALANCE_AMTFROM DEBIT_TRANSACTIONS WHERETRAN.DATE=31-DEC-951UNION ALLSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONS WHERETRANDATE=31-DEC-951用替代29WHERE ORDER BY子句只在两种严格的条件下使用索引ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序ORDER BY中所有的列必须定义为非空ORDERBY子句使用的索引和子句中所使用的索引不能并列WHERE ORDERBY例如表包含以下列DEPTDEPT_CODE PKNOT NULLDEPT_DESC NOTNULLDEPT_TYPE NULL低效索引不被使用SELECT DEPT_CODE FROMDEPT ORDERBY DEPT_TYPE高效:使用索引SELECT DEPT_CODE FROMDEPTWHEREDEPT_TYPE0避免改变索引列的类型30当比较不同数据类型的数据时,自动对列进行简单的类型转换假设是一个ORACLE EMPNO数值类型的索引列SELECT…FROMEMP实际上，经过类型转换，语句转化为WHERE EMPNO=123Oracle SELECT…FROM EMPWHEREEMPNO=TO_NUMBER“23幸运的是，类型转换没有发生在索引列上，索引的用途没有被改变现在，假设是EMP_TYPE一个字符类型的索引列SELECT…FROMEMP WHEREEMP_TYPE=123这个语句被转换为Oracle SELECT…FROMEMP因为内部发生的类型转换，这个索引将不会被用WHERETO_NUMBEREMP_TYPE=123到！为了避免对你的进行隐式的类型转换，最好把类型转换用显式表现出来OracleSQL。