oracle语句优化53个规则详解

1、oracle优化1. 选用适合合的ORACCLE优化器器 ORACCLE的优化化器共有3种种：a. RULEE （基于规规则） b. COSTT （基于成成本） c. CHOOOSE （选选择性） 设置缺缺省的优化器器，可以通过过对initt.ora文文件中OPTTIMIZEER_MODDE参数的各各种声明，如如RULE，CCOST，CCHOOSEE，ALL_ROWS，FFIRST_ROWS . 你当然然也在SQLL句级或是会会话（sesssion）级级对其进行覆覆盖。 为了使使用基于成本本的优化器（CCBO， CCost-BBased Optimmizer） ， 你必须须经常运行aanaly

2、zze 命令，以以增加数据库库中的对象统统计信息（oobjectt stattisticcs）的准确确性。 如果数数据库的优化化器模式设置置为选择性（CCHOOSEE），那么实实际的优化器器模式将和是是否运行过aanalyzze命令有关关。 如果ttable已已经被anaalyze过过， 优化器器模式将自动动成为CBOO ， 反之之，数据库将将采用RULLE形式的优优化器。 在缺省省情况下，OORACLEE采用CHOOOSE优化化器，为了避避免那些不必必要的全表扫扫描（fulll tabble sccan） ， 你必须尽量量避免使用CCHOOSEE优化器，而而直接采用基基于规则或者者基于成本的

3、的优化器。 2. 访问Taable的方方式ORACCLE 采用用两种访问表表中记录的方方式： a. 全表扫描描 全表扫描描就是顺序地地访问表中每每条记录。 ORACLLE采用一次次读入多个数数据块（daatabasse bloock）的方方式优化全表表扫描。 b. 通过ROOWID访问问表 你可以采采用基于ROOWID的访访问方式情况况，提高访问问表的效率， ROWIDD包含了表中中记录的物理理位置信息ORACCLE采用索索引（INDDEX）实现现了数据和存存放数据的物物理位置（RROWID）之之间的联系。 通常索引引提供了快速速访问ROWWID的方法法，因此那些些基于索引列列的查询就可可以得

4、到性能能上的提高。 3. 共享SQQL语句 为了不不重复解析相相同的SQLL语句，在第第一次解析之之后， ORRACLE将将SQL语句句存放在内存存中。这块位位于系统全局局区域SGAA（systtem gllobal area）的的共享池（ssharedd bufffer poool）中的的内存可以被被所有的数据据库用户共享享。 因此，当当你执行一个个SQL语句句（有时被称称为一个游标标）时，如果果它和之前的的执行过的语语句完全相同同， ORAACLE就能能很快获得已已经被解析的的语句以及最最好的执行路路径。 ORRACLE的的这个功能大大大地提高了了SQL的执执行性能并节节省了内存的的使用。

5、 可惜的的是ORACCLE只对简简单的表提供供高速缓冲（ccache buffeering） ，这个功能能并不适用于于多表连接查查询。 数据库库管理员必须须在initt.ora中中为这个区域域设置合适的的参数，当这这个内存区域域越大，就可可以保留更多多的语句，当当然被共享的的可能性也就就越大了。 当你向向ORACLLE 提交一一个SQL语语句，ORAACLE会首首先在这块内内存中查找相相同的语句。 这里需需要注明的是是，ORACCLE对两者者采取的是一一种严格匹配配，要达成共共享，SQLL语句必须完完全相同（包包括空格，换换行等）。 共享的的语句必须满满足三个条件件： A. 字符级的比比较：

6、当前被执执行的语句和和共享池中的的语句必须完完全相同。 例如： SELLECT * FROMM EMP； 和下列列每一个都不不同 SELLECT * fromm EMP； Sellect * Fromm Emp； SELLECT * FROM EMP； B. 两个语句所所指的对象必必须完全相同同： 例如： 用户 对对象名 如何何访问 Jacck saal_limmit priivate synonnym Worrk_citty pubblic ssynonyym Plaant_deetail ppublicc synoonym Jill sall_limiit privvate ssynony

7、ym Worrk_citty pubblic ssynonyym Plaant_deetail ttable ownerr考虑一下下列列SQL语句句能否在这两两个用户之间间共享。SQL 能否共共享 原因 select max(ssal_caap) frrom saal_limmit; 不不能 每个用用户都有一个个privaate syynonymm - saal_limmit , 它们是不同同的对象 select countt(*0 ffrom wwork_ccity wwhere sdescc likee NEWW%; 能能 两个用户户访问相同的的对象pubblic ssynonyym -

8、wwork_ccity select a.sdeesc,b.locattion ffrom wwork_ccity aa , pllant_ddetaill b whhere aa.cityy_id = b.ciity_idd 不能 用用户jackk 通过prrivatee synoonym访问问plantt_detaail 而jjill 是是表的所有者者,对象不同同. C. 两个SQLL语句中必须须使用相同的的名字的绑定定变量（biind vaariablles） 例如：第一组的两两个SQL语语句是相同的的（可以共享享），而第二二组中的两个个语句是不同同的（即使在在运行时，赋赋于不同的绑绑定

9、变量相同同的值） a. selecct pinn ， naame frrom peeople wheree pin = ：bllk1.piin； selecct pinn ， naame frrom peeople wheree pin = ：bllk1.piin；b. selectt pin ， namme froom peoople wwhere pin = ：blkk1.ot_ind；selectt pin ， namme froom peoople wwhere pin = ：blkk1.ov_ind；4. 选择最最有效率的表表名顺序（只只在基于规则则的优化器中中有效） ORAACLE

10、的解解析器按照从从右到左的顺顺序处理FRROM子句中中的表名，因因此FROMM子句中写在在最后的表（基基础表 drrivingg tablle）将被最最先处理。 在FROMM子句中包含含多个表的情情况下，你必必须选择记录录条数最少的的表作为基础础表。当ORRACLE处处理多个表时时， 会运用用排序及合并并的方式连接接它们。首先先，扫描第一一个表（FRROM子句中中最后的那个个表）并对记记录进行派序序，然后扫描描第二个表（FFROM子句句中最后第二二个表），最最后将所有从从第二个表中中检索出的记记录与第一个个表中合适记记录进行合并并。 例如： 表表 TAB11 16，3384 条记记录 表表 T

11、AB22 1 条记记录 选选择TAB22作为基础表表 （最好的的方法） sellect ccount（*） froom tabb1，tabb2 执执行时间0.96秒 选选择TAB22作为基础表表 （不佳的的方法） sellect ccount（*） froom tabb2，tabb1 执执行时间266.09秒 如如果有3个以以上的表连接接查询， 那那就需要选择择交叉表（iinterssectioon tabble）作为为基础表， 交叉表是指指那个被其他他表所引用的的表。 例如： EMMP表描述了了LOCATTION表和和CATEGGORY表的的交集。SELECT * FROM LOOCATIO

12、ON L , CCATEGOORY C, EEMP E WHERE EE.EMP_NO BEETWEENN 10000 AND 2000AND E.CCAT_NOO = C.CAT_NNOAND E.LLOCN = L.LOOCN 将比下列SQQL更有效率率SELECT * FROM EMMP E ,LOCATIOON L , CCATEGOORY CWHERE EE.CAT_NO = C.CATT_NOAND E.LLOCN = L.LOOCNAND E.EEMP_NOO BETWWEEN 11000 AAND 200005. WHEERE子句中中的连接顺序序。 ORAACLE采用用自下而上

13、的的顺序解析WWHERE子子句，根据这这个原理，表表之间的连接接必须写在其其他WHERRE条件之前前， 那些可可以过滤掉最最大数量记录录的条件必须须写在WHEERE子句的的末尾。 例如：（低效，执行行时间1566.3秒）SELECT FROM EMMP EWHERE SSAL 500000AND JOB = MANNAGERAND 25 (SELEECT COOUNT(*) FROOM EMPP WHERRE MGRR=E.EMMPNO); (高效,执行行时间10.6秒)SELECT FROM EMMP EWHERE 225 500000AND JOB = MANNAGER;6. SELEEC

14、T子句中中避免使用 * 当你想在SEELECT子子句中列出所所有的COLLUMN时，使使用动态SQQL列引用 * 是一个方方便的方法。不幸的是，这这是一个非常常低效的方法法。 实际上上，ORACCLE在解析析的过程中， 会将* 依次转换换成所有的列列名， 这个个工作是通过过查询数据字字典完成的， 这意味着将将耗费更多的的时间。 7. 减减少访问数据据库的次数当执行每条SSQL语句时时， ORAACLE在内内部执行了许许多工作： 解析SQLL语句， 估估算索引的利利用率， 绑绑定变量 ， 读数据块等等等。 由此此可见， 减减少访问数据据库的次数 ， 就能实实际上减少OORACLEE的工作量。 例

15、如，以以下有三种方方法可以检索索出雇员号等等于03422或02911的职员。 方法11 （最低效效） SELLECT EEMP_NAAME , SALARRY , GGRADE FROOM EMPP WHEERE EMMP_NO = 3422; SEELECT EMP_NNAME , SALAARY , GRADEE FROOM EMPP WHEERE EMMP_NO = 2911;方法2 （次低低效） DECLAARE CURSSOR C11 (E_NNO NUMMBER) IS SELEECT EMMP_NAMME,SALLARY,GGRADE FROMM EMP WHERRE EMPP_

16、NO = E_NOO; BEGGIN OPENN C1(3342); FETCCH C1 INTO ,.,. ; OOPEN CC1(2911); FETCHH C1 IINTO ,.,. ; CLOOSE C11; EEND;方法3 （高效效） SELLECT AA.EMP_NAME , A.SSALARYY , A.GRADEE, B.EMPP_NAMEE , B.SALARRY , BB.GRADDE FROOM EMPP A,EMMP B WHEERE A.EMP_NNO = 3342 ANDD B.EMP_NNO = 2291;注意：在SQL*PPlus ， SQL*FForms和和

17、Pro*CC中重新设置置ARRAYYSIZE参参数， 可以以增加每次数数据库访问的的检索数据量量 ，建议值值为200. 8. 使用用DECODDE函数来减减少处理时间间使用DECOODE函数可可以避免重复复扫描相同记记录或重复连连接相同的表表。 例如： SELEECT COOUNT(*)，SUMM(SAL) FROMMEMP WHERRE DEPPT_NO = 00220 AND ENAMEE LIKEESMITHH%; SELEECT COOUNT(*)，SUMM(SAL) FROMMEMP WHERRE DEPPT_NO = 00330 AND ENAMEE LIKEESMITHH%;你可

18、以用DEECODE函函数高效地得得到相同结果果SELECT COUNTT(DECOODE(DEEPT_NOO,00200,X,NULL) D00020_COOUNT, COUNNT(DECCODE(DDEPT_NNO,00330,X,NULLL) D00030_CCOUNT, SUM(DECODDE(DEPPT_NO,0020,SAL,NNULL) D00220_SALL, SUM(DECODDE(DEPPT_NO,0030,SAL,NNULL) D00330_SALLFROM EMMP WHEERE ENNAME LLIKE SMITHH%;类似的，DEECODE函函数也可以运运用于GROO

19、UP BYY 和ORDDER BYY子句中。 9. 整合简单单，无关联的的数据库访问问如果你有几个个简单的数据据库查询语句句，你可以把把它们整合到到一个查询中中（即使它们们之间没有关关系） 例如： SELECTT NAMEE FROM EMMP WHERE EEMP_NOO = 12234; SELECTT NAMEE FROM DPPTWHERE DDPT_NOO = 100 ;SELECT NAME FROM CAATWHERE CCAT_TYYPE = RD;上面的3个查查询可以被合合并成一个：SELECT E.NAMME , DD.NAMEE , C.NAMEFROM CAAT C ,

20、 DPT D , EEMP E,DUAL XWHERE NNVL(XX,X.DDUMMY) = NVVL(X,E.ROOWID(+)AND NVLL(X,X.DUMMMY) = NVL(X,DD.ROWIID(+)AND NVLL(X,X.DUMMMY) = NVL(X,CC.ROWIID(+)AND E.EEMP_NOO(+) = 12344AND D.DDEPT_NNO(+) = 10AND C.CCAT_TYYPE(+) = RRD;（译者按： 虽虽然采取这种种方法，效率率得到提高，但但是程序的可可读性大大降降低，所以读读者还是要权权衡之间的利利弊）10. 删除除重复记录最高效的删除除重

21、复记录方方法 （ 因因为使用了RROWID）DELETE FROM EMP EEWHERE EE.ROWIID (SELECCT MINN(X.ROOWID) FROOM EMPP X WHEERE X.EMP_NNO = EE.EMP_NO);11. 用用TRUNCCATE替代代DELETTE当删除表中的的记录时，在在通常情况下下， 回滚段段（rolllback segmeents ） 用来存放可可以被恢复的的信息。 如如果你没有CCOMMITT事务，ORRACLE会会将数据恢复复到删除之前前的状态（准准确地说是恢恢复到执行删删除命令之前前的状况） 而当运运用TRUNNCATE时时， 回滚段

22、段不再存放任任何可被恢复复的信息。当当命令运行后后，数据不能能被恢复。因因此很少的资资源被调用，执执行时间也会会很短。 （译者者按： TRRUNCATTE只在删除除全表适用，TTRUNCAATE是DDDL不是DMML） 12. 尽量多多使用COMMMIT只要有可能，在在程序中尽量量多使用COOMMIT， 这样程序的的性能得到提提高，需求也也会因为COOMMIT所所释放的资源源而减少：CCOMMITT所释放的资资源：a. 回滚滚段上用于恢恢复数据的信信息。 b. 被程序语语句获得的锁锁c. reedo loog bufffer 中中的空间d. ORRACLE为为管理上述33种资源中的的内部花费（

23、译者按： 在使用COOMMIT时时必须要注意意到事务的完完整性，现实实中效率和事事务完整性往往往是鱼和熊熊掌不可得兼兼） 13. 计算记记录条数和一般的观点点相反， ccount（*） 比coount（11）稍快 ， 当然如果可可以通过索引引检索，对索索引列的计数数仍旧是最快快的。 例如如 COUNNT（EMPPNO） （译者者按： 在CCSDN论坛坛中，曾经对对此有过相当当热烈的讨论论， 作者的的观点并不十十分准确，通通过实际的测测试，上述三三种方法并没没有显著的性性能差别） 14. 用Whhere子句句替换HAVVING子句句避免使用HAAVING子子句， HAAVING 只会在检索索出所

24、有记录录之后才对结结果集进行过过滤。 这个个处理需要排排序，总计等等操作。 如如果能通过WWHERE子子句限制记录录的数目，那那就能减少这这方面的开销销。 例如：低效： SEELECT REGIOON，AVGG(LOG_SIZE) FRROM LOOCATIOON GRROUP BBY REGGION HAAVING REGIOON REGGION != SYYDNEY ANND REGGION != PEERTH高效 SEELECT REGIOON，AVGG(LOG_SIZE) FRROM LOOCATIOON WHHERE RREGIONN REGIION != SYDDNEY ANND

25、REGGION != PEERTHGROUP BBY REGGION（译者按： HHAVINGG 中的条件件一般用于对对一些集合函函数的比较，如如COUNTT（） 等等等。 除此而而外，一般的的条件应该写写在WHERRE子句中） 15. 减少对对表的查询在含有子查询询的SQL语语句中，要特特别注意减少少对表的查询询。 例如：低效 SEELECT TAB_NNAME FRROM TAABLES WHHERE TTAB_NAAME = ( SELLECT TTAB_NAAME FROMM TAB_COLUMMNS WHEREE VERSSION = 604) ANNDDB_VER= ( SELLE

26、CT DDB_VERR FROM TAB_CCOLUMNNS WHEREE VERSSION = 604) 高效 SEELECT TAB_NNAME FRROM TAABLES WHHERE (TAB_NNAME,DDB_VERR) = ( SEELECT TAB_NNAME,DDB_VERR) FROOM TABB_COLUUMNS WHEERE VEERSIONN = 6004) Upddate 多多个Coluumn 例子子：低效： UUPDATEE EMP SSET EMMP_CATT = (SSELECTT MAX(CATEGGORY) FROM EMP_CCATEGOORIES),

27、SALL_RANGGE = (SELECCT MAXX(SAL_RANGEE) FROOM EMPP_CATEEGORIEES) WWHERE EMP_DDEPT = 00200;高效： UPPDATE EMP SEET (EMMP_CATT, SALL_RANGGE) = (SELEECT MAAX(CATTEGORYY) , MMAX(SAAL_RANNGE) FROM EEMP_CAATEGORRIES) WWHERE EMP_DDEPT = 00200;16. 通过过内部函数提提高SQL效效率。 SSELECTT H.EMMPNO,EE.ENAMME,H.HHIST_TTYPE,TT.

28、TYPEE_DESCC,COUNNT(*) FRROM HIISTORYY_TYPEE T,EMMP E,EEMP_HIISTORYY H WHHERE HH.EMPNNO = EE.EMPNNOAND H.HHIST_TTYPE = T.HIIST_TYYPEGROUP BBY H.EEMPNO,E.ENAAME,H.HIST_TYPE,T.TYPPE_DESSC;通过调用下面面的函数可以以提高效率。FUNCTIOON LOOOKUP_HHIST_TTYPE(TTYP INN NUMBBER) RRETURNN VARCCHAR2AS TDEESC VAARCHARR2(30); CURRS

29、OR CC1 IS SELEECT TYYPE_DEESC FROMM HISTTORY_TTYPE WHERRE HISST_TYPPE = TTYP;BEGIN OPEN C1; FETTCH C11 INTOO TDESSC; CLOOSE C11; RETTURN (NVL(TTDESC,?);END;FUNCTIOON LOOOKUP_EEMP(EMMP IN NUMBEER) REETURN VARCHHAR2AS ENAAME VAARCHARR2(30); CURRSOR CC1 IS SELEECT ENNAME FROMM EMP WHERRE EMPPNO=EMMP;BE

30、GIN OPEEN C1; FETTCH C11 INTOO ENAMME; CLOOSE C11; RETTURN (NVL(EENAME,?);END;SELECT H.EMPPNO,LOOOKUP_EMP(HH.EMPNNO),H.HIST_TYPE,LOOKUUP_HISST_TYPPE(H.HHIST_TTYPE),COUNTT(*)FROM EMMP_HISSTORY HGROUP BBY H.EEMPNO , H.HHIST_TTYPE;（译者按： 经经常在论坛中中看到如 能不能用一一个SQL写写出。 的贴子， 殊不知复杂杂的SQL往往往牺牲了执执行效率。 能够掌握上上面的运用函

31、函数解决问题题的方法在实实际工作中是是非常有意义义的）17. 使用用表的别名（AAlias） 当在SSQL语句中中连接多个表表时， 请使使用表的别名名并把别名前前缀于每个CColumnn上。这样一一来，就可以以减少解析的的时间并减少少那些由Coolumn歧歧义引起的语语法错误。 （译者者注： Coolumn歧歧义指的是由由于SQL中中不同的表具具有相同的CColumnn名，当SQQL语句中出出现这个Coolumn时时，SQL解解析器无法判判断这个Coolumn的的归属） 18. 用EXXISTS替替代IN在许多基于基基础表的查询询中，为了满满足一个条件件，往往需要要对另一个表表进行联接。在这种

32、情况况下， 使用用EXISTTS（或NOOT EXIISTS）通通常将提高查查询的效率。 低效：SELECT * FROM EMMP (基础础表)WHERE EEMPNO 0AND DEPPTNO IIN (SEELECT DEPTNNO FROM DEEPT WHERE LLOC = MELBB) 高效：SELECT * FROM EMMP (基础础表)WHERE EEMPNO 0AND EXIISTS (SELECCT X FROM DEEPT WHERE DDEPT.DDEPTNOO = EMMP.DEPPTNOAND LOCC = MMELB) （译者者按： 相对对来说，用NNOT E

33、XXISTS替替换NOT IN 将更更显著地提高高效率，下一一节中将指出出） 19. 用NOOT EXIISTS替代代NOT IIN在子查询中，NNOT INN子句将执行行一个内部的的排序和合并并。 无论在在哪种情况下下，NOT IN都是最最低效的 （因因为它对子查查询中的表执执行了一个全全表遍历）。 为了避避免使用NOOT IN ，我们可以以把它改写成成外连接（OOuter Joinss）或NOTT EXISSTS.例如：SELECT FROM EMMPWHERE DDEPT_NNO NOTT IN (SELECCT DEPPT_NO FRROM DEEPT WHHERE DDEPT_CCA

34、T=AA);为了提高效率率。改写为：（方法一： 高效）SELECT .FROM EMMP A,DDEPT BBWHERE AA.DEPTT_NO = B.DEEPT(+)AND B.DDEPT_NNO IS NULLAND B.DDEPT_CCAT(+) = AA（方法二： 最高效）SELECT .FROM EMMP EWHERE NNOT EXXISTS (SELEECT XX FRROM DEEPT D WHHERE DD.DEPTT_NO = E.DEEPT_NOO ANND DEPPT_CATT = AA);20. 用用表连接替换换EXISTTS通常来说 ， 采用表连接接的方式比EEX

35、ISTSS更有效率 SSELECTT ENAMME FFROM EEMP E WWHERE EXISTTS (SEELECT X FROM DEPT WHEREE DEPTT_NO = E.DEEPT_NOO ANDD DEPTT_CAT = A);（更高效） SSELECTT ENAMME FFROM DDEPT DD,EMP E WWHERE E.DEPPT_NO = D.DDEPT_NNO AAND DEEPT_CAAT = A ;（译者按： 在RBO的的情况下，前前者的执行路路径包括FIILTER，后后者使用NEESTED LOOP） 21. 用EXXISTS替替换DISTTINCT当

36、提交一个包包含一对多表表信息（比如如部门表和雇雇员表）的查查询时，避免免在SELEECT子句中中使用DISSTINCTT. 一般可可以考虑用EEXIST替替换例如：低效： SELEECT DIISTINCCT DEPPT_NO,DEPT_NAME FROOM DEPPT D,EEMP E WHEERE D.DEPT_NO = E.DEPPT_NO高效： SELLECT DDEPT_NNO,DEPPT_NAMME FROOM DEPPT D WHEERE EXXISTS ( SELLECT X FRROM EMMP E WHERRE E.DDEPT_NNO = DD.DEPTT_NO);EXIS

37、TSS 使查询更更为迅速，因因为RDBMMS核心模块块将在子查询询的条件一旦旦满足后，立立刻返回结果果。 22. 识别低效执行的SQL语语句用下列SQLL工具找出低低效SQL：SELECT EXECUUTIONSS , DIISK_REEADS, BUFFEER_GETTS, ROUNND(BUUFFER_GETS-DISK_READSS)/BUFFFER_GGETS,22) Hitt_radiio, ROOUND(DDISK_RREADS/EXECUUTIONSS,2) RReads_per_rrun, SQL_TEXTFROM V$SQLLAREAWHERE EEXECUTTIONS0AN

38、D BUFFFER_GEETS 0 AND (BUUFFER_GETS-DISK_READSS)/BUFFFER_GGETS llist 1 SELEECT * 2 FROMM deptt, empp 3* WHHERE eemp.deeptno = deppt.depptnoSQL seet auttotracce traaceonlly /*ttraceoonly 可可以不显示执执行结果*/SQL /14 rowss seleected.Executiion Pllan- 0 SEELECT STATEEMENT Optimmizer=CHOOSSE 1 0 NESTEED LOOOPS

39、2 1 TABBLE ACCCESS (FULLL) OF EMP 3 1 TABBLE ACCCESS (BY IINDEX ROWIDD) OF DEPTT 4 3 IINDEX (UNIQQUE SCCAN) OOF PKK_DEPTT (UNNIQUE)Statisttics- 0 recurrsive callss 2 db bllock ggets 30 consiistentt getss 0 physiical rreads 0 redo size 2598 bytess sentt via SQL*NNet too clieent 503 bytess receeived v

40、ia SSQL*Neet froom cliient 2 SQL*NNet rooundtrrips tto/froom cliient 0 sortss (memmory) 0 sortss (dissk) 14 rows proceessed通过以上分析析，可以得出出实际的执行行步骤是：1. TAABLE AACCESSS （FULLL） OFF EMPP2. INNDEX （UUNIQUEE SCANN） OF PK_DDEPT （UNIQQUE） 3. TABLLE ACCCESS （BBY INDDEX ROOWID） OF DDEPT 4. NEESTED LOOPSS （JOII

41、NING 1 ANDD 3） 注： 目前许多第第三方的工具具如TOADD和ORACCLE本身提提供的工具如如OMS的SSQL Annalyzee都提供了极极其方便的EEXPLAIIN PLAAN工具。也也许喜欢图形形化界面的朋朋友们可以选选用它们。 25. 用索引引提高效率 索引是是表的一个概概念部分，用用来提高检索索数据的效率率。 实际上上，ORACCLE使用了了一个复杂的的自平衡B-tree结结构。 通常常，通过索引引查询数据比比全表扫描要要快。 当OORACLEE找出执行查查询和Upddate语句句的最佳路径径时， ORRACLE优优化器将使用用索引。 同同样在联结多多个表时使用用索引也

42、可以以提高效率。 另一个使使用索引的好好处是，它提提供了主键（pprimarry keyy）的唯一性性验证。 除了那那些LONGG或LONGG RAW数数据类型， 你可以索引引几乎所有的的列。 通常常， 在大型型表中使用索索引特别有效效。 当然，你你也会发现， 在扫描小表表时，使用索索引同样能提提高效率。 虽然使使用索引能得得到查询效率率的提高，但但是我们也必必须注意到它它的代价。 索引需要空空间来存储，也也需要定期维维护， 每当当有记录在表表中增减或索索引列被修改改时， 索引引本身也会被被修改。 这这意味着每条条记录的INNSERT ， DELLETE ， UPDATTE将为此多多付出4 ，

43、 5 次的磁磁盘I/O . 因为索索引需要额外外的存储空间间和处理，那那些不必要的的索引反而会会使查询反应应时间变慢。 译者按按：定期的重重构索引是有有必要的。 ALTTER INNDEX REBUUILD 26. 索引的的操作 ORAACLE对索索引有两种访访问模式。 索引唯唯一扫描 （ INDEXX UNIQQUE SCCAN） 大多数数情况下， 优化器通过过WHEREE子句访问IINDEX. 例如：表LODGIING有两个个索引 ： 建立在LOODGINGG列上的唯一一性索引LOODGINGG_PK和建建立在MANNAGER列列上的非唯一一性索引LOODGINGG$MANAAGER. S

44、ELLECT * FROM LLODGINNG WHERE LODGIING = ROSE HILL； 在内部部 ， 上述述SQL将被被分成两步执执行， 首先先 ， LOODGINGG_PK 索索引将通过索索引唯一扫描描的方式被访访问 ， 获获得相对应的的ROWIDD， 通过RROWID访访问表的方式式执行下一步步检索。 如果被被检索返回的的列包括在IINDEX列列中，ORAACLE将不不执行第二步步的处理（通通过ROWIID访问表）。 因为检索索数据保存在在索引中， 单单访问索索引就可以完完全满足查询询结果。 下面SSQL只需要要INDEXX UNIQQUE SCCAN 操作作。 SELLE

45、CT LLODGINNG FROM LLODGINNG WHERE LODGIING = ROSE HILL； 索引范范围查询（IINDEX RANGEE SCANN） 适用于于两种情况：1. 基于于一个范围的的检索2. 基于于非唯一性索索引的检索 例1： SELLECT LLODGINNG FROOM LODDGING WHEREE LODGGING LLIKE M%； WHEERE子句条条件包括一系系列值， OORACLEE将通过索引引范围查询的的方式查询LLODGINNG_PK . 由于索索引范围查询询将返回一组组值， 它的的效率就要比比索引唯一扫扫描低一些。 例2： SELLECT L

46、LODGINNG FROM LLODGINNG WHERE MANAGGER = BILL GATESS； 这个SSQL的执行行分两步， LODGIING$MAANAGERR的索引范围围查询（得到到所有符合条条件记录的RROWID） 和下一步同同过ROWIID访问表得得到LODGGING列的的值。 由于于LODGIING$MAANAGERR是一个非唯唯一性的索引引，数据库不不能对它执行行索引唯一扫扫描。 由于SSQL返回LLODGINNG列，而它它并不存在于于LODGIING$MAANAGERR索引中， 所以在索引引范围查询后后会执行一个个通过ROWWID访问表表的操作。 WHEERE子句中中

47、， 如果索索引列所对应应的值的第一一个字符由通通配符（WIILDCARRD）开始， 索引将不被被采用。在这这种情况下，OORACLEE将使用全表表扫描。SELECT LODGIING FROM LLODGINNG WHERE MANAGGER LIIKE HANMMAN；27. 基础础表的选择 基础表表（Drivving TTable）是是指被最先访访问的表（通通常以全表扫扫描的方式被被访问）。 根据优化器器的不同， SQL语句句中基础表的的选择是不一一样的。 如果你你使用的是CCBO （CCOST BBASED OPTIMMIZER），优优化器会检查查SQL语句句中的每个表表的物理大小小，索

48、引的状状态，然后选选用花费最低低的执行路径径。 如果你你用RBO （RULEE BASEED OPTTIMIZEER） ， 并且所有的的连接条件都都有索引对应应， 在这种种情况下， 基础表就是是FROM 子句中列在在最后的那个个表。 举例：SELECTT A.NAAME ， B.MANNAGER FROMWWORKERR A，LOODGINGG B WHEREA.LODDGING = B.LLODINGG；由于LODGGING表的的LODINNG列上有一一个索引， 而且WORRKER表中中没有相比较较的索引， WORKEER表将被作作为查询中的的基础表。 28. 多个平平等的索引当SQL语句句

49、的执行路径径可以使用分分布在多个表表上的多个索索引时， OORACLEE会同时使用用多个索引并并在运行时对对它们的记录录进行合并， 检索出仅对对全部索引有有效的记录。 在ORRACLE选选择执行路径径时，唯一性性索引的等级级高于非唯一一性索引。 然而这个规规则只有当WWHERE子子句中索引列列和常量比较较才有效。如如果索引列和和其他表的索索引类相比较较。 这种子子句在优化器器中的等级是是非常低的。 如果不不同表中两个个想同等级的的索引将被引引用， FRROM子句中中表的顺序将将决定哪个会会被率先使用用。 FROOM子句中最最后的表的索索引将有最高高的优先级。 如果相相同表中两个个想同等级的的索

50、引将被引引用， WHHERE子句句中最先被引引用的索引将将有最高的优优先级。 举例：DEPTNOO上有一个非非唯一性索引引，EMP_CAT也有有一个非唯一一性索引。 SELLECT EENAME，FROM EEMP WHERE DEPT_NO = 20 AND EMMP_CATT = A；这里，DEPPTNO索引引将被最先检检索，然后同同EMP_CCAT索引检检索出的记录录进行合并。 执行路径径如下： TABBLE ACCCESS BY ROOWID OON EMPP AND-EEQUAL INDEEX RANNGE SCCAN ONN DEPTT_IDX INDEEX RANNGE SCCA

51、N ONN CAT_IDX 29. 等式式比较和范围围比较当WHEREE子句中有索索引列， OORACLEE不能合并它它们，ORAACLE将用用范围比较。 举例：DEPTNOO上有一个非非唯一性索引引，EMP_CAT也有有一个非唯一一性索引。 SELLECT EENAME FROM EEMP WHERE DEPTNNO 220 AND EMMP_CATT = A； 这里只只有EMP_CAT索引引被用到，然然后所有的记记录将逐条与与DEPTNNO条件进行行比较。 执执行路径如下下：TABLE ACCESSS BY ROWIDD ON EEMP INDEXX RANGGE SCAAN ON CAT

52、_IIDX 30. 不明确确的索引等级级 当ORRACLE无无法判断索引引的等级高低低差别，优化化器将只使用用一个索引，它它就是在WHHERE子句句中被列在最最前面的。 举例：DEPTNOO上有一个非非唯一性索引引，EMP_CAT也有有一个非唯一一性索引。 SELLECT EENAME FROM EEMP WHERE DEPTNNO 220 AND EMMP_CATT A； 这里， ORACLLE只用到了了DEPT_NO索引。 执行路径径如下： TABBLE ACCCESS BY ROOWID OON EMPP INDEXX RANGGE SCAAN ON DEPT_IDX 译者按按：我们来试

53、试一下以下这这种情况：SQL sselectt indeex_namme， unniquenness ffrom uuser_iindexees wheere taable_nname = EMPP； INDDEX_NAAME UNNIQUENNES- - EMPPNO UNNIQUE EMPTYPPE NONUNNIQUE SQLL sellect * fromm emp wheree empnno = 2 andd emp_type = A ； no rows seleccted Exeecutioon Plaan- 0 SSELECTT STATTEMENTT Optiimizerr=C

54、HOOOSE 1 00 TAABLE AACCESSS （BY INDEXX ROWIID） OFF EMPP 2 11 INDEXX （RANNGE SCCAN） OOF EMMPTYPEE （NOON-UNIIQUE）虽然EMPNOO是唯一性索索引，但是由由于它所做的的是范围比较较， 等级要要比非唯一性性索引的等式式比较低！31. 强制制索引失效 如果两两个或以上索索引具有相同同的等级，你你可以强制命命令ORACCLE优化器器使用其中的的一个（通过过它，检索出出的记录数量量少） . 举例： SELLECT EENAME FROM EEMP WHERE EMPNOO = 79935 AND

55、DEEPTNO + 0 = 10 /*DDEPTNOO上的索引将将失效*/ AND EMMP_TYPPE | = A /*EEMP_TYYPE上的索索引将失效*/ 这是一一种相当直接接的提高查询询效率的办法法。 但是你你必须谨慎考考虑这种策略略，一般来说说，只有在你你希望单独优优化几个SQQL时才能采采用它。 这里有有一个例子关关于何时采用用这种策略， 假设在在EMP表的的EMP_TTYPE列上上有一个非唯唯一性的索引引而EMP_CLASSS上没有索引引。 SELLECT EENAME FROM EEMP WHERE EMP_TTYPE = AAND EMMP_CLAASS = X； 优化器器

56、会注意到EEMP_TYYPE上的索索引并使用它它。 这是目目前唯一的选选择。 如果果，一段时间间以后， 另另一个非唯一一性建立在EEMP_CLLASS上，优优化器必须对对两个索引进进行选择，在在通常情况下下，优化器将将使用两个索索引并在他们们的结果集合合上执行排序序及合并。 然而，如果果其中一个索索引（EMPP_TYPEE）接近于唯唯一性而另一一个索引（EEMP_CLLASS）上上有几千个重重复的值。 排序及合并并就会成为一一种不必要的的负担。 在在这种情况下下，你希望使使优化器屏蔽蔽掉EMP_CLASSS索引。 用下面面的方案就可可以解决问题题。 SELLECT EENAME FROM EE

57、MP WHERE EMP_TTYPE = AAND EMMP_CLAASS| = X； 32. 避免免在索引列上上使用计算。 WHEERE子句中中，如果索引引列是函数的的一部分。优优化器将不使使用索引而使使用全表扫描描。 举例： 低效： SELECTT FROOM DEPPT WHERE SSAL * 12 250000； 高效： SELECTT FROOM DEPPT WHERE SAL 250000/12； 译者按按：这是一个个非常实用的的规则，请务务必牢记 33. 自动选选择索引 如果表中有两两个以上（包包括两个）索索引，其中有有一个唯一性性索引，而其其他是非唯一一性。 在这种种情况下，

58、OORACLEE将使用唯一一性索引而完完全忽略非唯唯一性索引。 举例： SELECTT ENAMME FROM EEMP WHERE EMPNOO = 23326 AND DEEPTNO = 20 ； 这里，只只有EMPNNO上的索引引是唯一性的的，所以EMMPNO索引引将用来检索索记录。 TABBLE ACCCESS BY ROOWID OON EMPP INDEXX UNIQQUE SCCAN ONN EMP_NO_IDDX 34. 避免在在索引列上使使用NOT 通常，我们要要避免在索引引列上使用NNOT， NNOT会产生生在和在索引引列上使用函函数相同的影影响。 当OORACLEE“遇到

59、”NOT，他他就会停止使使用索引转而而执行全表扫扫描。 举例： 低效： （这里，不不使用索引） SELLECT FROOM DEPPT WHERE DEPT_CODE NOT = 0； 高效： （这里，使使用了索引） SELLECT FROOM DEPPT WHERE DEPT_CODE 0； 需要注注意的是，在在某些时候， ORACLLE优化器会会自动将NOOT转化成相相对应的关系系操作符。 NOTT too = to NOT = NOT 译者按按：在这个例例子中，作者者犯了一些错错误。 例子子中的低效率率SQL是不不能被执行的的。 我做了了一些测试： SQLL sellect * from

60、m emp wheree NOT empnoo 1； no rowws sellectedd Executtion PPlan - 0 SSELECTT STATTEMENTT Optiimizerr=CHOOOSE 1 00 TAABLE AACCESSS （BY INDEXX ROWIID） OFF EMPP 2 11 INDEXX （RANNGE SCCAN） OOF EMMPNO （UNIQQUE） SQLL sellect * fromm emp wheree empnno =替代 如果DDEPTNOO上有一个索索引， 高效： SELLECT * FROM EEMP WHERE DE