Re: oracle's indexing methods

There are only two indexing techniques, b-tree and bitmap index. By the way, bitmap index only exists on Oracle Server (now Enterprise Edition). B-tree's are very efficient when the indexed column has many distinct values. They are less efficient when the indexed column has only a few distinct columns.
In this case , the bitmap index are better. Simply imagine, the bitmap index holds a list for each value. The list holds the rowid ranges where the value exits 1 or not exists 0. Because the list holds only ranges the list is very short and a query have to scan only those blocks of the table which mentioned in the list. In addition to that, there are two situations in which bitmap indexes allows much faster operations then b-tree. The bitmap or and bitmap and operations. If the where clause looks like 'where a = 4 and b = 5'. So bitmap indexes are faster with low distributed values and also consumes less disk storage. The disadvantage of the bitmap index is the maintainance by inserts and updates (when changing indexed values). The whole range in the list is locked during the transaction. So bitmap indexes can heavily reduce concurrency on OLTP systems.

Best regards,
Andreas Prusch

In article <35A4983D.9817F64F_at_cressoft.com.pk>,   Umar FArooq <umar.farooq_at_cressoft.com.pk> wrote:
> hi!
> im searching for a comparison between oracle's various indexing
> techniques....ie B-trees vs bitmapped indexes ....
>
> more specifically im looking for scenarios in which each type of index
> is used and a comprison of the performance, storage and maintenance
> requirements of each type
>
> Thx.
>
> umar farooq
> umar.farooq_at_cressoft.com.pk
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 09 1998 - 09:27:12 CDT