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: Growth of Local Partitioned Bitmap Indexes

Re: Growth of Local Partitioned Bitmap Indexes

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 7 Jan 2004 10:41:39 -0000
Message-ID: <btgnpi$bft$1$8302bc10@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 - 04:41:39 CST

Original text of this message

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