oracle表和视图能重名吗,物化视图与表同名的作用及用法 on prebuilt table

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_NAMEOBJECT_TYPE

------------------

EMPTABLE

EMPUNDEFINED

删除物化视图

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_NAMEOBJECT_TYPE

------------------

EMPTABLE

EMPUNDEFINED

表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_NAMEOBJECT_TYPE

------------------

EMPTABLE

因此使用on prebuilt table创建物化视图，更灵活，安全。

同样可以使用on prebuilt table创建快照，这样减少了快照重新建立给数据增量同步带来的时间成本。