Re: Disadvantages of Bitmap Indexes?

The locking issue is unlikely to impact you with only 1200 rows inserted in a week period. In any event, even is one session is blocked by a lock, it will clear in a reasonable period of time and then the next dml statement will execute.

What can cause a problem is that any alter table command run against the table with the bitmap index will put the index into a state called "direct load". This will prevent any updates to the index. I have no idea why this is, but I found out about it the hard way. The only way to get the index out of direct load state (that I know of) is to drop it and recreate it.

Bitmap indexes can dramatically improve performance in some cases and yours sounds like an appropriate use of the bitmap index. I do not believe you will have any "heavy load OLTP" issues with it as 1200 inserts per week isn't what I would call a heavy load. I would be *very* sure that you remain vigilant of any alter table commands run against the table though or you will find everything locked until you drop (and recreate) the index.

I would encourage you to place a bitmap index on a test table and then perform any alter table command on the table and then query dba_indexes for the status of the bitmap index to see what I am talking about.

--
Dave A


<joseharvey_at_my-deja.com> wrote in message
news:8s1g7p$ul8$1_at_nnrp1.deja.com...


> Hi gurus,

> I have a table containing about a million records. One column refers to

> an object type used as a foreign key to another table; there are about

> 150 different possible values. I think a bitmap index would be a good

> choice here; on our test environment it emproves performance

> dramatically. However, on my documentation there is a sentence about

> locking that makes me hesitate:

>

> "Bitmap indexes benefit data warehousing applications but they are not

> appropriate for OLTP applications with a heavy load of concurrent

> INSERTs, UPDATEs, and DELETEs."

>

> What is the meaning of "heavy load"? On my application, there will be

> about 1200 rows per week inserted on this table, performed by up to 150

> users. Will the bitmap index decrease performance considerably?

>

> Thanks for your answers

>

>

> Jose Harvey

>

>

> --

> -=[ Sancho ]=-

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.