oracle数据库数据操作优化(dataaccess)(讲)

《oracle数据库数据操作优化(dataaccess)(讲)》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、DataAccessMethods1.Dataaccessmethodsthatcanenhanceperformance2.Situationstoavoid3.HowtousehintstoforcevariousapproachesUsingIndexesWhentoCreateIndexeslImprovetheperformanceofqueriesthatselectasmallpercentageofrowsfromatable(lessthan2%or4%ofthetable'sr

2、ows)lValuemaybehigher:Alldatacanberetrievedfromanindex；Theindexedcolumnsandexpressionscanbeusedforjoiningtoothertables.TuningtheLogicalStructurelCBOavoidstheuseofnonselectiveindexeswithinqueryexecution(unused:seeEXPLAINPLAN)lSQLenginemustcontinuetomai

3、ntainallindexesdefinedagainstatableregardlessofwhethertheyareusedlIndexmaintenancecanpresentasignificantCPUandI/OresourcedemandOpenquestion:Thereistablewithtoomanyrowsandtherearemanyoperations(includingselect,insert,updateanddelete)onit,what’stheindex

4、policies?(first:reconstructtableandindex;second:dynamicindexpolicy)ChoosingColumnsandExpressionstoIndex1.keysthatarefrequentlyusedinWHEREclauses.2.keysthatarefrequentlyusedtojointablesinSQLstatements.3.keysthathavehighselectivity(usingtheANALYZEstatem

5、ent).·DonotusestandardB*-treeindexesonkeysorexpressionswithfewdistinctvalues.·Donotindexcolumnsthatarefrequentlymodified.9·DonotindexkeysthatappearonlyinWHEREclauseswithfunctionsoroperators(otherthanMINorMAX).·Considerindexingforeignkeysofreferentiali

6、ntegrityconstraintsincasesinwhichalargenumberofconcurrentINSERT,UPDATE,andDELETEstatementsaccesstheparentandchildtables.·ConsiderwhethertheperformancegainforqueriesisworththeperformancelossforINSERTs,UPDATEs,andDELETEsandtheuseofthespacerequiredtostor

7、etheindex.ChoosingCompositeIndexesProvideadditionaladvantagesoversingle-columnindexes:ImprovedselectivitySometimestwoormorecolumnsorexpressionscanbecombinedtoformacompositeindexwithmoreaccurateselectivity.ReducedI/OIfallcolumnsselectedbyaqueryareinaco

8、mpositeindex,thenOraclecanreturnthesevaluesfromtheindexwithoutaccessingthetable.ASQLstatementcanuseanaccesspathinvolvingacompositeindexifthestatementcontainsconstructsthatusealeadingportionoftheindex.CREATEINDEXcomp_indONtab1(x,y,z)x,xy,andxyz