Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
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
select t.*,sysdate mydate from t8@temp_link t;

注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针对的是对象,如果对象很多的话,建立起来比较麻烦。
 
 
posted on 2008-09-04 21:22 decode360 阅读(601) 评论(0)  编辑  收藏 所属分类: 07.Oracle

只有注册用户登录后才能发表评论。


网站导航: