摘自:http://blog.csdn.net/javaee_sunny/article/details/53439980
Oracle 10g 物化视图语法如下:
create materialized view [view_name] refresh [fast|complete|force] [ on [commit|demand] | start with (start_time) next (next_time) ] as {创建物化视图用的查询语句}
以上是Oracle创建物化视图(Materialized View,以下简称MV)时的常用语法,各参数的含义如下:
-
refresh [fast|complete|force] 视图刷新的方式:
fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据。 为了记录这种变化,建立增量刷新物化视图还需要一个物化视图日志表。 create materialized view log on (主表名)。(多张表时,此语句也生效,创建后,原来的表中会多出两类视图表:MLOG$_table_name和RUPD$_table_name) complete: 全部刷新。相当于重新执行一次创建视图的查询语句。 force: 这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。
2.MV数据刷新的时间:
-
n demand: 在用户需要刷新的时候刷新,这里就要求用户自己动手去刷新数据(可以使用JOB定时刷新); on commit: 当主表中有数据提交的时候,立即刷新MV中的数据; start ……:从指定的时间开始,每隔一段时间(由next指定)就刷新一次; 全刷新mv_test物化视图:
begin dbms_mview.refresh(TAB=>'MV_TEST', METHOD=>'COMPLETE', PARALLELISM=>8); end; /
增量刷新:
begin dbms_mview.refresh(TAB=>'MV_TEST', METHOD=>'FAST', PARALLELISM=>1); end; /
或者,也可以这样执行:
exec dbms_mview.refresh('MV_TEST','F'); dbms_mview.refresh('表名', 'F') --快速刷新,也就是增量刷新 dbms_mview.refresh('表名', 'C') --完全刷新
实例演示:
以下是我通过创建物化视图,实现不同数据库间表级别的数据同步的一个过程记录,(两台服务器A和B,其中要把数据从A节点的表zh_major_item同步到B节点上)。目前实现同步的过程还只是一个单向的过程,也就是从A节点 —> B节点:
主要步骤:
- 在A节点创建原表和物化视图日志
- 在B节点创建连接A节点的远程链接
- 在B节点处,创建目标表和与目标表名称相同的物化视图
1. 在A节点创建原表和物化视图日志:
--在源A处 创建物化视图日志 sqlplus tianzhi_smart/tianzhi_smart@localhost:1521/orcl --原表zh_major_item之前已经存在,这里就不赘述表创建的过程了 --创建物化视图日志 create materialized view log on zh_major_item;
2. 在B节点创建连接A节点的远程链接:
--在目标B处 创建远程连接 --如果没有创建远程连接的权限,需要登录sysdba,为用户授权; --之所以跟上面一样,因为我的用户名和密码相同而已 sqlplus tianzhi_smart/tianzhi_smart@localhost:1521/orcl --创建远程连接 db_link create public database link db_link_A connect to tianzhi_smart identified by "tianzhi_smart" using '192.168.56.6:1521/ORCL'; --验证是否创建成功 select * from zh_major_item@db_link_A ;
3. 在B节点处,创建目标表和与目标表名称相同的物化视图:
--B节点处创建目标表bc_major_item create table bc_major_item as select * from zh_major_item@db_link_A where 1=2; --通过远程连接向A节点建立一个与目标表(bc_major_item)名称相同的物化视图 --使用on prebuilt table注册新的物化视图,注意view名称必须和表名称一样 --使用on prebuilt table创建的物化视图被删除后,原来的表不被删除 --注意这里我创建是refresh fast on demand 类型的物化视图 create materialized view bc_major_item on prebuilt table refresh fast on demand as select * from zh_major_item@db_link_A ;
4.在B节点处,刷新物化视图
--刷新物化视图 exec dbms_mview.refresh('bc_major_item ','C'); --刷新后,查询目标表,比对是否和原表数据相同 select * from bc_major_item ;
5.升级采用存储过程+定时任务JOB方式,定时刷新物化视图
--创建存储过程,进行增量刷新 create or replace procedure refresh_bc_major_item as begin dbms_mview.refresh('bc_major_item','F'); end; / --创建任务JOB SQL> variable job1 number; SQL>begin dbms_job.submit(:job1,'refresh_bc_major_item;',sysdate,'sysdate+1/(86400)'); end; / --运行JOB SQL> begin dbms_job.run(:job1); end; /
============================================================================
老哥指导:
(1)预建表得加主键,预建表创建物化视图时加rowid会报错,使用预建表时就不能用with rowid
使用预建的表局限性大,只能用with primary key,不能用with rowid
预建表:是事先创建一个表后面用来存放物化视图数据的表
(2)创建物化视图语句(可修改的物化视图)
CREATE MATERIALIZED VIEW LOG ON BATCH_STEP_EXECUTION WITH ROWID ; CREATE MATERIALIZED VIEW MV_BATCH_STEP_EXECUTION TABLESPACE ETL NOLOGGING COMPRESS FOR OLTP REFRESH FAST WITH ROWID FOR UPDATE ENABLE QUERY REWRITE AS SELECT * FROM BATCH_STEP_EXECUTION@ETL_55_AP cu
如图:
(3)
登录hr用户下雨欣,ON PREBUILT TABLE子句允许咱们将现有已经存在的堆表注册为预初始化的物化视图里,
这种设计多数在数仓环境,物化视图被删除,可以通过预建表进行恢复
WITH REDUCED PRECISION就类似个授权,如果表或物化视图列的精度与子查询返回的精度不匹配,将导致精度缺失,给个关键字控制一下