# JL Computer Consultancy

## Cost Based Oracle: Fundamentals

## Chapter 3: Single Table Selectivity

**Addenda**

Closed
Ranges (1^{st} April 2006)

Errata

Bind
Variable Selectivity (24^{th} March 2006)

Clarification
(10^{th} Jan 2006)

**Addenda**

### Closed Ranges (1^{st} 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 (24^{th}
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 (10^{th} 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.

.