JL Computer Consultancy

Cost Based Oracle: Fundamentals

Chapter 3:  Single Table Selectivity


Addenda

Closed Ranges (1st April 2006)

Errata

Bind Variable Selectivity (24th March 2006)

Clarification (10th Jan 2006)

Addenda

Closed Ranges (1st April 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.

Back to Book Index

Top of page.


Errata

Bind Variable Selectivity (24th Mar 2006)

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.

Clarification (10th Jan 2006)

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.


Back to Book Index

Top of page.

.