> 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 a) you still have the bitmap indexes and b) why you haven't killed that user yet :) Rachel --- [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
