ORA-12015不能从复杂查询中创建⼀个可快速刷新的实体化
视图
物化视图的快速刷新（⼀）
创建唯一约束sql语句
物化视图有三种刷新⽅式：COMPLETE、FAST和FORCE。
完全刷新（COMPLETE）会删除表中所有的记录（如果是单表刷新，可能会采⽤TRUNCATE的⽅式），然后根据物化视图中查询语句的定义重新⽣成物化视图。快速刷新（FAST）采⽤增量刷新的机制，只将⾃上次刷新以后对基表进⾏的所有操作刷新到物化视图中去。采⽤FORCE⽅式，Oracle会⾃动判断是否满⾜快速刷新的条件，如果满⾜则进⾏快速刷新，否则进⾏完全刷新。
显然快速刷新是物化视图刷新⽅式的⾸选。但是，不是所有的物化视图都可以进⾏快速刷新，只有满⾜某些条件的物化视图才具有快速刷新的能⼒。
根据查询的不同，快速刷新的限制条件也不相同，下⾯总结⼀下不同类型的物化视图对快速刷新的限制条件。
可以参考：
物化视图的快速刷新（⼆）：
物化视图的快速刷新（三）：
所有类型的快速刷新物化视图都必须满⾜的条件：
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  wid 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  wid 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  wid 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过程来得到更加详细的错误信息。有关这个包的具体使⽤⽅法可以参考：
SQL> begin
2  plain_mview('wid 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  wid 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  wid 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  wid 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 wid, 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  wid 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  wid 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  plain_mview('wid 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';

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。