Re: Bitmap indexes

Oooh, I just forgot: I should have mentioned firing off

   alter index IDX_BI1_I modify partition foobar UNUSABLE; for the partitions being inserted into before running the load program. /Hans Henrik Krohn

"Hans Henrik Krohn" <hhk_at_tips.dk> skrev i en meddelelse news:98qhvk$hmj$1_at_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:08:06 CST