SQL物化视图自动更新定时刷新

2021-12-28 03:31:40

SQL物化视图自动更新定时刷新http://www.bieryun.com/3483.html

创建定时刷新--------------------------------------------------------------

create MATERIALIZED VIEW LOG on IT_INOUTROOM with rowid;

CREATE MATERIALIZED VIEWHJM_TEST_IT_INOUTROOM

REFRESH FAST on demand

WITH rowid

STAR TWITH SYSDATE NEXT SYSDATE + 1/1440

SELECT "A"."ID" "ID",

"A"."CODE" "CODE",

"A"."INOUT_CODE" "INOUT_CODE",

"A"."ERP_PACT_CODE" "ERP_PACT_CODE",

"A"."ERP_STOCK_CODE" "ERP_STOCK_CODE",

"A"."CUSTODY_ACCOUNT" "CUSTODY_ACCOUNT",

"A"."CREDIT_TYPE" "CREDIT_TYPE",

"A"."MEDICINE_NAME" "MEDICINE_NAME",

"A"."STANDARD" "STANDARD",

"A"."PRODUCING_AREA" "PRODUCING_AREA",

"A"."BASE_UNIT" "BASE_UNIT",

"A"."IN_QUANTITY" "IN_QUANTITY",

"A"."USE_STATE" "USE_STATE",

"A"."OUT_QUANTITY" "OUT_QUANTITY",

"A"."PARENT_UNIT_ID" "PARENT_UNIT_ID",

"A"."STORAGEID" "STORAGEID",

"A"."CREDATE" "CREDATE",

"A"."KEEPDATE" "KEEPDATE",

"A"."INVTYPEID" "INVTYPEID",

"A"."SOURCETABLE" "SOURCETABLE",

"A"."INOUTFLAG" "INOUTFLAG",

"A"."STORAGENAME" "STORAGENAME",

"A"."ENTRY_ID" "ENTRY_ID",

"A"."ENTRY_NAME" "ENTRY_NAME",

"A"."COMPANY_ERP_ID" "COMPANY_ERP_ID",

"A"."COMPANY_NAME" "COMPANY_NAME",

"A"."TRDTLID" "TRDTLID",

"A"."LIMIT_TYPE" "LIMIT_TYPE"

FROM "IT_INOUTROOM" "A"

WHERE "A"."KEEPDATE" >=

TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd HH24:mi:ss')

AND "A"."KEEPDATE" <=

TO_DATE('2016-01-16 23:59:59', 'yyyy-mm-dd HH24:mi:ss');

成功之后会自动生成一个JOB:

下面是自动跟随更新-----------------------------------------------------

CREATE materialized view log on IT_INOUTROOM;