建物化视图,完成query rewrite,refresh功能
1、创建物化视图 TEST8@ odb>create materialized view v_wuhua as select * from gaoshuiwei; create materialized view v_001 asselect * from gaoshuiwei * ERROR at line 1: ORA-01031: insufficient privileges sys@ odb>grant create materialized view to test8;
TEST8@ odb>create materializedview v_wuhua as select * from gaoshuiwei; Materialized view created.
TEST8@ odb>create view v_shituas select * from gaoshuiwei; create view v_shitu as select *from gaoshuiwei * ERROR at line 1: ORA-01031: insufficient privileges sys@ odb>grant create view to test8; TEST8@ odb>create view v_shituas select * from gaoshuiwei; View created.
清空v_wuhua数据提示数据操作不合法,说明物化视图只能读不能删除数据, 1、比较物化视图和视图的区别 TEST8@odb>delete from gaoshuiwei; //清空基表 68310rows deleted. 查询物化视图和普通视图的区别 TEST8@odb>select count(*) from v_wuhua; COUNT(*) ---------- 68310
TEST8@odb>select count(*) from v_shitu; COUNT(*) ---------- 0
可以看到随着基表的被清空,物化视图是没有变化的,而普通视图随之清空;重新插入数据效果还是一样物化视图不变,普通视图随着基表的变化而变化
TEST8@odb>insert into gaoshuiwei select * from all_objects; 68314rows created. TEST8@odb>commit; Commitcomplete. TEST8@odb>select count(*) from gaoshuiwei; COUNT(*) ---------- 68314 TEST8@odb>select count(*) from v_wuhua;
COUNT(*) ---------- 68310
TEST8@odb>select count(*) from v_shitu;
COUNT(*) ---------- 68314 TEST8@odb>exec dbms_mview.refresh('V_WUHUA'); //刷新物化视图数据这时和基表数据一致了。 物化视图不仅可以全量的刷新数据还可以增量的刷新数据; TEST8@odb>create materialized view v_wuhua1 as select * from gaoshuiwei whererownum<1;
Materializedview created. TEST8@odb>select count(*) from v_wuhua1;
COUNT(*) ---------- 0 selectdbms_metadata.get_ddl('MATERIALIZED_VIEW','V_WUHUA1','TEST8') FROM DUAL; 3、查询重写
TEST8@odb>create materialized view v_wuhua3 enable query rewrite as select * fromgaoshuiwei; Materializedview created. 看一下基表的查询执行计划,执行计划上看表面查询的是基表实际上查询的是物化视图; 4、删除物化视图 DROPMATERIALIZED VIEW V_TESTMV; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |