| 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: Bitmap Indexes
Kirti,
'> The only issue we had with bitmap indexes was the way it used up extents, if those were not droppped before the dataloads.'
If I remember correctly, this issue was fixed in 7.3.4.4 as I was having the same problem while dataload in our datawarehouse and workaround was to drop index before inserts and an index size 10M was using unlimited space. This problem was in 7.3.4.0...Besides there were couple of other issues relating to bitmap indexes specially rebuilding of bitmap index on a table which was having degree > 1 was resulting in ORA-600 messages even if you were not specifying parallel option. I worked with Oracle Support on this issue for around one year but not resloved till I left that company.....
Regards
Rafiq
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Fri, 08 Feb 2002 05:58:21 -0800
MOHAMMAD RAFIQ
--- Begin Message ---
Kirti,
Just cureous. Normally bitmap indexes ae ae used for low cardinality columns. I am confused when you state - "Just one table uses it(bitmap index), with b-tree indexes
>for PKry and one other non-unique index"
If you mean - you use Bitmap index for the non unique index with b-tree index for PKey I am OK with that.
Shaibal
>From: "Deshpande, Kirti"
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Bitmap Indexes
>Date: Thu, 07 Feb 2002 19:58:20 -0800
>
>Ethan,
> I have been using bitmap and b-tree indexes on the same table in our data
>mart since 7.3.4. Never had a problem with query performance. Now we have
>partitioned the tables in 8.1.7.x and most tables that were using bitmaps do
>not need to use them anymore. Just one table uses it, with b-tree indexes
>for PKry and one other non-unique index. No problems so far.
> The only issue we had with bitmap indexes was the way it used up extents,
>if those were not droppped before the dataloads. The problem still exists
>with 8.1.7.x. Have not tried with 9i yet..
>
>HTH,
>
>- Kirti
>
>
>-----Original Message-----
>Sent: Thursday, February 07, 2002 3:07 PM
>To: Multiple recipients of list ORACLE-L
>
>
>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
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.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).
MSN Photos is the easiest way to share and print your photos: Click Here
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaibal Talukder INET: shaibal_talukder_at_hotmail.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). --- End Message ---Received on Fri Feb 08 2002 - 13:09:52 CST
 |
 |