最近因为需求关系要弄一个物化视图,原本要将多行单列转为单行单列的表存在视图中,可是物化视图不支持group by跟
rownum,因此没办法on commit来进行实时更新。于是想请教下,我如何在不修改语句的情况下达到实时更新这张视图?
麻烦详细说明。。只有最后这点分了。这是我用来创建的SQL语句。
物化视图日志也已经创建,主键设好了已经。
--物化视图创建
CREATE MATERIALIZED VIEW MV_RFF
BUILD IMMEDIATE
AS
SELECT MST_ID, TRANSLATE (LTRIM (TEXT, '/'), '*/', '*,') RESEARCHERLIST
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY MST_ID ORDER BY MST_ID,
LVL DESC) RN,
MST_ID, TEXT
FROM (SELECT MST_ID, LEVEL LVL,
SYS_CONNECT_BY_PATH (C1_1154_2,'/') TEXT
FROM (SELECT MST_ID, C1_1154_2 AS C1_1154_2,
ROW_NUMBER () OVER (PARTITION BY MST_ID ORDER BY MST_ID,C1_1154_2) X
FROM EDIFACT_SEG_RFF
ORDER BY MST_ID, C1_1154_2) A
CONNECT BY MST_ID = PRIOR MST_ID AND X - 1 = PRIOR X))
WHERE RN = 1
ORDER BY MST_ID;
rownum,因此没办法on commit来进行实时更新。于是想请教下,我如何在不修改语句的情况下达到实时更新这张视图?
麻烦详细说明。。只有最后这点分了。这是我用来创建的SQL语句。
物化视图日志也已经创建,主键设好了已经。
--物化视图创建
CREATE MATERIALIZED VIEW MV_RFF
BUILD IMMEDIATE
AS
SELECT MST_ID, TRANSLATE (LTRIM (TEXT, '/'), '*/', '*,') RESEARCHERLIST
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY MST_ID ORDER BY MST_ID,
LVL DESC) RN,
MST_ID, TEXT
FROM (SELECT MST_ID, LEVEL LVL,
SYS_CONNECT_BY_PATH (C1_1154_2,'/') TEXT
FROM (SELECT MST_ID, C1_1154_2 AS C1_1154_2,
ROW_NUMBER () OVER (PARTITION BY MST_ID ORDER BY MST_ID,C1_1154_2) X
FROM EDIFACT_SEG_RFF
ORDER BY MST_ID, C1_1154_2) A
CONNECT BY MST_ID = PRIOR MST_ID AND X - 1 = PRIOR X))
WHERE RN = 1
ORDER BY MST_ID;
如果实效性要求不是那么高的话,可以采用JOB方式或者手工调用存储过程DBMS_MVIEW.REFRESH(物化视图)的方式进行物化视图的刷新。