有了物化视图,那些过去需要数小时运行的报告可以在几分钟内完成。物化视图可以包括联接(join)和集合(aggregate),它提供了一种储存预计算结果的方法。
代码清单 4:通过joinback和HIERARCHY获得销售总额
SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.year, p.category;
Id Operation Name
__________________________________________________
0 SELECT STATEMENT
1 SORT GROUP BY
2 HASH JOIN
3 HASH JOIN
4 VIEW
5 SORT UNIQUE
6 TABLE ACCESS FULL TIME
7 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV
8 TABLE ACCESS FULL PRODUCT |
维度的ATTRIBUTE子句指明了一对一关系。例如,你可以判定从time_key开始是一周中的哪一天。假设你希望得到每年1月份的销售总额:你仍然可以使用清单 5中所示的monthly_sales_mv物化视图。注意该查询的WHERE子句如何具有一个在物化视图中没有出现的选择条件。
代码清单 5:通过joinback和ATTRIBUTE获得销售总额
SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.month_name = 'January'
GROUP BY t.year, p.category;
Id Operation Name
__________________________________________________
0 SELECT STATEMENT
1 SORT GROUP BY
2 HASH JOIN
3 HASH JOIN
4 VIEW
5 SORT UNIQUE
6 TABLE ACCESS FULL TIME
7 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV
8 TABLE ACCESS FULL PRODUCT |
如果优化器并未如期改写一个查询,你可以使用DBMS_MVIEW .EXPLAIN_REWRITE 过程来诊断此问题。注释:这一特性出现在Oracle9i数据库及以后的版本中。