ITPub博客

首页 > 数据库 > Oracle > 物化视图学习--Datawarehouse中的这东东很多,很多之前job都是更新他的

物化视图学习--Datawarehouse中的这东东很多,很多之前job都是更新他的

原创 Oracle 作者: 时间:2007-11-28 14:51:01 0 删除 编辑

有两篇网上的文章结合着看比较好

http://www.chinaitpower.com/A/2002-05-02/21578.html

提供了笼统的mv的概貌介绍

http://yangtingkun.itpub.net/post/468/11356

提供了定义物化视图的子句的详细介绍

http://dev.csdn.net/article/45/45638.shtm

有些权限说明及注意问题

自己也做了个简单测试:见内

[@more@]

需要主键

cycker> create materialized view mv_robincui refresh fast on commit with primary key as select * from robincui;
create materialized view mv_robincui refresh fast on commit with primary key as select * from robincui
*
ERROR at line 1:
ORA-12014: table 'ROBINCUI' does not contain a primary key constraint

cycker> alter table robincui add primary key PK_TEST (TEST);
alter table robincui add primary key PK_TEST (TEST)
*
ERROR at line 1:
ORA-00906: missing left parenthesis


cycker> alter table robincui add primary key (TEST);

Table altered.

需要MV LOG对基表

cycker> create materialized view mv_robincui refresh fast on commit with primary key as select * from robincui;
create materialized view mv_robincui refresh fast on commit with primary key as select * from robincui
*
ERROR at line 1:
ORA-23413: table "ROBIN"."ROBINCUI" does not have a materialized view log


cycker> CREATE MATERIALIZED VIEW LOG ON robincui;

Materialized view log created.

cycker> create materialized view mv_robincui refresh fast on commit with primary key as select * from robincui;

Materialized view created.


cycker> select * from mv_robincui;

TEST
--------------------
test1
test2

cycker> select * from robincui;

TEST
--------------------
test1
test2

cycker> insert into robincui values('test3');

1 row created.

cycker> select * from mv_robincui;

TEST
--------------------
test1
test2

cycker> commit;

Commit complete.

cycker> select * from mv_robincui;

TEST
--------------------
test1
test2
test3

增加字段,MV没有更新

cycker> alter table robincui add (TEST_COL2 varchar(10));

Table altered.

cycker> select * from mv_robincui;

TEST
--------------------
test1
test2
test3

cycker> select * from robincui;

TEST TEST_COL2
-------------------- ----------
test1
test2
test3

cycker> update robincui set test_col2 = 'TEST';

3 rows updated.

cycker> commit;

Commit complete.

cycker> select * from mv_robincui;

TEST
--------------------
test1
test2
test3

cycker> insert into robincui values('test4','TEST1');

1 row created.

cycker> commit;

Commit complete.

cycker> select * from mv_robincui;

TEST
--------------------
test1
test2
test3
test4

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

请登录后发表评论 登录
全部评论
  • 博文量
    89
  • 访问量
    370976