Oracle 物化视图使用

﹏ヽ暗。殇╰゛Y 2022-05-18 10:10 305阅读 0赞

### 一、物化视图用法： ###

物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。

1、物化视图的类型：ON DEMAND、ON COMMIT  
  
    二者的区别在于刷新方法的不同，ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。  
  
  
2、ON DEMAND物化视图     
  
物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。     
  
  
物化视图的特点：  
  
  
    (1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user\_tables查询出来，而得到佐证；  
  
    (2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性；  
  
    (3) 物化视图会占用数据库磁盘空间，这点从user\_segment的查询结果，可以得到佐证；  
  
    创建语句：create materialized view mv\_name as select \* from table\_name    默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。  
    物化视图的数据怎么随着基表而更新？    Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。  
  
    ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别，即前者不刷新(手工或自动)就不更新物化视图，而后者不刷新也会更新物化视图，——只要基表发生了COMMIT。  
  
    创建定时刷新的物化视图：create materialized view mv\_name refresh force on demand start with sysdate next sysdate+1 （指定物化视图每天刷新一次）  
  
    上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间（比如每天晚上10：00定时刷新一次）：create materialized view mv\_name refresh force on demand start with sysdate next to\_date( concat( to\_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')  
  
3、ON COMMIT物化视图  
  
     ON COMMIT物化视图的创建，和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的，所以ON COMMIT物化视图，需要再增加个参数即可。   需要注意的是，无法在定义时仅指定ON COMMIT，还得附带个参数才行。  
  
     创建ON COMMIT物化视图：create materialized view mv\_name refresh force on commit as select \* from table\_name    备注：实际创建过程中，基表需要有主键约束，否则会报错（ORA-12014）  
  
4、物化视图的刷新  
  
     刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。（如上所述）  
  
     刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。  
  
     对于已经创建好的物化视图，可以修改其刷新方式，比如把物化视图mv\_name的刷新方式修改为每天晚上10点刷新一次：alter materialized view mv\_name refresh force on demand start with sysdate next to\_date(concat(to\_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')      
  
5、物化视图具有表一样的特征，所以可以像对表一样，我们可以为它创建索引，创建方法和对表一样。  
  
6、物化视图的删除：  
  
      虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中，并不能用删除表的方式来删除（在表上右键选择‘drop’并不能删除物化视图），可以使用语句来实现：drop materialized view mv\_name   
  
物化视图创建参数  
  
(1)BUILD BUILD IMMEDIATE 是在创建物化视图的时候就生成数据 BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。 默认为BUILD IMMEDIATE。  
(2)REFRESH FAST 增量刷新用物化视图日志，来发送主表已经修改的数据行到物化视图中。 COMPLETE 完全刷新重新生成整个视图，如果请求完全刷新，oracle会完成完全刷新即使增量刷新可用。 FORCE 如果增量刷新可用Oracle将完成增量刷新，否则将完成完全刷新，如果不指定刷新方法(FAST, COMPLETE, or FORCE)。 默认选项是Force。  
(3)ON ON DEMAND 指物化视图在用户需要的时候进行刷新。 ON COMMIT 指出物化视图在对基表的DML操作提交的同时进行刷新。 默认是ON DEMAND.  
(4)START WITH 通知数据库完成从主表到本地表第一次复制的时间。  
(5)NEXT 说明了刷新的时间间隔 根据下一次刷新的时间=上一次执行完成的时间+时间间隔。 为了保证在用户需要的时间点刷新，一般使用TRUNC()命令对时间取整到天数，然后加上时间。  
  
具体示例代码如下所示：

1.  `--删除日志`
2.  `TRUNCATE TABLE mlog$_members;`
3.  `DROP MATERIALIZED VIEW LOG ON members;`
4.   
5.  `--创建基表日志`
6.  `CREATE MATERIALIZED VIEW LOG ON members WITH ROWID, SEQUENCE(memberno) INCLUDING NEW VALUES;`
7.   
8.  `--创建物化视图`
9.  `DROP MATERIALIZED VIEW mv_members;`
10.  
11. `CREATE MATERIALIZED VIEW mv_members`
12. `BUILD IMMEDIATE`
13. `REFRESH FAST`
14. `ON DEMAND`
15. `START WITH SYSDATE`
16. `NEXT SYSDATE + 5/(60*24)`
17. `AS`
18. `Select`
19. `"MEMBER_CARD_NO" memberno,`
20. `b.bindingphone mobile,"AIRWAYNAME","PAYTYPE","DELIVERY_TYPE","ODR_TYPE","ROOMCHNNAME","BED","ASSURETYPE","ORDERCURLOCATION"`
21. `From customer_new.t_memhabit@link_118 a,members b`
22. `Where a.member_card_no = b.memberno`

### 二、查看Oracle物化视图（MVIEW）的刷新起始时间和结束时间  ###

DBA\_MVIEWS的LAST\_REFRESH\_DATE字段或者DBA\_MVIEW\_REFRESH\_TIMES的LAST\_REFRESH字段记录了Oracle物化视图刷新的起始时间。但如果我们想查看物化视图的刷新到底花费了多长时间，应该怎么做呢。其实，我们可以查询DBA\_MVIEW\_ANALYSIS。  
  
对于完全刷新（Complete）,刷新耗时会记录在DBA\_MVIEW\_ANALYSIS的FULLREFRESHTIM列中。而对于可快速刷新（Fast）的时间，会记录在INCREFRESHTIM列中。  
这两个值的单位都是秒。  
  
  
SQL> SELECT  
       mview\_name,  
       last\_refresh\_date "START\_TIME",  
       CASE  
          WHEN fullrefreshtim <> 0 THEN  
             LAST\_REFRESH\_DATE + fullrefreshtim/60/60/24  
          WHEN increfreshtim <> 0 THEN  
             LAST\_REFRESH\_DATE + increfreshtim/60/60/24  
          ELSE  
            LAST\_REFRESH\_DATE  
      END "END\_TIME",  
      fullrefreshtim,  
      increfreshtim  
   FROM all\_mview\_analysis;  
  
  
MVIEW\_NAME                     START\_TIME  END\_TIME    FULLREFRESHTIM INCREFRESHTIM  
\------------------------------ ----------- ----------- -------------- -------------  
MGMT\_ECM\_MD\_ALL\_TBL\_COLUMNS    2013-08-24  2013-08-24               0             0  
MV\_JPUSER\_CONSUME              2016-04-21  2016-04-21             355             0  
MV\_MEMHABIT                    2016-04-21  2016-04-21              88             0  
MV\_JDUSER\_CONSUME              2016-04-21  2016-04-21             168             0  
MV\_MEMBERS\_0                   2016-04-21  2016-04-21            1765             0  
MV\_MEMBERS                     2016-04-21  2016-04-21             540             0