RE: Implementing Incremental View Maintenance

From: Tatsuo Ishii <ishii@sraoss.co.jp>
> 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.

I agree about incremental feature introduction.  What I'm simply asking is the concrete use case (workload and data),
sothat I can convince myself to believe that this feature is useful and focus on reviewing and testing (because the
patchseems big and difficult...) 


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

Yes, I know Oracle's ON STATEMENT refresh mode (I attached references at the end for others.)

Unfortunately, it's not clear to me which of ON STATEMENT or ON COMMIT the user should choose.  The benefit of ON
STATEMENTis that the user does not have to create and maintain the materialized view log.  But I'm not sure if and when
thebenefit defeats the performance overhead on DML statements.  It's not disclosed whether ON STATEMENT uses triggers. 

Could you give your opinion on the following to better understand the proposed feature and/or Oracle's ON STATEMENT
refreshmode? 

* What use case does the feature fit?
If the trigger makes it difficult to use in the data ware house, does the feature target OLTP?
What kind of data and query would benefit most from the feature (e.g. join of a large sales table and a small product
table,where the data volume and frequency of data loading is ...)? 
In other words, this is about what kind of example we can recommend as a typical use case of this feature.

* Do you think the benefit of ON STATEMENT (i.e. do not have to use materialized view log) outweighs the drawback of ON
STATEMENT (i.g. DML overhead)? 

* Do you think it's important to refresh the materialized view after every statement, or the per-statement refresh is
nota requirement but simply the result of implementation? 


[References]

https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/refreshing-materialized-views.html#GUID-C40C225A-8328-44D5-AE90-9078C2C773EA
--------------------------------------------------
7.1.5 About ON COMMIT Refresh for Materialized Views

A materialized view can be refreshed automatically using the ON COMMIT method. Therefore, whenever a transaction
commitswhich has updated the tables on which a materialized view is defined, those changes are automatically reflected
inthe materialized view. The advantage of using this approach is you never have to remember to refresh the materialized
view.The only disadvantage is the time required to complete the commit will be slightly longer because of the extra
processinginvolved. However, in a data warehouse, this should not be an issue because there is unlikely to be
concurrentprocesses trying to update the same table.  


7.1.6 About ON STATEMENT Refresh for Materialized Views

A materialized view that uses the ON STATEMENT refresh mode is automatically refreshed every time a DML operation is
performedon any of the materialized view’s base tables.  

With the ON STATEMENT refresh mode, any changes to the base tables are immediately reflected in the materialized view.
Thereis no need to commit the transaction or maintain materialized view logs on the base tables. If the DML statements
aresubsequently rolled back, then the corresponding changes made to the materialized view are also rolled back.  

The advantage of the ON STATEMENT refresh mode is that the materialized view is always synchronized with the data in
thebase tables, without the overhead of maintaining materialized view logs. However, this mode may increase the time
takento perform a DML operation because the materialized view is being refreshed as part of the DML operation.  
--------------------------------------------------



https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/release-changes.html#GUID-2A2D6E3B-A3FD-47A8-82A3-1EF95AEF5993
--------------------------------------------------
ON STATEMENT refresh mode for materialized views
The ON STATEMENT refresh mode refreshes materialized views every time a DML operation is performed on any base table,
withoutthe need to commit the transaction. This mode does not require you to maintain materialized view logs on the
basetables.  
--------------------------------------------------


http://www.oracle.com/us/solutions/sap/matview-refresh-db12c-2877319.pdf
--------------------------------------------------
We have introduced a new Materialized View (MV) refresh mechanism called ON STATEMENT refresh. With the ON STATEMENT
refreshmethod, an MV is automatically refreshed whenever DML happens on a base table of the MV. Therefore, whenever a
DMLhappens on any table on which a materialized view is defined, the change is automatically reflected in the
materializedview. The advantage of using this approach is that the user no long needs to create a materialized view log
oneach of the base table in order to do fast refresh. The refresh can then avoid the overhead introduced by MV logging
butstill keep the materialized view refreshed all the time. 

Specify ON STATEMENT to indicate that a fast refresh is to occur whenever DML happens on a base table of the
materializedview. This is to say, ON STATEMENT materialized view is always in sync with base table changes even before
thetransaction commits. If a transaction that made changes to the base tables rolls back, the corresponding changes in
onstatement MV are rolled back as well. This clause may increase the time taken to complete a DML, because the database
performsthe refresh operation as part of the DML execution. However, unlike other types of fast refreshable
materializedviews, ON STATEMENT MV refresh no longer requires MV log on the base tables or any extra work on MV logs in
orderto do fast refresh. 
--------------------------------------------------


Regards
Takayuki Tsunakawa