谢谢刚才各位朋友的指教~出现了这样一个问题,现在另开一贴:
CREATE MATERIALIZED VIEW LOG ON t_wp_xh WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON t_wp_cx WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;
都成功了,然后
CREATE MATERIALIZED VIEW MVB_WP_CXXH REFRESH FAST ON DEMAND
AS
select xtm,htm from T_WP_CX
union all
select xtm,htm from T_WP_XH
报错,说ORACLE不能从一个复杂查询的中创建一个快速刷新的实体化视图,难到就只是用了个UNION ALL就算复杂查询了吗?
CREATE MATERIALIZED VIEW LOG ON t_wp_xh WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON t_wp_cx WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;
都成功了,然后
CREATE MATERIALIZED VIEW MVB_WP_CXXH REFRESH FAST ON DEMAND
AS
select xtm,htm from T_WP_CX
union all
select xtm,htm from T_WP_XH
报错,说ORACLE不能从一个复杂查询的中创建一个快速刷新的实体化视图,难到就只是用了个UNION ALL就算复杂查询了吗?
AS
select xtm,htm from T_WP_CX
union all
select xtm,htm from T_WP_XH
去掉红色部分就可以了。
AS
select xtm,htm from T_WP_CX
union all
select xtm,htm from T_WP_XH
这个应该可以
AS
select xtm,htm from T_WP_CX,T_WP_XH;
CREATE MATERIALIZED VIEW MVB_WP_CXXH REFRESH FAST ON DEMAND
AS
select xtm a,htm b from T_WP_CX t1,T_WP_XH t2 where (t1.a<>t2.a or t1.b<>t2.b);
--try it
CREATE MATERIALIZED VIEW MVB_WP_CXXH REFRESH FORCE or REFRESH COMPLETE
AS
select xtm,htm from T_WP_CX
union all
select xtm,htm from T_WP_XH
x列用于标识是哪个表,rid列是oracle要求取出rowid, 楼主可再试试
create materialized view mv_x
REFRESH FAST ON demand
as
select 1 x, rowid rid, a from t_wp_xh
union all
select 2 x, rowid rid, a from t_wp_cx
等明天我再加100分,结贴感谢各位!