Re: Indexes on Views
From: <steve.tolkin_at_fmr.com>
Date: 2000/04/24
Message-ID: <u3dobjx2m.fsf_at_fmr.com>#1/1
Date: 2000/04/24
Message-ID: <u3dobjx2m.fsf_at_fmr.com>#1/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.
- 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).
- 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.Received on Mon Apr 24 2000 - 00:00:00 CEST