Re: Bitmap Index Sizing

Andreas Hess wrote:
>
> Good Morning
>
> Does anyone have any ideas on how to work out sizes
> of bitmap indexes ? I haven't found any info on bimap
> sizing in any documentation.
>
> Any help will be appreciated.
>
> Cheers,
>
> Andreas

Here's some data:

For a given table, the index size seems to be proportional to the log of the cardinality of the column indexed.

On a 1.8M-row table, we got the following sizes:

Cardinality log(card) Index size (size/log card)

3            .47        1M          2.1
63           1.8        4M          2.2
2112         3.3        7M          2.1

This is for indexes created with pctfree 5. In comparison, b-tree indexes on the above columns run 50-75M apiece, so we're doing pretty well.

Another example from Corey & Abbey's book on Oracle DW shows more variable numbers, on a 1M-row table:

card size (M?) size/log card

5        .78           1.1
100      1.38           .7
10000    2.99           .7 (this is the max recommended % cardinality)
100k     5.25           1.0  
500k     12.35          2.2

From all the above, we get a relationship like

    size/(Millions of rows * log card) = from .7 to 1.2

within the recommended range, which simplifies to

    size (M) <= 1.2 * (M rows * log cardinality)

----> Add a factor of two for good measure :-)

This is pretty amateurish curve-fitting, but if anyone knows what the formula *should* be, I'd like to hear it.

Kendall Received on Mon Aug 25 1997 - 00:00:00 CDT