本文主要谈及以下两种物化视图的应用：
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
扫一扫
887
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
