Re: materialized view scannability in other DBs

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Oracle has (FWICT) had materialized views since 8i (1999)

8i was when they expanded beyond requiring a manual refresh, and
changed the name of the feature from "snapshot" to "materialized
view".  I'm not sure how long they had "snapshots" before 8i.

> Microsoft SQL Server ... you can't build them deferred, so the
> question of what data you'd see if you could doesn't arise.

> in SQL Anywhere and DB2, it seems that it works the way you've
> implemented it

Sybase ASE, which is probably a better comparison than Sybase SQL
Anywhere, has the concept, too:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1572/html/commands/commands31.htm

See the populate | nonpopulate section.  If not populated, you
can't enable it for operations.

> MySQL ... doesn't seem to support materialized views at all.

Correct.

> the fact that Oracle has [...] not felt compelled to add a flag
> of this type, suggests to me that the feature can't be considered
> mandatory for a minimal implementation.

It seems to me pretty fundamental to have a way to avoid quietly
generating completely bogus results, whether or not one other
vendor has decided it doesn't matter.  It's not like they are
completely without the concept of "freshness" (or, as they seem to
express it, "staleness").  If you build with DEFERRED that property
of the matview is set to UNUSABLE; but in their world that doesn't
mean it's unusable by direct reference -- only for automatic query
rewrites.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company