ORACLE基于SQL查询优化培训文档

ORACLE基于SQL查询优化培训文档2013年11月查询优化基本准则详解2ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理。在

FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。例如：表TAB116384条记录表TAB21条记录选择TAB2作为基础表(最好的方法)selectcount(*)fromtab1，tab2执行时间0.96秒选择TAB2作为基础表(不佳的方法)selectcount(*)fromtab2，tab1执行时间26.09秒查询优化基本准则详解3ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。例如：(低效，执行时间156.3秒)SELECT…FROMEMPEWHERESAL>50000ANDJOB=‘MANAGER'AND25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效,执行时间10.6秒)SELECT…FROMEMPEWHERE25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL>50000ANDJOB=‘MANAGER';在进行多表关联时，多用Where语句把单个表的结果集最小化，多用聚合函数汇总结果集后再与其它表做关联，以使结果集数据量最小化查询优化基本准则详解4减少对表的查询在含有子查询的SQL语句中，要特别注意减少对表的查询例如：低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)高效SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604)查询优化基本准则详解5用EXISTS替代IN在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOTEXISTS)通常将提高查询的效率。使用exists而不用IN因为Exists只检查行的存在，而in检查实际值例如：低效SELECT*FROMEMP(基础表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB')高效SELECT*FROMEMP(基础表)WHEREEMPNO>0ANDEXISTS(SELECT‘X'FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB')用IN的SQL性能总是比较低，原因是：对于用IN的SQL语句ORACLE总是试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录如果转换成功就转换成多个表的连接。因此不管理怎么，用IN的SQL语句总是多了一个转换的过程。因此在业务密集的SQL当中尽量不采用IN操作符。查询优化基本准则详解6用NOTEXISTS替代NOTIN在子查询中，NOTIN子句将执行一个内部的排序和合并。无论在哪种情况下，NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOTIN，我们可以把它改写成外连接(OuterJoins)或NOTEXISTS.例如：SELECT…FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT='A');为了提高效率。改写为：SELECT….FROMEMPEWHERENOTEXISTS(SELECT‘X'FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A');NOTIN操作符此操作强烈推荐不使用，因为其不能应用表的索引。如遇这种情况，应该用EXISTS，NOTEXISTS方案代替。查询优化基本准则详解7用EXISTS替换DISTINCT当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在

SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换例如：低效：SELECTDISTINCTDEPT_NO,DEPT_NFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO高效：SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X'FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);查询优化基本准则详解8用表连接替换EXISTS通常来说，采用表连接的方式比EXISTS更有效率例如：SELECTENAMEFROMEMPEWHEREEXISTS(SELECT‘X'FROMDEPTWHEREDEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A');为了提高效率。改写为：SELECTENAMEFROMDEPTD,EMPEWHEREE.DEPT_NO=D.DEPT_NOANDDEPT_CAT=‘A';查询优化基本准则详解9避免在索引列上使用计算WHERE子句中，如