From: Tatsuo Ishii <ishii@sraoss.co.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
SQLData Warehouse). AWS also has previewed Redshift's materialized view feature in re:Invent 2019. Are you targeting
thedata 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
dataloading 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
thefact 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
logsto save changes and incrementally apply them later to the materialized views (REFRESH ON STATEMENT materialized
viewsdoesn't require the materialized view log, so it might use triggers.) Does any commercial grade database
implementmaterialized 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