Oracle物化视图with rowid和with primary key区别

太过爱你忘了你带给我的痛 2022-05-17 03:44 103阅读 0赞

**本文主要谈及以下两种物化视图的应用：**  
**1、用于分布式环境里的物化视图，例如master table在A库，B库通过dblink创建了一个指向A库taba的物化视图mvb:**   
**create materialized view mvb ... as select \* from** [**taba@link\_a**][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;

\*\*\*创建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)<−−−withrowid就会生成一列MROW

记录变更行的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;

\*\*\*含有聚合函数的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

"=:1AND("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$,(SELECTDISTINCTMROW

FROM "HR"."MLOG$\_TEST3" MLOG$ WHERE "XID

"=:1AND("DMLTYPE

" != 'D')) LOG$ WHERE CURRENT$.ROWID = LOG$.M\_ROW$$)"AV$" ON ("SNA$"."M\_ROW

"="AV$"."MROW

") 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$"."MROW

",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"."USERID")),0)"D2"FROM(SELECT/∗+CARDINALITY(MAS$0)∗/CHARTOROWID("MAS$"."MROW

") 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

[taba_link_a]: mailto:taba@link_a