On Tue, Apr 30, 2013 at 10:40 AM, Kevin Grittner <kgri...@ymail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
