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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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?
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.