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: Thu, 8 Jan 2004 08:45:23 -0000
Message-ID: <btj5b9$6d7$2$8300dec7@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 Thu Jan 08 2004 - 02:45:23 CST

Original text of this message

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