我们如果遇到需要从其它系统的数据库中取数据进行统计分析的问题,可疑选择使用ORACLE
的ODI工具进行抽数,但是对方提供的数据库用户下没有任何对象,只是有查询所有表的权限,因此无法做数据反向。
于是决定使用物化视图,把对方数据库中的数据拿过来,虽然数据量比较大,但是每月只拿一次,而且如果设置成增量更新,也不会太慢。现在记录下物化视图的创建过程(以一张表为例)。
一、准备条件以及备注
假设双方数据库都是ORACLE10g,需要同步过来的表名叫:GG_ZLX_ZHU,对方数据库用户名:username,密码:password,SID:CPEES。
二、开始干活
1、首先要创建DB_LINK
-
CREATE DATABASE LINK to_cpees
-
-
CONNECT TO "username" identified by "password"
-
-
using "CPEES"
其中CPEES为新建的到对方数据库的TNS。执行,现在我们就已经创建了到对方数据库的DB_LINK TO_CPEES。
2、创建Oracle物化视图快速刷新日志
因为上面说过,以后视图的刷新将采用增量刷新的方式,因此,为配合增量刷新,ORACLE要求要在住表上建立物化视图日志。
-
CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU
-
-
WITH PRIMARY KEY
-
-
INCLUDING NEW VALUES;
-
(上面的SQL要在远程数据库上执行,不能在本地执行)
3、创建Oracle物化视图
Oracle物化视图,从名字上面来开,它应该是属于视图,但是确实物化。其物化是针对普通视图并没有真正的物理存储而言,其实可以简单的把物化视图看做一个物理表(不再做具体解释)。
-
CREATE MATERIALIZED VIEW GG_ZLX_ZHU --创建物化视图
-
-
BUILD IMMEDIATE --在视图编写好后创建
-
-
REFRESH FAST WITH PRIMARY KEY --根据主表主键增量刷新(FAST,增量)
-
-
ON DEMAND -- 在用户需要时,由用户刷新
-
-
ENABLE QUERY REWRITE --可读写
-
-
AS
-
-
SELECT * FROM GG_ZLX_ZHU@TO_CPEES; --查询语句
4、视图刷新
根据业务需要,每月不定时刷新,所以不能是JOB,而且数量多,所以也不能一个一个刷新。根据以上条件,选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数,第一个参数是需要刷新的物化视图名称,第二个参数是刷新方式。我们可以写存储过来,对每个物化视图调用一次REFRESH方法,也可以使用“,”把物化视图连接以来,一次刷新。如下:
-
CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS
-
-
BEGIN
-
-
DBMS_MVIEW.REFRESH('GG_ZLX_ZHU','f');
-
-
END P_MVIEW_REFRESH;
-
或者使用
-
CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS
-
-
BEGIN
-
-
DBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU','ff');
-
-
END P_MVIEW_REFRESH;
-
注意:
1、如果需要同时刷新多个物化视图,必须用逗号把各个物化视图名称连接起来,并对
每个视图都要指明刷新方式(f、增量刷新,c、完全刷新,?、强制刷新)。
2、当日志和物化视图创建好后,删除日志,则需要重新创建物化视图,否则无法增量
刷新。
3、因为上面写的物化视图时根据主键进行更新,因此,主表必须有主键。
4、以上文章中红色是为可替换的,大家可以根据自己项目需求来修改。
希望上面的内容对大家能有帮助。
忘了写删除方法了,日志和物化视图要分开删除
-
DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@TOCPEES;
-
-
DROP MATERIALIZED VIEW GG_ZLX_ZHU;
相关推荐
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
说明:Oracle中需要创建用户一定是要具有dba(数据库管理员)权限的用户才能创建,而且创建的新用户不具备任何权限,连登录都不可以。 用法:create user 新用户名 identified by 密码 例子: 2. 修改密码 说明:...
10.5 使用物化视图进行翻页性能调整 514 10.5.1 系统环境 514 10.5.2 问题描述 514 10.5.3 捕获排序SQL语句 515 10.5.4 确定典型问题SQL 515 10.5.5 选择解决办法 517 10.5.6 进一步的调整优化 518 ...
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
2.10 使用物化视图进行查询重写 44 2.11 确定执行计划 46 2.12 执行计划并取得数据行 50 2.13 SQL执行——总览 52 2.14 小结 53 第3章 访问和联结方法 55 3.1 全扫描访问方法 55 3.1.1 如何选择全扫描操作 56...
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
【课程列表】 index 共14页.ppt ...第10章 物化视图 共38页.ppt 第11章 留下元数据线索 共32页.ppt 第12章 数据仓库实施注意事项 共18页.ppt xpp_C 使用SQL Developer 共30页.ppt xpp_D 使用SQL Plus 共34页.ppt
第一部分sql基础 9 基本查询语句 9 实验1:书写一个最简单的sql语句,查询一张表的所有行和所有列 9 ...实验161:物化视图的建立 253 实验162:查询重写 255 实验163:最后的sql优化办法,使用hints