some questions about bitmap

[edgetechservices]

recently, I read a book 'oracle dba guide to dw and star schemas'. It is a good book. but I wondered if some author said is right. he said: you need to index all dimension columns? did anyone really do it in his work?

 

[avjoshi]

I don't know what book you red but this is a very generic comment. The idea is that the index attributes are typically used in where clause of the query and hence indexing them will give you better performance. Also it is assumed that Dimensions are very small as compared to facts or measures. Hence the foreign key columns in facts are less selective; therefore bitmap indexes.
This is not necessarily true for all Dw implementations. I , for one, have never created indexes on all dimension table columns.

Hope this helps.

Anand.

 

[monsky]

It really depends. In my experience it was not necessary for me to index on all dimensions. Bitmap indexing is useful for fields containing data like yes/no, male/female. In my project I generally used B-Tree indexing.

I used Oracle and one that really helped me improve performance, aside from properly indexing, is to use the Cost Base Analyzer (CBO) through the use of ANALYZE and COMPUTE commands. These commands will help the CBO plan and create the most efficient way to execute SQL commands. I use these commands whenever I make changes (like loading) to a particular table or index.