>> 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
IVM_sucessfull_queries.tar.gz
Description: Binary data
