快速重新整理物化檢視有with rowid和with primary key兩種，建立時該如何選擇？

本文主要談及以下兩種物化檢視的應用：
1、用於分散式環境裡的物化檢視，例如master table在A庫，B庫透過dblink建立了一個指向A庫taba的物化檢視mvb:
create materialized view mvb ... as select * from taba@link_a，連線B庫的應用不必透過link_a遠端訪問A庫上的taba表，只需訪問本地的mvb表，提高了訪問效率，mvb相當於A庫taba表的一個副本，每次A庫更新完taba後會將修改的內容利用物化檢視日誌增量重新整理到mvb，實現mvb的實時更新

2、DSS環境下實現統計彙總功能的物化檢視，把經常需要用到的一些統計語句定義成物化檢視，例如：
create materialized view mvsta ... as select cu.cust_name,p.product_name,sum(s.amount) sale_amount from sales s,customers c,products p where s.cust_id=c.cust_id and s.product_id=p.product_id group by cu.cust_name,p.product_name

當事實表或者維表內容發生變化時最新的統計結果也能增量更新到物化檢視

要實現物化檢視的增量重新整理，必須在master table上先建立物化檢視日誌，再建立物化檢視，物化檢視日誌記錄了對master table的所有更改操作實現增量重新整理主要靠它。

建立物化檢視日誌的過程就是我們告訴oracle該如何記錄這些更改操作，這些內容主要是在with clause裡定義的，常用的主要有

with primary key
with rowid

上面兩個with clause的可選項的使用原則歸納如下：
with primary key：主要用在分散式環境實現master table與遠端庫MV建立一對一的複製關係，master table上必須有主鍵(主鍵必須是enabled的)，定義MV的select語句中必須包含構成主鍵的所有欄位，在此基礎上也可以包含其它非主鍵欄位，唯一的限制是這些欄位上不能使用聚合函式；DSS環境下實現統計彙總的MV對應的MV log建立時也可以使用primary key，但不是一定要使用，如果用了那麼primary key所包含的欄位就不能再重複定義於with (col1,col2...)裡了，

with rowid：DSS環境下當MV的Select語句裡帶有聚合函式，必須使用rowid，此外還必須用上including new values，且要將select list裡的所有欄位都定義在with (col1,col2....)裡，如果同時使用了with primary key那麼要注意primary key所包含的欄位就不能再重複定義於with (col1,col2...)裡。分散式環境下with rowid主要用於master table沒有主鍵的情況，因為實現的是MV和master table間一對一的複製，所以無需定義with (col1,col2...)

有點繞，舉幾個例子：

###分散式環境with primary key的使用：
create table ad.t0829_1 as select * from all_users;

SQL> desc ad.t0829_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)
USER_ID                                   NOT NULL NUMBER
CREATED                                   NOT NULL DATE

alter table ad.t0829_1 add constraint pk_t0829_1 primary key(username,created);

create materialized view log on ad.t0829_1 with primary key,sequence including new values;

SQL> desc ad.mlog$_t0829_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(30)
CREATED                                            DATE
SEQUENCE$$                                         NUMBER
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)
XID$$                                              NUMBER

***create MV的select語句裡包含了主鍵裡的全部兩個欄位username、created，select list裡沒有聚合函式，且針對主鍵所在的欄位亦不能使用upper、substr等函式，不是主鍵列的user_id可以定義在此

create materialized view ad.mv0829_1 build immediate refresh fast with primary key enable query rewrite as select username,created,user_id from ad.t0829_1;

###DSS環境with primary key的使用：
create table ad.t0829_1 as select * from all_users;

SQL> desc ad.t0829_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)
USER_ID                                   NOT NULL NUMBER
CREATED                                   NOT NULL DATE

alter table ad.t0829_1 add constraint pk_t0829_1 primary key(username,created);

***建立MV log時必須定義rowid以及所有即將出現在select list裡的列，為啥只有user_id，沒有username和created？因為with primary key隱含包括了username和create materialized view log on ad.t0829_1 with primary key,rowid,sequence (user_id) including new values;

>>PK裡的列不能重複定義，像下面這樣是錯誤的，username,created已經是PK的組成列，就不要在()裡再去定義這些PK列了
create materialized view log on ad.t0829_1 with primary key,rowid,sequence (username,created,user_id) including new values;

>>假設主鍵pk_t0829_1不存在就必須用如下語句建立MV log
create materialized view log on ad.t0829_1 with rowid,sequence (username,created,user_id) including new values;

***無論逐漸是否存在，建立出來的MV log要適用於更新帶有聚合函式的MV，必須具有如下結構：username,user_id,created三個列外加M_ROW$$列都要有
SQL> desc ad.mlog$_t0829_1;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(30)     <---結果列
CREATED                                            DATE                      <---結果列
USER_ID                                            NUMBER                  <---結果列
M_ROW$$                                            VARCHAR2(255)     <--- with rowid就會生成一列M_ROW$$記錄變更行的rowid
SEQUENCE$$                                         NUMBER
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)
XID$$                                              NUMBER

***建立含有聚合函式的MV時以下三種形式均可，因為mlog$_t0829_1表裡包含了所有結果欄位和M_ROW$$欄位
create materialized view ad.mv0829_1 build immediate refresh fast with rowid enable query rewrite as select username,created,sum(user_id),count(*),count(user_id) from ad.t0829_1 group by username,created;

create materialized view ad.mv0829_1 build immediate refresh fast with primary key enable query rewrite as select username,created,sum(user_id),count(*),count(user_id) from ad.t0829_1 group by username,created;

create materialized view ad.mv0829_1 build immediate refresh fast enable query rewrite as select username,created,sum(user_id),count(*),count(user_id) from ad.t0829_1 group by username,created;

###分散式環境with rowid的使用：
create table ad.t0829_1 as select * from all_users;

SQL> desc ad.t0829_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)
USER_ID                                   NOT NULL NUMBER
CREATED                                   NOT NULL DATE

***建立MV log時只要寫上with rowid
create materialized view log on ad.t0829_1 with rowid,sequence including new values;

SQL> desc ad.mlog$_t0829_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
M_ROW$$                                            VARCHAR2(255)
SEQUENCE$$                                         NUMBER
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)
XID$$                                              NUMBER

***可以但是沒有必要加上列名，這樣會在MV log表裡多出額外的欄位，增加儲存開銷
create materialized view log on ad.t0829_1 with rowid,sequence (username,created,user_id) including new values;

SQL> desc ad.mlog$_t0829_1;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(30)   <---多餘列
CREATED                                            DATE                    <---多餘列
USER_ID                                            NUMBER                <---多餘列
M_ROW$$                                            VARCHAR2(255)
SEQUENCE$$                                         NUMBER
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)
XID$$                                              NUMBER

***建MV時一定要加上with rowid，因為沒有聚合函式的MV預設是with primary key，如果表上沒有主鍵就會報ORA-12014
create materialized view ad.mv0829_1 build immediate refresh fast with rowid enable query rewrite as select username,created,user_id from ad.t0829_1;

###DSS環境with rowid的使用：
create table ad.t0829_1 as select * from all_users;

SQL> desc ad.t0829_1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)
USER_ID                                   NOT NULL NUMBER
CREATED                                   NOT NULL DATE

***含有聚合函式的MV log在定義時必須將所有的結果欄位都寫在()裡=>(username,user_id,created)，缺一不可，including new values也是必須加上的
create materialized view log on ad.t0829_1 with rowid,sequence (username,user_id,created) including new values;

SQL> desc ad.mlog$_t0829_1;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                           VARCHAR2(30)
USER_ID                                            NUMBER
CREATED                                            DATE
M_ROW$$                                            VARCHAR2(255)
SEQUENCE$$                                         NUMBER
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)
XID$$                                              NUMBER

***MV建立時 with rowid必須指定
create materialized view ad.mv0829_1 build immediate refresh fast with rowid enable query rewrite as select username,created,sum(user_id),count(*),count(user_id) from ad.t0829_1 group by username,created;

需要說明的是在DSS環境下rowid是標配，雖然primary key也可以使用但是沒有這個必要，容易引起混淆

物化檢視建立時也有with primary key、with rowid兩個選項，使用的原則如下：
建立MV時如果不指明with 選項，那麼建立出的MV是with rowid的還是with primary key的取決於對應的MV log是with rowid還是primary key屬性；如果建立MV log同時使用了with rowid和with primary key，那麼MV建立的時候可以指定with rowid或者with primary key來指明採用哪種方式。有一種情況例外，使用with rowid建立出MV log，之後在建立不含聚合函式的MV時預設會使用with primary key來創MV，因此就需要顯式定義with rowid。

使用with rowid與with primary key建立出來的MV在結構上是有區別的，這個區別主要體現在MV底下的container table上，說具體點就是container table上的索引以何種形式建立，共有三種可能的形式：

(1) 如果MV不包含聚合函式，以with primary key建立出的MV具有與master table相同的主鍵
create table hr.test3 as select * from all_users;

drop materialized view log on hr.test3;

alter table hr.test3 add constraint pk_test3_uncrt primary key(username,created) using index tablespace users;

create materialized view log on hr.test3 tablespace users with primary key including new values;

drop materialized view hr.mvtest3;

create materialized view hr.mvtest3 tablespace users build immediate refresh fast on commit with primary key enable query rewrite as select username,created,user_id from hr.test3;

***與master table相同的主鍵
select owner,table_name,constraint_name,constraint_type from dba_constraints where table_name='MVTEST3' and constraint_type='P';
OWNER TABLE_NAME CONSTRAINT_NAME C
----- ---------- ------------------------------ -
HR MVTEST3 PK_TEST3_UNCRT1 P

INDEX INDEX_NAME           TABLE_NAME COLUMN_NAM
----- -------------------- ---------- ----------
HR    PK_TEST3_UNCRT1      MVTEST3    USERNAME
HR    PK_TEST3_UNCRT1      MVTEST3    CREATED

**PK_TEST3_UNCRT1如何被使用到？
update hr.test3 set user_id=700 where username='SYS';
commit;

上面的update執行後可以在shared pool裡搜到如下語句，紅色的部分即是用到了索引PK_TEST3_UNCRT1：

/* MV_REFRESH (MRG) */ MERGE INTO "HR"."MVTEST3" "SNA$" USING (SELECT CURRENT$."USERNAME",CURRENT$."CREATED",CURRENT$."USER_ID" FROM (SELECT "TEST3"."USERNAME" "USERNAME","TEST3"."CREATED" "CREATED","TEST3"."USER_ID" "USER_ID" FROM "HR"."TEST3" "TEST3") CURRENT$, (SELECT DISTINCT MLOG$."USERNAME" , MLOG$."CREATED" FROM "HR"."MLOG$_TEST3" MLOG$ WHERE "XID$$" = :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."USERNAME" = LOG$."USERNAME" AND CURRENT$."CREATED" = LOG$."CREATED")"AV$" ON ("SNA$"."USERNAME" = "AV$"."USERNAME" AND "SNA$"."CREATED" = "AV$"."CREATED") WHEN MATCHED THEN UPDATE SET "SNA$"."USERNAME" = "AV$"."USERNAME","SNA$"."CREATED" = "AV$"."CREATED","SNA$"."USER_ID" = "AV$"."USER_ID" WHEN NOT MATCHED THEN INSERT (SNA$."USERNAME",SNA$."CREATED",SNA$."USER_ID") VALUES (AV$."USERNAME",AV$."CREATED",AV$."USER_ID")

其執行邏輯大致為：從MV log裡將更新後的值取出去匹配master table裡的主鍵，然後找出master table裡對應的那一行所有列的值用來更新MV

(2) 如果MV不包含聚合函式，以with rowid建立出的MV具有基於rowid的索引
drop materialized view log on hr.test3;

create materialized view log on hr.test3 with rowid including new values;

drop materialized view hr.mvtest3;

create materialized view hr.mvtest3 build immediate refresh fast on commit with rowid enable query rewrite as select username,created from hr.test3;

***基於rowid的索引I_SNAP$_MVTEST3
SQL> select index_owner,index_name,table_name,column_name from dba_ind_columns where table_name='MVTEST3' and table_owner='HR';

INDEX INDEX_NAME TABLE_NAME COLUMN_NAM
----- -------------------- ---------- ----------
HR I_SNAP$_MVTEST3 MVTEST3 M_ROW$$

***M_ROW$$是container table hr.mvtest3裡的隱含欄位
select owner,table_name,column_name,hidden_column from dba_tab_cols where table_name='MVTEST3' and owner='HR' and column_name='M_ROW$$';
OWNER TABLE_NAME COLUMN_NAME HID
----- ---------- -------------------- ---
HR MVTEST3 M_ROW$$ YES

update hr.test3 set user_id=7000 where username='SYS';
commit;

上面的update執行後可以在shared pool裡搜到如下語句，紅色的部分即是用到了索引PK_TEST3_UNCRT1：
/* MV_REFRESH (MRG) */ MERGE INTO "HR"."MVTEST3" "SNA$" USING (SELECT CURRENT$."USERNAME",CURRENT$."CREATED",ROWIDTOCHAR(CURRENT$.ROWID) M_ROW$$ FROM (SELECT "TEST3"."USERNAME" "USERNAME","TEST3"."CREATED" "CREATED" FROM "HR"."TEST3" "TEST3") CURRENT$, (SELECT DISTINCT M_ROW$$ FROM "HR"."MLOG$_TEST3" MLOG$ WHERE "XID$$" = :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$.ROWID = LOG$.M_ROW$$)"AV$" ON ("SNA$"."M_ROW$$" = "AV$"."M_ROW$$") WHEN MATCHED THEN UPDATE SET "SNA$"."USERNAME" = "AV$"."USERNAME","SNA$"."CREATED" = "AV$"."CREATED" WHEN NOT MATCHED THEN INSERT ("SNA$"."M_ROW$$",SNA$."USERNAME",SNA$."CREATED") VALUES ("AV$"."M_ROW$$",AV$."USERNAME",AV$."CREATED")

其執行邏輯和(1)基本一致，這裡是透過rowid來匹配

(3) 如果MV包含了聚合欄位無論使用with primary key還是with rowid都會建立出基於filter column的函式索引，filter column簡單說就是group by 後面的欄位
drop materialized view log on hr.test3;

create materialized view log on hr.test3 with rowid,sequence (username,created,user_id) including new values;

drop materialized view hr.mvtest3;

create materialized view hr.mvtest3 build immediate refresh fast on commit with rowid enable query rewrite as select username,created,sum(user_id),count(user_id),count(*) from hr.test3 group by username,created;

***I_SNAP$_MVTEST3是基於函式SYS_OP_MAP_NONNULL的索引，USERNAME、CREATED就是所謂的filter columns
SQL> select index_owner,index_name,table_name,column_name from dba_ind_columns where table_name='MVTEST3' and table_owner='HR';

INDEX INDEX_NAME           TABLE_NAME COLUMN_NAME
----- -------------------- ---------- --------------------
HR    I_SNAP$_MVTEST3      MVTEST3    SYS_NC00006$
HR    I_SNAP$_MVTEST3      MVTEST3    SYS_NC00007$

SQL> select index_owner,index_name,table_name,column_expression from dba_ind_expressions where index_name='I_SNAP$_MVTEST3' and index_owner='HR';

INDEX INDEX_NAME           TABLE_NAME COLUMN_EXPRESSION
----- -------------------- ---------- --------------------------------------------------------------------------------
HR    I_SNAP$_MVTEST3      MVTEST3    SYS_OP_MAP_NONNULL("USERNAME")
HR    I_SNAP$_MVTEST3      MVTEST3    SYS_OP_MAP_NONNULL("CREATED")

***SYS_NC00006$、SYS_NC00007$也是隱含欄位
SQL> select owner,table_name,column_name,hidden_column from dba_tab_cols where table_name='MVTEST3' and column_name like '%SYS_NC%' and owner='HR';

OWNER TABLE_NAME COLUMN_NAME          HID
----- ---------- -------------------- ---
HR    MVTEST3    SYS_NC00006$         YES
HR    MVTEST3    SYS_NC00007$         YES

SYS_OP_MAP_NONNULL函式能將包括null在內的所有值轉化為內部儲存的十六進位制值，因此該函式可以實現等值匹配的功能

update hr.test3 set user_id=70000 where username='SYS';
commit;

update語句執行後可以觀察到如下SQL，紅色的部分用到了函式索引：
/* MV_REFRESH (MRG) */ MERGE INTO "HR"."MVTEST3" "SNA$" USING (SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */ "DLT$0"."USERNAME" "GB0", "DLT$0"."CREATED" "GB1", SUM(DECODE("DLT$0"."DML$$", 'I', 1, -1)) "D0", SUM(DECODE("DLT$0"."DML$$", 'I', 1, -1)* DECODE(("DLT$0"."USER_ID"), NULL, 0, 1)) "D1", NVL(SUM(DECODE("DLT$0"."DML$$", 'I', 1, -1)* ("DLT$0"."USER_ID")), 0) "D2" FROM (SELECT /*+ CARDINALITY(MAS$ 0) */ CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."CREATED", "MAS$"."USERNAME", "MAS$"."USER_ID" , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$, "MAS$"."DMLTYPE$$" "DMLTYPE$$" FROM "HR"."MLOG$_TEST3" "MAS$" WHERE "MAS$".XID$$ = :1 ) "DLT$0" GROUP BY "DLT$0"."USERNAME","DLT$0"."CREATED")"AV$" ON (SYS_OP_MAP_NONNULL("SNA$"."USERNAME")=SYS_OP_MAP_NONNULL("AV$"."GB0") AND SYS_OP_MAP_NONNULL("SNA$"."CREATED")=SYS_OP_MAP_NONNULL("AV$"."GB1")) WHEN MATCHED THEN UPDATE SET "SNA$"."COUNT(*)"="SNA$"."COUNT(*)"+"AV$"."D0", "SNA$"."COUNT(USER_ID)"="SNA$"."COUNT(USER_ID)"+"AV$"."D1", "SNA$"."SUM(USER_ID)"=DECODE("SNA$"."COUNT(USER_ID)"+"AV$"."D1",0,NULL,NVL("SNA$"."SUM(USER_ID)",0)+"AV$"."D2") DELETE WHERE ("SNA$"."COUNT(*)" = 0) WHEN NOT MATCHED THEN INSERT ("SNA$"."USERNAME", "SNA$"."CREATED", "SNA$"."COUNT(*)", "SNA$"."COUNT(USER_ID)", "SNA$"."SUM(USER_ID)") VALUES ( "AV$"."GB0", "AV$"."GB1", "AV$"."D0", "AV$"."D1", DECODE ("AV$"."D1", 0, NULL, "AV$"."D2")) WHERE ("AV$"."D0" > 0)

所有更新的欄位和rowid都記錄在了MV log表裡，所以也就沒有必要去訪問master table了，利用SYS_OP_MAP_NONNULL函式在MV的filter columns與MV log的等值列間進行匹配，再在相應的聚合欄位上做數學運算來更新MV

建立快速重新整理物化檢視使用with rowid還是with primary key？

相關文章