Re: Implementing Incremental View Maintenance

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

AFAIK benefit of ON STATEMENT is the transaction can see the result of
update to the base tables. With ON COMMIT, the transaction does not
see the result until the transaction commits.

> Could you give your opinion on the following to better understand the proposed feature and/or Oracle's ON STATEMENT refresh mode?
>
> * 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?

Well, I can see use cases of IVM in both DWH and OLTP.

For example, a user create a DWH-like data using materialized
view. After the initial data is loaded, the data is seldom updated.
However one day a user wants to change just one row to see how it
affects to the whole DWH data. IVM will help here because it could be
done in shorter time than loading whole data.

Another use case is a ticket selling system. The system shows how many
tickets remain in a real time manner. For this purpose it needs to
count the number of tickets already sold from a log table. By using
IVM, it could be accomplished in simple and effective way.

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

Here are some use cases suitable for IVM I can think of:

- Users are creating home made triggers to get data from tables. Since
IVM could eliminates some of those triggers, we could expect less
maintenance cost and bugs accidentally brought in when the triggers
were created.

- Any use case in which the cost of refreshing whole result table
(materialized view) is so expensive that it justifies the cost of
updating of base tables. See the example of use cases above.

> * 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)?

Outweights to what?

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

I think it's important to refresh the materialized view after every
statement and the benefit for users are apparent because it brings
real time data refresh to users.

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