Best Practice For Indexes

Many books have very different opinions on what indexes you should create for a typical star schema having the following generic structure:

Fact table
fact_surrogate_key
dim1_sk
dim2_sk
dim2_bk (business key -- assuming hybrid type 2 scd, joins both with sk and bk with current_indicator=y constraint)
dim3_sk
...
measure1
measure2

Dim1
dim1_sk
attribs

Dim2
dim2_sk
dim2_bk
dim2_number
attribs
begin_date
end_date
current_ind

Do you prefer:
- Bitmap join indexes
- Bitmap indexes
- B-Tree indexes

What indexes do you prefer on fact table? Dimension Table?

Can you share experiences or perceptions on performance of the type(s) of indexes you have? Please do not point to links on theories / best practices available in books or Google.

-Mora

kmorasoabi wrote:dim2_bk (business key -- assuming hybrid type 2 scd, joins both with sk and bk with current_indicator=y constraint)

I've never seen that before... can you explain how it is supposed to work?


As far as indexes goes, it depends on the database system. If it is Oracle Enterprise Edition and you have purchased the appropriate add-ons, set the star optimization option on, and create bitmap indexes on all foreign keys on all fact tables. For dimensions, the usual b-tree index for the PK and NK columns and either bitmaps or b-trees on assorted attibutes (but for small dimension tables (< 200 rows), don't bother indexing attributes, it won't matter much).

I am a newbie in data warehousing.
I had gone thru your posts and they are really helpful for me.

I had a question relating to indexes.

Will it be beneficial to create bitmap join indexes between dimensions and facts in addition to bitmap indexes on foreign key columns in fact table?
If we need to create one, do we need to include all columns from each dimension while creating the bitmap join index?

Thanks in advance.


Rama.

It depends... use them if you need to. I would not include them in an initial implementation, but rather wait and see if there are performance issues that can be resolved by bitmap join indexes.

Indexes are not cheap. While I have never been very concerned with how much space an index takes (bitmaps are much, much smaller than b-trees in this regard), I am concerned with the amount of time it takes to maintain them. If you are updating or inserting into a table with a lot of active bitmap indexes, it usually takes longer than the same table with b-trees. The bitmaps themselves become fractured and may increase significantly in size from the update activity. It is usually necessary to rebuild the indexes after each load so they are in their most efficient form for queries. This takes time. So rather than overengineer a solution that anticipates performance issues that may never occur, I would implement the minimal set of indexes and tune the solution later when needed.