Re: Growth of Local Partitioned Bitmap Indexes

Since when is a column that is updated frequently a good candidate for a bitmap index? Do you realize that while that update is occurring, every friggin row with the same index value is LOCKED? Use a regular index instead or switch it to bitmap when it becomes mostly read only.

Dom.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<btgnpi$bft$1$8302bc10_at_news.demon.co.uk>...
> It's expected behaviour when you update
> columns that are bitmapped. You tend to
> get away with large inserts, because these
> tend to be at the end of the table. You
> can get away with large deletes. But any
> process which does lots of little updates
> tends to explode the index catastrophically.
>
> This is because a bitmap index entry is
> very large, and when you change a row
> in the table, you modify two bitmap index
> entries - and in indexes, this means the old
> entry is marked as deleted and a new entry
> is created. Because a bitmap index entry
> may go up to 50% of the block size, copying
> a single entry can easily cause a block split.
>
> If the block splits, you need a new branch
> block entry; and if the old and new entries
> are the things that start the resulting leaf blocks,
> the branch block has to copy the whole
> entry, up to the point where the one bit
> changed.
>
> You may be able to circumvent the issue
> (if your application doesn't do single row
> updates instead of batch updates) by
> creating the bitmap indexes with pctfree
> greater than 33
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
> news:1a75df45.0401070215.42623a5a_at_posting.google.com...
> > HP-UX 11i running Oracle 9.2.0
> >
> > I have a number of partitioned tables using local bitmap indexes. The
> > indexes (and partitions) do make a significant difference (a good one
> > that is) ito performance.
> >
> > However, during the dynamic stage of production process (data loaded
> > and processed, including a lot of bitmapped column value changes),
> > these local bitmap indexes get huge.
> >
> > And I mean huge. If I recall correctly, at one time last year
> > November, over 4GB (total index size) for a single column local bitmap
> > index on a 12GB partitioned table.
> >
> > Re-create the index and the size drops by over 80%.
> >
> > This growth seems to be linked to the amount of value changing being
> > done to the bitmap indexed column. During the initial stages there are
> > about 5+ distinct states/values in these columns. These change to 3
> > distinct states as the data "settles" down towards the end of the
> > month.
> >
> > The sizes of these bitmap indexes are causing space issues.. and is
> > something that I've not seen when dealing with local bitmap indexes on
> > largely static warehouse data (i.e. data volume increases but data
> > itself is static).
> >
> > With dynamically and continually changing OLTP data this is seems to
> > be a problem.
> >
> > Googling and looking at Metalink have not turned up anything
> > interesting.
> >
> > Any comments?
> >
> > --
> > Billy
Received on Wed Jan 07 2004 - 14:12:42 CST