建立物化檢視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;