Error in Note:122008.1 good candidate for a bitmap index ?
435938Apr 27 2006 — edited Apr 27 2006Hi,
I have been looking at the script described in Note:122008.1 on metalink.
This script determines whether an index is a good candidate for a rebuild or for
a bitmap index.
The note says:
Index is (possible) candidate for a bitmap index when :
-- - distinctiveness is more than 99%
The used formula to calculate the distictiveness is (these are colums from the index_stats view)
(lf_rows-distinct_keys)*100/lf_rows
From the Oracle Reference Guide (index_stats):
LF_ROWS : Number of leaf rows (values in the index)
DISTINCT_KEYS : Number of distinct keys in the index (may include rows that have been deleted)
I think that the formula above is wrong.
Suppose that I have 100 rows in my table. I create an index on a column and there are 99 distinct values for that column in my table.
According to the formula above that would give:
(100-99)*100/100 = 1
I would expect the value for distictiveness to be 99% in this case
So I think that this formula should be 100 - ((lf_rows-distinct_keys)*100/lf_rows)
Am I wrong or not?
Thank You.
Pieter