欢迎来到天天文库
浏览记录
ID:50370101
大小:162.50 KB
页数:15页
时间:2020-03-08
《ORACLE数据库数据操作优化(优化器)(讲).doc》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、TheOptimizer1.SQLprocessing2.Optimizationmethods3.ExecuteSQLstatementsSQLProcessingArchitectureEXPLAINPLANWhatIsTheOptimizer?ChoosinganOptimizerApproachandGoalCost-BasedOptimizer(CBO)CBOParametersExtensibleOptimizerRule-BasedOptimizer(RBO)OverviewofOptimizerOperati
2、onsOptimizingJoinsOptimizingStatementsthatUseCommonSubexpressionsEvaluationofExpressionsandConditionsTransformingandOptimizingStatementsSQLProcessingArchitectureParserSyntaxanalysis:checksSQLstatementsforcorrectsyntax.Semanticanalysis:checksifthecurrentdatabaseobjects
3、andattributesreferencedarecorrect.Optimizerrule-basedoptimizer(RBO)cost-basedoptimizer(CBO)RowSourceGeneratorInputs:optimalplanfromtheoptimizerOutputs:executionplanfortheSQLstatementExecutionplan:acollectionofrowsources(atree)Rowsource:aniterativecontrolstructureSQLEx
4、ecutionSQLexecution:acomponentthatoperatesontheexecutionplanOutput: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);StepsofExecutionPlanArowsource:asetofrowsreturnedbyastepThenumberingofthesteps:theorderfortheEXPLAINPLANstatementStepsindicatedbytheshadedboxesretrievedatafromanobjectinthedatabase.Suchstepsarecalledaccesspaths:oSteps3and6readalltherowsoftheempandsalgradetables,respec
7、tively.oStep5looksupeachdeptnovalueinthepk_deptnoindexreturnedbystep3.oStep4retrievestherowswhoserowidswerereturnedbystep5fromthedepttable.Stepsindicatedbytheclearboxesoperateonrowsources:oStep2performsanestedloopsoperation,acceptingrowsourcesfromsteps3and4,joiningeachrowf
8、romstep3sourcetoitscorrespondingrowinstep4,andreturningtheresultingrowstostep1.oSt
此文档下载收益归作者所有