oracle物化视图的两个典型应用场景

物化视图是oracle一个比较有特色的东西，自oracle9i起，应用非常广泛，不像mysql，不支持原生物化视图，要借助flexviews去实现。物化视图到底有什么用呢？要回答这个问题，必须先搞清楚物化视图与普通视图的区别：

物化视图是有一个与之对应的容器表的。容器表是一个跟物化视图同名的“规则”的表，用于存储查询返回的结果集。这是物化视图与普通视图的根本区别，它是有储存结果集的“物理存在”的，而普通视图则没有这个物理存在，只是一个虚表，每访问一次，查询就要执行一次基表访问（不考虑cache）。

 

物化视图的应用场景有两种：1、用于查询优化 2、用于高级复制，下面分别举一些实际工作中的case来进行说明。

1、查询优化。

某电信增值业务，在进程启动的时候，需要加载一些重要的业务初始化数据（比如全球运营商的networkid、cc、ndc等基础数据），这些业务初始化数据要从四个表中查询获取。为了提高这部分数据的access性能，加快进程启动速度，可以将这四个表的数据组合为一个物化视图，定义如下：

CREATE MATERIALIZED VIEW IRDB_NETWORKLIST
REFRESH FORCE ON COMMIT
AS
SELECT
   A.NETWORKID AS NETWORKID,
   A.NETWORKNAME AS NAME,
   C.CC AS CC,
   C.NDC AS NDC,
   B.MCC AS MCC,
   B.MNC AS MNC,
   A.NEWVISITINTERVAL AS NEWVISITINTERVAL,
   A.OUTNEWVISITINTERVAL AS OUTNEWVISITINTERVAL,
   D.MNP_ENABLED AS HASMNP,
   A.BRANDNAME AS BRANDNAME,
   A.LANGUAGECODE AS DEFAULTLANGUAGECODE,
   C.TIMEZONE AS TIMEZONE,
   A.NDD AS NDD,
   A.ZONEID AS ZONEID
FROM
IRDB_NETWORK_MASTER A, IRDB_NETWORK_GSM_DETAIL B, IRDB_NETWORK_CODES C, IRDB_COUNTRY_MASTER D
WHERE
A.NETWORKID = B.NETWORKID AND A.NETWORKID = C.NETWORKID AND A.COUNTRYID = D.COUNTRYID AND A.STATUS = '1' AND C.STATUS = '1'

 

后续当我们对IRDB_NETWORK_MASTER、IRDB_NETWORK_GSM_DETAIL、IRDB_NETWORK_CODES和IRDB_COUNTRY_MASTER这四个业务基表中的任何一个或者多个进行DML commit的时候，物化视图IRDB_NETWORKLIST就能自动更新了。当然了，基表与物化视图的同步也是有一定代价的，但如果不建立物化视图，那么每次外部调用都会去查询基表，而物化视图会将这种压力分散，将基表查询、基表连接与外部业务接口访问错开，有利于降低数据库负载的peak值，这也是数据库性能优化的核心思想之一。

 

2、高级复制

很多业务场景，我们不需要对整个数据库进行同步，只需要对部分表的部分字段进行同步，这个时候，物化视图可以派上用场。下面是某直辖市移动彩铃业务数据库结构图：

 

 

这里，一台P650小机作为管理节点，负责业务开销户、业务话单等数据处理，其余5台P650小机作为呼叫节点使用，只提供用户数据的查询功能。这里不需要用DG等全库同步的技术，因为呼叫节点只需要同步部分与呼叫业务相关的数据。管理节点建立物化视图，这里以t_userinfo用户表为例：

CREATE MATERIALIZED VIEW                     

usdptemp.T_USERINFO_MV

REFRESH  FAST                                

AS                                           

selectPHONENUMBER,LOCALID,BRANDID,PAYKIND from usdp604.T_USERINFO@admin_node

 

在呼叫节点建立一个同义词：

create or replace synonym T_USERINFO
 for T_USERINFO_mv;

这样子可以保持和管理节点完全一样的对象名称。

然后建立一个刷新组，每10秒刷新一次物化视图：

--create a refresh group

BEGIN   

   DBMS_REFRESH.MAKE(   

   name => 'usdpsync',   

    list => 'T_USERINFO_mv',   

    next_date=> sysdate,   

    interval => 'sysdate + 10/86400'   

);   

END;   

/

在初始化同步的时候，首先用临时用户从管理节点导出数据，注意表后跟上管理节点的global_name(与呼叫节点上建的dblink名称一致），然后在呼叫节点上impdp数据，也就是说，呼叫节点上的物化视图T_USERINFO_mv是基于管理节点上的基表物化视图日志建立的，这样子就可以跟随管理节点基表更新了。

 

物化视图在使用过程中，还有几点需要留意，在此罗列一下：

1、物化视图有两种刷新模式ON DEMAND和ON COMMIT，前者需要调用dbms_mview手工刷新，后者在基表提交的时候会自动刷新。如果建立物化视图的时候不指定刷新方式，默认是ON DEMAND，此时一般需要建立一个job，在job里按照某个固定周期调用dbms_mview进行刷新。

2、物化视图日志在建立时有多种选项：可以指定为ROWID、PRIMARY KEY和OBJECT ID几种类型，同时还可以指定SEQUENCE或明确指定列名。上面这些情况产生的物化视图日志的结构都不相同。

3、刷新方法有四种：fast、complete、force和never。
fast是增量刷新，只刷新上次刷新以后进行的修改。
complete是对整个物化视图进行完全的刷新。
force，oracle在刷新时会判断是否可以进行快速刷新，如果可以进行fast，否则进行complete
never，物化视图不进行任何刷新。

如果要进行快速刷新，必须建立物化视图日志。物化视图日志命名规则为mlog$_+基表名称。
查看当前系统的物化视图日志表：
select * from dba_mview_logs;

force方式刷新则不需要建立物化视图日志。

 

4、如果物化视图遇到同步问题，紧急情况下，可以执行手动刷新：

SQL> exec dbms_mview.refresh('IRDB_NETWORKLIST');
PL/SQL procedure successfully completed

此时会根据基表对IRDB_NETWORKLIST物化视图进行刷新。

 

5、物化视图日志优化：

对于物化视图日志，我们可以建立索引提高性能。同时在排查同步慢等性能问题的时候也需要检查看是否出现高水位，物化视图日志表的高水位会影响刷新性能，处理高水位的方法，在此省略。