Oracle物化视图3 - Prebuilt MV

Oracle中，Prebuilt MV建立在同名的普通表上。Prebuilt MV的列是该同名表的列的一个子集。也就是说，该同名表可以包含不在MV中出线的列(Unmanaged columns)。

在MV刷新时，这些列的值采用默认值。假如没有定义默认值，这些列可以不能有Not Null约束。

与普通物化视图相比，Prebuilt MV提供的Query Rewrite功能有限。只有当Query_Rewrite_Integrity为trusted或Stale_Tolerated时，Prebuilt MV可以被Oracle Query Rewrite所使用。

与普通MV不同，当用户删除(Drop)MV时，该同名表不会被删除。在建立了Prebuilt MV后，该同名表不能用drop table xxx语句删除。

下面是Oracle自带SH Schema中一个Prebuilt MV的例子：

CREATE MATERIALIZED VIEW CAL_MONTH_SALES_MV

ON PREBUILT TABLE

USING INDEX

REFRESH ON DEMAND

FORCE

USING DEFAULT LOCAL ROLLBACK SEGMENT

ENABLE QUERY REWRITE AS

SELECT   t.calendar_month_desc

 ,        sum(s.amount_sold) AS dollars

 FROM     sales s

 ,        times t

 WHERE    s.time_id = t.time_id

 GROUP BY t.calendar_month_desc;

当需要在MV里新增一列时，普通物化视图需要重建(drop materialized view, create materialized view)，数据量大时，成本相当高。但是Prebuilt MV没有这一缺陷。因为删除物化视图时，同名表仍然存在。下面给出一个例子：

desc cal_month_sales_mv
Name                Null     Type        
------------------- -------- -----------
CALENDAR_MONTH_DESC NOT NULL VARCHAR2(8)
DOLLARS                      NUMBER

Drop materialized viewcal_month_sales_mv;
materialized view CAL_MONTH_SALES_MV dropped.

alter table cal_month_sales_mv add(quantity_sold number default 0);
table CAL_MONTH_SALES_MV altered.

CREATE MATERIALIZED VIEWCAL_MONTH_SALES_MV
ON PREBUILT TABLE
USING INDEX
REFRESH ON DEMAND
FORCE
USING DEFAULT LOCAL ROLLBACK SEGMENT
ENABLE QUERY REWRITE AS
SELECT   t.calendar_month_desc
 ,        sum(s.amount_sold) AS dollars
 , count(s.quantity_sold) as quantity_sold
 FROM     sales s
 ,        times t
 WHERE    s.time_id = t.time_id
 GROUP BY t.calendar_month_desc;
materialized view CAL_MONTH_SALES_MV created.

desc cal_month_sales_mv
Name                Null     Type        
------------------- -------- -----------
CALENDAR_MONTH_DESC NOT NULL VARCHAR2(8)
DOLLARS                      NUMBER      
QUANTITY_SOLD                NUMBER