First page Back Continue Last page Graphics
Calculating Predicate Selectivity
Value between 0 and 1
- 0 = best selectivity (high selectivity)
- 1 = worst selectivity (low selectivity)
If data not skewed
- c1 = value
- selectivity = 1 / C1.NUM_DISTINCT
- c1 > value
- selectivity = (Hi - value) / (Hi - Lo)
- c1 between val1 and val2
- selectivity =
(val2 – val1) / (Hi - Lo) + 2 * 1/ C1.NUM_DISTINCT
Notes:
One of the critical calculations made by CBO is predicate selectivity, also called the “filter factor”. Selectivity is a measure the value of a predicate and is a factor in the decision to use or not to use an index. Selectivity is in the range 0 to 1, with 0 being good (increase the likelihood that an index will be used) and 1 being bad selectivity.
Each condition has an associated selectivity calculation. Note that these calculations can change as the optimizer changes:
Predicate Filter factor
c1 = value 1 / num_distinct or c1.density (if no histogram)
c1 like value 1 / num_distinct or c1.density (if no histogram)
c1 > value (Hi - value) / (Hi - Lo)
c1 >= value (Hi - value) / (Hi - Lo) + 1/c1.num_distinct
c1 < value (value - Lo) / (Hi - Lo)
c1 <= value (value - Lo) / (Hi - Lo) + 1/c1.num_distinct
c1 between val1 and val2 (val2 – val1) / (Hi - Lo) + 2 * 1/c1.num_distinct