使用 on prebuilt table 创建物化视图(转)
<P>原文：<a href="http://space.itpub.net/index.php?action/viewspace/itemid/84370" target="_blank">http://space.itpub.net/index.php?action/viewspace/itemid/84370</A></P><P> </P>
<P> </P>
<P>Connected to:<BR>Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production<BR>With the Partitioning option<BR>JServer Release 8.1.7.0.0 - Production</P>
<P>创建物化视图<BR>SQL> create materialized view emp as select * from scott.emp;</P>
<P>Materialized view created.</P>
<P>SQL> select object_name,object_type from user_objects where object_name='EMP';</P>
<P>OBJECT_NAME OBJECT_TYPE<BR>------------------<BR>EMP TABLE<BR>EMP UNDEFINED</P>
<P>删除物化视图<BR>SQL> drop materialized view emp;<BR>Materialized view dropped.<BR>以上2个对象都被删除了，包括UNDEFINED的EMP<BR>SQL> select object_name,object_type from user_objects where object_name='EMP';<BR>No row selected。<BR>先手工创建表<BR>SQL> create table emp as select * from scott.emp;<BR>Table created.<BR>使用on prebuilt table注册新的物化视图，注意view名称必须和表名称一样。<BR>SQL> create materialized view emp on prebuilt table as select * from scott.emp;</P>
<P>Materialized view created.</P>
<P>SQL> select object_name,object_type from user_objects where object_name='EMP';</P>
<P>OBJECT_NAME OBJECT_TYPE<BR>------------------<BR>EMP TABLE<BR>EMP UNDEFINED</P>
<P>表emp已经作为物化视图了。<BR>SQL> delete from emp;<BR>delete from emp<BR>*<BR>ERROR at line 1:<BR>ORA-01732: data manipulation operation not legal on this view</P>
<P>删除物化视图后，原来的表未被删除。 使用on prebuilt table创建的物化视图被删除后，原来的表不被删除。<BR>SQL> drop materialized view emp;<BR>Materialized view dropped.</P>
<P>SQL> select object_name,object_type from user_objects where object_name='EMP';</P>
<P>OBJECT_NAME OBJECT_TYPE<BR>------------------<BR>EMP TABLE</P>
<P><BR>因此使用 on prebuilt table 创建物化视图，更灵活，安全。<BR>同样可以使用on prebuilt table 创建快照，这样减少了快照重新建立给数据增量同步带来的时间成本</P>
<P> </P>
页:
[1]