My Index Strategy

View previous topic View next topic Go down

My Index Strategy

Post  abgoosht on Fri May 11, 2012 5:24 am

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


abgoosht

Posts : 5
Join date : 2012-01-24

View user profile

Back to top Go down

Re: My Index Strategy

Post  ngalemmo on Fri May 11, 2012 9:43 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: My Index Strategy

Post  John Simon on Sat May 12, 2012 2:30 am

Neil is right.

Which DBMS are you using?

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: My Index Strategy

Post  abgoosht on Sun May 13, 2012 9:21 am

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

John Simon wrote:Neil is right.

Which DBMS are you using?

abgoosht

Posts : 5
Join date : 2012-01-24

View user profile

Back to top Go down

Re: My Index Strategy

Post  ngalemmo on Mon May 14, 2012 1:13 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: My Index Strategy

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum