Re: Re: Bitmap Join Indexes

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 13 Feb 2004 14:01:37 -0000
Message-ID: <00f101c3f239$eb3eb980$6702a8c0@Primary>

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

Original Message ----- From: <ryan.gaffuri_at_cox.net> To: <oracle-l_at_freelists.org>; <oracle-l_at_freelists.org> Sent: Friday, February 13, 2004 1:48 PM Subject: Re: Re: Bitmap Join Indexes

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
-----------------------------------------------------------------

Received on Fri Feb 13 2004 - 08:01:37 CST