indexed views, some clarifying terminology

From: <steve.tolkin_at_fmr.com>
Date: 2000/06/02
Message-ID: <un1l42jxl.fsf_-__at_fmr.com>#1/1


In the hope of clarifying the subject let me introduce some terminology.

A "view" (aka a viewed table) is a table whose contents correspond to a select query. The dbms may impose limitations on what language constructs can be be used in the view's defining query.

A "materialized view" is a view table that stores its contents, i.e. the results of running the view's query.

A "pure view" or "virtual view" is a view that is not a materialized view. This is the usual connotation of the word view. The increased use of materialized views has forced us to invent this term. (I do not recall the name for this linguistic phenomenon, e.g. the need to add the adjective in analog watch, whale oil, etc.)

A "snapshot view" is a materialized view that might be out of date with respect to its query. The normal connotation of materialized view is a snapshot view. This is what Oracle currently supports.

A "maintained table" is a stored table whose contents may be changed when other tables in the database are changed. These changes are most often implemented via triggers on the other tables. These triggers may be defined explicitly by the user, or automatically by the dbms.

A "maintained view" is a kind of materialized view that is always up to date with respect to its query. The maintenance operations are implemented automatically by the dbms. (It is also a kind of maintained table.)

An "indexed view" is any view, pure or materialized, that supports the use of indexes. (An index is any physically stored auxiliary data structure intended to improve performance. An index must not change semantics, i.e. the contents of the view.)

It is possible, and desirable for space reasons, to index pure views. In the simplest case of a select project view of a single table the index just needs to just store one tuple id. (For views that include joins it still might be possible to store only one tuple id, e.g. if the dbms knows there is a 1:M join. In other cases the index might need to store one tuple id per lowest level base table.)

By this terminology a Microsoft SQL Server 2000 "indexed view" is really a maintained view. If some dbms later supports indexes on a pure view we could say "indexed pure view", but this term is awkward.

It also seems that Microsoft has somehow conflated the separate notions of updateability of a view and indexing of a view. Some of the limitations of the SQL allowed in an indexed view are too restrictive, i.e. indexing would work fine even if they are allowed. Specifically, the very unfortunate limitation of requiring a *unique* index on the view. I hope this is just an implementation limit of the first release. (When virtual views first were introduced they did not allow UNION, and this badly hurt their usefulness and adoption rate.)

P.S. I have worked on systems that implemented maintained tables (Wang EZQuery, later Wang PACE), and indexed pure views (Spires, from Stanford). Even dBase supported indexes on computed columns! These techniques are very practical, and completely consistent with the ideal of the relational model, to cleanly separate the logical model of tables from the physical implementation.

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 Fri Jun 02 2000 - 00:00:00 CEST

Original text of this message