Implementing secondary indexes on fact tables. Is it a good idea?

View previous topic View next topic Go down

Implementing secondary indexes on fact tables. Is it a good idea?

Post  sreekanth573 on Tue Nov 29, 2011 2:11 am

Hello, we have surrogate keys on our fact tables and dimension tables.

While designing my stars, I had to leave two columns (eg: doc_id and doc_item_num) that are a part of business keys on the fact table itself. The other columns were Customer Dimension and Date dimension. I defined the primary index as (doc_id, doc_item_num, dte_surr_key). This was a good selection as the users are running time-based queries quite often.

Now, on certain cases, the queries are being written with just doc_id, doc_item_num and the database is not able to respond. The query is taking a long time to respond. The DBA suggested we add secondary indexes on the fact table for doc_id and for doc_item_num. I know that adding secondary indexes on a fact table will slow down ETL load processes. I also read that adding additional indexes on fact tables is not a good practice.

Please, suggest any alternatives or your points of view on this one.

Thank you,
Sreekanth




sreekanth573

Posts : 3
Join date : 2010-11-08
Location : Dublin

View user profile

Back to top Go down

Re: Implementing secondary indexes on fact tables. Is it a good idea?

Post  BoxesAndLines on Tue Nov 29, 2011 10:04 am

Drop indexes before loading and rebuild when done. Secondary indexes are fine and required.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

SQL Server?

Post  TenchiJin on Tue Nov 29, 2011 12:28 pm

If this is a SQL Server database then the engine can actually use the the existing clustered index for both those scenarios. It can use columns from left to right so if the index is set as you have specified "(doc_id, doc_item_num, dte_surr_key)" then the engine will still use this index to run a query where the predicates are "doc_id, doc_item_num" as they are the first two columns in the index.

As for dropping indexes to to clarify, only drop the non clustered indexes not the clustered index. Also this is not always a 100% sure shot to speed ETL processing.

TenchiJin

Posts : 4
Join date : 2011-11-28

View user profile

Back to top Go down

Re: Implementing secondary indexes on fact tables. Is it a good idea?

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