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: Bitmap indexes

Re: Bitmap indexes

From: Hans Henrik Krohn <hhk_at_tips.dk>
Date: Thu, 15 Mar 2001 15:02:10 +0100
Message-ID: <98qhvk$hmj$1@news.inet.tele.dk>

Oh I forgot to add in my first post: You could try using "alter session set skip_unusable_indexes = TRUE" in the load program, and then rebuilding the index-partitions afterwards. This might help - if you don't run into bug 1395260 concerning 'get ORA-1502 on DML even though set skip_unusable_indexes=true'.

Regards, Hans Henrik Krohn

"Mark Wagoner" <mwagoner_at_iac.net> skrev i en meddelelse news:98qgid$1nfo$1_at_genma.iac.net...
> 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 - 08:02:10 CST

Original text of this message

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