Re: Disadvantages of Bitmap Indexes?

In article <8s1lja$1g6$1_at_soap.pipex.net>,   "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote:
> The disadvantage and the reason for the warning is that the bitmap
 index has
> to be recalculated in its entirety for each of those operations. I
 believe
> it also locks the underlying table while it does this , but I may
 well be
> wrong about that.
>
> HTH
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> <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.
>
>

A B*-tree index entry contains a single rowid. Therefore, when the index entry is locked, a single row is locked. With bitmap indexes, an entry can potentially contain a range of rowids. When a bitmap index entry is locked, the entire range of rowids is locked. The table itself is not locked, but it is possible that a substantial number of rows will be locked for the given INSERT/UPDATE operation.

Locking issues affect DML operations, and may affect heavy OLTP environments. Locking issues do not, however, affect query performance. As with other types of indexes, updating bitmap indexes is a costly operation. Nonetheless, for bulk inserts and updates where many rows are inserted or many updates are made in a single statement, performance with bitmap indexes can be better than with regular B*-tree indexes.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.