CREATE DIMENSION time_dim
LEVEL time_key IS time.time_key
LEVEL month IS time.month
LEVEL quarter IS time.quarter
LEVEL year IS time.year
HIERARCHY calendar_rollup (
time_key CHILD OF
month CHILD OF
quarter CHILD OF
year
)
ATTRIBUTE time_key determines (day_of_week, holiday)
ATTRIBUTE month determines (month_name);
现在，如果具有清单 4中要求按年的销售额的查询，你仍然可以使用monthly_sales_mv物化视图，因为维度对象中的HIERARCHY子句告诉Oracle数据库月销售额可以积累成年销售额。它利用前面描述的joinback技巧由物化视图中的“月”列得到“年”列的值。
代码清单 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数据库及以后的版本中。
过滤后的数据
到目前为止，我们所给出的所有示例都使用了与采购表中的所有数据对应的物化视图。Oracle9i数据库具备在物化视图仅有一个数据子集情况下改写查询的能力。例如，如果你只对1997年到2002年的销售额感兴趣，你可以将物化视图修改如下：
CREATE MATERIALIZED VIEW five_yr_monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, p.product_id,
SUM(ps.purchase_price) as sum_of_sales,
COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.year between 1997 and 2002
GROUP BY t.month, p.product_id;
此物化视图可用于响应要求从1997年至2002年数据的查询，例如，清单 6中的查询要求2000年的销售额。
代码清单 6：只查询物化视图
SELECT t.month, p.product_id, 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.year = 2000
GROUP BY t.month, p.product_id;
Id Operation Name
__________________________________________________
SELECT STATEMENT
1 HASH JOIN
2 VIEW
3 SORT UNIQUE
4 TABLE ACCESS FULL TIME
5 MAT_VIEW REWRITE ACCESS FULL FIVE_YR_MONTHLY_SALES_MV
在Oracle9i数据库中，如果物化视图中没有查询所需要的全部数据，查询就不会使用物化视图。在Oracle数据库10g中，已经放松了这一限制，因此查询改写可以由物化视图中获得尽可能多的数据，并利用细目表获得物化视图中没有的数据。和往常一样，优化器在做出执行此操作的决定时考虑了有改写和无改写情况下的查询成本。
例如，清单 7中的查询要求2000年至2003年之间的月销售额，它将使用从2000年至2002年的物化视图，而只需要2003年的细目表。
代码清单 7：查询物化视图和细目表
SELECT t.month, p.product_id, 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.year BETWEEN 2000 and 2003
GROUP BY t.month, p.product_id;
Id Operation Name
__________________________________________________
0 SELECT STATEMENT
1 SORT GROUP BY
2 VIEW
3 UNION-ALL
4 HASH JOIN
5 VIEW
6 SORT UNIQUE
7 TABLE ACCESS FULL TIME
8 MAT_VIEW REWRITE ACCESS FULL FIVE_YR_MONTHLY_SALES_MV
9 SORT GROUP BY
10 NESTED LOOPS
11 HASH JOIN
12 TABLE ACCESS FULL TIME
13 PARTITION RANGE ALL
14 TABLE ACCESS FULL PURCHASES
15 INDEX RANGE SCAN PRODUCT_PK_INDEX
使用失效的物化视图进行查询改写
你可能想知道如果细目表中的数据发生了变化会发生什么情况。查询改写仍将使用物化视图吗？答案决定于初始化参数QUERY_REWRITE_ INTEGRITY的设置。QUERY_REWRITE_INTEGRITY参数有三个取值：
STALE_TOLERATED表示即使细目表中的数据已经发生了变化，也仍然使用物化视图。
TRUSTED 表示物化视图未失效时才使用该视图。但是，查询改写可以使用信任关系，如那些由维度对象或尚未生效的约束所声明的关系。
ENFORCED（缺省）表示当物化视图保证能给出与使用细目表相同的结果时才使用它。使用这一参数意味着查询改写将不使用失效的物化视图或信任关系。
正确的设置决定于应用程序的数据需求。使用失效物化视图的查询改写可能会产生与没有使用查询改写时不同的结果。然而，如果使用细目数据，可能会因为响应查询需要处理的大量数据而使性能恶化。在一个数据仓库中，通常使用TRUSTED完整级别，因为这样才可以保证你只使用那些具有最新数据的物化视图；然而，被声明为正确（可信任）的关系也可用于查询改写。在大多数数据仓库中，这些关系已经在提取、转换和加载（ETL）过程得到了验证，因此不再需要进行验证。
分区变化跟踪
在Oracle9i数据库中，Oracle引入了分区变化跟踪（PCT，Partition Change Tracking）。利用这一特性，Oracle9i数据库可以跟踪物化视图的哪一部分对应于分区细目表的已更新部分。因此，如果查询不需要已更新表的部分，那么该物化视图仍然可以使用。
为了在物化视图中跟踪一个细目表的变化，必须对该表进行分区，并且该物化视图（在SELECT列表中）必须包括细目表的分区键或一个特殊函数：DBMS_MVIEW.PMARKER。此函数为细目表中的每个分区生成一个唯一的标识符。
例如，由time_key对采购表进行分区。清单 8中创建的物化视图与前面使用的monthly_sales_mv 物化视图几乎完全相同，只是该物化视图在采购表上包含了一个附加的DBMS_MVIEW.PMARKER函数。通过包含这一函数，当更新采购表时该物化视图允许PCT。注意：该物化视图自身并不需要被分区。
代码清单 8：具有DBMS_MVIEW.PMARKER函数的物化视图