Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: Bitmap Join Indexes

RE: Re: Bitmap Join Indexes

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 13 Feb 2004 09:30:23 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC0D@bosmail00.bos.il.pqe>


And, as far as the cost of rebuilding goes, well, ideally, you'd set up your partition scheme so that new data is only loaded into a new partition, and then you build the biptmaps and bitmap joins only for the new partition. That way, you don't suffer the overhead of a rebuild of bitmaps on the existing data every time you load new data.
-Mark

-----Original Message-----

From:	Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
Sent:	Fri 2/13/2004 9:01 AM
To:	oracle-l_at_freelists.org
Cc:	
Subject:	Re: Re: Bitmap Join Indexes

I think there are two possible wins,
CPU or disk space/IO

Assume you have a dimension like:

    cities(city_id pk, state_id)
and the fact table contains

    (city_id, .......)
and you often query the fact table by state. (Forget about all the other dimension tables that you would be using at the same time, for the purposes of this argument).

Option (1):
You copy the state_id down to the
fact table, so that you can do a

    single value bitmap query
this increases the size of the fact table and the amount of I/O you generally
have to do. If you do this for a dozen
other attributes of dimension data as well you're looking at a lot more I/O.

Option (2)
You let Oracle do a star-transformation
type of thing - then Oracle has to acquire the bitmap on the fact table for every city_id in the given state and do a bitmap merge of all those bitmaps. Bitmap methods are necessarily quite CPU intensive at the best of times - and this probably the nastiest bitmap thing to do.

If you create the bitmap join index, the fact table doesn't increase in size, and the bitmap access is a single value bitmap access, not a bitmap merge.

It probably boils down to how fast you can recreate the bitmap join indexes, and the trade-off between cost of build and frequency of use.

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

Next public appearances:
 March 2004 Hotsos Symposium - The Burden of Proof  March 2004 Charlotte NC OUG - CBO Tutorial  April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

as far as performance on selects, how would you rate it? I played around it with the Oracle demo 'SH' schema. There is a fact table with some dimension tables there. I noticed a radical reductions in LIOs, by using a bitmap join index.

what is your opinion of using them with non-star or snowflake schemas(i know the tables need to be read only).



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Fri Feb 13 2004 - 08:30:23 CST

Original text of this message

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