因为某个数据库内的表会被定期move,因此要求我们不能建立基于rowid进行快速刷新的物化视图,只好建立基于primary key的,可是发现了个问题,脚本如下:
-- 建立物化视图日志
create materialized view log on db_djgl.T_DJ_JGNSR with primary key, sequence including new values;
create materialized view log on db_djgl.T_DJ_JGNSRFB with primary key, sequence including new values;
create materialized view log on db_djgl.T_DJ_ZRR with primary key, sequence including new values;
-- 上面这三个表的主键,都是nsrnbm这个字段
-- 建立物化视图
create materialized view mv_kt_nsr
build immediate
refresh fast on demand with primary key
as
SELECT a.rowid jgrid, c.rowid fbrid, null zrrrid,
A.NSRBM,
A.NSRNBM,
A.NSR_MC,
A.ZSJG_DM,
A.HSJG_DM,
A.GLJG_DM,
A.JCJG_DM,
A.ZCLX_DM,
A.LSGX_DM,
A.HY_DM,
A.ZC_DZ NSRDZ,
c.frgj_dm GJDQ_DM,
A.gykglx_dm gykglx_dm,
'jgnsr' maker
FROM db_djgl.T_DJ_JGNSR A, db_djgl.t_dj_jgnsrfb c
where a.nsrnbm = c.nsrnbm
UNION ALL
SELECT null jgrid, null fbrid, b.rowid zrrrid,
B.NSRBM,
B.NSRNBM,
B.XM NSR_MC,
B.ZSJG_DM,
B.HSJG_DM,
B.GLJG_DM,
B.JCJG_DM,
B.ZCLX_DM,
B.LSGX_DM,
B.HY_DM,
B.TX_DZ NSRDZ,
b.gjdq_dm GJDQ_DM,
'03' GYKGLX_DM,
'zrr' maker
FROM db_djgl.T_DJ_ZRR B;
然后就报了这个错误:
ORA-12032: 不能使用 "DB_DJGL"."T_DJ_ZRR" 上实体化视图日志中的 rowid 列
可是我查了半天文档,也没有说基于primary key的物化视图不能用union all啊……
555……严重郁闷,哪位大侠能帮我看看啊,谢谢了~~~