Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because indexes itself will get so large. One should only consider partitioning these tables. I this right? I don't have real life experience about these cases.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

As long as the indices are smaller than the original table, or sorted differently, they can offer a performance boost, though in practice if you have low cardinality they may not.

The main cost of indices is increased time required for inserts and updates. If your database is updated daily but read from frequently, even large indices may be worthwhile. It depends on your data. Remember: all generalizations are false.

Can you give a more specific example of where you're considering applying an index? If you've already created one, does it show up in execution plans?

share|improve this answer
add comment

You should be creating indexes as needed to improve query performance. Large indexes are not something that you need to shy away from. On large OLTP systems it isn't uncommon for the indexes to be larger than the table. The same applies to data warehouses. As indexes are sorted they will always perform better than a table scan, especially on large tables.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.