ITPub博客

首页 > IT基础架构 > 网络安全 > mview on prebuilt table用法尝试

mview on prebuilt table用法尝试

原创 网络安全 作者:watershed 时间:2005-12-14 11:16:30 0 删除 编辑

物化视图的on prebuilt table特性可以用于减少跨平台不同host之间数据迁移的业务中断时间,对于7*24小时的应用系统是很实用的,以下是在同一台host上的试验,主要是掌握原理.对于不同host的实际情况,无非是多建立一个dblink的问题.

[@more@]

SQL> create table a (col1 varchar2(20),col2 number);

Table created.

SQL> create table b (col1 varchar2(20),col2 number);

Table created.


SQL> alter table a add constraint pk_a primary key(col1);

Table altered.


SQL> insert into a values('1',1);

1 row created.

SQL> insert into a values('2',2);

1 row created.

SQL> commit;

Commit complete.


SQL> create materialized view log on a with primary key;

Materialized view log created.


SQL> create materialized view b on prebuilt table
2 as select * from a;

Materialized view created.

SQL> select object_name,object_type from user_objects
2 where object_name='B';

OBJECT_NAME

OBJECT_TYPE


----------------------------------------------
B TABLE


B

MATERIALIZED VIEW

2 rows selected.

SQL> select count(*) from b;

COUNT(*)


----------


0

1 row selected.

SQL> exec dbms_mview.refresh('b','c');

PL/SQL procedure successfully completed.

SQL> select * from b;

COL1 COL2


-------------------- ----------


1 1


2 2

2 rows selected.

SQL> insert into a values('3','3');

1 row created.

SQL> commit;

Commit complete.


SQL> select * from a;

COL1 COL2


-------------------- ----------


1 1


2 2


3 3

3 rows selected.

SQL> insert into a values('4',4);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('b','f');

PL/SQL procedure successfully completed.

SQL> select * from b;

COL1 COL2


-------------------- ----------


1 1


2 2


3 3


4 4

4 rows selected.

SQL> select object_name,object_type from user_objects
2 where object_name='B';

OBJECT_NAME OBJECT_TYPE


------------------------------------------------------------
B TABLE


B MATERIALIZED VIEW

2 rows selected.

SQL> drop materialized view b;

Materialized view dropped.

SQL> select object_name,object_type from user_objects
2 where object_name='B';

OBJECT_NAME OBJECT_TYPE


------------------------------------------------------
B TABLE

1 row selected.

SQL> select * from b;

COL1 COL2


-------------------- ----------


1 1


2 2


3 3


4 4

4 rows selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/75730/viewspace-811685/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 我家美图......
请登录后发表评论 登录
全部评论

注册时间:2011-07-25

  • 博文量
    35
  • 访问量
    1389756