物化檢視學習--Datawarehouse中的這東東很多，很多之前job都是更新他的

有兩篇網上的文章結合著看比較好

提供了籠統的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