> First, thanks for the summary of the Oracle implementation, very
> very informative.

You're welcome.

> > Note that implementing query rewriting delivers 80% of the
> > materialised view benefit to the end user, because IMHO doing a
> > COMPLETE refresh on the summary tables, say every night, will do
> > fine in most cases.
>
> Does query rewrite 'really' work with COMPLETE views?
>
> Do data changes made since the COMPLETE refresh get reflected in the
> query results? Or does the query report 'out-of-date' values?
>
> It would seem incorrect for the query rewrite to use the view data
> which is not up to date.

I'll give you a very european answer: "yesnomaybe":

No, because changes do not occur asynchroneously. Remote replication
aside, materialised views are used most often in data warehousing
type settings. The "detail table" is not filled continuously, but
during scheduled updates, such as the daily/weekly/monthly
reorganisation of transaction tables in an ERP system. The COMPLETE
refresh can occur immediately following the data upload.

Maybe, because the system should protect against errors reulting
from unforeseen modifications to the detail table. One solution could
be to invalidate the summary table and exclude it from query rewrites
until a refresh has taken place. I believe this is the Oracle
solution to avoiding incorrectness.

Yes, because the 80% is indeed a poor man's solution that requires a
lot of DBA attention. Firebirds philosophy is to be low in DBA need
and self-configuring/tuning. FAST updates should be part of a
Firebird implementation of materialised views.

> > PS For those who wonder: no, I am not implementing this -- it is
> > *way* down on my "things I would like to do" list
>
> Come on!!! It's just a day or two of coding! ;-)))

Sean, by Jove, you are slow coder....

But seriously, this stuff may be a lot more trivial than it first
appears:

1. Creating and maintaining change logs can be done using the
existing 'user land' architectural facilities. The FB replication
tools are built this way and "internal replication" is a lot easier
than remote replication, as one does not have to consider topology,
conflicting updates, broken network links, etc.

2. I currently believe that all of the many limitations that Oracle
places on views for FAST refresh to work are driven by a requirement
to relate each row in a detail table to a single, uniquely
identifiable row in the materialized view. Where such a 1:1 mapping
exists, the actual update of the summary row is almost trivial.

3. For the subset of queries defined above, I postulate that the
triggers that 'propagate' a change log entry to a materialised view
are less than one page of PSQL each.

In summary, I am currently willing to believe that FAST refresh can
be implemented even in plain vanilla FB15 as a set of tables (change
logs, materialised views) interacting with a set of triggers (change
logging, propagation). The big time investment will be in really
understanding the implications of item 2, not in coding it up in the
DSQL compiler.

Implementing query rewrites will be harder IMHO. Currently I am even
unsure what would be the best architectural layer for query rewrites
to sit in. Oracle designers have chosen to stick it in the optimiser
(they seem to have a penchant for solving things in that layer, to
their detriment). This choice would seem to be right only if there
are cases where going through the summary table increases the cost of
the plan. Are there such cases ?? I don't know...

I look forward to hearing the views of minds sharper than mine on
this topic. Materialised views preferred.

Paul