Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: When to use bitmap indexes

Re: When to use bitmap indexes

From: MT <mtechera_at_wpmc.com>
Date: Fri, 21 Sep 2001 14:50:03 +0200
Message-ID: <9ofcrb$udq$1@news.online.de>


Hello Michiel:

You should be OK although you should first experiment. My experience with BMIs is very good. I have a table with over 40 million records and 250.000 products. The BMI give much better performance than the B*Tree indexes.

Make sure you create histograms on the critical columns and use the INDEX_COMBINE hint to make it clear to the optimizer what path it should take.

Once you have created the BMIs you should also test your loading procedures because we found that the indexes would in some cases cause a slowdown of inserts with respect to normal B*Tree indexes.

Good luck,
Mario

"Michiel Brunt" <mbrunt_at_inergy.nl> wrote in message news:9oet4l$252u$1_at_scavenger.euro.net...
> In our data warehouse we have a large fact table with keys to dimension
> tables.
> Something like:
>
> Product_key Customer_key Time_key Revenue
> 1 1 1 1000
> 1 1 2 2000
> 1 2 2 1000
> 1 3 1 1000
> 1 3 2 2000
> 2 1 1 500
> 2 1 2 1000
> 2 2 2 2000
>
> This fact table contains 10 mln records.
> There are 10.000 products and 500.000 customers
> The question is wether the use of bitmap indexes is useful.
>
> I heard that the number of distinct values in a column should be less than
> 10% of the total number of rows in the table.
> In that case 10.000 / 10.000.000 is fine and even 500.000 / 10.000.000 would
> work.
>
> Thanks,
> Michiel
>
>
Received on Fri Sep 21 2001 - 07:50:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US