mview on prebuilt table用法嘗試

物化檢視的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.