Oracle® DBA Guide to Data Warehousing and Star Schemas

Star Schema Index Design

This is the section where most DBAs' eyes roll back and they begin to question these techniques with heated fervor. This section will discuss the bitmap index design required for getting star transformation explain plans. So without further adieu, let me give the recommendations and then try to explain exactly why it must be done this way:

• Create a separate bitmap index on each fact table's dimension table's foreign key columns
• Create a separate bitmap index on each non-key column in the dimension tables

That does not sound too bad, until you think a little more about it. What I am saying is that you fully index your dimension table columns using bitmap indexes, and that you also create bitmap indexes on your fact table ...