ORACLE数据库数据操作优化(优化器)(讲).doc

《ORACLE数据库数据操作优化(优化器)(讲).doc》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、TheOptimizer1.SQLprocessing2.Optimizationmethods3.ExecuteSQLstatementsSQLProcessingArchitectureEXPLAINPLANWhatIsTheOptimizer?ChoosinganOptimizerApproachandGoalCost-BasedOptimizer(CBO)CBOParametersExtensibleOptimizerRule-BasedOptimizer(RBO)OverviewofOptimizerOperati

2、onsOptimizingJoinsOptimizingStatementsthatUseCommonSubexpressionsEvaluationofExpressionsandConditionsTransformingandOptimizingStatementsSQLProcessingArchitectureParserSyntaxanalysis:checksSQLstatementsforcorrectsyntax.Semanticanalysis:checksifthecurrentdatabaseobjects

3、andattributesreferencedarecorrect.Optimizerrule-basedoptimizer(RBO)cost-basedoptimizer(CBO)RowSourceGeneratorInputs:optimalplanfromtheoptimizerOutputs:executionplanfortheSQLstatementExecutionplan:acollectionofrowsources(atree)Rowsource:aniterativecontrolstructureSQLEx

4、ecutionSQLexecution:acomponentthatoperatesontheexecutionplanOutput:theresultsofthequery.WhatIsTheOptimizer?Optimizer:themostefficientwaytoexecuteaSQLstatementDMLstatement:SELECT,INSERT,UPDATE,orDELETEInfluencetheoptimizer:bysettingtheoptimizerapproachandgoal,bygatheringst

5、atisticsfortheCBO,byusinghintsinSQLstatementsExecutionPlanAnexecutionplanincludes:anaccessmethodforeachtableandanorderingofthetables(thejoinorder).SELECTename,job,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptnoANDNOTEXISTS(SELECT*FROMsalgradeWHEREemp.salBETWEENlosalANDhisa

6、l);StepsofExecutionPlanArowsource:asetofrowsreturnedbyastepThenumberingofthesteps:theorderfortheEXPLAINPLANstatementStepsindicatedbytheshadedboxesretrievedatafromanobjectinthedatabase.Suchstepsarecalledaccesspaths:oSteps3and6readalltherowsoftheempandsalgradetables,respec

7、tively.oStep5looksupeachdeptnovalueinthepk_deptnoindexreturnedbystep3.oStep4retrievestherowswhoserowidswerereturnedbystep5fromthedepttable.Stepsindicatedbytheclearboxesoperateonrowsources:oStep2performsanestedloopsoperation,acceptingrowsourcesfromsteps3and4,joiningeachrowf

8、romstep3sourcetoitscorrespondingrowinstep4,andreturningtheresultingrowstostep1.oSt