> why the bit maps don't cause a problem. Since a bit map index is
> only usable by the cost based optimizer and the user who built the
>warehouse refuses to compute the statistics on those indexes, so their
>not in use.
Dick -- based on the snippet above, I want to know why
- you still have the bitmap indexes
and b) why you haven't killed that user yet :)
Rachel
- dgoulet_at_vicr.com wrote:
> Ethan,
>
> Our data warehouse has both bit map and b-tree indexes and we
> normally do
> not have a problem. One headache we do have is that one of the
> indexes is on a
> column that accumulates a lot on new values each time the warehouse
> is loaded
> which really makes a mess of the load process, namely it takes way
> toooooo long.
> The answer is to drop the bitmap indexes before loading new data and
> then
> rebuilding them afterwards. Come to think of it that may be part of
> the reason
> why the bit maps don't cause a problem. Since a bit map index is
> only usable by
> the cost based optimizer and the user who built the warehouse refuses
> to compute
> the statistics on those indexes, so their not in use.
>
> Interesting, I'll have to do some experimenting now!!
>
> Dick Goulet
>
> ____________________Reply Separator____________________
> Author: "Post; Ethan" <Ethan.Post_at_ps.net>
> Date: 2/7/2002 1:06 PM
>
> Posted on behalf of a friend...
>
>
> Fellow Oracle data warehouse DBAs (if this isn't you, you can delete
> this
> note now):
> I have a question regarding Oracle's bitmap indexes. We have been
> DABBLING
> with bitmap indexes with mixed results here. In
> many cases, they are great solutions. In some cases, where we have a
> mix of
> bitmap and b-tree indexes on the same table, we occasionally get into
> trouble --- this has to do with the Oracle optimizer deciding, on
> the fly,
> to convert a regular b-tree index into a bitmap. It does this so
> that it
> can AND or OR the various bitmap indexes together. Sounds great on
> the
> surface but when this occurs, response time goes in the toilet.
>
> In one situation we have, we have a fact table with two bitmap
> indexes and a
> few other b-tree indexes. A particular query we run bogs down (NEVER
> COMPLETES) with this mix of indexes. Based on the access path that's
> being
> chosen, we know which b-tree index is being converted on the fly. If
> we
> convert that b-tree index into a bitmap (so we now have 3 bitmap
> indexes and
> Oracle does not need to create the third one on the fly), the query
> really
> runs well. If we replace the bitmap indexes with b-tree indexes (so
> we only
> have b-tree indexes), we get decent response times. (This is all on
> Oracle
> 8.1.7.2.0, by the way.)
>
> I'm wondering if the rest of you data warehouse DBAs have gone "whole
> hog"
> with bitmap indexes. My testing shows that when Oracle doesn't have
> to
> create a bitmap index on the fly, the queries respond wonderfully.
> So, I'm
> wondering if our dabbling is actually a bad validation approach and,
> instead, we should be 'running' with LOTS bitmap indexes instead of
> 'crawling' with only a few of them. In other words, maybe we're not
> "taking
> all of our medication", as someone else put it recently.
>
> Any insight would be most appreciated. I'm not looking for insight
> on the
> query I have used as an example. I'm looking for a generalized
> answer that
> says, "Yes, if you start using bitmap indexes, you should go TOTALLY
> to
> bitmap indexes" or, "Hmmm, we're using some bitmap indexes and some
> b-tree
> indexes and don't have the problem you have".
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Post, Ethan
> INET: Ethan.Post_at_ps.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: dgoulet_at_vicr.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Feb 08 2002 - 04:02:33 CST
