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

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

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

2、zingStatementsthatUseCommonSubexpressionsEvaluationofExpressionsandConditionsTransformingandOptimizingStatementsSQLProcessingArchitecture1ParserSyntaxanalysis:checksSQLstatementsforcorrectsyntax.Semanticanalysis:checksifthecurrentdatabaseobjectsandattributesreferencedarecorrect.Optimizerrule-ba

3、sedoptimizer(RBO)cost-basedoptimizer(CBO)RowSourceGeneratorInputs:optimalplanfromtheoptimizerOutputs:executionplanfortheSQLstatementExecutionplan:acollectionofrowsources(atree)Rowsource:aniterativecontrolstructureSQLExecutionSQLexecution:acomponentthatoperatesontheexecutionplanOutput:theresul

4、tsofthequery.WhatIsTheOptimizer?Optimizer:themostefficientwaytoexecuteaSQLstatementDMLstatement:SELECT,INSERT,UPDATE,orDELETEInfluencetheoptimizer:bysettingtheoptimizerapproachandgoal,bygatheringstatisticsfortheCBO,byusinghintsinSQLstatementsExecutionPlanAnexecutionplanincludes:anaccessmethodforeach

5、tableandanorderingofthetables(thejoinorder).SELECTename,job,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptnoANDNOTEXISTS(SELECT*FROMsalgradeWHEREemp.salBETWEENlosalANDhisal);2StepsofExecutionPlanArowsource:asetofrowsreturnedbyastepThenumberingofthesteps:theorderfortheEXPLAINPLANstatementStepsindi

6、catedbytheshadedboxesretrievedatafromanobjectinthedatabase.Suchstepsarecalledaccesspaths:oSteps3and6readalltherowsoftheempandsalgradetables,respectively.oStep5looksupeachdeptnovalueinthepk_deptnoindexreturnedbystep3.oStep4retrievestherowswhoserowidswerereturnedbystep5fromthedepttable.Stepsindicated

7、bytheclearboxesoperateonrowsources:oStep2performsanestedloopsoperation,acceptingrowsourcesfromsteps3and4,joiningeachrowfromstep3sourcetoitscorrespondingrowinstep4,andreturningtheresultingrowstostep1.o