14. Evaluating Relational Operators.ppt

14. Evaluating Relational Operators Table Statistics Operators and example schema Selection Projection Equijoins General Joins Set Operators Buffering Intergalactic standard reference G. Graefe, Query Evaluation Techniques for Large Databases, ACM Computing Surveys, 25(2) (1993), pp. 73-170 Learning Objectives In a typical major DBMS, what statistics are automatically collected and when? Given collected statistics, estimate a predicate’s output size/selectivity. For each relational operator, describe the major algorithms, their optimizations, their pros and cons, and their costs Selection Projection Equijoins General Joins Set Operators Motivation/Review Assume Sailors has an index on age. Does the optimal plan for this query use the index? SELECT * FROM Sailors S WHERE S.age 31 Moral: In order to choose the optimal plan we need to know the selectivity of the predicate/size of the output. Collecting Statistics What are statistics Table sizes, index sizes, ranges of values, etc. Where are statistics kept? In the system catalog. Why collect statistics? Statistics are needed to determine selectivity of predicates and sizes of outputs of operators. Data in the previous bullet is needed to calculate costs of plans. Data in the previous bullet is needed by the optimizer to find the optimal plan. How often are statistics collected? Typically done when 10% of the data has been updated. Can be overidden manually UPDATE STATISTICS, ANALYZE Typically done by sampling, if table is large Which tables/columns are monitored? Typically all tables/columns are monitored Sailors Example What statistics would you collect to find the number of rows satisfying Age 31? Rank =4 ? In Intro DB we assumed data was uniformly distributed. Histograms Histograms are more accurate than the uniformity assumption. Equiwidth Histogram: Estimate number of rows/selectivity for the predicate “age 31”: Equidepth Histogram: Estimate number of rows/selectivity for the predicate “age 31”: Actua