Tell me more ×
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.
up vote 0 down vote favorite

I have a view which is doing summation of several columns:

Select cola, colb, cola + colb + colc from table tbl_a

Can I know what are the indexes I should create for the view to improve its performances?

share|improve this question
3  
What is the actual performance problem that you have? How do the searches usually look? – Marian Jan 11 at 9:58

1 Answer

up vote 5 down vote

Some thoughts...

  • what filters or joins will you use on the view? These determine what indexes you'll need on the table
  • have you considered a computed column to pre-sum cola+colb at the table level?

Personally, I avoid views because a view doesn't have persistence (unless it is indexed itself): it is simply a macro expanded by the query optimiser. That is, say you have 2 views that join (one with 3 table, one with 4 tables): the actual query plan will show all 7 tables.

In the case above, you have no filters. This means that no index can help you: you'll get a clustered index (which is actually the table and you do have a clustered index, right?) scan

share|improve this answer
Well, depending on the width of the table, an index on cola, colb, colc might be a lot narrower than the clustered index and therefore might provide a slightly more efficient plan. But otherwise I agree. Too many people think an indexed view is a magic way to make a view perform better, when the problem isn't necessarily the view at all... – Aaron Bertrand Jan 11 at 14:12

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.