Bitmap Indexing

[hellraiser007]

Hello,

Can you guys please tell me whats bitmap indexing and is it supported by SQL server version you have on your servers?? its critical for me to know that...

Also can you please tell me how it is implemented in SQL Server??

Thanks for advance and also waiting for your reply in couple of days probably 1-2 days as i need to finalize the webhost.. I will probably need a dedicated server... also want details for that but critical is bitmap index... I am new to SQL server 2k...

Hoping for best answer

Hellraiser007
(Rest In Peace)

[Jason]

Hi,

Bitmap indexes are widely used in data warehousing environments. The environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transactions. For such applications, bitmap indexing provides:

* Reduced response time for large classes of ad hoc queries
* Reduced storage requirements compared to other indexing techniques
* Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory
* Efficient maintenance during parallel DML and loads

Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of space because the indexes can be several times larger than the data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table.

An index provides pointers to the rows in a table that contain a given key value. A regular index stores a list of rowids for each key corresponding to the rows with that key value. In a bitmap index, a bitmap for each key value replaces a list of rowids.

Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so that the bitmap index provides the same functionality as a regular index. If the number of different key values is small, bitmap indexes save space.

Bitmap indexes are most effective for queries that contain multiple conditions in the WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.


Presently it seems that it is only supported with the ORACLE 9i onwards
It is not supported with SQL Server Database.