物化視圖的快速重新整理（一）

物化視圖的快速重新整理（一）

物化視圖有三種重新整理方式：COMPLETE、FAST和FORCE。

完全重新整理（COMPLETE）會刪除表中所有的記錄（如果是單表重新整理，可能會採用TRUNCATE的方式），然後根據物化視圖中查詢語句的定義重建物化視圖。快速重新整理（FAST）採用增量重新整理的機制，只將自上次重新整理以後對基表進行的所有操作重新整理到物化視圖中去。採用FORCE方式，Oracle會自動判斷是否滿足快速重新整理的條件，如果滿足則進行快速重新整理，否則進行完全重新整理。

顯然快速重新整理是物化視圖重新整理方式的首選。但是，不是所有的物化視圖都可以進行快速重新整理，只有滿足某些條件的物化視圖才具有快速重新整理的能力。

根據查詢的不同，快速重新整理的限制條件也不相同，下面總結一下不同類型的物化視圖對快速重新整理的限制條件。

可以參考：

物化視圖的快速重新整理（二）：http://yangtingkun.itpub.net/post/468/16456

物化視圖的快速重新整理（三）：http://yangtingkun.itpub.net/post/468/16496

所有類型的快速重新整理物化視圖都必須滿足的條件：

1.物化視圖不能包含對不重複表格達式的引用，如SYSDATE和ROWNUM；

2.物化視圖不能包含對LONG和LONG RAW資料類型的引用。

只包含串連的物化視圖：

1.必須滿足所有快速重新整理物化視圖都滿足的條件；

2.不能包括GROUP BY語句或聚集操作；

3.如果在WHERE語句中包含外串連，那麼唯一約束必須存在於串連中內表的串連列上；

4.如果不包含外串連，那麼WHERE語句沒有限制，如果包含外串連，那麼WHERE語句中只能使用AND串連，並且只能使用“＝”操作。

5.FROM語句列表中所有表的ROWID必須出現在SELECT語句的列表中。

6.FROM語句列表中的所有表必須建立基於ROWID類型的物化視圖日誌。

下面，我們看一個滿足快速重新整理條件的例子，然後依次違反上述6個條件，分別測試，查看會出現什麼錯誤。

首先，建立測試環境：

SQL> create table dim_a (id number primary key, name varchar2(30));

表已建立。

SQL> create table dim_b (id number primary key, name varchar2(30));

表已建立。

SQL> create table fact (id number, aid number, bid number, num number);

表已建立。

SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id);

表已更改。

SQL> alter table fact add constraint fk_fact_bid foreign key (bid) references dim_b(id);

表已更改。

SQL> insert into dim_a select rownum, 'a'||rownum from user_objects;

已建立74行。

SQL> insert into dim_b select rownum, 'b'||rownum from user_objects;

已建立74行。

SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2 
2 from user_objects;

已建立74行。

SQL> commit;

提交完成。

接下來建立一個可以成功快速重新整理的例子，這個物化視圖只包含串連：

SQL> create materialized view log on dim_a with rowid;

實體化視圖日誌已建立。

SQL> create materialized view log on dim_b with rowid;

實體化視圖日誌已建立。

SQL> create materialized view log on fact with rowid;

實體化視圖日誌已建立。

SQL> create materialized view mv_fact refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;

實體化視圖已建立。

下面我們依次違反上面的6個條件，觀察錯誤資訊。

1.包含SYSDATE/ROWNUM或RAW/LONG RAW資料類型。

SQL> create materialized view mv_fact_err refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num, sysdate time
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位於第 4 行:
ORA-12054: 無法為實體化視圖設定 ON COMMIT 重新整理屬性

錯誤提示是不能設定ON COMMIT屬性，去掉ON COMMIT語句再次嘗試：

SQL> create materialized view mv_fact_err refresh fast as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num, sysdate time
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位於第 4 行:
ORA-12015: 不能從複雜查詢中建立一個可快速重新整理的實體化視圖

根據預期一樣，建立快速重新整理的物化視圖報錯，不過提示資訊不明確，很難定位具體問題。使用ROWNUM後報錯的提示資訊和使用SYSDATE的一樣。

可以通過DBMS_MVIEW包的EXPLAIN_MVIEW過程來得到更加詳細的錯誤資訊。有關這個包的具體使用方法可以參考：http://blog.itpub.net/post/468/13318

SQL> begin
2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num, rownum row_id
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id');
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT';

MSGTXT
---------------------------------------
在實體化視圖中引用了 ROWNUM

使用這種方法就可以得到更加準確的錯誤資訊了。

測試另外一個約束之前要說明一下，Oracle文檔上給出的約束條件是不能包括RAW和LONG RAW類型，根據我的測試結果，估計是文檔出現了錯誤，這裡應該是LONG和LONG RAW類型。

SQL> alter table dim_a add (col_raw raw(10) default '1');

表已更改。

SQL> create materialized view mv_fact_err refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, num, a.col_raw
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;

實體化視圖已建立。
SQL> update dim_a set col_raw = '2';

已更新74行。

SQL> commit;

提交完成。

SQL> select distinct col_raw from mv_fact_err;

COL_RAW
--------------------
02
SQL> drop materialized view mv_fact_err;

實體化視圖已刪除。

SQL> alter table dim_a drop (col_raw);

表已更改。

SQL> alter table dim_a add (col_long long default '1');

表已更改。

SQL> create materialized view mv_fact_err refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, num, a.col_long 
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位於第 4 行:
ORA-00997: 非法使用 LONG 資料類型

SQL> alter table dim_a drop (col_long);

表已更改。

2.不能包括GROUP BY語句或聚集操作

SQL> create materialized view mv_fact_err refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, count(num) count_num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id
7 group by f.rowid, a.rowid, b.rowid, f.id, a.name, b.name;
from fact f, dim_a a, dim_b b
*
ERROR 位於第 4 行:
ORA-32401: "YANGTK"."DIM_B" 上的實體化視圖日誌沒有新值

不管是否存在串連，只要包括聚集操作，就要滿足聚集物化視圖快速重新整理的條件。這個錯誤提示正是聚集物化視圖快速重新整理需要滿足的條件之一。

3.如果在WHERE語句中包含外串連，那麼唯一約束必須存在於串連中內表的串連列上：

SQL> drop materialized view mv_fact;

實體化視圖已刪除。

SQL> create materialized view mv_fact refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, num 
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid = b.id;

實體化視圖已建立。

SQL> drop materialized view mv_fact;

實體化視圖已刪除。

SQL> alter table fact drop constraint fk_fact_aid;

表已更改。

SQL> alter table dim_a drop primary key;

表已更改。

SQL> create materialized view mv_fact refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, num 
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位於第 4 行:
ORA-12052: 無法快速重新整理實體化視圖 YANGTK.MV_FACT

這個錯誤提示不是很明確，但是Oracle錯誤訊息文檔上對這個錯誤產生的原因已經分析的很清楚了。

ORA-12052 cannot fast refresh materialized view string.string

Cause: Either ROWIDs of certain tables were missing in the definition or the inner table of an outer join did not have UNIQUE constraints on join columns.

Action: Specify the FORCE or COMPLETE option. If this error occurred during creation, the materialized view definition may have been changed. Refer to the documentation on materialized views.

SQL> delete mv_capabilities_table;

已刪除13行。

SQL> begin
2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, num 
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid = b.id');
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT';

MSGTXT
----------------------------------------------------------
在內部表的聯結列上沒有唯一性限制式條件

SQL> alter table dim_a add primary key (id);

表已更改。

SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id);

表已更改。

4.如果不包含外串連，那麼WHERE語句沒有限制，如果包含外串連，那麼WHERE語句中只能使用AND串連，並且只能使用“＝”操作：

SQL> create materialized view mv_fact refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, num 
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 or f.bid = b.id;

實體化視圖已建立。

SQL> create materialized view mv_fact_err refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, num 
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 or f.bid = b.id;
where f.aid = a.id(+)
*
ERROR 位於第 5 行:
ORA-01719: OR 或 IN 運算元中不允許外部串連運算子 (+)

這個錯誤提示是十分明顯的，不過下面的又只能通過EXPLAIN_MVIEW過程來定位錯誤原因。

SQL> create materialized view mv_fact_err refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid != b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位於第 4 行:
ORA-12054: 無法為實體化視圖設定 ON COMMIT 重新整理屬性

SQL> create materialized view mv_fact_err refresh fast as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid != b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位於第 4 行:
ORA-12015: 不能從複雜查詢中建立一個可快速重新整理的實體化視圖

SQL> delete mv_capabilities_table;

已刪除15行。

SQL> begin
2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid != b.id');
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT';

MSGTXT
------------------------------------------------------------
使用了除等號 (=) 外的運算子聯結謂詞
實體化視圖中的外部聯結
在實體化視圖中存在一個或多個聯結

5.FROM語句列表中所有表的ROWID必須出現在SELECT語句的列表中：

SQL> create materialized view mv_fact_err refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, f.id, 
3 a.name a_name, b.name b_name, num 
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 or f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位於第 4 行:
ORA-12052: 無法快速重新整理實體化視圖 YANGTK.MV_FACT_ERR

這個錯誤的資訊在上面已經列出了，錯誤原因說明比較明確。

SQL> delete mv_capabilities_table;

已刪除17行。

SQL> begin
2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, f.id, 
3 a.name a_name, b.name b_name, num 
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 or f.bid = b.id');
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT';

MSGTXT
---------------------------------------------------
SELECT 列表沒有所有從表的 rowid

6.FROM語句列表中的所有表必須建立基於ROWID類型的物化視圖日誌。

SQL> drop materialized view log on dim_a;

實體化視圖日誌已刪除。

SQL> drop materialized view mv_fact;

實體化視圖已刪除。

SQL> create materialized view mv_fact refresh fast on commit as 
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位於第 4 行:
ORA-23413: 表 "YANGTK"."DIM_A" 不帶實體化視圖日誌

這個錯誤說明十分明確，可以直接定位錯誤原因。