Re: Materialized views in Oracle

On Fri, Sep 23, 2011 at 3:03 AM, Mike Christensen <mike@kitchenpc.com> wrote:
> 1) Though I might have given the impression that a “manual complete
> refresh” is not useful, I definitely see value in this especially for
> data warehousing scenarios.  However, I’d almost want to call this a
> “snapshot” and not a “materialized view” – the two things are so
> different, I think it warrants different syntax.
[snip]

I like the terminology employed by this page:
  http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views
which clarifies between "snapshot", "eager", "lazy", and "very lazy"
materialized views.

[snip]

> One could argue that if you’re going this far, you’ve basically turned
> the whole trigger mechanism inside out and one could already implement
> this whole thing on Postgres 9 using real tables and triggers when the
> appropriate data changes.  This is something I’m struggling with as
> well.  Materialized views seem to be great for quick database
> snapshots and very simple selects, but anything too complicated and
> it’s either not an option, or you’d have to explain so much logic that
> you’re better off using a series of triggers and writing a custom
> solution anyway.
>
> This makes me really question the fundamental use of materialized
> views.  In other words, what is a real solid scenario that you would
> use one for that simply cannot be done currently using triggers?  Or,
> is it simply the “ease of creation” people are after?  There must be
> things I’m just not seeing, as this is the number one feature request
> on postgresql.uservoice.com by a massive landslide, and I fail to
> believe all those people are just “lazy.”

As a preface, I think materialized views are awesome when used
correctly. I don't have first-hand experience with Oracle's
implementation of them.

As far as I know, the maintenance of the matview itself can always be
done just as well using triggers as it can by a database engine (like
Oracle). In fact, I'd bet that for any reasonably complicated matview,
you're going to be able to wring out quite a bit more performance from
the hand-written one, as you can employ optimizations that the
database engine just doesn't know about.

For example, a while back I wrote a heavily-queried ~50M row matview
which was derived from ~12 or so base relations, some of which were
heavily updated. I was able to make the load imposed by the matview a
small part of the bulk-loading of the base tables by keeping track of
which changes to the base tables were actually "interesting" to the
matview, where "interesting" meant "could possibly cause a significant
change to the corresponding row in the matview". Plus little tricks
like knowing when it'd be cheaper to perform a full refresh of the
matview vs. slogging through millions of rows of UPDATEs. I imagine a
database-engine maintained matview in Oracle would have imposed more
system load and been slower to keep up-to-date.

Of course, one cool thing a database-engine-supported matview can do
is automatic query-rewriting, if it can prove that the materialized
view is both up to date, and capable of satisfying the user's query.
But then, if you are using materialized views, is it really that hard
to change your performance-intensive queries to select from the view
instead of the base table?

As for materialized views being the #1 most-requested feature on
uservoice... well, perhaps the voters don't have a good idea of how
much effort it would be to implement (vs. other compelling features we
could add), and perhaps also don't understand how powerful and
flexible trigger-maintained matviews can be. The fact that the
suggestion claims it would "... boost performance for every web
application" makes me pretty skeptical right off the bat. I'm not
saying it wouldn't be cool to have, just that there is still a lot
more low-hanging fruit that the data-warehousing crowd would benefit
from.

Josh