使用 on prebuilt table 建立物化檢視 (ZT)

tolywang發表於2006-04-26
http://www.itpub.net/showthread.php?threadid=309939&pagenumber=


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

建立物化檢視
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個物件都被刪除了,包括UNDEFINED的EMP
SQL> select object_name,object_type from user_objects where object_name='EMP';
No row selected。
先手工建立表
SQL> create table emp as select * from scott.emp;
Table created.
使用on prebuilt table註冊新的物化檢視,注意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已經作為物化檢視了。
SQL> delete from emp;
delete from emp
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

刪除物化檢視後,原來的表未被刪除。 使用on prebuilt table建立的物化檢視被刪除後,原來的表不被刪除。
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 建立物化檢視,更靈活,安全。
同樣可以使用on prebuilt table 建立快照,這樣減少了快照重新建立給資料增量同步帶來的時間成本。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-84370/,如需轉載,請註明出處,否則將追究法律責任。

相關文章