创建物化视图MV ( Materialized View )

创建物化视图时候的一些选项 ： 

ON PREBUILD TABLE
将已经存在的表注册为实体化视图。同时还必须提供描述创建该表的查询的 SELECT 子句。可能无法始终保证查询的精度与表的精度匹配。为了克服此问题，应该在规范中包含 WITH REDUCED PRECISION 子句。注意：这种方法不能用于以ROWID方式建立MVIEW，因为按照ROWID方式建立的ROWID所对应的表
需要包含一个隐含的ROWID列。

Build Clause
创建方式，决定是否在创建MV的时候生成数据。
· BUILD IMMEDIATE（默认）: 在创建实体化视图的时候就生成数据
· BUILD DEFERRED: 在创建时不生成数据，以后在刷新MV的时候生成数据

 

 

下面语句在数据库中不存在表的情况下建立物化视图, 建立物化视图后，会自动生成一个同名称的表, 且会自动创建同义词 。

如果表 SFISM4.R_PKGID_BOM_T 已经存在，可以在建立物化视图的语句中加入 ON PREBUILD TABLE，以免提示物件名称已经存在 。 

 

CREATE MATERIALIZED VIEW SFISM4.R_PKGID_BOM_T
TABLESPACE SN_DATA
PCTUSED    40
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          100M
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
BUILD  IMMEDIATE 
USING INDEX
            TABLESPACE SN_IDX
            PCTFREE    10
            INITRANS   2
            MAXTRANS   255
            STORAGE    (
                        INITIAL          50M
                        NEXT             100M
                        MINEXTENTS       1
                        MAXEXTENTS       UNLIMITED
                        PCTINCREASE      0
                        FREELISTS        1
                        FREELIST GROUPS  1
                        BUFFER_POOL      DEFAULT
                       )
REFRESH FAST
START WITH TO_DATE('15-06-2010 08:04:09','dd-mm-yyyy hh24:mi:ss')
NEXT   sysdate + 1/24
WITH ROWID
AS  
SELECT   "R_PKGID_BOM_T"."HH_PN" "HH_PN",
         "R_PKGID_BOM_T"."QTY" "QTY",
         "R_PKGID_BOM_T"."MFG_PN" "MFG_PN",
         "R_PKGID_BOM_T"."DATE_CODE" "DATE_CODE",
         "R_PKGID_BOM_T"."LOT_NO" "LOT_NO",
         "R_PKGID_BOM_T"."PO" "PO",
         "R_PKGID_BOM_T"."PKG_ID" "PKG_ID",
         "R_PKGID_BOM_T"."CTC" "CTC",
         "R_PKGID_BOM_T"."EMP_NO" "EMP_NO",
         "R_PKGID_BOM_T"."CDATE" "CDATE",
         "R_PKGID_BOM_T"."CHECK_FLAG" "CHECK_FLAG",
         "R_PKGID_BOM_T"."CHECK_RES" "CHECK_RES",
         "R_PKGID_BOM_T"."CHECK_TIME" "CHECK_TIME",
         "R_PKGID_BOM_T"."SPEC_FLAG" "SPEC_FLAG",
         "R_PKGID_BOM_T"."IC_SIZE" "IC_SIZE",
         "R_PKGID_BOM_T"."SIZE_DATE" "SIZE_DATE",
         "R_PKGID_BOM_T"."LF_FLAG" "LF_FLAG",
         "R_PKGID_BOM_T"."REV" "REV",
         "R_PKGID_BOM_T"."VENDOR_NAME" "VENDOR_NAME",
         "R_PKGID_BOM_T"."REMAIN_QTY" "REMAIN_QTY",
         "R_PKGID_BOM_T"."DISTRIBUTE_ORD" "DISTRIBUTE_ORD",
         "R_PKGID_BOM_T"."TIPTOP_ORD" "TIPTOP_ORD"
  FROM   "SFISM4"."R_PKGID_BOM_T"@TO_EPD3 "R_PKGID_BOM_T";

CREATE INDEX SFISM4.IDX_MFG_PN_BOM ON SFISM4.R_PKGID_BOM_T
(MFG_PN)
LOGGING
TABLESPACE SN_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX SFISM4.INDEX_BOM_PKGID ON SFISM4.R_PKGID_BOM_T
(PKG_ID)
LOGGING
TABLESPACE SN_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX SFISM4.INDEX_CHECKTIME ON SFISM4.R_PKGID_BOM_T
(CHECK_TIME)
LOGGING
TABLESPACE SN_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX SFISM4.INDEX_EMP ON SFISM4.R_PKGID_BOM_T
(EMP_NO)
LOGGING
TABLESPACE SN_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX SFISM4.INDEX_HH_PN ON SFISM4.R_PKGID_BOM_T
(HH_PN)
LOGGING
TABLESPACE SN_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

-- Note: Index I_SNAP$_R_PKGID_BOM_T will be created automatically
--       by Oracle with the associated materialized view.

CREATE INDEX SFISM4.PKGID_BOM_CDATE ON SFISM4.R_PKGID_BOM_T
(CDATE)
LOGGING
TABLESPACE SN_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

GRANT DELETE, INSERT, SELECT, UPDATE ON SFISM4.R_PKGID_BOM_T TO DMD_ALL_ROLE;

GRANT SELECT ON SFISM4.R_PKGID_BOM_T TO DMD_MLINE_ROLE;

GRANT DELETE, INSERT, SELECT, UPDATE ON SFISM4.R_PKGID_BOM_T TO MACADMIN;

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON SFISM4.R_PKGID_BOM_T TO SFIS1;

------------------------------------------------------------- 

 

重要的一点，如果要快速刷新，必须在基表上建立mv log .  这里到上面dblink   "TO_EPD3"  所指的基表数据库中去建立mv log ,  以便于能设置快速刷新。

 

 

CREATE MATERIALIZED VIEW LOG ON SFISM4.R_PKGID_BOM_T
TABLESPACE LOG_DATA
PCTUSED    30
PCTFREE    60
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          20M
            NEXT             20M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID
EXCLUDING NEW VALUES;

 

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

转载于:http://blog.itpub.net/35489/viewspace-665370/