Oracle-物化视图

我们遇到这样的问题，通过etl工具从一个数据库的多张关联表定时抽取信息写入目标数据库对应的表中。由于表之间有关联性，所以我们想在目标库中为这些表建一个物化视图。通过对物化视图进行增删改查，统一实现对各基表的增删改查，其实思路是错误的，物化视图没有解决我们的问题。因为物化视图的使用是这样子的，如果遇到需要从其它系统的数据库中取数据进行统计分析的问题，而且对方提供的数据库用户下没有任何对象，只是有查询所有表的权限时，可以使用物化视图，把对方数据库中的各基表的数据拿过来，如果设置成增量更新，就可以在视图中实时获得基表数据的信息。我们可以看出，对基表的修改可以呈现在视图上。物化视图不适用于我们的原因是，我们的需求是反向的，希望对视图的修改能作用于基表。

最终，我们通过建一张涵盖各基表字段的综合表，外加一个触发器解决了我们的问题。以下详细记录了我们的各种尝试：

首先，如何建一张物化视图？物化视图没有解决我们的问题，但是总结我们的尝试心得如下，仅供参考。

一，准备条件以及备注

假设双方数据库都是ORACLE10g，需要同步过来的表名（又称基表）：SYN_QX，对方数据库用户名：username，密码：password，SID：demo。

二，首先要创建DB_LINK

　　CREATE DATABASE LINK to_demo

　　CONNECT TO "username" identified by "password"

　　using "demo"  

其中demo为新建的到对方数据库的TNS。执行，现在我们就已经创建了到对方数据库的DB_LINK： to_demo。

三，创建Oracle物化视图的日志

因为上面说过，以后视图的刷新将采用增量刷新的方式，因此，为配合增量刷新，ORACLE要求要在基表上建立物化视图日志。

　　CREATE MATERIALIZED VIEW LOG ON SYN_QX 

　　WITH PRIMARY KEY  

　　INCLUDING NEW VALUES;   

(上面的SQL要在远程数据库上执行，不能在本地执行)

四，创建Oracle物化视图

Oracle物化视图，从名字上面来开，它应该是属于视图，但是确实物化。其物化是针对普通视图并没有真正的物理存储而言，其实可以简单的把物化视图看做一个物理表

　　CREATE MATERIALIZED VIEW SYN_QX --创建物化视图  

　　BUILD IMMEDIATE  --在视图编写好后创建  

　　REFRESH FAST WITH PRIMARY KEY  --根据主表主键增量刷新（FAST，增量）  

　　ON DEMAND  -- 在用户需要时，由用户刷新  

　　ENABLE QUERY REWRITE  --可读写  

　　AS  

　　SELECT * FROM SYN_QX@TO_DEMO; --查询语句  

五，视图刷新

根据业务需要，每月不定时刷新，所以不能是JOB，而且数量多，所以也不能一个一个刷新。根据以上条件，选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数，第一个参数是需要刷新的物化视图名称，第二个参数是刷新方式。如果多个视图的话，我们可以写存储过程，对每个物化视图调用一次REFRESH方法，也可以使用“，”把物化视图连接以来，一次刷新。如下：

　　CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS  

　　BEGIN  

　　DBMS_MVIEW.REFRESH('SYN_QX','f');  

　　END P_MVIEW_REFRESH;   

或者使用

　　CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS  

　　BEGIN  

　　DBMS_MVIEW.REFRESH('SYN_QX,'SYN_QX2','ff');  

　　END P_MVIEW_REFRESH;   

注意:

1、如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新）。

2、当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量刷新。

3、因为上面写的物化视图时根据主键进行更新，因此，主表必须有主键。

六，删除方法，日志和物化视图要分开删除。

　　DROP MATERIALIZED VIEW LOG ON SYN_QX@TO_DEMO;  

　　DROP MATERIALIZED VIEW SYN_QX;