白色IT商务企划：数据库工程师工作分析SQLQueryO.pptx

David J. DeWitt Microsoft Jim Gray Systems Lab Madison, Wisconsin dewitt@ ;2;Day 3;How About a Quiz to Start!;;;Anonymous Quote;The Role of the Query Optimizer (100,000 ft view);What’s the Magic?;Some Historical Background;;A First Example;Query Plan #1;Query Plan #2;Which Plan Will be Faster?;A Slightly More Complex Query;Enumerate logically equivalent plans by applying equivalence rules;Equivalence Rules;Equivalence Rules (cont.);Example of Equivalent Logical Plans;Five Logically “Equivalent” Plans;Four More!;9 Logically Equivalent Plans, In Total;Enumerate logically equivalent plans by applying equivalence rules;Physical Plan Example;Enumerate logically equivalent plans by applying equivalence rules;Selectivity Estimation;Histogram Motivation;Equi-Width Histogram Example;Equi-Height Histograms;Example #2: Predicate: CID = 6 Actual Sel. Factor = 157/939 = .167 Estimated Sel. Factor = (157/1)/993 = .167 ;;Enumerate logically equivalent plans by applying equivalence rules;Estimating Costs;An Example;Plan #1;Plan #2;But …;Estimating Join Costs;Sort-Merge Join Algorithm;Nested-Loops Join ;Main Idea: Scan R, and for each tuple in R probe tuples in M (by probing its index). Output result tuples.;Estimating Result Cardinalities;This is Why!;Multidimensional Histograms;A Little Bit About Estimating Join Cardinalities; General case: join on {A} (where {A} is key for neither) estimate each tuple r of R generates uniform number of matches in S and each tuple s of S generates uniform number of matches in R, e.g. SF = min(||R|| * ||S|| / NKeys(A,S) ||S|| * ||R|| / NKeys(A,R)) e.g., SELECT M.title, R.title FROM Movies M, Reviews R WHERE M.title = R.title Movies: 100 tuples, 75 unique titles ? 1.3 rows for each title Reviews: 20 tuples, 10 unique titles ? 2 rows for each title ;Enumerate logically equivalent plans by applying equivalence rules;Two Common Query “Shapes”;Pruning the Plan Space;;52;53;Two Relation Plans Starting With A;Two Rela