使用 on prebuilt table 創(chuàng)建物化視圖(轉(zhuǎn))

lizisor

原文：http://space.itpub.net/index.php?action/viewspace/itemid/84370

 

 

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

創(chuàng)建物化視圖
SQL> create materialized view emp as select * from scott.emp;

Materialized view created.

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

OBJECT_NAME OBJECT_TYPE
------------------
EMP TABLE
EMP UNDEFINED

刪除物化視圖
SQL> drop materialized view emp;
Materialized view dropped.
以上2個(gè)對(duì)象都被刪除了，包括UNDEFINED的EMP
SQL> select object_name,object_type from user_objects where object_name='EMP';
No row selected。
先手工創(chuàng)建表
SQL> create table emp as select * from scott.emp;
Table created.
使用on prebuilt table注冊(cè)新的物化視圖，注意view名稱必須和表名稱一樣。
SQL> create materialized view emp on prebuilt table as select * from scott.emp;

Materialized view created.

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

OBJECT_NAME OBJECT_TYPE
------------------
EMP TABLE
EMP UNDEFINED

表emp已經(jīng)作為物化視圖了。
SQL> delete from emp;
delete from emp
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

刪除物化視圖后，原來的表未被刪除。 使用on prebuilt table創(chuàng)建的物化視圖被刪除后，原來的表不被刪除。
SQL> drop materialized view emp;
Materialized view dropped.

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

OBJECT_NAME OBJECT_TYPE
------------------
EMP TABLE

因此使用 on prebuilt table 創(chuàng)建物化視圖，更靈活，安全。
同樣可以使用on prebuilt table 創(chuàng)建快照，這樣減少了快照重新建立給數(shù)據(jù)增量同步帶來的時(shí)間成本