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 ...

Get Oracle® DBA Guide to Data Warehousing and Star Schemas now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.