Re: Bitmap indexes

I have learned (the hard way) and Oracle has confirmed that bitmap indexes have concurrency issues. When doing an insert or update, an entire block is locked rather than just the row. Buried somewhere in the Oracle docs it is stated that bitmap indexes should primarily be used on read-only (or occasionally updated) data such as a data warehouse. I can't recall where it is any more, I only know of it because Oracle support pointed it out once.

Try dropping the index, doing the update, then recreating the index.

"Janek A." <anisimowicz_at_poczta.onet.pl> wrote in message news:98q7f5$odb$1_at_sunsite.icm.edu.pl...
> I have big (40 mln records) partitioned table (about 40 partitions). I
 added
> 2 bitmap indexes for fields with low cardinality (5 to 10 different
 values).
> When I try to load next volume of data to this table my loading process
> takes longer time. Of course, I know that when I add new indexes loading
> process takes more time, but I show an example.
> Example:
> - Without 2 bitmap indexes: a portion of data (100.000 records) takes: 6
> minuts
> - With 2 bitmap indexes: a portion of data (100.000 records) takes: 10
> hours!!!
>
> When I remove this indexes loading process takes 6 minuts again
>
> I check this situations on servers: 8.0.5, 8.0.6, 8.1.6 and the problem
> occurs.
>
> Create index example:
> CREATE BITMAP INDEX IDX_BI1_I ON MY_TABLE
> (MY_FIELD)
> PCTFREE 1
> STORAGE
> (
> INITIAL 10M
> NEXT 10M
> PCTINCREASE 0
> MINEXTENTS 1
> MAXEXTENTS 5000
> )
> TABLESPACE IDX1
> LOCAL
> NOLOGGING
> /
>
> Thanx,
> Janek A.
>
>
Received on Thu Mar 15 2001 - 07:37:16 CST