In post SQL Server CE: Multiple single-column statistics, I discussed the selectivity when AND is used to join the columns in Where clause. Today, I’m going to talk about the OR operator
Let’s say we have four predicates connected by OR with selectivites P0,P1,P2 and P3,
Legacy CE
The combined selectivity is : 1-(1-P0)*(1-P1)*(1-P2)*(1-P3).
If there are only two predicates, the formula is relative simple: (P0+P1)-(P0*P1)
New CE
1.The combined selectivity is : 1-(1-P0)*(1-P1)^(1/2)*(1-P2)^(1/4)*(1-P3)^(1/8)
2.P0,P1 are the selectivity of each value of the column in WHERE clause and P0>P1.
3.We can have up to 4 predicates. If there are