Materialized View 简单实践
为了对物化视图的一些性能进行测试，做了几个简单的实验。都是一些最最进本的操作，主要是为了确认物化视图在各种设置下的真实反映。例如是否可以使用FAST刷新，以及on prebuilt table子句对表和物化视图的不同影响，下面看一下例子：
1、首先在实例A中创建表T8：
create table t8 (i int primary key,a number,n varchar2(100));
insert into t8 values(1,10,'aaaaaaaaaaa');
insert into t8 values(2,20,'bbbbbbbbbbb');
insert into t8 values(3,30,'ccccccccccc');
insert into t8 values(4,40,'ddddddddddd');
insert into t8 values(5,50,'eeeeeeeeeee');
insert into t8 values(6,60,'fffffffffff');
insert into t8 values(7,70,'ggggggggggg');
commit;
2、在实例B中创建A的DBLINK temp_link：
create public database link temp_link
connect to wxq identified by wxq
using 'SID';
3、在实例B中创建物化视图wxq_mv：
create materialized view wxq_mv
tablespace wxq_tbs
build deferred --延迟刷新不立即刷新,immediate为立即刷新
refresh force --如果可以快速刷新则进行快速刷新，否则完全刷新
on demand --按照指定方式刷新，远程复制不能使用commit
start with to_date('10-09-2008 16:45:10', 'dd-mm-yyyy hh24:mi:ss')
next sysdate + 1
with primary key
as
注1：这个过程遇到一个错误，在next子句后面加了注释后编译报错，晕，搞了N久才发现。
注2：发现在MV自动自制JOB后到了时间没有触发，查了下资料，是JOB_QUEUE_INTERVA参数设置为0，alter system set job_queue_processes=10后可以自动运行。
注3：手动运行job——execute dbms_job.run(21)
4、检查数据：
执行job刷新视图后查询数据：
SQL> execute dbms_job.run(26);
PL/SQL procedure successfully completed
SQL> column i format a5
SQL> column a format a5
SQL> column n format a15
SQL> column mydate format a20
SQL> select * from wxq_mv;
I A N MYDATE
----- ----- --------------- --------------------
1 10 aaaaaaaaaaa 2008-9-10 17:41:38
2 20 bbbbbbbbbbb 2008-9-10 17:41:38
3 30 ccccccccccc 2008-9-10 17:41:38
4 40 ddddddddddd 2008-9-10 17:41:38
5 50 eeeeeeeeeee 2008-9-10 17:41:38
6 60 fffffffffff 2008-9-10 17:41:38
7 70 ggggggggggg 2008-9-10 17:41:38
7 rows selected
在A实例中修改数据：
SQL> update t8 set a=100 where i=1;
1 row updated
SQL> commit;
Commit complete
再从B实例中查询数据：
SQL> execute dbms_job.run(26);
PL/SQL procedure successfully completed
SQL> select * from wxq_mv;
I A N MYDATE
----- ----- --------------- --------------------
1 100 aaaaaaaaaaa 2008-9-10 17:47:20
2 20 bbbbbbbbbbb 2008-9-10 17:47:20
3 30 ccccccccccc 2008-9-10 17:47:20
4 40 ddddddddddd 2008-9-10 17:47:20
5 50 eeeeeeeeeee 2008-9-10 17:47:20
6 60 fffffffffff 2008-9-10 17:47:20
7 70 ggggggggggg 2008-9-10 17:47:20
7 rows selected
从时间可以看到，是全部刷新的
5、创建log后检查数据：
在A实例中创建materialized view log
SQL> create materialized view log on t8 with rowid;
Materialized view log created
在B实例中建立的物化视图：
SQL> create materialized view wxq_mv2
2 tablespace wxq_tbs
3 build deferred --延迟刷新不立即刷新,immediate为立即刷新
4 refresh fast --使用增量刷新
5 on demand --按照指定方式刷新
6 start with to_date('10-09-2008 16:45:10', 'dd-mm-yyyy hh24:mi:ss')
7 next sysdate + 1
8 with rowid
9 as
10 select * from
t8@temp_link
t;
Materialized view created
注：这里不能加入sysdate字段了，因为远程快速刷新只能是简单的表复制
只有创建materialized view log 后才可以refresh fast
6、查看已经创建的materialized view：
SQL> select query from dba_mviews where mview_name='WXQ_MV2';
QUERY
--------------------------------------------------------------------------------
SELECT "T"."I" "I","T"."A" "A","T"."N" "N" FROM
"T8"@TEMP_LINK.SINATAY.COM
"T"
发现已经把*自动转化为每个列名，这跟view是一样的
所以，当远程数据库A增加列时，对本地B的mv不产生影响，B将忽略新增的列
但是当A改变原有的列名，或删除了B的mv引用的列名时，mv更新时就会报错
7、物化视图将新建一个表、一个job：
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='WXQ_MV2';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
WANGXIAOQI WXQ_MV2 WXQ_TBS
SQL> desc WXQ_MV2
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
I INTEGER
A NUMBER Y
N VARCHAR2(100) Y
SQL> select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;
JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT
----- --------------- ------------ ----------- ----------- --------- ------------ ----------------------------------------------
29 WANGXIAOQI 2008-9-17 09:48:10 2008-9-17 14:04:12 sysdate + 1 dbms_refresh.refresh('"WANGXIAOQI"."WXQ_MV2"');
每个mv都会对应一个实体表和一个job
8、查看是否会创建主键：
当原始表T8没有主键时，MV只能创建为with rowid模式
当然生成的mv table也是没有主键的
当原始表T8含有主键时，必须要重新生成log，然后再创建mv时必须使用with primary key
SQL> alter table t8 add constraint t8_key primary key(i);
Table altered
SQL> drop materialized view log on t8 ;
Materialized view log dropped
SQL> create materialized view log on t8 ;
Materialized view log created
SQL> select constraint_name,table_name,status from user_constraints where table_name='T8';
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
T8_KEY T8 ENABLED
然后在B创建MV：
SQL> create materialized view wxq_mv2
2 tablespace wxq_tbs
3 build deferred --延迟刷新不立即刷新,immediate为立即刷新
4 refresh fast --使用增量刷新
5 on demand --按照指定方式刷新
6 start with to_date('10-09-2008 16:45:10', 'dd-mm-yyyy hh24:mi:ss')
7 next sysdate + 1
8 with primary key --使用rowid时报错
9 as
10 select * from
t8@temp_link
t;
Materialized view created
SQL> select constraint_name,table_name,status from dba_constraints where table_name='WXQ_MV3';
CONSTRAINT_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
T8_KEY WXQ_MV3 ENABLED
生成的表中已经含有主键和索引。
9、关于使用on prebuilt table在已有表上建立MV的操作和应用：
先创建一个新表T2：
SQL> create table t2 (a int primary key,b varchar2(10));
Table created
创建对应的MV：
SQL> create materialized view wxq_mv as select * from t2;
Materialized view created
已经生成了一个table和一个MV：
SQL> select object_name,object_type from user_objects where object_name='WXQ_MV';
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------- ------------------
WXQ_MV TABLE
WXQ_MV MATERIALIZED VIEW
删除MV：
SQL> drop materialized view wxq_mv;
Materialized view dropped
发现table和MV都已经被删除：
SQL> select object_name,object_type from user_objects where object_name='WXQ_MV';
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------- ------------------
下面使用on prebuilt table来创建MV：
先建一个新表T3用来存放MV
SQL> create table t3 as select * from t2;
Table created
在T3上建立MV：
SQL> create materialized view T3 on prebuilt table as select * from t2;
Materialized view created
查看新建的MV，还是有table：
SQL> select object_name,object_type from user_objects where object_name='T3';
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------- ------------------
T3 TABLE
T3 MATERIALIZED VIEW
再删除MV：
SQL> drop materialized view t3;
Materialized view dropped
发现T3任然保留，其数据也保留到最后更新状态：
SQL> select object_name,object_type from user_objects where object_name='T3';
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------- ------------------
T3 TABLE
应用说明：可以先使用表基础上的MV来进行增量更新，当需要切换数据库数据时，断开源数据库，然后删除MV即可
缺点是MV针对的是对象，如果对象很多的话，建立起来比较麻烦。