Re: Implementing Incremental View Maintenance

>> But if you want to get always up-to-data you need to pay the cost for
>> REFRESH MATERIALIZED VIEW. IVM gives a choice here.
>
> Thank you, that clarified to some extent. What kind of data do you think of as an example?
>
> Materialized view reminds me of the use in a data warehouse. Oracle handles the top in its Database Data Warehousing Guide, and Microsoft has just started to offer the materialized view feature in its Azure Synapse Analytics (formerly SQL Data Warehouse). AWS also has previewed Redshift's materialized view feature in re:Invent 2019. Are you targeting the data warehouse (analytics) workload?

First of all, we do not think that current approach is the final
one. Instead we want to implement IVM feature one by one: i.e. we
start with "immediate update" approach, because it's simple and easier
to implement. Then we will add "deferred update" mode later on.

In fact Oracle has both "immediate update" and "deferred update" mode
of IVM (actually there are more "mode" with their implementation).

I recommend you to look into Oracle's materialized view feature
closely. For fair evaluation, probably we should compare the IVM patch
with Oracle's "immediate update" (they call it "on statement") mode.

> IIUC, to put (over) simply, the data warehouse has two kind of tables:

Probably deferred IVM mode is more suitable for DWH. However as I said
earlier, we hope to implement the immediate mode first then add the
deferred mode. Let's start with simple one then add more features.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp