物化视图与表同名的作用及用法 on prebuilt table

Materialized view和snapshot是同义词，都可以使用on prebuilt table。它的好处是 ，当删除Materialized view/snapshot的时候，基表不会被删除，数据仍然有效；为视图/快照的重组带来管理上的方便。

 create materialized view emp on prebuilt table as select * from scott.emp;

Materialized view created.

[@more@]

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 创建快照，这样减少了快照重新建立给数据增量同步带来的时间成本。