indexed views, some clarifying terminology
Date: 2000/06/02
Message-ID: <un1l42jxl.fsf_-__at_fmr.com>#1/1
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