Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Growth of Local Partitioned Bitmap Indexes
Notes in-line.
-- 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 "Domenic G." <domenicg_at_hotmail.com> wrote in message news:c7e08a19.0401071212.3f5f5f88_at_posting.google.com...Received on Wed Jan 07 2004 - 15:17:12 CST
> Since when is a column that is updated frequently a good candidate for
> a bitmap index?
In this particular case, if you read the original posting. General rule - if the benefit outweighs the cost, then you use the feature, even is 'everyone knows' that it's a bad idea.
> Do you realize that while that update is
occurring,
> every friggin row with the same index value is LOCKED?
Wrong. No other rows are locked. There are two values to consider, by the way, the FROM value and the TO value. And you only have a collision problem if other processes want to update the indexed columns of rows which are covered by the same two bitmap chunks. So if there are no other processes you don't get a collision problem; or if there are other processes they can go ahead and do other types of updates.
>
Use a regular
> index instead or switch it to bitmap when it becomes mostly read only.
>
To paraphrase your original outburst: "Since when is a column that is subject to update a good candidate for a b-tree index" ? Presumably you don't realise that the undo and redo generated by a single statement update that changes an indexed column in thousands of rows may generated far more undo and redo for a b-tree index than the same update would for a bitmap index. Billy, Thinking about it, 33 pct MAY be okay for 9i, which behaves a little differently from 8i - but it's possible that 50 pct is still the best bet. It depends on the way in which your updates work. In passing, I think the size problem more or less disappears in 10g,
> 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