Re: Implementing Incremental View Maintenance

> 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.

I think that depends on use case of the DWH. If the freshness of
materialized view tables is important for a user, then the cost of the
trigger overhead may be acceptable for the user.

> 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.

I heard that REFRESH ON STATEMENT of Oracle has been added after ON
COMMIT materialized view. So I suspect Oracle realizes that there are
needs/use case for ON STATEMENT, but I am not sure.

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

I am not sure it's worth the trouble. If it involves some form of
logging, then I think it should be used for deferred IVM first because
it has more use case than on commit IVM.

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