Re: Implementing Incremental View Maintenance

>> Hi,
>>
>> Attached is the latest patch (v15) to add support for Incremental Materialized
>> View Maintenance (IVM). It is possible to apply to current latest master branch.

I have tried to use IVM against TPC-DS (http://www.tpc.org/tpcds/)
queries. TPC-DS models decision support systems and those queries are
modestly complex. So I thought applying IVM to those queries could
show how IVM covers real world queries.

Since IVM does not support queries including ORDER BY and LIMIT, I
removed them from the queries before the test.

Here are some facts so far learned in this attempt.

- Number of TPC-DS query files is 99.
- IVM was successfully applied to 20 queries.
- 33 queries failed because they use WITH clause (CTE) (currenly IVM does not support CTE).
- Error messages from failed queries (except those using WITH) are below:
(the number indicates how many queries failed by the same reason)

11 aggregate functions in nested query are not supported on incrementally maintainable materialized view
8 window functions are not supported on incrementally maintainable materialized view
7 UNION/INTERSECT/EXCEPT statements are not supported on incrementally maintainable materialized view
5 WHERE clause only support subquery with EXISTS clause
3 GROUPING SETS, ROLLUP, or CUBE clauses is not supported on incrementally maintainable materialized view
3 aggregate function and EXISTS condition are not supported at the same time
2 GROUP BY expression not appeared in select list is not supported on incrementally maintainable materialized view
2 aggregate function with DISTINCT arguments is not supported on incrementally maintainable materialized view
2 aggregate is not supported with outer join
1 aggregate function stddev_samp(integer) is not supported on incrementally maintainable materialized view
1 HAVING clause is not supported on incrementally maintainable materialized view
1 subquery is not supported with outer join
1 column "avg" specified more than once

Attached are the queries IVM are successfully applied.

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