欢迎来到天天文库
浏览记录
ID:8969812
大小:152.00 KB
页数:15页
时间:2018-04-13
《oracle数据库数据操作优化(优化器)(讲)》由会员上传分享,免费在线阅读,更多相关内容在应用文档-天天文库。
1、TheOptimizer1.SQLprocessing2.Optimizationmethods3.ExecuteSQLstatementsSQLProcessingArchitectureEXPLAINPLANWhatIsTheOptimizer?ChoosinganOptimizerApproachandGoalCost-BasedOptimizer(CBO)CBOParametersExtensibleOptimizerRule-BasedOptimizer(RBO)OverviewofOptimizerOperationsOptimizingJoinsOptimi
2、zingStatementsthatUseCommonSubexpressionsEvaluationofExpressionsandConditionsTransformingandOptimizingStatementsSQLProcessingArchitecture1ParserSyntaxanalysis:checksSQLstatementsforcorrectsyntax.Semanticanalysis:checksifthecurrentdatabaseobjectsandattributesreferencedarecorrect.Optimizerrule-ba
3、sedoptimizer(RBO)cost-basedoptimizer(CBO)RowSourceGeneratorInputs:optimalplanfromtheoptimizerOutputs:executionplanfortheSQLstatementExecutionplan:acollectionofrowsources(atree)Rowsource:aniterativecontrolstructureSQLExecutionSQLexecution:acomponentthatoperatesontheexecutionplanOutput: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);2StepsofExecutionPlanArowsource:asetofrowsreturnedbyastepThenumberingofthesteps:theorderfortheEXPLAINPLANstatementStepsindi
6、catedbytheshadedboxesretrievedatafromanobjectinthedatabase.Suchstepsarecalledaccesspaths:oSteps3and6readalltherowsoftheempandsalgradetables,respectively.oStep5looksupeachdeptnovalueinthepk_deptnoindexreturnedbystep3.oStep4retrievestherowswhoserowidswerereturnedbystep5fromthedepttable.Stepsindicated
7、bytheclearboxesoperateonrowsources:oStep2performsanestedloopsoperation,acceptingrowsourcesfromsteps3and4,joiningeachrowfromstep3sourcetoitscorrespondingrowinstep4,andreturningtheresultingrowstostep1.o
此文档下载收益归作者所有