materialized view scannability in other DBs

On Tue, Apr 30, 2013 at 10:40 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> "Staleness" is a completely different issue, in my view, from
> quietly returning results that are not, and never were, accurate.
> Sure we need to implement more refined "scannability" tests than
> whether valid data from *some* point in time is present. But that
> should always be *part* of the scannability testing, and without it
> I don't feel we have a feature of a quality suitable for delivery.

I did a little more research on this. The Oracle equivalent of WITH
NO DATA is apparently BUILD DEFERRED. And as far as I can see, if you
specify BUILD DEFERRED when creating the view, then it just shows up
as having no rows:

http://dbataj.blogspot.com/2007/11/materialized-view-for-data-warehouse.html

The system declines to use such a view for query rewrite, but you can
still select from it without an error. See also Pro Oracle Database
11g Administration by Darl Kuhn, page 378, which confirms that this is
the case and that it will simply return 0 rows.

On the other hand, in SQL Anywhere and DB2, it seems that it works the
way you've implemented it:

http://dcx.sybase.com/1201/en/saerrors/errm1077.html
http://www.ibm.com/developerworks/data/library/techarticle/dm-0509melnyk/

The question doesn't seem to arise for Microsoft SQL Server or MySQL.
The former supports "indexed views" rather than materialized views,
but you can't build them deferred, so the question of what data you'd
see if you could doesn't arise. The latter doesn't seem to support
materialized views at all.

I have to admit that I'm slightly surprised to find that both DB2 and
Sybase have this concept, so maybe this is not as much of a random
wart as I had been thinking. However, the fact that Oracle has
(FWICT) had materialized views since 8i (1999), and that they have 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company