Обсуждение: materialized view scannability in other DBs
On Tue, Apr 30, 2013 at 10:40 AM, Kevin Grittner <kgrittn@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
Robert Haas <robertmhaas@gmail.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
On Tue, Apr 30, 2013 at 2:39 PM, Kevin Grittner <kgrittn@ymail.com> wrote: >> 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. I understand that it seems fundamental to you. What I'm trying to establish is that reasonable people could disagree about that. I think the fact that Oracle doesn't have one is a compelling argument for that position. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2013-05-01 11:38:36 -0400, Robert Haas wrote: > On Tue, Apr 30, 2013 at 2:39 PM, Kevin Grittner <kgrittn@ymail.com> wrote: > >> 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. > > I understand that it seems fundamental to you. What I'm trying to > establish is that reasonable people could disagree about that. I > think the fact that Oracle doesn't have one is a compelling argument > for that position. I don't think the argument that oracle doesn't do something holds all that much weight. We have loads of features that aren't in oracle that we consider essential. It also obviously doesn't say the feature is essential. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Robert Haas <robertmhaas@gmail.com> wrote: > Kevin Grittner <kgrittn@ymail.com> wrote: >> It seems to me pretty fundamental to have a way to avoid quietly >> generating completely bogus results > I understand that it seems fundamental to you. What I'm trying > to establish is that reasonable people could disagree about that. > I think the fact that Oracle doesn't have one is a compelling > argument for that position. Oracle has a "staleness" property for matviews which is set to UNUSABLE when the matview is not populated. The fact that this only prohibits use of the matview for query rewrite and not direct reference is more a reflection of the orientation of Oracle toward using matviews to accelerate queries written against the base tables, much as indexes do. Direct access seems to be taken as more useful for diagnostic purposes. Still, if you want to have application-side responsibility for not using matviews when they are not populated, the application can check this "staleness" property. You don't seem to be suggesting that we provide any equivalent. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company