物化视图可以查询表，视图和其它的物化视图。
通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。
对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。
对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。
本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。
在复制环境下，创建的物化视图通常情况下主键，rowid,和子查询视图。
1.主键物化视图：
下面的语法在远程数据库表emp上创建主键物化视图
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
Materialized view created.
注意：当用FAST选项创建物化视图，必须创建基于主表的视图日志,如下:
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
2.Rowid物化视图
下面的语法在远程数据库表emp上创建Rowid物化视图
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote_db;
Materialized view log created.
3.子查询物化视图
下面的语法在远程数据库表emp上创建基于emp和dept表的子查询物化视图
SQL> CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
(SELECT * FROM dept@remote_db d
WHERE e.dept_no = d.dept_no)
Materialized view log created.
REFRESH 子句
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
Refresh选项说明:
a. oracle用刷新方法在物化视图中刷新数据.
b. 是基于主键还是基于rowid的物化视图
c. 物化视图的刷新时间和间隔刷新时间
Refresh方法-FAST子句
增量刷新用物化视图日志（参照上面所述）来发送主表已经修改的数据行到物化视图中.如果指定REFRESH FAST子句，那么应该对主表创建物化视图日志
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
Refresh方法- COMPLETE子句
完全刷新重新生成整个视图，如果请求完全刷新，oracle会完成 完全刷新即使增量刷新可用。
Refresh Method – FORCE 子句
当指定FORCE子句，如果增量刷新可用Oracle将完成增量刷新，否则将完成完全刷新,如果不指定刷新方法(FAST, COMPLETE, or FORCE),Force选项是默认选项
主键和ROWD子句
WITH PRIMARY KEY选项生成主键物化视图,也就是说物化视图是基于主表的主键，而不是ROWID(对应于ROWID子句). PRIMARY KEY是默认选项,为了生成PRIMARY KEY子句，应该在主表上定义主键，否则应该用基于ROWID的物化视图.
主键物化视图允许识别物化视图主表而不影响物化视图增量刷新的可用性。
Rowid物化视图只有一个单一的主表，不能包括下面任何一项:
n Distinct 或者聚合函数.
n Group by，子查询，连接和SET操作
刷新时间
START WITH子句通知数据库完成从主表到本地表第一次复制的时间,应该及时估计下一次运行的时间点, NEXT 子句说明了刷新的间隔时间.
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 2
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
Materialized view created.
在上面的例子中，物化视图数据的第一个副本在创建时生成，以后每两天刷新一次.
总结
物化视图提供了可伸缩的基于主键或ROWID的视图,指定了刷新方法和自动刷新的时间
物化视图对于前台数据库使用者来说如同一个实际的表,具有和表相通的一般select操作,而其实际上是一个视图,一个定期刷新数据的视图(具体刷新时间在定义物化视图的时候已有定义),使用物化视图可以实现视图的所有功能,而物化视图确不是在使用时才读取,大大提高了读取速度,特别适用抽取大数据量表某些信息以及数据链连接表使用.具体语法如下:
CREATE MATERIALIZED VIEW an_user_base_file_no_charge
REFRESH COMPLETE START WITH SYSDATE
NEXT TRUNC(SYSDATE+29)+5.5/24
as
select distinct user_no
from cw_arrearage t
where (t.mon = dbms_tianjin.getLastMonth or
t.mon = add_months(dbms_tianjin.getLastMonth, -1))
drop materialized view an_user_base_file_no_charge;
比如一个很复杂的sql
select tt.CGFS CGFS,
tt.XQJHID XQJHID,
tt.XQJHBH XQJHBH,
tt.YHZBH YHZBH,
tt.TMBH TMBH,
tt.JHLX JHLX,
tt.JHSXBH JHSXBH,
tt.SXRQ SXRQ,
tt.JYHSFSBH JYHSFSBH,
tt.XMBH XMBH,
tt.XMMC XMMC,
tt.WZSXBH WZSXBH,
tt.GHQDBH GHQDBH,
tt.JHLBBH JHLBBH,
tt.ZZSXBH ZZSXBH,
tt.ZCZJLYBH ZCZJLYBH,
tt.SFCG SFCG,
tt.SFDL SFDL,
tt.CGDWBH CGDWBH,
tt.CGDWMC CGDWMC,
tt.SSDWBH SSDWBH,
tt.SSDWMC SSDWMC,
tt.DLRGWBH DLRGWBH,
tt.DLRGWMC DLRGWMC,
tt.DLRBH DLRBH,
tt.DLRMC DLRMC,
tt.DLRQ DLRQ,
tt.ZDGWBH ZDGWBH,
tt.ZDGWMC ZDGWMC,
tt.ZDRBH ZDRBH,
tt.ZDRMC ZDRMC,
tt.CGFAJHZBH CGFAJHZBH,
tt.CGFATBRQ CGFATBRQ,
tt.ZDRQ ZDRQ,
tt.BZ BZ,
tt.GSJE GSJE,
tt.JHXS JHXS,
tt.cgfagysbh cgfagysbh,
tt.cgfagysmc cgfagysmc,
tt.cgfajhbh cgfajhbh,
tt.cgfatbdwbh cgfatbdwbh,
tt.cgfatbdwmc cgfatbdwmc,
tt.mxzt mxzt,
tt.xqjhmc xqjhmc,
tt.SXKZZT SXKZZT,
tt.bjdbz bjdbz
from (select cc.cgfs, cc.mxzt, ab.*
from (select distinct c.xqjhid, a.cgfs, a.mxzt
from jh_b_xqjhmx a, jh_b_jhzdmxdy c
where 1 = 1
and a.xqjhmxbh = c.ysjhmxbh
and ((a.mxzt >= 28 and a.mxzt < 200) or a.mxzt in (12, 13))
and (a.flgw2 like '01010109%' escape '/')) cc
inner join (select e.XQJHID XQJHID,
e.sxkzzt sxkzzt,
e.XQJHBH XQJHBH,
e.xqjhmc xqjhmc,
e.YHZBH YHZBH,
e.TMBH TMBH,
e.JHLX JHLX,
e.JHSXBH JHSXBH,
e.SXRQ SXRQ,
e.JYHSFSBH JYHSFSBH,
e.XMBH XMBH,
e.XMMC XMMC,
e.WZSXBH WZSXBH,
e.GHQDBH GHQDBH,
e.JHLBBH JHLBBH,
e.ZZSXBH ZZSXBH,
e.ZCZJLYBH ZCZJLYBH,
e.SFCG SFCG,
e.SFDL SFDL,
e.CGDWBH CGDWBH,
e.CGDWMC CGDWMC,
e.SSDWBH SSDWBH,
e.SSDWMC SSDWMC,
e.DLRGWBH DLRGWBH,
e.DLRGWMC DLRGWMC,
e.DLRBH DLRBH,
e.DLRMC DLRMC,
e.DLRQ DLRQ,
e.CGFAJHZBH CGFAJHZBH,
e.CGFATBRQ CGFATBRQ,
e.ZDGWBH ZDGWBH,
e.ZDGWMC ZDGWMC,
e.ZDRBH ZDRBH,
e.ZDRMC ZDRMC,
e.ZDRQ ZDRQ,
e.BZ BZ,
e.GSJE GSJE,
e.JHXS JHXS,
e.cgfagysbh cgfagysbh,
e.cgfagysmc cgfagysmc,
e.cgfajhbh cgfajhbh,
e.cgfatbdwbh cgfatbdwbh,
e.cgfatbdwmc cgfatbdwmc,
ttt.bjdbz bjdbz
from jh_b_xqjh e,
(select distinct n.cgfabh, m.bjdbz
from cg_b_xbjd m, cg_b_xbjdmx n
where m.xjdbh = n.xjdbh) ttt
where jhlx != 1
and jhlx != 2
and e.xqjhbh = ttt.cgfabh(+)) ab on cc.xqjhid =
ab.xqjhid) tt
order by tt.mxzt, tt.CGFATBRQ desc
调整成
select tt.CGFS CGFS,
tt.XQJHID XQJHID,
tt.XQJHBH XQJHBH,
tt.YHZBH YHZBH,
tt.TMBH TMBH,
tt.JHLX JHLX,
tt.JHSXBH JHSXBH,
tt.SXRQ SXRQ,
tt.JYHSFSBH JYHSFSBH,
tt.XMBH XMBH,
tt.XMMC XMMC,
tt.WZSXBH WZSXBH,
tt.GHQDBH GHQDBH,
tt.JHLBBH JHLBBH,
tt.ZZSXBH ZZSXBH,
tt.ZCZJLYBH ZCZJLYBH,
tt.SFCG SFCG,
tt.SFDL SFDL,
tt.CGDWBH CGDWBH,
tt.CGDWMC CGDWMC,
tt.SSDWBH SSDWBH,
tt.SSDWMC SSDWMC,
tt.DLRGWBH DLRGWBH,
tt.DLRGWMC DLRGWMC,
tt.DLRBH DLRBH,
tt.DLRMC DLRMC,
tt.DLRQ DLRQ,
tt.ZDGWBH ZDGWBH,
tt.ZDGWMC ZDGWMC,
tt.ZDRBH ZDRBH,
tt.ZDRMC ZDRMC,
tt.CGFAJHZBH CGFAJHZBH,
tt.CGFATBRQ CGFATBRQ,
tt.ZDRQ ZDRQ,
tt.BZ BZ,
tt.GSJE GSJE,
tt.JHXS JHXS,
tt.cgfagysbh cgfagysbh,
tt.cgfagysmc cgfagysmc,
tt.cgfajhbh cgfajhbh,
tt.cgfatbdwbh cgfatbdwbh,
tt.cgfatbdwmc cgfatbdwmc,
tt.mxzt mxzt,
tt.xqjhmc xqjhmc,
tt.SXKZZT SXKZZT,
tt.bjdbz bjdbz
from mv_test tt
order by tt.mxzt, tt.CGFATBRQ desc
就可以了。]
mv_test tt
create materialized view MV_TEST
refresh force on demand
as
select cc.cgfs, cc.mxzt, ab.*
from (select distinct c.xqjhid, a.cgfs, a.mxzt
from wz_wlpt.jh_b_xqjhmx a, wz_wlpt.jh_b_jhzdmxdy c
where 1 = 1
and a.xqjhmxbh = c.ysjhmxbh
and ((a.mxzt >= 28 and a.mxzt < 200) or a.mxzt in (12, 13))
and (a.flgw2 like '01010109%' escape '/')) cc
inner join (select e.XQJHID XQJHID,
e.sxkzzt sxkzzt,
e.XQJHBH XQJHBH,
e.xqjhmc xqjhmc,
e.YHZBH YHZBH,
e.TMBH TMBH,
e.JHLX JHLX,
e.JHSXBH JHSXBH,
e.SXRQ SXRQ,
e.JYHSFSBH JYHSFSBH,
e.XMBH XMBH,
e.XMMC XMMC,
e.WZSXBH WZSXBH,
e.GHQDBH GHQDBH,
e.JHLBBH JHLBBH,
e.ZZSXBH ZZSXBH,
e.ZCZJLYBH ZCZJLYBH,
e.SFCG SFCG,
e.SFDL SFDL,
e.CGDWBH CGDWBH,
e.CGDWMC CGDWMC,
e.SSDWBH SSDWBH,
e.SSDWMC SSDWMC,
e.DLRGWBH DLRGWBH,
e.DLRGWMC DLRGWMC,
e.DLRBH DLRBH,
e.DLRMC DLRMC,
e.DLRQ DLRQ,
e.CGFAJHZBH CGFAJHZBH,
e.CGFATBRQ CGFATBRQ,
e.ZDGWBH ZDGWBH,
e.ZDGWMC ZDGWMC,
e.ZDRBH ZDRBH,
e.ZDRMC ZDRMC,
e.ZDRQ ZDRQ,
e.BZ BZ,
e.GSJE GSJE,
e.JHXS JHXS,
e.cgfagysbh cgfagysbh,
e.cgfagysmc cgfagysmc,
e.cgfajhbh cgfajhbh,
e.cgfatbdwbh cgfatbdwbh,
e.cgfatbdwmc cgfatbdwmc,
ttt.bjdbz bjdbz
from wz_wlpt.jh_b_xqjh e,
(select distinct n.cgfabh, m.bjdbz
from wz_wlpt.cg_b_xbjd m, wz_wlpt.cg_b_xbjdmx n
where m.xjdbh = n.xjdbh) ttt
where jhlx != 1
and jhlx != 2
and e.xqjhbh = ttt.cgfabh(+)) ab on cc.xqjhid =ab.xqjhid
这样原来的sql执行 要用1.433秒多，而使用物化视图执行才0.2秒……
但也不能多用，因为他需要实时来刷新视图才能保持和基表的一致……
扫一扫
2053
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
