Closed Ranges (1st April 2006)
Bind Variable Selectivity (24th March 2006)
Clarification (10th Jan 2006)
Page 53 and following: The selectivity of a closed ranges is slightly different from the selecitivy of an open (or half-open) range. The terms closed and open describe whether range comparisons include an equality, so x > 10 is open, x >= 10 is closed. In the book I point out that you cater for the “extra” equality by adding 1/num_distinct to formula for the open range. This is not a complete description of the change, but is generally a reasonable approximation. However, there is a boundary condition – when 1/num_distinct about the same size as the basic selectivity, then an extra fudge-factor appears in the calculation to stop it introducing an excessive change to the selectivity.
Page 52, second and third bullets: give the selectivity for unbounded ranges and bounded ranges as 5% and 0.25% respectively. In fact there is a special boundary condition pointed out to me by Martin Frauendorfer. If the number of distinct values recorded (NDV) for a column is less than 20, then the selectivity for an unbounded range is given as 1/NDV; and the selectivity of a bounded range is 1/(NDV * NDV).
Later in the text I point out that it is important to know about the basic 5% constant because it is used in other places in the optimizer code to handle SQL with predicates like “column operator unknown” such as “colX > (subquery)”. Consequently you might expect this special boundary condition to appear in other cases. However the optimizer may be a little inconsistent; the boundary condition seems to be relevant only for cases involving bind variables – but I haven’t had to check this claim thoroughly yet, so if you find other cases where it applies, please let me know.
Page 50, figure 3-1: This has the title “10g enhancement of selectivity”. Very specifically this is a change introduced in 10.1.0.4, and should have been labeled as such. Moreover the following 4 pages about Range Predicates are relevant up to, but not including, 10.1.0.4. The paragraph on page 54 headed 10g Update then describes how the 10.1.0.4 enhancement affects range predicates.