物化试图日志过大

发现一张物化试图日志表达到了2G。...

[@more@]

1. 远程试图使用的rowid刷新，可我的日志用的是主键创建的，所以fast刷新失败，结果使用了完全刷新，导致日志表一直增加。

顺便看了下物化视图刷新的顺序。

更新的顺序是先删除后更新，最后插入。

首先查询出需要删除的数据：

SELECT /*+ remote_mapped(MASPROD104.CIMC.COM) */
DISTINCT M_ROW$$
FROM (SELECT M_ROW$$
FROM "MAS9I"."MLOG$_ACTION_TEST1"@MASPROD104.CIMC.COM MLOG$
WHERE "SNAPTIME$$" > :1
AND ("DMLTYPE$$" != 'I')) LOG$
WHERE (M_ROW$$) NOT IN
(SELECT ROWID
FROM "ACTION_TEST1"@MASPROD104.CIMC.COM "MAS_TAB$"
WHERE MAS_TAB$.ROWID = LOG$.M_ROW$$);

DELETE FROM "MAS9I"."MV_ACTION_TEST1" SNAP$ WHERE M_ROW$$ = :1;

然后查出需要update和insert的数据
SELECT /*+ remote_mapped(MASPROD104.CIMC.COM) */
CURRENT$."R_ID",
CURRENT$."OWNER",
CURRENT$."OBJECT_NAME",
CURRENT$."SUBOBJECT_NAME",
CURRENT$."OBJECT_ID",
CURRENT$."DATA_OBJECT_ID",
CURRENT$."OBJECT_TYPE",
CURRENT$."CREATED",
CURRENT$."LAST_DDL_TIME",
CURRENT$."TIMESTAMP",
CURRENT$."STATUS",
CURRENT$."TEMPORARY",
CURRENT$."GENERATED",
CURRENT$."SECONDARY",
ROWIDTOCHAR(CURRENT$.ROWID) M_ROW$$
FROM (SELECT "A".ROWID "R_ID",
"A"."OWNER" "OWNER",
"A"."OBJECT_NAME" "OBJECT_NAME",
"A"."SUBOBJECT_NAME" "SUBOBJECT_NAME",
"A"."OBJECT_ID" "OBJECT_ID",
"A"."DATA_OBJECT_ID" "DATA_OBJECT_ID",
"A"."OBJECT_TYPE" "OBJECT_TYPE",
"A"."CREATED" "CREATED",
"A"."LAST_DDL_TIME" "LAST_DDL_TIME",
"A"."TIMESTAMP" "TIMESTAMP",
"A"."STATUS" "STATUS",
"A"."TEMPORARY" "TEMPORARY",
"A"."GENERATED" "GENERATED",
"A"."SECONDARY" "SECONDARY"
FROM "ACTION_TEST1"@MASPROD104.CIMC.COM "A") CURRENT$,
(SELECT DISTINCT M_ROW$$
FROM "MAS9I"."MLOG$_ACTION_TEST1"@MASPROD104.CIMC.COM MLOG$
WHERE "SNAPTIME$$" > :1
AND ("DMLTYPE$$" != 'D')) LOG$
WHERE CURRENT$.ROWID = LOG$.M_ROW$$;

UPDATE "MAS9I"."MV_ACTION_TEST1"
SET "R_ID" = :1,
"OWNER" = :2,
"OBJECT_NAME" = :3,
"SUBOBJECT_NAME" = :4,
"OBJECT_ID" = :5,
"DATA_OBJECT_ID" = :6,
"OBJECT_TYPE" = :7,
"CREATED" = :8,
"LAST_DDL_TIME" = :9,
"TIMESTAMP" = :10,
"STATUS" = :11,
"TEMPORARY" = :12,
"GENERATED" = :13,
"SECONDARY" = :14
WHERE M_ROW$$ = :15
select * from sys.snap_reftime$;

INSERT INTO "MAS9I"."MV_ACTION_TEST1" ("R_ID","OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","M_ROW$$") VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15)
UPDATE sys.snap$ SET status = :1, rscn = :2, refhnt = :3, flag = :4 WHERE vname = :5 AND sowner = :6 AND instsite = :7

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133835/viewspace-1005195/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133835/viewspace-1005195/