| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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).
>
> From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> Date: 2004/02/13 Fri AM 04:44:28 EST
> To: <oracle-l_at_freelists.org>
> Subject: Re: Bitmap Join Indexes
>
>
> In principle, there should be no reason why
> these two features should not be used together.
>
> Query rewrite operates by examining MVs,
> base tables, constraints, and dimensions to
> produce SQL that can satisfy the original
> query. It then passes this SQL and the original
> SQL through the optimizer to calculate the cost
> and see which one is cheaper.
>
> The first step OUGHT to be independent
> of bitmap join indexes, the second step
> OUGHT to consider bitmap join indexes
> in the standard way. So there seems to be
> reason why the two features should collide.
>
> However, it is often the case that features
> are disabled (in initial releases) for no apparent
> reason.
>
> One of my articles on www.dbazine.com talks
> about the bitmap join index - this may give you
> some ideas about potential costs and benefits.
>
> Bottom line, really, is that they can take MUCH
> longer than simple bitmap indexes to build, and
> you almost certainly have to drop or mark them
> as unusable when you load new data.
>
>
> 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: "Freeman, Donald" <dofreeman_at_state.pa.us>
> To: "Oracle-L (E-mail)" <oracle-l_at_freelists.org>
> Sent: Thursday, February 12, 2004 9:17 PM
> Subject: Bitmap Join Indexes
>
>
> In our datawarehouse we chose to go with materialized views and query =
> rewrite to speed up our report performance. Ever since I read about =
> Bitmap Join Indexes I've been wondering whether these are exclusive =
> features, or can be implemented together, or one or the other is =
> superior. I'm not sure how to evaluate the benefit of these features. =
> Would someone comment?
>
> Don Freeman
> Database Administrator
> Bureau of Information Technology
> PA Department of Health
> 717-783-4743 Ext 337
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
-- 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 - 07:48:12 CST
 |
 |