oracle语句优化53个新版规则详解

oracle优化1.选用适合ORACLE优化器ORACLE优化器共有3种：a.RULE（基于 规则 编码规则下载淘宝规则下载天猫规则下载麻将竞赛规则pdf麻将竞赛规则pdf ）b.COST（基于成本）c.CHOOSE（选取性）设立缺省优化器，可以通过对init.ora文献中OPTIMIZER_MODE参数各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS.你固然也在SQL句级或是会话（session）级对其进行覆盖。为了使用基于成本优化器（CBO，Cost-BasedOptimizer），你必要经常运营analyze命令，以增长数据库中对象记录信息（objectstatistics）精确性。如果数据库优化器模式设立为选取性（CHOOSE），那么实际优化器模式将和与否运营过analyze命令关于。如果table已经被analyze过，优化器模式将自动成为CBO，反之，数据库将采用RULE形式优化器。在缺省状况下，ORACLE采用CHOOSE优化器，为了避免那些不必要全表扫描（fulltablescan），你必要尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本优化器。2.访问Table方式ORACLE采用两种访问表中记录方式：a.全表扫描全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入各种数据块（databaseblock）方式优化全表扫描。b.通过ROWID访问表你可以采用基于ROWID访问方式状况，提高访问表效率，ROWID包括了表中记录物理位置信息……ORACLE采用索引（INDEX）实现了数据和存储数据物理位置（ROWID）之间联系。普通索引提供了迅速访问ROWID办法，因而那些基于索引列查询就可以得到性能上提高。3.共享SQL语句为了不重复解析相似SQL语句，在第一次解析之后，ORACLE将SQL语句存储在内存中。这块位于系统全局区域SGA（systemglobalarea）共享池（sharedbufferpool）中内存可以被所有数据库顾客共享。因而，当你执行一种SQL语句（有时被称为一种游标）时，如果它和之前执行过语句完全相似，ORACLE就能不久获得已经被解析语句以及最佳执行途径。ORACLE这个功能大大地提高了SQL执行性能并节约了内存使用。可惜是ORACLE只对简朴表提供高速缓冲（cachebuffering），这个功能并不合用于多表连接查询。数据库管理员必要在init.ora中为这个区域设立适当参数，当这个内存区域越大，就可以保存更多语句，固然被共享也许性也就越大了。当你向ORACLE提交一种SQL语句，ORACLE会一方面在这块内存中查找相似语句。这里需要注明是，ORACLE对两者采用是一种严格匹配，要达到共享，SQL语句必要完全相似（涉及空格，换行等）。共享语句必要满足三个条件：A.字符级比较：当前被执行语句和共享池中语句必要完全相似。例如：SELECT*FROMEMP；和下列每一种都不同SELECT*fromEMP；Select*FromEmp；SELECT*FROMEMP；B.两个语句所指对象必要完全相似：例如：顾客对象名如何访问Jacksal_limitprivatesynonym　　　　Work_citypublicsynonym　　　　Plant_detailpublicsynonym　Jillsal_limitprivatesynonym　　　　Work_citypublicsynonym　　　　Plant_detailtableowner　考虑一下下列SQL语句能否在这两个顾客之间共享。SQL能否共享因素selectmax(sal_cap)fromsal_limit；不能每个顾客均有一种privatesynonym-sal_limit，它们是不同对象selectcount(*0fromwork_citywheresdesclike'NEW%'；能两个顾客访问相似对象publicsynonym-work_cityselecta.sdesc,b.locationfromwork_citya，plant_detailbwherea.city_id=b.city_id不能顾客jack通过privatesynonym访问plant_detail而jill是表所有者,对象不同.C.两个SQL语句中必要使用相似名字绑定变量（bindvariables）例如：第一组两个SQL语句是相似（可以共享），而第二组中两个语句是不同（虽然在运营时，赋于不同绑定变量相似值）a.　selectpin，namefrompeoplewherepin=：blk1.pin；　selectpin，namefrompeoplewherepin=：blk1.pin；　b.　selectpin，namefrompeoplewherepin=：blk1.ot_ind；　selectpin，namefrompeoplewherepin=：blk1.ov_ind；　4.选取最有效率表名顺序（只在基于规则优化器中有效）ORACLE解析器按照从右到左顺序解决FROM子句中表名，因而FROM子句中写在最后表（基本表drivingtable）将被最先解决。在FROM子句中包括各种表状况下，你必要选取记录条数至少表作为基本表。当ORACLE解决各种表时，会运用排序及合并方式连接它们。一方面，扫描第一种表（FROM子句中最后那个表）并对记录进行派序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出记录与第一种表中适当记录进行合并。例如：表TAB116，384条记录表TAB21条记录选取TAB2作为基本表（最佳办法）selectcount（*）fromtab1，tab2执行时间0.96秒选取TAB2作为基本表（不佳办法）selectcount（*）fromtab2，tab1执行时间26.09秒如果有3个以上表连接查询，那就需要选取交叉表（intersectiontable）作为基本表，交叉表是指那个被其她表所引用表。例如：EMP表描述了LOCATION表和CATEGORY表交集。SELECT*FROMLOCATIONL，CATEGORYC,EMPEWHEREE.EMP_NOBETWEEN1000ANDANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN将比下列SQL更有效率SELECT*FROMEMPE,LOCATIONL，CATEGORYCWHEREE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCNANDE.EMP_NOBETWEEN1000AND5.WHERE子句中连接顺序。ORACLE采用自下而上顺序解析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’;　6.SELECT子句中避免使用‘*’　当你想在SELECT子句中列出所有COLUMN时，使用动态SQL列引用‘*’是一种以便办法。不幸是，这是一种非常低效办法。事实上，ORACLE在解析过程中，会将‘*’依次转换成所有列名，这个工作是通过查询数据字典完毕，这意味着将耗费更多时间。7.减少访问数据库次数　当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引运用率，绑定变量，读数据块等等。由此可见，减少访问数据库次数，就能事实上减少ORACLE工作量。例如，如下有三种办法可以检索出雇员号等于0342或0291职工。办法1（最低效）SELECTEMP_NAME，SALARY，GRADEFROMEMPWHEREEMP_NO=342;SELECTEMP_NAME，SALARY，GRADEFROMEMPWHEREEMP_NO=291;办法2（次低效）DECLARECURSORC1(E_NONUMBER)ISSELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=E_NO;BEGINOPENC1(342);FETCHC1INTO…,..,..;OPENC1(291);FETCHC1INTO…,..,..;CLOSEC1;END;办法3（高效）SELECTA.EMP_NAME，A.SALARY，A.GRADE,B.EMP_NAME，B.SALARY，B.GRADEFROMEMPA,EMPBWHEREA.EMP_NO=342ANDB.EMP_NO=291;　注意：　在SQL*Plus，SQL*Forms和Pro*C中重新设立ARRAYSIZE参数，可以增长每次数据库访问检索数据量，建议值为200.　8.使用DECODE函数来减少解决时间　使用DECODE函数可以避免重复扫描相似记录或重复连接相似表。例如：SELECTCOUNT(*)，SUM(SAL)FROM　EMPWHEREDEPT_NO=0020ANDENAMELIKE　‘SMITH%’;SELECTCOUNT(*)，SUM(SAL)FROM　EMPWHEREDEPT_NO=0030ANDENAMELIKE　‘SMITH%’;　你可以用DECODE函数高效地得到相似成果SELECTCOUNT(DECODE(DEPT_NO,0020,’X’,NULL))D0020_COUNT,COUNT(DECODE(DEPT_NO,0030,’X’,NULL))D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,NULL))D0020_SAL,SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030_SALFROMEMPWHEREENAMELIKE‘SMITH%’;　类似，DECODE函数也可以运用于GROUPBY和ORDERBY子句中。9.整合简朴，无关联数据库访问　如果你有几种简朴数据库查询语句，你可以把它们整合到一种查询中（虽然它们之间没关于系）例如：SELECTNAMEFROMEMPWHEREEMP_NO=1234;SELECTNAMEFROMDPTWHEREDPT_NO=10;SELECTNAMEFROMCATWHERECAT_TYPE=‘RD’;　上面3个查询可以被合并成一种：SELECTE.NAME，D.NAME，C.NAMEFROMCATC，DPTD，EMPE,DUALXWHERENVL(‘X’,X.DUMMY)=NVL(‘X’,E.ROWID(+))ANDNVL(‘X’,X.DUMMY)=NVL(‘X’,D.ROWID(+))ANDNVL(‘X’,X.DUMMY)=NVL(‘X’,C.ROWID(+))ANDE.EMP_NO(+)=1234ANDD.DEPT_NO(+)=10ANDC.CAT_TYPE(+)=‘RD’;（译者按：虽然采用这种办法，效率得到提高，但是程序可读性大大减少，因此读者还是要权衡之间利弊）10.删除重复记录　最高效删除重复记录办法（由于使用了ROWID）DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);　11.用TRUNCATE代替DELETE　当删除表中记录时，在普通状况下，回滚段（rollbacksegments）用来存储可以被恢复信息。如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前状态（精确地说是恢复到执行删除命令之前状况）而当运用TRUNCATE时，回滚段不再存储任何可被恢复信息。当命令运营后，数据不能被恢复。因而很少资源被调用，执行时间也会很短。（译者按：TRUNCATE只在删除全表合用，TRUNCATE是DDL不是DML）12.尽量多使用COMMIT　只要有也许，在程序中尽量多使用COMMIT，这样程序性能得到提高，需求也会由于COMMIT所释放资源而减少：COMMIT所释放资源：　a.回滚段上用于恢复数据信息。b.被程序语句获得锁　c.redologbuffer中空间　d.ORACLE为管理上述3种资源中内部耗费　（译者按：在使用COMMIT时必要要注意到事务完整性，现实中效率和事务完整性往往是鱼和熊掌不可得兼）13.计算记录条数　和普通观点相反，count（*）比count（1）稍快，固然如果可以通过索引检索，对索引列计数仍旧是最快。例如COUNT（EMPNO）（译者按：在CSDN论坛中，曾经对此有过相称热烈讨论，作者观点并不十分精确，通过实际测试，上述三种办法并没有明显性能差别）14.用Where子句替代HAVING子句　避免使用HAVING子句，HAVING只会在检索出所有记录之后才对成果集进行过滤。这个解决需要排序，总计等操作。如果能通过WHERE子句限制记录数目，那就能减少这方面开销。例如：　低效：SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION!=‘SYDNEY’ANDREGION!=‘PERTH’　高效SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=‘SYDNEY’ANDREGION!=‘PERTH’GROUPBYREGION（译者按：HAVING中条件普通用于对某些集合函数比较，如COUNT（）等等。除此而外，普通条件应当写在WHERE子句中）15.减少对表查询　在具有子查询SQL语句中，要特别注意减少对表查询。例如：　低效SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)AND　DB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604)高效SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_COLUMNSWHEREVERSION=604)Update各种Column例子：　低效：UPDATEEMPSETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;　高效：UPDATEEMPSET(EMP_CAT，SAL_RANGE)=(SELECTMAX(CATEGORY)，MAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;　16.通过内部函数提高SQL效率。SELECTH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)FROMHISTORY_TYPET,EMPE,EMP_HISTORYHWHEREH.EMPNO=E.EMPNOANDH.HIST_TYPE=T.HIST_TYPEGROUPBYH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;　通过调用下面函数可以提高效率。FUNCTIONLOOKUP_HIST_TYPE(TYPINNUMBER)RETURNVARCHAR2ASTDESCVARCHAR2(30);CURSORC1ISSELECTTYPE_DESCFROMHISTORY_TYPEWHEREHIST_TYPE=TYP;BEGINOPENC1;FETCHC1INTOTDESC;CLOSEC1;RETURN(NVL(TDESC,’?’));END;FUNCTIONLOOKUP_EMP(EMPINNUMBER)RETURNVARCHAR2ASENAMEVARCHAR2(30);CURSORC1ISSELECTENAMEFROMEMPWHEREEMPNO=EMP;BEGINOPENC1;FETCHC1INTOENAME;CLOSEC1;RETURN(NVL(ENAME,’?’));END;SELECTH.EMPNO,LOOKUP_EMP(H.EMPNO),H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)FROMEMP_HISTORYHGROUPBYH.EMPNO，H.HIST_TYPE;（译者按：经常在论坛中看到如‘能不能用一种SQL写出…。’贴子，殊不知复杂SQL往往牺牲了执行效率。可以掌握上面运用函数解决问题办法在实际工作中是非常故意义）17.使用表别名（Alias）当在SQL语句中连接各种表时，请使用表别名并把别名前缀于每个Column上。这样一来，就可以减少解析时间并减少那些由Column歧义引起语法错误。（译者注：Column歧义指是由于SQL中不同表具备相似Column名，当SQL语句中浮现这个Column时，SQL解析器无法判断这个Column归属）18.用EXISTS代替IN　在许多基于基本表查询中，为了满足一种条件，往往需要对另一种表进行联接。在这种状况下，使用EXISTS（或NOTEXISTS）普通将提高查询效率。低效：SELECT*FROMEMP(基本表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB’)高效：SELECT*FROMEMP(基本表)WHEREEMPNO>0ANDEXISTS(SELECT‘X’FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB’)（译者按：相对来说，用NOTEXISTS替代NOTIN将更明显地提高效率，下一节中将指出）19.用NOTEXISTS代替NOTIN　在子查询中，NOTIN子句将执行一种内部排序和合并。无论在哪种状况下，NOTIN都是最低效（由于它对子查询中表执行了一种全表遍历）。为了避免使用NOTIN，咱们可以把它改写成外连接（OuterJoins）或NOTEXISTS.　例如：SELECT…FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT=’A’);　为了提高效率。改写为：　（办法一：高效）SELECT….FROMEMPA,DEPTBWHEREA.DEPT_NO=B.DEPT(+)ANDB.DEPT_NOISNULLANDB.DEPT_CAT(+)=‘A’　（办法二：最高效）SELECT….FROMEMPEWHERENOTEXISTS(SELECT‘X’FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A’);　20.用表连接替代EXISTS　普通来说，采用表连接方式比EXISTS更有效率SELECTENAMEFROMEMPEWHEREEXISTS(SELECT‘X’FROMDEPTWHEREDEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A’);　（更高效）SELECTENAMEFROMDEPTD,EMPEWHEREE.DEPT_NO=D.DEPT_NOANDDEPT_CAT=‘A’;　（译者按：在RBO状况下，前者执行途径涉及FILTER，后者使用NESTEDLOOP）21.用EXISTS替代DISTINCT　当提交一种包括一对多表信息（例如部门表和雇员表）查询时，避免在SELECT子句中使用DISTINCT.普通可以考虑用EXIST替代　例如：　低效：SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO　高效：SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X’FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);　EXISTS使查询更为迅速，由于RDBMS核心模块将在子查询条件一旦满足后，立即返回成果。22.辨认‘低效执行’SQL语句　用下列SQL工具找出低效SQL：SELECTEXECUTIONS，DISK_READS，BUFFER_GETS,ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,SQL_TEXTFROMV$SQLAREAWHEREEXECUTIONS>0ANDBUFFER_GETS>0AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8ORDERBY4DESC;　（译者按：虽然当前各种关于SQL优化图形化工具层出不穷，但是写出自己SQL工具来解决问题始终是一种最佳办法）23.使用TKPROF工具来查询SQL性能状态　SQLtrace工具收集正在执行SQL性能状态数据并记录到一种跟踪文献中。这个跟踪文献提供了许多有用信息，例如解析次数。执行次数，CPU使用时间等。这些数据将可以用来优化你系统。设立SQLTRACE在会话级别：　有效　ALTERSESSIONSETSQL_TRACETRUE　设立SQLTRACE在整个数据库有效仿，你必要将SQL_TRACE参数在init.ora中设为TRUE，USER_DUMP_DEST参数阐明了生成跟踪文献目录　（译者按：这一节中，作者并没有提到TKPROF用法，对SQLTRACE用法也不够精确，设立SQLTRACE一方面要在init.ora中设定TIMED_STATISTICS，这样才干得到那些重要时间状态。生成trace文献是不可读，因此要用TKPROF工具对其进行转换，TKPROF有许多执行参数。人们可以参照ORACLE 手册 华为质量管理手册 下载焊接手册下载团建手册下载团建手册下载ld手册下载 来理解详细配备。）24.用EXPLAINPLAN分析SQL语句　EXPLAINPLAN是一种较好分析SQL语句工具，它甚至可以在不执行SQL状况下分析语句。通过度析，咱们就可以懂得ORACLE是怎么样连接表，使用什么方式扫描表（索引扫描或全表扫描）以及使用到索引名称。你需要按照从里到外，从上到下顺序解读分析成果。EXPLAINPLAN分析成果是用缩进格式排列，最内部操作将被最先解读，如果两个操作处在同一层中，带有最小操作号将被一方面执行。NESTEDLOOP是少数不按照上述规则解决操作，对的执行途径是检核对NESTEDLOOP提供数据操作，其中操作号最小将被最先解决。译者按：通过实践，感到还是用SQLPLUS中SETTRACE功能比较以便。举例：SQL>list1SELECT*2FROMdept，emp3*WHEREemp.deptno=dept.deptnoSQL>setautotracetraceonly/*traceonly可以不显示执行成果*/SQL>/14rowsselected.ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10NESTEDLOOPS21TABLEACCESS(FULL)OF'EMP'31TABLEACCESS(BYINDEXROWID)OF'DEPT'43INDEX(UNIQUESCAN)OF'PK_DEPT'(UNIQUE)Statistics----------------------------------------------------------0recursivecalls2dbblockgets30consistentgets0physicalreads0redosize2598bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)14rowsprocessed　通过以上分析，可以得出实际执行环节是：　1.TABLEACCESS（FULL）OF'EMP'　2.INDEX（UNIQUESCAN）OF'PK_DEPT'（UNIQUE）3.TABLEACCESS（BYINDEXROWID）OF'DEPT'　4.NESTEDLOOPS（JOINING1AND3）注：当前许多第三方工具如TOAD和ORACLE自身提供工具如OMSSQLAnalyze都提供了极其以便EXPLAINPLAN工具。也许喜欢图形化界面朋友们可以选用它们。25.用索引提高效率索引是表一种概念某些，用来提高检索数据效率。事实上，ORACLE使用了一种复杂自平衡B-tree构造。普通，通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句最佳途径时，ORACLE优化器将使用索引。同样在联结各种表时使用索引也可以提高效率。另一种使用索引好处是，它提供了主键（primarykey）唯一性验证。除了那些LONG或LONGRAW数据类型，你可以索引几乎所有列。普通，在大型表中使用索引特别有效。固然，你也会发现，在扫描小表时，使用索引同样能提高效率。虽然使用索引能得到查询效率提高，但是咱们也必要注意到它代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引自身也会被修改。这意味着每条记录INSERT，DELETE，UPDATE将为此多付出4，5次磁盘I/O.由于索引需要额外存储空间和解决，那些不必要索引反而会使查询反映时间变慢。译者按：定期重构索引是有必要。ALTERINDEXREBUILD26.索引操作ORACLE对索引有两种访问模式。索引唯一扫描（INDEXUNIQUESCAN）大多数状况下，优化器通过WHERE子句访问INDEX.例如：　表LODGING有两个索引：建立在LODGING列上唯一性索引LODGING_PK和建立在MANAGER列上非唯一性索引LODGING$MANAGER.SELECT*　FROMLODGING　WHERELODGING=‘ROSEHILL’；在内部，上述SQL将被提成两步执行，一方面，LODGING_PK索引将通过索引唯一扫描方式被访问，获得相相应ROWID，通过ROWID访问表方式执行下一步检索。如果被检索返回列涉及在INDEX列中，ORACLE将不执行第二步解决（通过ROWID访问表）。由于检索数据保存在索引中，单单访问索引就可以完全满足查询成果。下面SQL只需要INDEXUNIQUESCAN操作。SELECTLODGING　FROMLODGING　WHERELODGING=‘ROSEHILL’；索引范畴查询（INDEXRANGESCAN）合用于两种状况：　1.基于一种范畴检索　2.基于非唯一性索引检索例1：SELECTLODGINGFROMLODGINGWHERELODGINGLIKE‘M%’；WHERE子句条件涉及一系列值，ORACLE将通过索引范畴查询方式查询LODGING_PK.由于索引范畴查询将返回一组值，它效率就要比索引唯一扫描低某些。例2：SELECTLODGING　FROMLODGING　WHEREMANAGER=‘BILLGATES’；这个SQL执行分两步，LODGING$MANAGER索引范畴查询（得到所有符合条件记录ROWID）和下一步同过ROWID访问表得到LODGING列值。由于LODGING$MANAGER是一种非唯一性索引，数据库不能对它执行索引唯一扫描。由于SQL返回LODGING列，而它并不存在于LODGING$MANAGER索引中，因此在索引范畴查询后会执行一种通过ROWID访问表操作。WHERE子句中，如果索引列所相应值第一种字符由通配符（WILDCARD）开始，索引将不被采用。在这种状况下，ORACLE将使用全表扫描。SELECTLODGING　FROMLODGING　WHEREMANAGERLIKE‘％HANMAN’；27.基本表选取基本表（DrivingTable）是指被最先访问表（普通以全表扫描方式被访问）。依照优化器不同，SQL语句中基本表选取是不同样。如果你使用是CBO（COSTBASEDOPTIMIZER），优化器会检查SQL语句中每个表物理大小，索引状态，然后选用耗费最低执行途径。如果你用RBO（RULEBASEDOPTIMIZER），并且所有连接条件均有索引相应，在这种状况下，基本表就是FROM子句中列在最后那个表。举例：　SELECTA.NAME，B.MANAGER　FROM　WORKERA，　　　　　LODGINGB　WHERE　A.LODGING=B.LODING；　由于LODGING表LODING列上有一种索引，并且WORKER表中没有相比较索引，WORKER表将被作为查询中基本表。28.各种平等索引　当SQL语句执行途径可以使用分布在各种表上各种索引时，ORACLE会同步使用各种索引并在运营时对它们记录进行合并，检索出仅对所有索引有效记录。在ORACLE选取执行途径时，唯一性索引级别高于非唯一性索引。然而这个规则只有当WHERE子句中索引列和常量比较才有效。如果索引列和其她表索引类相比较。这种子句在优化器中档级是非常低。如果不同表中两个想同级别索引将被引用，FROM子句中表顺序将决定哪个会被率先使用。FROM子句中最后表索引将有最高优先级。如果相似表中两个想同级别索引将被引用，WHERE子句中最先被引用索引将有最高优先级。举例：　DEPTNO上有一种非唯一性索引，EMP_CAT也有一种非唯一性索引。SELECTENAME，　FROMEMP　WHEREDEPT_NO=20　ANDEMP_CAT=‘A’；　这里，DEPTNO索引将被最先检索，然后同EMP_CAT索引检索出记录进行合并。执行途径如下：TABLEACCESSBYROWIDONEMP　　AND-EQUAL　　　INDEXRANGESCANONDEPT_IDX　　　INDEXRANGESCANONCAT_IDX29.等式比较和范畴比较　当WHERE子句中有索引列，ORACLE不能合并它们，ORACLE将用范畴比较。举例：　DEPTNO上有一种非唯一性索引，EMP_CAT也有一种非唯一性索引。SELECTENAME　FROMEMP　WHEREDEPTNO>20　ANDEMP_CAT=‘A’；这里只有EMP_CAT索引被用到，然后所有记录将逐条与DEPTNO条件进行比较。执行途径如下：　TABLEACCESSBYROWIDONEMP　　INDEXRANGESCANONCAT_IDX30.不明确索引级别当ORACLE无法判断索引级别高低差别，优化器将只使用一种索引，它就是在WHERE子句中被列在最前面。举例：　DEPTNO上有一种非唯一性索引，EMP_CAT也有一种非唯一性索引。SELECTENAME　FROMEMP　WHEREDEPTNO>20　ANDEMP_CAT>‘A’；这里，ORACLE只用到了DEPT_NO索引。执行途径如下：TABLEACCESSBYROWIDONEMP　　INDEXRANGESCANONDEPT_IDX译者按：咱们来试一下如下这种状况：　SQL>selectindex_name，uniquenessfromuser_indexeswheretable_name='EMP'；INDEX_NAMEUNIQUENES　---------------------------------------EMPNOUNIQUE　EMPTYPENONUNIQUESQL>select*fromempwhereempno>=2andemp_type='A'；norowsselectedExecutionPlan　----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE　10TABLEACCESS（BYINDEXROWID）OF'EMP'　21INDEX（RANGESCAN）OF'EMPTYPE'（NON-UNIQUE）虽然EMPNO是唯一性索引，但是由于它所做是范畴比较，级别要比非唯一性索引等式比较低！31.强制索引失效如果两个或以上索引具备相似级别，你可以强制命令ORACLE优化器使用其中一种（通过它，检索出记录数量少）.举例：SELECTENAME　FROMEMP　WHEREEMPNO=7935　ANDDEPTNO+0=10/*DEPTNO上索引将失效*/　ANDEMP_TYPE||‘’=‘A’/*EMP_TYPE上索引将失效*/这是一种相称直接提高查询效率办法。但是你必要谨慎考虑这种方略，普通来说，只有在你但愿单独优化几种SQL时才干采用它。这里有一种例子关于何时采用这种方略，假设在EMP表EMP_TYPE列上有一种非唯一性索引而EMP_CLASS上没有索引。SELECTENAME　FROMEMP　WHEREEMP_TYPE=‘A’　ANDEMP_CLASS=‘X’；优化器会注意到EMP_TYPE上索引并使用它。这是当前唯一选取。如果，一段时间后来，另一种非唯一性建立在EMP_CLASS上，优化器必要对两个索引进行选取，在普通状况下，优化器将使用两个索引并在她们成果集合上执行排序及合并。然而，如果其中一种索引（EMP_TYPE）接近于唯一性而另一种索引（EMP_CLASS）上有几千个重复值。排序及合并就会成为一种不必要承担。在这种状况下，你但愿使优化器屏蔽掉EMP_CLASS索引。用下面 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 就可以解决问题。SELECTENAME　FROMEMP　WHEREEMP_TYPE=‘A’　ANDEMP_CLASS||‘’=‘X’；32.避免在索引列上使用计算。WHERE子句中，如果索引列是函数一某些。优化器将不使用索引而使用全表扫描。举例：低效：　SELECT…FROMDEPTWHERESAL*12>25000；高效：　SELECT…FROMDEPT　WHERESAL>25000/12；译者按：这是一种非常实用规则，请务必紧记33.自动选取索引　如果表中有两个以上（涉及两个）索引，其中有一种唯一性索引，而其她是非唯一性。在这种状况下，ORACLE将使用唯一性索引而完全忽视非唯一性索引。举例：　SELECTENAME　FROMEMP　WHEREEMPNO=2326　ANDDEPTNO=20；这里，只有EMPNO上索引是唯一性，因此EMPNO索引将用来检索记录。TABLEACCESSBYROWIDONEMP　　INDEXUNIQUESCANONEMP_NO_IDX34.避免在索引列上使用NOT　普通，咱们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相似影响。当ORACLE“遇到”NOT，她就会停止使用索引转而执行全表扫描。举例：低效：（这里，不使用索引）SELECT…FROMDEPT　WHEREDEPT_CODENOT=0；高效：（这里，使用了索引）SELECT…FROMDEPT　WHEREDEPT_CODE>0；需要注意是，在某些时候，ORACLE优化器会自动将NOT转化成相相应关系操作符。NOT>to<=　NOT>=to<　NOT=　NOT<=to>译者按：在这个例子中，作者犯了某些错误。例子中低效率SQL是不能被执行。我做了某些测试：SQL>select*fromempwhereNOTempno>1；　norowsselected　ExecutionPlan　----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE　10TABLEACCESS（BYINDEXROWID）OF'EMP'　21INDEX（RANGESCAN）OF'EMPNO'（UNIQUE）SQL>select*fromempwhereempno<=1；　norowsselected　ExecutionPlan　----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE　10TABLEACCESS（BYINDEXROWID）OF'EMP'　21INDEX（RANGESCAN）OF'EMPNO'（UNIQUE）两者效率完全同样，也许这符合伙者关于“在某些时候，ORACLE优化器会自动将NOT转化成相相应关系操作符”观点。35.用>=代替>如果DEPTNO上有一种索引，高效：SELECT*　FROMEMP　WHEREDEPTNO>=4低效：SELECT*　FROMEMP　WHEREDEPTNO>3两者区别在于，前者DBMS将直接跳到第一种DEPT等于4记录而后者将一方面定位到DEPTNO=3记录并且向前扫描到第一种DEPT不不大于3记录。36.用UNION替代OR（合用于索引列）普通状况下，用UNION替代WHERE子句中OR将会起到较好效果。对索引列使用OR将导致全表扫描。注意，以上规则只针对各种索引列有效。如果有column没有被索引，查询效率也许会由于你没有选取OR而减少。在下面例子中，LOC_ID和REGION上都建有索引。高效：　SELECTLOC_ID，LOC_DESC，REGION　FROMLOCATION　WHERELOC_ID=10　UNION　SELECTLOC_ID，LOC_DESC，REGION　FROMLOCATION　WHEREREGION=“MELBOURNE”低效：　SELECTLOC_ID，LOC_DESC，REGION　FROMLOCATION　WHERELOC_ID=10ORREGION=“MELBOURNE”如果你坚持要用OR，那就需要返回记录至少索引列写在最前面。注意：WHEREKEY1=10（返回至少记录）ORKEY2=20（返回最多记录）ORACLE内部将以上转换为　WHEREKEY1=10AND（（NOTKEY1=10）ANDKEY2=20）译者按：下面测试数据仅供参照：（a=1003返回一条记录，b=1返回1003条记录）SQL>select*fromunionvsor/*1sttest*/　2wherea=1003orb=1；　1003rowsselected.　ExecutionPlan　----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE　10CONCATENATION　21TABLEACCESS（BYINDEXROWID）OF'UNIONVSOR'　32INDEX（RANGESCAN）OF'UB'（NON-UNIQUE）41TABLEACCESS（BYINDEXROWID）OF'UNIONVSOR'　54INDEX（RANGESCAN）OF'UA'（NON-UNIQUE）Statistics　----------------------------------------------------------0recursivecalls　0dbblockgets　144consistentgets　0physicalreads　0redosize　63749bytessentviaSQL*Nettoclient　7751bytesreceivedviaSQL*Netfromclient　68SQL*Netroundtripsto/fromclient　0sorts（memory）0sorts（disk）1003rowsprocessed　SQL>select*fromunionvsor/*2ndtest*/　2whereb=1ora=1003；　1003rowsselected.　ExecutionPlan　----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE　10CONCATENATION　21TABLEACCESS（BYINDEXROWID）OF'UNIONVSOR'　32INDEX（RANGESCAN）OF'UA'（NON-UNIQUE）41TABLEACCESS（BYINDEXROWID）OF'UNIONVSOR'　54INDEX（RANGESCAN）OF'UB'（NON-UNIQUE）Statistics　----------------------------------------------------------0recursivecalls　0dbblockgets　143consistentgets　0physicalreads　0redosize　63749bytessentviaSQL*Nettoclient　7751bytesreceivedviaSQL*Netfromclient　68SQL*Netroundtripsto/fromclient0sorts（memory）0sorts（disk）1003rowsprocessedSQL>select*fromunionvsor/*3rdtest*/　2wherea=1003　3union　4select*fromunionvsor　5whereb=1；　1003rowsselected.ExecutionPlan　----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE　10SORT（UNIQUE）21UNION-ALL　32TABLEACCESS（BYINDEXROWID）OF'UNIONVSOR'　43INDEX（RANGESCAN）OF'UA'（NON-UNIQUE）52TABLEACCESS（BYINDEXROWID）OF'UNIONVSOR'　65INDEX（RANGESCAN）OF'UB'（NON-UNIQUE）Statistics　----------------------------------------------------------0recursivecalls　0dbblockgets　10consistentgets　0physicalreads　0redosize　63735bytessentviaSQL*Nettoclient　7751bytesreceivedviaSQL*Netfromclient　68SQL*Netroundtripsto/fromclient1sorts（memory）0sorts（disk）1003rowsprocessed　用UNION效果可以从consistentgets和SQL*NET数据互换量减少看出37.用IN来替代OR下面查询可以被更有效率语句替代：低效：SELECT…FROMLOCATION　WHERELOC_ID=10　ORLOC_ID=20　ORLOC_ID=30高效：　SELECT…FROMLOCATION　WHERELOC_ININ（10，20，30）；译者按：这是一条简朴易记规则，但是实际执行效果还须检查，在ORACLE8i下，两者执行途径似乎是相似。38.避免在索引列上使用ISNULL和ISNOTNULL　避免在索引中使用任何可觉得空列，ORACLE将无法使用该索引。对于单列索引，如果列包括空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。　如果至少有一种列不为空，则记录存在于索引中。举例：　如果唯一性索引建立在表A列和B列上，并且表中存在一条记录A，B值为（123，null），ORACLE将不接受下一条具备相似A，B值（123，null）记录（插入）。然而如果所有索引列都为空，ORACLE将以为整个键值为空而空不等于空。因而你可以插入1000条具备相似键值记录，固然它们都是空！由于空值不存在于索引列中，因此WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。举例：低效：（索引失效）SELECT…FROMDEPARTMENT　WHEREDEPT_CODEISNOTNULL；高效：（索引有效）SELECT…FROMDEPARTMENT　WHEREDEPT_CODE>=0；　39.总是使用索引第一种列　如果索引是建立在各种列上，只有在它第一种列（leadingcolumn）被where子句引用时，优化器才会选取使用该索引。译者按：这也是一条简朴而重要规则。见如下实例。SQL>createtablemultiindexusage（indanumber，indbnumber，descrvarchar2（10））；　Tablecreated.　SQL>createindexmultindexonmultiindexusage（inda，indb）；　Indexcreated.　SQL>setautotracetraceonlySQL>select*frommultiindexusagewhereinda=1；　ExecutionPlan　----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE　10TABLEACCESS（BYINDEXROWID）OF'MULTIINDEXUSAGE'　21INDEX（RANGESCAN）OF'MULTINDEX'（NON-UNIQUE）SQL>select*frommultiindexusagewhereindb=1；　ExecutionPlan　----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE　10TABLEACCESS（FULL）OF'MULTIINDEXUSAGE'很明显，当仅引用索引第二个列时，优化器使用了全表扫描而忽视了索引40.ORACLE内部操作　当执行查询时，ORACLE采用了内部操作。下表显示了几种重要内部操作。ORACLEClause内部操作ORDERBYSORTORDERBYUNIONUNION-ALLMINUSMINUSINTERSECTINTERSECTDISTINCT,MINUS,INTERSECT,UNIONSORTUNIQUEMIN,MAX,COUNTSORTAGGREGATEGROUPBYSORTGROUPBYROWNUMCOUNTorCOUNTSTOPKEYQueriesinvolvingJoinsSORTJOIN,MERGEJOIN,NESTEDLOOPSCONNECTBYCONNECTBY41.用UNION-ALL替代UNION（如果有也许话）当SQL语句需要UNION两个查询成果集合时，这两个成果集合会以UNION-ALL方式被合并，然后在输出最后成果迈进行排序。如果用UNIONALL代替UNION，这样排序就不是必要了。效率就会因而得到提高。举例：　低效：　SELECTACCT_NUM，BALANCE_AMT　FROMDEBIT_TRANS