oracle语句优化30个规则详解

1、1. 选用用适合的OOraclle优化器器Oraacle的的优化器共共有3种：a. RULEE (基于于规则)b. COSTT (基于于成本)c. CHOOOSE (选择性)设置缺缺省的优化化器，可以以通过对iinit.ora文文件中OPPTIMIIZER_MODEE参数的各各种声明，如如RULEE，COSST，CHHOOSEE，ALLL_ROWWS，FIIRST_ROWSS . 你你当然也在在SQL句句级或是会会话(seessioon)级对对其进行覆覆盖。为了使使用基于成成本的优化化器(CBBO， CCost-Baseed Opptimiizer) ， 你你必须经常常运行annalyzze

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

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

4、查查询就可以以得到性能能上的提高高。3. 共享SQQL语句为了不不重复解析析相同的SSQL语句句，在第一一次解析之之后， OORACLLE将SQQL语句存存放在内存存中。这块块位于系统统全局区域域SGA(systtem gglobaal arrea)的的共享池(sharred bbuffeer poool)中中的内存可可以被所有有的数据库库用户共享享。 因此此，当你执执行一个SSQL语句句(有时被被称为一个个游标)时时，如果它它和之前的的执行过的的语句完全全相同， ORACCLE就能能很快获得得已经被解解析的语句句以及最好好的执行路路可惜的是OORACLLE只对简简单的表提提供高速缓缓冲(ca

5、ache buffferinng) ，这这个功能并并不适用于于多表连接接查询。数据库库管理员必必须在innit.oora中为为这个区域域设置合适适的参数，当当这个内存存区域越大大，就可以以保留更多多的语句，当当然被共享享的可能性性也就越大大了。当你向向ORACCLE 提提交一个SSQL语句句，ORAACLE会会首先在这这块内存中中查找相同同的语句。这里需需要注明的的是，ORRACLEE对两者采采取的是一一种严格匹匹配，要达达成共享，SSQL语句句必须完全全相同(包包括空格，换换行等)。共享的的语句必须须满足三个个条件：A. 字符级的的比较：当前被被执行的语语句和共享享池中的语语句必须完完全相同

6、。例如：SELEECT*FFROMEEMP;和下列列每一个都都不同SEELECTT*froomEMPP;Sellect*FrommEmp;SELEECT*FFROMEEMP;B. 两个语句句所指的对对象必须完完全相同：例如：用户 对象名 如何访问问 Jaacksaal_liimitpprivaatesyynonyymWorrk_ciitypuubliccsynoonymPPlantt_dettailppubliicsynnonymmJilllsal_limiitpriivateesynoonymWWork_cityypubllicsyynonyymPlaant_ddetaiiltabbleow

7、wner考虑一一下下列SSQL语句句能否在这这两个用户户之间共享享。SQLL 能否共共享 原因因sellect max(sal_cap) froom saal_liimit; 不能 每个用户户都有一个个privvate synoonym - saal_liimit , 它们们是不同的的对象sellect counnt(*00 froom woork_ccity wherre sddesc likee NEEW%; 能 两两个用户访访问相同的的对象puublicc synnonymm - wwork_cityysellect a.sddesc,b.loocatiion ffrom workk_c

8、itty a , pllant_detaail bb wheere aa.citty_idd = bb.citty_idd 不能 用户jaack 通通过priivatee synnonymm访问pllant_detaail 而而jilll 是表的的所有者,对象不同同.C. 两个个SQL语语句中必须须使用相同同的名字的的绑定变量量(binnd vaariabbles)例如：第一组的的两个SQQL语句是是相同的(可以共享享)，而第第二组中的的两个语句句是不同的的(即使在在运行时，赋赋于不同的的绑定变量量相同的值值)a.selecctpinn，nammefroompeooplewwhereepin=

9、：blkk1.piin;seelecttpin，nnameffromppeopllewheerepiin=：bblk1.pin;b.selecctpinn，nammefroompeooplewwhereepin=：blkk1.ott_indd;sellectppin，nnameffromppeopllewheerepiin=：bblk1.ov_iind;4. 选择最有有效率的表表名顺序(只在基于于规则的优优化器中有有效)ORAACLE的的解析器按按照从右到到左的顺序序处理FRROM子句句中的表名名，因此FFROM子子句中写在在最后的表表(基础表表 driivingg tabble)将将被最先处

10、处理。 在在FROMM子句中包包含多个表表的情况下下，你必须须选择记录录条数最少少的表作为为基础表。当ORAACLE处处理多个表表时，会运运用排序及及合并的方方式连接它它们。首先先，扫描第第一个表(FROMM子句中最最后的那个个表)并对对记录进行行派序，然然后扫描第第二个表(FROMM子句中最最后第二个个表)，最最后将所有有从第二个个表中检索索出的记录录与第一个个表中合适适记录进行行合并。例如：表 TTAB1 16，3384 条条记录表 TTAB2 1 条记记录选择TTAB2作作为基础表表 (最好好的方法)selecctcouunt(*)froomtabb1，taab2执行行时间0.96秒选择

11、TTAB2作作为基础表表 (不佳佳的方法)selecctcouunt(*)froomtabb2，taab1执行行时间266.09秒秒如果有有3个以上上的表连接接查询， 那就需要要选择交叉叉表(innterssectiion ttablee)作为基基础表， 交叉表是是指那个被被其他表所所引用的表表。例如： EMPP表描述了了LOCAATIONN表和CAATEGOORY表的的交集。SELLECT*FROMMLOCAATIONNL,CAATEGOORYC,EMPEEWHERREE.EEMP_NNOBETTWEENN10000AND22000AANDE.CAT_NO=CC.CATT_NOAANDE.L

12、OCNN=L.LLOCN将比下列SSQL更有有效率 SEELECTT*FROOMEMPPE,LOOCATIIONL,CATEEGORYYCWHEEREE.CAT_NO=CC.CATT_NOAANDE.LOCNN=L.LLOCNAANDE.EMP_NOBEETWEEEN10000ANDD200005. WHERRE子句中中的连接顺顺序。ORAACLE采采用自下而而上的顺序序解析WHHERE子子句，根据据这个原理理，表之间间的连接必必须写在其其他WHEERE条件件之前， 那些可以以过滤掉最最大数量记记录的条件件必须写在在WHERRE子句的的末尾。例如：（低效效，执行时时间1566.3秒）SELLE

13、CTFROMMEMPEEWHERRESALL500000ANNDJOBB=MAANAGEERANND25(SELLECTCCOUNTT(*)FFROMEEMPWHHEREMMGR=EE.EMPPNO);(高效效,执行时时间10.6秒)SEELECTTFROOMEMPPEWHEERE2555500000ANDJJOB=MANAAGER;6. SELEECT子句句中避免使使用 * 当你想想在SELLECT子子句中列出出所有的CCOLUMMN时，使使用动态SSQL列引引用 * 是一一个方便的的方法。不不幸的是，这这是一个非非常低效的的方法。实实际上，OORACLLE在解析析的过程中中， 会将将* 依

14、次转换换成所有的的列名， 这个工作作是通过查查询数据字字典完成的的， 这意意味着将耗耗费更多的的时间。7. 减少访问问数据库的的次数当执行行每条SQQL语句时时， ORRACLEE在内部执执行了许多多工作： 解析SQQL语句， 估算索引引的利用率率， 绑定定变量 ， 读数据块块等等。 由此可见见， 减少少访问数据据库的次数数 ， 就就能实际上上减少ORRACLEE的工作量量。例如，以以下有三种种方法可以以检索出雇雇员号等于于03422或02991的职员员。方法11 (最低低效)SEELECTTEMP_NAMEE,SALLARY,GRADDEFROOMEMPPWHERREEMPP_NO=342;

15、SELEECTEMMP_NAAME,SSALARRY,GRRADEFFROMEEMPWHHEREEEMP_NNO=2991;方法2 (次低效)DECCLAREECURSSORC11(E_NNONUMMBER)ISSEELECTTEMP_NAMEE,SALLARY,GRADDEFROOMEMPPWHERREEMPP_NO=E_NOO;BEGGINOPPENC11(3422);FEETCHCC1INTTO,.,.;OPEENC1(291);FETTCHC11INTOO,.,.;CLOSSEC1; ENDD;方法33 (高效效)SEELECTTA.EMMP_NAAME,AA.SALLARY,A.GR

16、RADE,B.EMMP_NAAME,BB.SALLARY,B.GRRADEFFROMEEMPA,EMPBBWHERREA.EEMP_NNO=3442ANDDB.EMMP_NOO=2911;注意：在SQQL*Pllus ， SQL*Formms和Prro*C中中重新设置置ARRAAYSIZZE参数， 可以增加加每次数据据库访问的的检索数据据量 ，建建议值为2200.8. 使用DEECODEE函数来减减少处理时时间使用DDECODDE函数可可以避免重重复扫描相相同记录或或重复连接接相同的表表。例如： SEELECTTCOUNNT(*)，SUMM(SALL)FROOMEMMPWHEEREDEEPT_

17、NNO=00020ANNDENAAMELIIKESMITTH%;SELEECTCOOUNT(*)，SSUM(SSAL)FFROMEMPWWHEREEDEPTT_NO=00300ANDEENAMEELIKEESMMITH%;你可以以用DECCODE函函数高效地地得到相同同结果SELLECTCCOUNTT(DECCODE(DEPTT_NO,00200,X,NULLL)DD00200_COUUNT,CCOUNTT(DECCODE(DEPTT_NO,00300,X,NULLL)DD00300_COUUNT,SSUM(DDECODDE(DEEPT_NNO,00020,SSAL,NNULL)D00020_

18、SSAL,SSUM(DDECODDE(DEEPT_NNO,00030,SSAL,NNULL)D00030_SSALFRROMEMMPWHEEREENNAMELLIKESMITTH%;类似的的，DECCODE函函数也可以以运用于GGROUPP BY 和ORDDER BBY子句中中。9. 整合简单单，无关联联的数据库库访问如果你你有几个简简单的数据据库查询语语句，你可可以把它们们整合到一一个查询中中(即使它它们之间没没有关系)例如：SEELECTTNAMEEFROMMEMPWWHEREEEMP_NO=11234;SELEECTNAAMEFRROMDPPTWHEEREDPPT_NOO=10;SELE

19、ECTNAAMEFRROMCAATWHEERECAAT_TYYPE=RD;上面的的3个查询询可以被合合并成一个个： SEELECTTE.NAAME,DD.NAMME,C.NAMEEFROMMCATCC,DPTTD,EMMPE,DDUALXXWHERRENVLL(X,X.DDUMMYY)=NVVL(XX,E.ROWIID(+)ANDDNVL(X,X.DUUMMY)=NVLL(X,D.RROWIDD(+)ANDNNVL(X,XX.DUMMMY)=NVL(X,C.ROOWID(+)AANDE.EMP_NO(+)=12234ANNDD.DDEPT_NO(+)=100ANDCC.CATT_TYPPE(+

20、)=RDD;10. 删除重重复记录最高效效的删除重重复记录方方法 ( 因为使用用了ROWWID) DEELETEEFROMMEMPEEWHERREE.RROWIDD(SEELECTTMIN(X.ROOWID)FROMMEMPXXWHERREX.EEMP_NNO=E.EMP_NO);11. 用TRRUNCAATE替代代DELEETE当删除除表中的记记录时，在在通常情况况下， 回回滚段(rrollbback segmmentss ) 用用来存放可可以被恢复复的信息。 如果你你没有COOMMITT事务，OORACLLE会将数数据恢复到到删除之前前的状态(准确地说说是恢复到到执行删除除命令之前前的状况

21、)而当运运用TRUUNCATTE时， 回滚段不不再存放任任何可被恢恢复的信息息。当命令令运行后，数数据不能被被恢复。因因此很少的的资源被调调用，执行行时间也会会很短。(译者者按： TTRUNCCATE只只在删除全全表适用，TTRUNCCATE是是DDL不不是DMLL)12. 尽量多多使用COOMMITT只要有有可能，在在程序中尽尽量多使用用COMMMIT， 这样程序序的性能得得到提高，需需求也会因因为COMMMIT所所释放的资资源而减少少：COMMMIT所所释放的资资源：a. 回滚段上上用于恢复复数据的信信息。b. 被程程序语句获获得的锁c. redoo logg bufffer 中的空间间d

22、. ORACCLE为管管理上述33种资源中中的内部花花费(译者者按： 在在使用COOMMITT时必须要要注意到事事务的完整整性，现实实中效率和和事务完整整性往往是是鱼和熊掌掌不可得兼兼)13. 计算记记录条数和一般般的观点相相反， ccountt(*) 比couunt(11)稍快 ， 当然然如果可以以通过索引引检索，对对索引列的的计数仍旧旧是最快的的。 例如如 COUUNT(EEMPNOO)(译者者按： 在在CSDNN论坛中，曾曾经对此有有过相当热热烈的讨论论， 作者者的观点并并不十分准准确，通过过实际的测测试，上述述三种方法法并没有显显著的性能能差别)14. 用Whhere子子句替换HHAV

23、INNG子句避免使使用HAVVING子子句， HHAVINNG 只会会在检索出出所有记录录之后才对对结果集进进行过滤。 这个处处理需要排排序，总计计等操作。 如果能能通过WHHERE子子句限制记记录的数目目，那就能能减少这方方面的开销销。例如：低效：SELLECTRREGIOON，AVVG(LOOG_SIIZE)FFROMLLOCATTIONGGROUPPBYREEGIONNHAVIINGREEGIONNREGIION!=SYDDNEYANDRREGIOON!=PERTTH高效：SEELECTTREGIION，AAVG(LLOG_SSIZE)FROMMLOCAATIONNWHERREREGGI

24、ONRREGIOON!=SYDNNEYAANDREEGIONN!=PPERTHHGROOUPBYYREGIION15. 减少对对表的查询询在含有有子查询的的SQL语语句中，要要特别注意意减少对表表的查询。例如：低效SEELECTTTAB_NAMEEFROMMTABLLESWHHERETTAB_NNAME=(SELLECTTTAB_NNAMEFFROMTTAB_CCOLUMMNSWHHEREVVERSIION=6604)AANDDDB_VEER=(SSELECCTDB_VERFFROMTTAB_CCOLUMMNSWHHEREVVERSIION=6604)高效SEELECTTTAB_NAMEEFR

25、OMMTABLLESWHHERE(TAB_NAMEE,DB_VER)=(SEELECTTTAB_NAMEE,DB_VER)FROMMTAB_COLUUMNSWWHEREEVERSSION=604)Updaate多个个Coluumn例子子：低效：UPPDATEEEMPSSETEMMP_CAAT=(SSELECCTMAXX(CATTEGORRY)FRROMEMMP_CAATEGOORIESS),SAAL_RAANGE=(SELLECTMMAX(SSAL_RRANGEE)FROOMEMPP_CATTEGORRIES)WHERREEMPP_DEPPT=00020;高效： UPPDATEEEMPSSET

26、(EEMP_CCAT,SSAL_RRANGEE)=(SSELECCTMAXX(CATTEGORRY),MMAX(SSAL_RRANGEE)FROOMEMPP_CATTEGORRIES)WHERREEMPP_DEPPT=00020;16. 通过内内部函数提提高SQLL效率。SEELECTTH.EMMPNO,E.ENNAME,H.HIIST_TTYPE,T.TYYPE_DDESC,COUNNT(*)FROMMHISTTORY_TYPEET,EMMPE,EEMP_HHISTOORYHWWHEREEH.EMMPNO=E.EMMPNOAANDH.HISTT_TYPPE=T.HISTT_TYPPEGROO

27、UPBYYH.EMMPNO,E.ENNAME,H.HIIST_TTYPE,T.TYYPE_DDESC;通过调调用下面的的函数可以以提高效率率。FUUNCTIIONLOOOKUPP_HISST_TYYPE(TTYPINNNUMBBER)RRETURRNVARRCHARR2ASTDEESCVAARCHAAR2(330);CCURSOORC1IISSELLECTTTYPE_DESCCFROMMHISTTORY_TYPEEWHERREHISST_TYYPE=TTYP;BBEGINNOPENNC1;FFETCHHC1INNTOTDDESC;CLOSSEC1;RETUURN(NNVL(TTDESCC,?)

28、;EEND;FFUNCTTIONLLOOKUUP_EMMP(EMMPINNNUMBEER)REETURNNVARCCHAR22ASENAAMEVAARCHAAR2(330);CCURSOORC1IISSELLECTEENAMEEFROMMEMPWWHEREEEMPNNO=EMMP;BEEGINOOPENCC1;FEETCHCC1INTTOENAAME;CCLOSEEC1;RRETURRN(NVVL(ENNAME,?);ENND;SEELECTTH.EMMPNO,LOOKKUP_EEMP(HH.EMPPNO),H.HIIST_TTYPE,LOOKKUP_HHIST_TYPEE(H.HHIST_T

29、YPEE),COOUNT(*)FRROMEMMP_HIISTORRYHGRROUPBBYH.EEMPNOO,H.HHIST_TYPEE;(译者者按： 经经常在论坛坛中看到如如 能不不能用一个个SQL写写出。 的贴子子， 殊不不知复杂的的SQL往往往牺牲了了执行效率率。 能够够掌握上面面的运用函函数解决问问题的方法法在实际工工作中是非非常有意义义的)低效：UPPDATEEEMPSSETEMMP_CAAT=(SSELECCTMAXX(CATTEGORRY)FRROMEMMP_CAATEGOORIESS),SAAL_RAANGE=(SELLECTMMAX(SSAL_RRANGEE)FROOMEMPP

30、_CATTEGORRIES)WHERREEMPP_DEPPT=00020;高效： UPPDATEEEMPSSET(EEMP_CCAT,SSAL_RRANGEE)=(SSELECCTMAXX(CATTEGORRY),MMAX(SSAL_RRANGEE)FROOMEMPP_CATTEGORRIES)WHERREEMPP_DEPPT=00020;16. 通过内内部函数提提高SQLL效率。SEELECTTH.EMMPNO,E.ENNAME,H.HIIST_TTYPE,T.TYYPE_DDESC,COUNNT(*)FROMMHISTTORY_TYPEET,EMMPE,EEMP_HHISTOORYHWWH

31、EREEH.EMMPNO=E.EMMPNOAANDH.HISTT_TYPPE=T.HISTT_TYPPEGROOUPBYYH.EMMPNO,E.ENNAME,H.HIIST_TTYPE,T.TYYPE_DDESC;通过调调用下面的的函数可以以提高效率率。FUUNCTIIONLOOOKUPP_HISST_TYYPE(TTYPINNNUMBBER)RRETURRNVARRCHARR2ASTDEESCVAARCHAAR2(330);CCURSOORC1IISSELLECTTTYPE_DESCCFROMMHISTTORY_TYPEEWHERREHISST_TYYPE=TTYP;BBEGINNOPENN

32、C1;FFETCHHC1INNTOTDDESC;CLOSSEC1;RETUURN(NNVL(TTDESCC,?);EEND;FFUNCTTIONLLOOKUUP_EMMP(EMMPINNNUMBEER)REETURNNVARCCHAR22ASENAAMEVAARCHAAR2(330);CCURSOORC1IISSELLECTEENAMEEFROMMEMPWWHEREEEMPNNO=EMMP;BEEGINOOPENCC1;FEETCHCC1INTTOENAAME;CCLOSEEC1;RRETURRN(NVVL(ENNAME,?);ENND;SEELECTTH.EMMPNO,LOOKKUP_EEM

33、P(HH.EMPPNO),H.HIIST_TTYPE,LOOKKUP_HHIST_TYPEE(H.HHIST_TYPEE),COOUNT(*)FRROMEMMP_HIISTORRYHGRROUPBBYH.EEMPNOO,H.HHIST_TYPEE;(译者者按： 经经常在论坛坛中看到如如 能不不能用一个个SQL写写出。 的贴子子， 殊不不知复杂的的SQL往往往牺牲了了执行效率率。 能够够掌握上面面的运用函函数解决问问题的方法法在实际工工作中是非非常有意义义的)(方法二： 最高效效)SEELECTT.FRROMEMMPEWHHERENNOTEXXISTSS(SELLECTXFRROMDEEPTDW

34、WHEREED.DEEPT_NNO=E.DEPTT_NOAANDDEEPT_CCAT=A);20. 用表连连接替换EEXISTTS通常来来说 ， 采用表连连接的方式式比EXIISTS更更有效率SELLECTEENAMEEFROMMEMPEEWHERREEXIISTS(SELEECTXXFROOMDEPPTWHEEREDEEPT_NNO=E.DEPTT_NOAANDDEEPT_CCAT=A);(更高高效)SEELECTTENAMMEFROOMDEPPTD,EEMPEWWHEREEE.DEEPT_NNO=D.DEPTT_NOAANDDEEPT_CCAT=A;(译者者按： 在在RBO的的情况下，前前

35、者的执行行路径包括括FILTTER，后后者使用NNESTEED LOOOP)21. 用EXXISTSS替换DIISTINNCT当提交交一个包含含一对多表表信息(比比如部门表表和雇员表表)的查询询时，避免免在SELLECT子子句中使用用DISTTINCTT. 一般般可以考虑虑用EXIIST替换换例如：低效： SEELECTTDISTTINCTTDEPTT_NO,DEPTT_NAMMEFROOMDEPPTD,EEMPEWWHEREED.DEEPT_NNO=E.DEPTT_NO高效：SELLECTDDEPT_NO,DDEPT_NAMEEFROMMDEPTTDWHEEREEXXISTSS(SELLEC

36、TXFRROMEMMPEWHHEREEE.DEPPT_NOO=D.DDEPT_NO);EXIISTS 使查询更更为迅速，因因为RDBBMS核心心模块将在在子查询的的条件一旦旦满足后，立立刻返回结结果。22. 识别低效执行行的SQQL语句用下列列SQL工工具找出低低效SQLL：SELLECTEEXECUUTIONNS,DIISK_RREADSS,BUFFFER_GETSS,ROUUND(BUFFFER_GGETS-DISKK_REAADS)/BUFFFER_GGETS,2)Hiit_raadio,ROUNND(DIISK_RREADSS/EXEECUTIIONS,2)Reeads_per_run

37、,SQL_TEXTTFROMMV$SQQLAREEAWHEEREEXXECUTTIONSS0ANNDBUFFFER_GETSS0ANND(BUUFFERR_GETTS-DIISK_RREADSS)/BUUFFERR_GETTSlisst1SEELECTT*2FRROMdeept,eemp3*WHERREempp.depptno=deptt.depptnoSSQLssetauutotrracettraceeonlyy/*trraceoonly可可以不显示示执行结果果*/SQQL/114rowwssellecteed.ExxecuttionPPlan-0SEELECTTSTATTEMENNTpti

38、imizeer=CHHOOSEE10NEESTEDDLOOPPS21TTABLEEACCEESS(FFULL)OFEEMP331TABBLEACCCESSS(BYIINDEXXROWIID)OFFDEPPT433INDEEX(UNNIQUEESCANN)OFPK_DDEPT(UNIIQUE)Stattistiics-0reccursiivecaalls22dbbllockggets330connsisttentggets00physsicallreadds0reedosiize25598byytesssentvviaSQQL*Neettoccliennt5033byteesrecceiveed

39、viaaSQL*Netffromccliennt2SQQL*Neetrouundtrripstto/frromcllientt0sorrts(mmemorry)0ssortss(dissk)144rowssproccesseed通过以以上分析，可可以得出实实际的执行行步骤是： 1.TABLLEACCCESS(FULLL)OFEMP2.INNDEX(UNIQQUESCCAN)OOFPKK_DEPPT(UUNIQUUE)3.TABLLEACCCESS(BYINNDEXRROWIDD)OFDEPTT4.NNESTEEDLOOOPS(JJOINIING1AAND3)注： 目前许多多第三方的的工具如TT

40、OAD和和ORACCLE本身身提供的工工具如OMMS的SQQL Annalyzze都提供供了极其方方便的EXXPLAIIN PLLAN工具具。也许喜喜欢图形化化界面的朋朋友们可以以选用它们们。25. 用索引引提高效率率索引是是表的一个个概念部分分，用来提提高检索数数据的效率率。 实际际上，ORRACLEE使用了一一个复杂的的自平衡BB-treee结构。通常，通通过索引查查询数据比比全表扫描描要快。 当ORAACLE找找出执行查查询和Uppdatee语句的最最佳路径时时， ORRACLEE优化器将将使用索引引。同样在在联结多个个表时使用用索引也可可以提高效效率。 另另一个使用用索引的好好处是，它

41、它提供了主主键(prrimarry keey)的唯唯一性验证证。践， 感到到还是用SSQLPLLUS中的的SET TRACCE 功能能比较方便便。除了那些LLONG或或LONGG RAWW数据类型型， 你可可以索引几几乎所有的的列。 通通常， 在在大型表中中使用索引引特别有效效。 当然然，你也会会发现， 在扫描小小表时，使使用索引同同样能提高高效率。虽然使使用索引能能得到查询询效率的提提高，但是是我们也必必须注意到到它的代价价。 索引引需要空间间来存储，也也需要定期期维护，每每当有记录录在表中增增减或索引引列被修改改时， 索索引本身也也会被修改改。 这意意味着每条条记录的IINSERRT ，

42、DELEETE ， UPDAATE将为为此多付出出4 ， 5 次的的磁盘I/O . 因为索引引需要额外外的存储空空间和处理理，那些不不必要的索索引反而会会使查询反反应时间变变慢。译者按按：定期的的重构索引引是有必要要的。ALTTER IINDEXX REBBUILDD26. 索引的的操作ORAACLE对对索引有两两种访问模模式。索引唯唯一扫描 ( INNDEX UNIQQUE SSCAN)大多数数情况下， 优化器通通过WHEERE子句句访问INNDEX.例如：表LOODGINNG有两个个索引 ： 建立在在LODGGING列列上的唯一一性索引LLODGIING_PPK和建立立在MANNAGERR

43、列上的非非唯一性索索引LODDGINGG$MANNAGERR.SEELECTT*FROOMLODDGINGGWHERRELODDGINGG=ROOSEHIILL;在内部部 ， 上上述SQLL将被分成成两步执行行， 首先先 ， LLODGIING_PPK 索引引将通过索索引唯一扫扫描的方式式被访问 ， 获得得相对应的的ROWIID， 通通过ROWWID访问问表的方式式执行下一一步检索。如果被被检索返回回的列包括括在INDDEX列中中，ORAACLE将将不执行第第二步的处处理(通过过ROWIID访问表表)。 因因为检索数数据保存在在索引中， 单单访问问索引就可可以完全满满足查询结结果。下面SSQL

44、只需需要INDDEX UUNIQUUE SCCAN 操操作。SELLECTLLODGIINGFRROMLOODGINNGWHEERELOODGINNG=RROSEHHILL;索引范范围查询(INDEEX RAANGE SCANN)适用于于两种情况况：1. 基于于一个范围围的检索2. 基于非唯唯一性索引引的检索例1：SELLECTLLODGIINGFRROMLOODGINNGWHEERELOODGINNGLIKKEM%;WHEERE子句句条件包括括一系列值值， ORRACLEE将通过索索引范围查查询的方式式查询LOODGINNG_PKK . 由由于索引范范围查询将将返回一组组值， 它它的效率就就

45、要比索引引唯一扫描描低一些。例2： SEELECTTLODGGINGFFROMLLODGIINGWHHEREMMANAGGER=BILLLGATEES;这个SSQL的执执行分两步步， LOODGINNG$MAANAGEER的索引引范围查询询(得到所所有符合条条件记录的的ROWIID)和下下一步同过过ROWIID访问表表得到LOODGINNG列的值值。 由于于LODGGING$MANAAGER是是一个非唯唯一性的索索引，数据据库不能对对它执行索索引唯一扫扫描。由于SSQL返回回LODGGING列列，而它并并不存在于于LODGGING$MANAAGER索索引中， 所以在索索引范围查查询后会执执行一

46、个通通过ROWWID访问问表的操作作。WHEERE子句句中， 如如果索引列列所对应的的值的第一一个字符由由通配符(WILDDCARDD)开始， 索引将不不被采用。在这种情情况下，OORACLLE将使用用全表扫描描。 SEELECTTLODGGINGFFROMLLODGIINGWHHEREMMANAGGERLIIKE%HANMMAN;27. 基础表表的选择基础表表(Driivingg Tabble)是是指被最先先访问的表表(通常以以全表扫描描的方式被被访问)。 根据优优化器的不不同， SSQL语句句中基础表表的选择是是不一样的的。如果你你使用的是是CBO (COSST BAASED OPTIIM

47、IZEER)，优优化器会检检查SQLL语句中的的每个表的的物理大小小，索引的的状态，然然后选用花花费最低的的执行路径径。如果你你用RBOO (RUULE BBASEDD OPTTIMIZZER) ， 并且且所有的连连接条件都都有索引对对应， 在在这种情况况下， 基基础表就是是FROMM 子句中中列在最后后的那个表表。bloog举例：SELLECTAA.NAMME，B.MANAAGERFFROMWORKKERA，LODGINGBWHEREA.LODGING=B.LODING;由于LODDGINGG表的LOODINGG列上有一一个索引， 而且WOORKERR表中没有有相比较的的索引， WORKKER表将将被作为查查询中的基基础表。28. 多个平平等的索引引当SQQL语句的的执行路径径可以使用用分布在多多个表上的的多个索引引时， OOR