物化视图操刀案例

materialized view
select * from TB1 t for update;
select * from tb2;

查询物化视图日志：如果指定commit，则表被自动清空。
select * from mlog$_tb1;
select * from mlog$_TB2;

删除物化视图日志
drop materialized view log on tb2;
drop materialized view log on TB1;--删除物化视图日志

手动刷新物化视图 ，没有on commit需要手动
SQL> exec dbms_mview.refresh('MV_FAST_TB');
PL/SQL procedure successfully completed

创建物化视图日志：--rowid
create materialized view log on TB1 tablespace ice with rowid; --依据主键创建物化视图日志
create materialized view log on TB2 tablespace ice with rowid; --依据主键创建物化视图日志

创建物化视图：
create materialized view mv_fast_tb tablespace ice  refresh  fast   as 
 select distinct a.id,a.name,a.rowid a_rowid,b.rowid b_rowid
 from TB1 a,TB2 b
 where a.id = b.id(+)

创建物化视图索引：
create index ix_mv_id on mv_fast_tb(id);
select * from all_indexes a where a.INDEX_NAME='IX_MV_ID'


分组例子：分组需要指定sequence
create materialized view log on TB1 tablespace ice with  rowid ,sequence (id)
; --依据主键创建物化视图日志
create materialized view log on TB2 tablespace ice with  rowid, sequence (id)--依据主键创建物化视图日志

create materialized view mv_fast_tb tablespace ice  refresh force   as 
 select  a.id,count(*)
 from TB1 a,TB2 b
 where a.id = b.id(+)
 group by a.id
或：
create materialized view mv_fast_tb tablespace ice  refresh force on commit  as 
 select  a.id,count(*)
 from TB1 a,TB2 b
 where a.id = b.id(+)
 group by a.id---也可以是其他字段