Re: Re: Bitmap Join Indexes
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 Feb 2004 14:01:37 -0000
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@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
