物化视图

1.创建物化视图

create materialized view mv as select * from t;

 

2.手动更新物化视图

 exec dbms_mview.refesh('MV','C');

 

3.Fast更新

  1.创建materialized view log

   create materialized view log on t;

 

  2.创建物化视图

    create materialized view mv refresh fast as select * from t;

 

  3.查看是否更改

   select count(*) from mlog$_t;

 

 4.更新基表

   update t set name = 'aa' where id = 1;

 

 5.再次查看是否有更改

   select count(*) from mlog$_t;

 

6.手动更新物化视图

 exec dbms_mview.refesh('MV','F');

 

7.验证是否更新

 select * from mv;

 

4.query rewrite

 

1.alter session set query_rewrite_enabled = true;

 

2.alter session set query_rewrite_integrity=enforced;

 

3.创建物化视图

 create materialized view my_all_objects_eggs

build immediate ---- 立即把数据拷入视图

refresh commit

enable query rewrite

as

select owner,count(*) from my_all_objects group by owner;

 

5.相关视图

 v$sql_plan