RE: Implementing Incremental View Maintenance

From: Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>
> the target data? In the discussion, someone referred to master data with low
> update frequency, because the proposed IVM implementation adds triggers on
> source tables, which limits the applicability to update-heavy tables.
>
> 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?

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

* Facts (transaction data): e.g. sales, user activity
Large amount. INSERT only on a regular basis (ETL/ELT) or continuously (streaming)

* Dimensions (master/reference data): e.g. product, customer, time, country
Small amount. Infrequently INSERTed or UPDATEd.

The proposed trigger-based approach does not seem to be suitable for the facts, because the trigger overhead imposed on data loading may offset or exceed the time saved by incrementally refreshing the materialized views.

Then, does the proposed feature fit the dimension tables? If the materialized view is only based on the dimension data, then the full REFRESH of the materialized view wouldn't take so long. The typical materialized view should join the fact and dimension tables. Then, the fact table will have to have the triggers, causing the data loading slowdown.

I'm saying this because I'm concerned about the trigger based overhead. As you know, Oracle uses materialized view logs to save changes and incrementally apply them later to the materialized views (REFRESH ON STATEMENT materialized views doesn't require the materialized view log, so it might use triggers.) Does any commercial grade database implement materialized view using triggers? I couldn't find relevant information regarding Azure Synapse and Redshift.

If our only handy option is a trigger, can we minimize the overhead by doing the view maintenance at transaction commit?

Regards
Takayuki Tsunakawa