Cardinality

I received an email recently which said:

I have a question about Page 54 of Cost Based Oracle.

10g Update
As with equalities, 10.1.0.4 suddenly changes what happens when you fall outside the low/high limits. The arithmetic, or picture, used for ranges outside the limits is just the same as the new mechanism we saw for equalities.

I can’t work out how the formula provides a cardinality of 82 for the values ” month_no between 14 and 17″. Can you please elaborate on the out of bounds formula ?

The example in the book was only intended to show the general pattern of behaviour, and I didn’t attempt to explain what I thought the optimizer was doing – which is an odd little oversight.

The code to create the table, and the example I ran were as follows:

create table audience as
select
	trunc(dbms_random.value(1,13))	month_no
from
	all_objects
where
	rownum <= 1200
;
-- Collect statistics

select	count(*)
from	audience
where	month_no between 14 and 17;

---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |          |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| AUDIENCE |    82 |   246 |     2 |
---------------------------------------------------------------

Now run the query with the following predicates:

month_no between 14 and 16
month_no between 14 and 17
month_no between 14 and 18
month_no between 14 and 21
month_no between 14 and 27
month_no = 14

You will see that the cardinality doesn’t change. It is the cardinality of month_no = 14 … the value at the lower end of the range.

To see why this cardinality is 82: it takes 11 steps to get from the known low value (1) to the known high value (12), so to extrapolate past the high value the optimizer steps from 100 down to zero in 11 more steps – which means 9.0909 per step.

 Since 14 is two steps from 12, the cardinality is (100 – 2 * 9.0909) which rounds to 82.