CS 186

Selectivity Estimation - Equalities Predicate Selectivity Assumption c=vV 1/ (number of distinct values of c in index) We know |c|. c=vV 1/10 We don’t know |c|. cl1=c2 1/ MAX(number of distinct values of c1, number of | We know |c1| and |c2]|. distinct values of c2) cl1=c2 1/ (number of distinct values of ci) We know |ci| but not |other column|. cl1=c2 1/10 We don't know |c1| or |c2|. e |columnl| = the number of distinct values for the column e Ifyou have an index on the column, you can assume you know |column|, max(c), and min(c) e When applying selectivity to # of records, take the floor of the result. (e.g. 256.3 — 256 records) Selectivity Estimation - Inequalities on Integers Predicate Selectivity Assumption c<v (v - min(c)) / (max(c) - min(c) + 1) We know max(c) and min(c). c>v (max(c) - v) / (max(c) - min(c) + 1) cis an integer. c<v 1/10 We don’t know max(c) and min(c). c>v c is an integer. c<=v (v - min(c)) / (max(c) - min(c) + 1) + (1 /c]|) We know max(c) and min(c). c is an integer. (max(c) - v) / (max(c) - min(c) + 1) + (1 /|c|) cC>=V c<=v 1/10 We don’t know max(c) and min(c). c>=v cis an integer. NOTICE: We add 1 to the denominator in order for our [low, high] range to be inclusive. E.g.range [2,4]=2,3,4>(4-2)+1=3