| |
 |
|
SQL Server
Tips by Burleson |
snapshot SQL Server Indexed
View
The terms are not perfect synonyms because
each vendor’s implementation also has some distinguishing features;
however, I'd like to emphasize what the three DBMSs have in common,
which happens to be what an advanced DBMS ought to have.
-
First, permanent materialized views are
maintainable. Effectively, this means that if you have a
permanent materialized view (say, View1) based on table Table1,
then any update to Table1 must cause an update to View1. Since
View1 is often a grouping of Table1, this is not an easy matter:
either the DBMS must figure out what the change is to be as a
delta, or it must recompute the entire grouping from scratch. To
save some time on this, a DBMS may defer the change until: (a)
it's necessary because someone is doing a select or (b) some
arbitrary time interval has gone by. Oracle's term for the
deferral is "refresh interval" and can be set by the user.
(Oracle also allows the data to get stale, but let's concentrate
on the stuff that's less obviously a compromise.)
(By the way, deferrals work only because the DBMS has a "log" of
updates, see my earlier DBAzine.com article, Transaction Logs.
It's wonderful how after you make a feature for one purpose, it
turns out to be useful for something else.)
-
Second, permanent materialized views can
be indexed. This is at least the case with SQL Server, and is
probably why Microsoft calls them "indexed views". It is also
the case with DB2 and Oracle.
-
Third, permanent materialized views
don't have to be referenced explicitly. For example, if a view
definition includes an aggregate function (e.g.: CREATE VIEW
View1 AS SELECT MAX(column1) FROM Table1) then the similar query
-- SELECT MAX(column1) FROM Table1 -- can just select from the
view, even though the SELECT doesn't ask for the view. A DBMS
might sometimes fail to realize that the view is usable, though,
so occasionally you'll have to check what your DBMS's "explain"
facility says. With Oracle you'll then have to use a hint, as in
this example:
SELECT/*+ rewrite(max_salary)
*/ max(salary)
FROM Employees WHERE position = 'Programmer'
Permanent materialized views are best for
groupings, because for non-grouped calculations (such as one column
multiplied by another) you'll usually find that the DBMS has a
feature for "indexing computed columns" (or "indexing generated
columns") which is more efficient. Also, there are some restrictions
on permanent materialized views (for example, views within views are
difficult). But in environments where grouped tables are queried
often, permanent materialized views are popular.
This is a book excerpt from:
Advanced SQL Database Programmer Handbook
Donald K. Burleson, Joe Celko, John Paul Cook, Peter Gulutzan
ISBN: 0-9744355-2-X
http://www.rampant-books.com/ebook_dbazine_SQL_prog.htm
|
