Re: Indexes on Views

1. As others have said, a query on a view can often benefit from using indexes defined on its underlying physical base tables. This assumes the optimizer/runtime is good enough to find/use the appropriate index(es), which often is not the case.
2. A materialized view is a physically stored table that corresponds to (has the same content as) a select query. These too can generally have indexes defined that will be used (hopefully).
3. My main point is that indexes on ordinary views are perfectly well defined, useful, and have been implemented in database systems supporting the relational model. (It is a pity that they are not supported by the SQL standard. This is just another case where SQL does not cleanly separate the logical relational model from its physical implementation.)

   This technique (indexing a view) is useful for almost any    computed column.
   One specific example would be to support case insensitive    searching, e.g. of names. The view could have a column    e.g. upper(original_last_name) as last_name,    with an index defined on it. Then a typical query    would be: select original_last_name from people where    last_name like 'SMITH%'
   The point of using an index on the view column is to    save space; this avoids storing the uppercase version of the name    column both in the table (where it is not needed) and in the index.      

Hopefully helpfully yours,
Steve

-- 
Steven Tolkin          steve.tolkin_at_fmr.com      617-563-0516 
Fidelity Investments   82 Devonshire St. R24D    Boston MA 02109
There is nothing so practical as a good theory.  Comments are by me, 
not Fidelity Investments, its subsidiaries or affiliates.