一、简介
物化视图顾名思义就是物理化了视图，使它实际存储到了硬盘上，查询性能能比普通的视图好 二、物化视图原理： 1、生成数据 两大选项：build immediate build deferred Build immediate:在创建物化视图的同时根据主表生成数据 Bulid deferred:在创建物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取：EXEC DBMS_MVIEW.Refresh(‘MV_name’,’C’)，注意必须使用全量刷新，默认是增量刷新，所以这里参数必须是C，因为之前都没有生成数据，所以必须全量。 2、关于刷新 三种刷新方式：complete fast force Complete :完全刷新整个物化视图，相当于重新生成物化视图，此时即时增量刷新可用也全量刷新 Fast:当有数据更新时依照相应的规则对物化视图进行更新（此时必须创建物化视图日志（物化视图日志记录了数据更新的日志） Force:当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项） 不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下（在创建物化视图语句中，可能某些限制查询的条件，导致了增量刷新无法使用，这个是需要注意的，具体是哪类语句导致fast刷新不可用，有待总结…..） 三种刷新时间：on demand on commit start with/ next On demand:在需要刷新时进行刷新（人工判断） On commit:在基表上有提交操作时，进行更新 Start with:指定首次刷新的时间（一般指定的是当前时间，不过也可以在创建物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据） Next:刷新的周期时间 三、实例演示(分两台主机操作，主表主机和物化视图主机) 1、创建表空间 (主表机器操作) create tablespace TEST_view datafile '/home/oracle/oradata/orcl/TEST_view.dbf' size 100m autoextend on next 50m; 2、创建用户(主表机器操作) create user sc identified by test; alter user sc default tablespace TEST_TABLE; 物化视图机器 create user sc1 identified by test; alter user sc1 default tablespace TEST_view; 3、给用户授权(主表机器操作) SC和SC1都要赋这些权限 grant connect,resource to sc; GRANT UNLIMITED TABLESPACE TO sc; grant create session to sc; grant create table to sc; GRANT CREATE MATERIALIZED VIEW TO sc; 4、用户建立测试表(主表机器操作)conn sc/test
create table sc_test(id int primary key, name char(10)); 5、插入数据(主表机器操作) insert into sc_test(id,name) values(1,'a'); insert into sc_test(id,name) values(2,'a'); insert into sc_test(id,name) values(3,'a'); insert into sc_test(id,name) values(4,'a'); insert into sc_test(id,name) values(5,'a'); insert into sc_test(id,name) values(6,'a'); insert into sc_test(id,name) values(7,'a'); 6、创建DBLINK (物化视图机器操作)必须要具有DBA权限的用户创建 CREATE PUBLIC DATABASE LINK to_test CONNECT TO "sc" identified by "test" using 'Sc_test'; 注：加上PUBLIC就是所有用户都可以用这个DBLINK啦 删除DBLINK drop DATABASE LINK to_test; 7、建立视图日志(主表机器操作)conn sc/test
CREATE MATERIALIZED VIEW LOG ON sc_test WITH primary key INCLUDING NEW VALUES; 注：表上一定要主键 8、创建物化视图 (物化视图机器操作)必须具有CREATE MATERIALIZED VIEW权限conn sc1/test
create MATERIALIZED VIEW sc_test_view REFRESH fast ON DEMAND with primary key start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'00:00:00'),'dd-mm-yyyy hh24:mi:ss') AS SELECT * FROM sc.sc_test@to_test; 注：这里采用的是定时同步，有需求可以实时同步 修改更新时间 alter MATERIALIZED VIEW sc_test_view REFRESH fast ON DEMAND with primary key start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'00:00:00'),'dd-mm-yyyy hh24:mi:ss') 9、查询最后刷新时间，两个表都可以查询的到 select owner,name,MASTER_OWNER,MASTER,to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') last_update from dba_mview_refresh_times; select owner,mview_name,LAST_REFRESH_TYPE,to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') last_update from user_mviews;