Guest

• Mar 23rd, 2005

 

bitmap index

JaiTHM

• Jul 15th, 2005

 

Function Index, B-tree Index, Partition Index, Hash index etc..

J Lehew

• Aug 25th, 2005

 

On the fact table it is best to use bitmap indexes. Dimension tables can use bitmap and/or the other types of clustered/non-clustered, unique/non-unique indexes. 
 
To my knowledge, SQLServer does not support bitmap indexes. Only Oracle supports bitmaps. 

shivi

• Aug 26th, 2005

 

It generally depends upon the data which u hav ein table if u have less distinct values in particular column its always that u built up bit map index... rather that other one on dimension tables generally we have indexes... 
 

sithusithu

 Profile Answers by sithusithu 

• Jan 19th, 2006

 

That is based on requirement and size of your data mart/data warehouse; Most of the data warehouse is in Bitmap index

 Cheers,

Sithu

sri

• May 25th, 2007

 

Typically for dimension tables, we use bitmap index and for fact tables we use B-Tree index.

cetzhbo

• Jun 19th, 2007

 

Space requirements for indexes in a warehouse are often significantly larger than the space needed to store the data, especially for the fact table and particularly if the indexes are B*trees.Hence, you may want to keep indexing on the fact table to a minimum. Typically, you may have one or two concatenated B*tree indexes on the fact table; however, most of your indexes should be bitmap indexes. Bitmap indexes on the foreign key columns on the fact table are often useful for star query transformation.
The maintenance window will also dictate whether you use partitioned indexes, which can be faster and easier to maintain.
Bitmap indexes also take up much less space than B*tree indexes and so should be preferred. On the other hand, dimension tables are much smaller compared with the fact table and could be indexed much more extensively. Any column of the dimension table that is frequently used in selections or is a level in a dimension object is a good candidate for indexing.

ibrarx

 Profile Answers by ibrarx 

• Mar 7th, 2009

 

1. Hash Index - If we know that the hashing will result in uniform distribution and doesn't create hotspots (spikes-chaining).
2. Bitmap Index -
   If the degree of cardinality is high for the attribute, means that there are more unique number of values for a particular attribute. Low cardinality attribute is not suitable for bitmap index because more number of records are locked which result in the locking of a whole table, leading to the lock on a whole database.
3. Join Index -
   If we have queries that target most of the time to a specific subset of data. This is similar to the creation of views, but with the exception that it is materialized view.
4. Cluster Index -
   This index is suitable for range queries.

• OLTP's conventional indexes are not used for data warehouse, these indexes are :
• DENSE INDEX
• SPARSE INDEX
• MULTI LEVEL INDEX (B-TREE INDEX)