Re: Growth of Local Partitioned Bitmap Indexes

Any change in the from or to value will lock them all -- test it. I did a long time ago with a simple two column table (name and gender) -- alot of people undergoing sex changes!

All the rows get locked. Yes, updates to other columns do not cause a lock. But, if that column is frequently updated, column, not row, then it is generally not a good idea to bitmap index it in a multiuser env. If it's batch, then it's OK. Because low cardinality column changes will affect a large number of rows. Unless of course, you're like some guy I knew who sprinkled bitmap indexes everywhere as the new feature of the month.

Dom.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<btj5b9$6d7$2$8300dec7_at_news.demon.co.uk>...
> 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
>
>
> "Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
> news:1a75df45.0401072235.64ca1b02_at_posting.google.com...
> > "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote
> >
> > > Notes in-line.
> > <snipped>
> >
> > Thanks Jonathan for the input. The storage mechanism used by bitmap
> > indexes is now a tad clearer. Feels like parts of the Oracle docs are
> > written in a strang English dialect that's at times hard for me to
> > understand the actual details. :-)
> >
> > Oh yeah - neglect to mention that this is a batch database and not
> > really OLTP. These specific tables are hit by a single large
> > transaction process - very little concurrent processing. Which is in
> > part why I hoped to get away with it.
> >
>
> You can get away with it in these circumstances - but you do
> need a large PCTFREE, and you do need to do large-array
> processing, or bulk updates with a single SQL statement to
> limit the damage.
>
> > The index growth has become larger in recent months - recent
> > production processing changes (and a few physical design changes, and
> > an 8i to 9i upgrade) have been made.., including now using more status
> > flags before the data is settled into the final 2 states at the end of
> > the month.
> >
>
> A few extra value would make a big difference if they were evenly
> spread; both in the build size of the index, and in the update effects
> as you pass through more changes. Especially relevant if there are
> only a few values to start with.
>
> > Performance has however improved overall with a factor of 2 if not
> > more (mostly due to physical re-design and partitioning).
>
> If you have only a few flag values making a significant difference,
> I suspect you must be in a state where most of the rows have one
> uninsteresting state, and the interesting data is a relatively small
> percentage of the data identified by a few other values.
>
> If this is true, you might consider having several function-based
> b-tree indexes on the same column, one for each legal value, each
> function returning NULL for all other values. Typically you impose a view
> over the table so that you can query a pseudo-column, and make
> the table look as if it has several different flag fields. This avoids
> the bitmap issues, keeps the btrees small.
>
> >
> But seems
> > like we now have reached a stage where the space required for the
> > bitmap indexes during monthly processing are too costly versus the
> > performance gains obtained it.
> >
> > Besides, the reporting processes (which find the bitmap indexes most
> > useful) usually run towards month-end anyway - thus that will likely
> > be a much better time to slap these indexes on the table when the data
> > has settled.
> >
>
> Building bitmap indexes on demand is a fairly common practice. (In fact,
> building b-trees on demand should be commoner than it is, but it takes
> more courage (and more time, which usually isn't available)). In many
> cases, "on demand" means - 'drop before update', 'build after update'.
>
>
> >
> > --
> > Billy
Received on Fri Jan 09 2004 - 11:38:32 CST