資源描述:
《SQL多表連接查詢優(yōu)化的相關(guān)研究.ppt》由會員上傳分享,免費在線閱讀,更多相關(guān)內(nèi)容在應(yīng)用文檔-天天文庫。
1、多表連接查詢優(yōu)化的相關(guān)研究呂彬2009.3.5motivation圖中表示連接順序?qū)Σ樵冃实挠绊懀簻?zhǔn)確估計選擇度要考慮屬性間的相關(guān)性問題關(guān)鍵:高效地計算屬性間的相關(guān)度AgendaMulti-tablejoinoverviewHeuristicandrandomizedoptimizationforthejoinorderingproblemMichaelSteinbrunn,etal,TheVLDBJournal(1997)6:191–208AttributecorrelationdetectionBHUNT:AutomaticDiscoveryofFuzzyAlgebraicCo
2、nstraintsinRelationalDataPaulG.BrownPeterJ.Haas,Proceedingsofthe29thVLDBConference,2003CORDS:AutomaticDiscoveryofCorrelationsandSoftFunctionalDependenciesIhabF.Ilyas,VolkerMarkl,etal,SIGMOD2004,June13–18,2004,COCA:MoreAccurateMultidimensionalHistogramsoutofMoreAccurateCorrelationsDetectionCAOWe
3、i1,QINXiongpai,WANGShan,WAIM2008StarjoinStarGazingfromatopyourDB2z/OSDatabaseServerTerryPurcell,etal,IntelligentOptimizerStarjoinrevisited:PerformanceinternalsforclusterarchitecturesJosepAguilar-Saborit,Data&KnowledgeEngineering63(2007)995–1013Heuristicandrandomizedoptimizationforthejoinorderin
4、gproblemChoosingjointypebasedoncostSolutionspaceforthejoinorderingproblemJoinorderingstrategiesQuantitativeanalysisConclusionMulti-tablejoinoverviewChoosingjointypebasedoncostCostmodelsNestedloopjoinSort-mergejoinHashjoinMulti-tablejoinoverviewIOCostElapsedTimeRowCostCPUCostBaseCostScanCostPage
5、CostSolutionspaceforthejoinorderingproblemLeft-deeptreesn!waystoallocatenbaserelationstothetree’sleavesgoodsolutionsbecauseofexploitingthecost-reducingpipeliningtechniqueBushytreesanadaptableplanenumerationstrategylineargraphs(n3?n)/6stargraphs(n?1)2n?2Multi-tablejoinoverviewJoinorderingstrateg
6、iesDeterministicalgorithmsheuristicorexhaustivesearchRandomizedalgorithmsDefineasetofmoveswhichconstituteedgesbetweenthedifferentsolutionsofthesolutionspaceperformsarandomwalkalongtheedgesaccordingtocertainrules,terminatingassoonasnomoreapplicablemovesexistoratimelimitisexceededGeneticalgorithm
7、smakeuseofarandomizedsearchstrategyverysimilartobiologicalevolutionHybridalgorithmscombinethestrategiesofpuredeterministicandpurerandomizedalgorithmsMulti-tablejoinoverviewQuantitativeanalysisClassofthejoingraphRelationcardinaliti