My Index Strategy

[abgoosht]

Hi experts,

after reading a lot about indexing strategies of fact table, I am much more confused then before :-). So I will be very appreciated about suggestion for the following fact table and my actually chosen index strategy:

FactOrderTable
--------------
FK_1
FK_2
Fk_n
Order_numer (DD)
Order_line_numer (DD)
Year (DD)
Fact_1
Fact_n

E.g.: FactOrderTable
FK_1 | FK_2 | Order_numer | Order_line_number | Year | Fact_1

1 | 2 | 123456 | 1 | 2009| 54.00 €
1 | 2 | 123456 | 2 | 2009| 34.00 €
1 | 2 | 123456 | 3 | 2009| 14.00 €

Distinct rows of Order_number of the FactOrderTable ~ 60 Mio.
Distinct rows of Order_line_number ~ 7667
Distinct rows of Year = 7 (2007-2012)

My index strategy:
> non-clustered, non-unique index on all Fks
> clustered, unique-index for (Order_number, Order_line_numer, Year) < This three columns are not declarated as PKs in the Fact Table but these there Columns make a row unique.

Make it sense to declare a non-clustered and non-unique index for the Order_number, Order_line_numer and Year columns? I read about such a strategy for DD.

Is there any relationship between the cardinality of data in a column and whether to index or not a column?
E.g. Columns with high-cardinality (distinct Order_number ~ 60 Mio.) should be indexed separately?

Thank and best regards,
Abgoosht

[ngalemmo]

Indexing strategies will vary among DBMS products. Consult your DB's user manuals. Most of them have a document or section on data warehousing and indexing for star schema.

[John Simon]

Neil is right.

Which DBMS are you using?

[abgoosht]

Oracle 11g DBMS. Unfortunatley just the Standard Edition and not the Enterprise Edition.

Neil is right.

Which DBMS are you using?

[ngalemmo]

Bitmap indexes on each fact table FK column. Normal b-tree PK index on dimensions and various alternate indexes on comonly used dimension columns as needed.

Oh, John... its Nick, not Niel.

[Sponsored content]