Oracle物化视图之on prebuilt table

搜ijsio

　　创建物化视图添加on prebuilt table的好处是 ，在创建的物化视图的schema下要有与物化视图同名的表，如果更新物化视图，同名表也会被更新。当删除物化视图，不会删除同名的表，且保留从基表更新过来的数据。如果创建物化视图不用on prebuilt table，则不需要在创建的物化视图的schema下要有与物化视图同名的表。
　　在用on prebuilt table创建物化视图时，需要注意的几点：
　　1.创建的物化视图的schema下必须要有一个与物化视图同名的表。
　　2.与物化视图同名的表结构，必须与创建物化视图select query语句字段名相同，且一一对应。
　　1.在test1下创建基表：
　　SQL> conn test1/test1
　　Connected.
　　SQL> create table testdb1(col1 varchar2(20),col2 number);
　　Table created.

　　SQL>>
　　Table>　　SQL> insert into testdb1 values('1',1);
　　1 row created.
　　SQL> insert into testdb1 values('2',2);
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　2.在test1下创建物化视图日志。
　　SQL> create materialized view log on testdb1 with primary key;
　　Materialized view log created.
　　SQL> select * from tab;
　　TNAME       TABTYPECLUSTERID
　　------------------------------ ------- ----------
　　MLOG$_TESTDB1       TABLE
　　RUPD$_TESTDB1       TABLE
　　TESTDB1        TABLE
　　3.在SYS用户下授予test2相关的权限：
　　SQL> conn  / as sysdba
　　Connected.
　　SQL> grant select on test1.testdb1 to test2;
　　Grant succeeded.
　　SQL> grant select on test1.MLOG$_TESTDB1 to test2;
　　Grant succeeded.
　　SQL> grant create materialized view to  test2;
　　Grant succeeded.
　　4.在test2下创建物化视图
　　SQL> conn test2/test2
　　Connected.
　　--通过下面错误可以看出on prebuilt table创建物化视图必须要创建一张与物化视图同名的表
　　SQL> create materialized view testdb2 on prebuilt table as select * from test1.testdb1
　　*
　　ERROR at line 1:
　　ORA-12059: prebuilt table "TEST2"."TESTDB2" does not exist
　　SQL> create table testdb2(col1 varchar2(20));
　　Table created.
　　--通过下面错误可以看出创建的物化视图query数据项必须要与testdb2相同且一一对应
　　SQL> create materialized view testdb2 on prebuilt table as select * from test1.testdb1;
　　create materialized view testdb2 on prebuilt table as select * from test1.testdb1
　　*
　　ERROR at line 1:
　　ORA-12060: shape of prebuilt table does not match definition query
　　SQL> create materialized view testdb2 on prebuilt table as select col2 from test1.testdb1;
　　create materialized view testdb2 on prebuilt table as select col2 from test1.testdb1
　　*
　　ERROR at line 1:
　　ORA-12060: shape of prebuilt table does not match definition query
　　SQL> create materialized view testdb2 on prebuilt table as select col1 from test1.testdb1;
　　Materialized view created.
　　SQL> select count(*) from testdb2;
　　COUNT(*)
　　----------
　　0
　　testdb2表已经做物化视图了：
　　SQL> delete from testdb2;
　　delete from testdb2
　　*
　　ERROR at line 1:
　　ORA-01732: data manipulation operation not legal on this view
　　5.更新物化视图
　　SQL> exec dbms_mview.refresh('testdb2','c');
　　PL/SQL procedure successfully completed.
　　SQL> select count(*) from testdb2;
　　COUNT(*)
　　----------
　　2
　　SQL> conn test1/test1
　　Connected.
　　SQL>  insert into testdb1 values('3','3');
　　1 row created.
　　SQL> commit;
　　Commit complete.
　　SQL> conn test2/test2
　　Connected.
　　SQL> exec dbms_mview.refresh('testdb2','c');
　　PL/SQL procedure successfully completed.
　　SQL> select count(*) from testdb2;
　　COUNT(*)
　　----------
　　3
　　6.在test2下删除物化视图：
　　SQL> drop materialized view testdb2;
　　Materialized view dropped.
　　--可见同名的表还存在。
　　SQL> select count(*) from testdb2;
　　COUNT(*)
　　----------
　　3