RE: What type of Indexing mechanism do we need to use for a typical datawarehouse
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.
RE: What type of Indexing mechanism do we need to use for a typical datawarehouse
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...
RE: What type of Indexing mechanism do we need to use ...
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.
RE: What type of Indexing mechanism do we need to use for a typical datawarehouse
Hash Index - If we know that the hashing will result in uniform distribution and doesn't create hotspots (spikes-chaining).
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.
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.
Cluster Index - This index is suitable for range queries.
OLTP's conventional indexes are not used for data warehouse these indexes are :
