In this post, originally written by Glenn Paulley and posted to sybase.com in April of 2009, Glenn discusses the different terminology used by other vendors to talk about what we call materialized views in SQL Anywhere.
I also want to note that in the SAP HANA architecture, materialized views should be unnecessary because all data is available in main memory and can be processed/aggregated on the fly.
Every so often we encounter a situation where a prospective customer asks whether or not SQL Anywhere offers support for materialized views - or, more commonly, supports "materialized views" by another name. Below is a list of synonyms for materialized views across different commercial RDBMS products:
| DBMS | Terminology | When | How | Who |
|---|
| MS SQL Server | Indexed Views | Immediate | Incremental | System |
| IBM DB2 | Materialized Query Tables | Immediate and Deferred | Incremental or Rebuild | System or User |
| Oracle | Materialized Views | Deferred | Incremental or Rebuild | System or User |
| SQL Anywhere | Materialized Views | Immediate and Deferred | Incremental or Rebuild | System or User |
In the above table, the "when" column refers to when a materialized view is updated with respect to changes made to one or more underlying base table rows. In a nutshell the choices are:
- Immediate maintenance:
- Update materialized view as part of the same (update) transaction;
- Propagate base table changes to the view data in a manner consistent with underlying base tables;
- View data is never stale;
- Has the potential to cause high rates of locking conflicts and deadlocks, together with degraded concurrency for update transactions.
- Lazy, 'just in time', maintenance:
- Apply changes to base tables without updating dependent materialized views;
- May log changes if incremental updates possible;
- At query execution time, a view can be used to provide results if the view is (already) fresh; otherwise, a separate synchronous transaction applies logged changes or re-computes the materialized view: query execution waits.
- Deferred, on demand, maintenance:
- Apply changes to base tables without updating dependent materialized views;
- May log changes if incremental updates possible;
- Use an independent asynchronous process to update views, typically by complete recomputation.
- Applications typically allowed to control the staleness of the view.
The "how" column refers to how the view is kept up-to-date in the face of changes to the view's underlying base tables. The choices are:
- Rebuild: re-compute the views from scratch;
- Incremental: apply individual updates to the materialized view without complete re-computation. This is not always possible; there are efficiency tradeoffs. In practice, immediate view maintenance is always incremental.
The term "materialized view" isn't standardized; since materialized views are a performance optimization feature, like indexes, materialized views aren't covered by the ANSI/ISO SQL standards. We chose "materialized views" for SQL Anywhere not (simply) to match Oracle, but also that "materialized views" is in relatively common use in the academic literature.
Some may question the omission of Sybase IQ from the above table. Actually, Sybase IQ doesn't offer support for materialized views. Instead, Sybase IQ supports a related (and older) idea called a join index. Join indices were originally described in a 1987 TODS paper [1] by Patrick Valduriez, then at MCC and now at INRIA. Similar to a materialized view, a join index materializes the result of an inner | outer | full-outer join between two tables. The advantage of a full-outer join index is that it can be used to answer any inner | outer | full-outer join query simply through applying restriction. The difference is that a materialized view is more generalized, able to materialize n-way inner | outer | full-outer joins along with restriction, projection, and grouping. In fact, batch-update materialized views can contain arbitrary relational algebra operations. My thanks to my colleague Anil Goel for putting this material together for a presentation on materialized views given at Techwave 2008.
[1] Patrick Valduriez (June 1987). Join indices. ACM Transactions on Database Systems 12(2), pp. 218-246.
