| 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
oops ... forgot ...
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Sent: Friday, February 08, 2002 11:23
AM
Subject: Re: Bitmap Indexes
Helen,
Attachments do not make it to the list.
Igor Neyman, OCP DBA<A
href="mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Sent: Friday, February 08, 2002 12:18
PM
Subject: Re: Bitmap Indexes
Hi,
Here is an article about it bitmap indexes
... I hope this gives you information.
Helen
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
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: <A
href="http://go.msn.com/bql/hmtag3_etl_EN.asp">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).
Received on Fri Feb 08 2002 - 14:25:21 CST
 |
 |