| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: bitmap indexes
Diane wrote:
> In article <72pjd1$km5$1_at_nnrp1.dejanews.com>, slniemann_at_my-dejanews.com
> writes
> >I am trying to get some general information about bitmap indexes fitting to my
> >situation.
> >
> >I have a database containing tables with rows of 750,000+
> >
> >In these tables is a foreign key of codes (72 distinct) which is set to its
> >description table. These tables do receive a lot of update and insertion,
> >many, many per day.
> >
> >As I understand it, the bitmap index works best with a static table, but
> >would it be completely idiotic to form bitmap indexes for this column in
> >these tables?
> >
> >I am still learning about bitmap indexes and could use any feedback you have.
> >Thanks.
> >
> >
> >S Niemann
> >
> >-----------== Posted via Deja News, The Discussion Network ==----------
> >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> Bitmap indexes are best for when there are very few different value
> posibilities compared to the total number of rows. For example if you
> have 10,000 rows and a column with 10/20 different possible values a
> bitmap index would be useful.
> We use bitmapped indexes on a number of tables in our data warehouse -
> invoices, invoice lines, and the fact tables, none of which are static
> in that they are being added to every day - although they are not being
> amended or deleted from so do not match your situation.
> The best thing I can suggest is 'suck it and see'! add a bitmap index,
> measure performance, drop the bitmap and replace with a standard index
> and measure performance.
> Good luck.
> --
> Diane
I would like to remind you that Oracle's documentation recommends using bitmapindexes ONLY for DSS and NEVER for OLTP.
In your example, (Niemann) you have 72 distinct values out of 750,000+ rows,
sounds perfect for a bitmapped index, not so when DML are performed on the
underlying table.
The reason why is simple, the internal layout of a bitmapped index will only have
72 "rows" as
compared the a regular B-tree index which will hold a "row" in the index for each
value in underlying table (750,000).
Duplicate rows in bitmap index are bundled under same "row" in index, whereas they
get separate "rows" in
regular B-tree index.
Recall Oracle's row-level locking, hence when you INSERT (or any other DML) a
value into a table, the corresponding "row" in the
bitmap index is locked, thus preventing any DML's on that value, until that lock
is released (commit, or rollback).
If you had a bitmap index on sex column (50% M, 50% F), by inserting one row
(without committing) you are now locking 1/2 of the table.
Such condition will not hold true with a regular B-tree index, since when you
insert a new row into the table you are also creating a new "row"
in the B-tree index (in the bitmap similar values are bundled under same "row"),
so there are no locking issues with a regular B-tree index.
If you do have a bitmap index and DML going at the same time, you will see increase in enqueue waits, specifically TX enqueue.
I think the concepts manual talks about it in more details. bottom line, bitmap index is good for DSS (no/little DML's) and very bad for OLTP (lots of DML's).
Saar.
--
__ _ _ __ _ _ _ _ ___ ______________________________ (( /\\ /\\ ||) |\V/| /\\ /\\ >/ Principal Performance Engineer_))//-\\//-\\||\ |||||//-\\\\//<_ Oracle Corporation Compaq SBU
////////////////// Drop x's in email (spam) //////////////////////Received on Wed Nov 18 1998 - 16:11:39 CST
 |
 |