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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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:
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers