oracle物化视图【转】
来源:互联网 发布:知行汽车江苏重大项目 编辑:程序博客网 时间:2024/05/02 01:06
一、----------------------------------------------------------------------------------
物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。
本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。
1.主键物化视图:
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.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
2.Rowid物化视图
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote_db;
Materialized view log created.
3.子查询物化视图
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 [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
a.
b.
c.
Refresh方法-FAST子句
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
Refresh方法-
Refresh Method – FORCE 子句
主键和ROWD子句
n
n
刷新时间
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的视图,指定了刷新方法和自动刷新的时间。
二、----------------------------------------------------------------------------------
Oracle的物化视图提供了强大的功能,可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能;物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性;物化视图需要占用存储空间;当基表发生变化时,物化视图也应当刷新。
物化视图可以分为以下三种类型:包含聚集的物化视图;只包含连接的物化视图;嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别,而对于其他方面则区别不大。创建物化视图时可以指定多种选项,下面对几种主要的选择进行简单说明:
创建方式(Build Methods):包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILDIMMEDIATE是在创建物化视图的时候就生成数据,而BUILDDEFERRED则在创建时不生成数据,以后根据需要在生成数据。默认为BUILD IMMEDIATE。
查询重写(Query Rewrite):包括ENABLE QUERY REWRITE和DISABLE QUERYREWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为DISABLEQUERY REWRITE。
刷新(Refresh):指当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种:ON DEMAND和ONCOMMIT。ONDEMAND指物化视图在用户需要的时候进行刷新,可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新,也可以通过JOB定时进行刷新。ONCOMMIT指出物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种:FAST、COMPLETE、FORCE和NEVE*。**ST刷新采用增量刷新,只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式,则Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCEON DEMAND。
在建立物化视图的时候可以指定ORDERBY语句,使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中,而且对以后的刷新也无效。
物化视图日志:如果需要进行快速刷新,则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要,可以建立为ROWID或PRIMARYKEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
可以指明ON PREBUILDTABLE语句将物化视图建立在一个已经存在的表上。这种情况下,物化视图和表必须同名。当删除物化视图时,不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。
物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪(PCT)。具有这种特性的物化视图,当基表进行了分区维护操作后,仍然可以进行快速刷新操作。对于聚集物化视图,可以在GROUPBY列表中使用CUBE或ROLLUP,来建立不同等级的聚集物化视图。
物化视图的基本操作和使用可以查看网址:http://blog.itpub.net/post/468/13318相关的东东。我主要说明一下使用物化视图的基本东东。如如何建立在特定的表空间上,这些在其他的物化视图上面几乎都没有任何介绍的。主要以我做的一个例子来操作,
如果对物化视图的基本概念清楚了就比较明白在那里写特定的表空间存储了。
创建物化视图时应先创建存储的日志空间
CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID,sequence(LEVYDETAILDATAID);
CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH rowid,sequence(LEVYDATAID);
然后创建物化视图
--创建物化视图
create materialized view MV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA --保存表空间
BUILD DEFERRED --延迟刷新不立即刷新
refresh force --如果可以快速刷新则进行快速刷新,否则完全刷新
on demand --按照指定方式刷新
start with to_date('24-11-2005 18:00:10', 'dd-mm-yyyy hh24:mi:ss')--第一次刷新时间
next TRUNC(SYSDATE+1)+18/24 --刷新时间间隔
as
SELECT levydetaildataid, detaildatano, taxtermbegin,taxtermend,
......
ROUND(taxdeduct * taxpercent1, 2) - ROUND(taxdeduct * taxpercent2,2) -
ROUND(taxdeduct * taxpercent3, 2) - ROUND(taxdeduct * taxpercent4,2) -
ROUND(taxdeduct * taxpercent5, 2) taxdeduct, ROUND(taxfinal *taxpercent1, 2) -
ROUND(taxfinal * taxpercent2, 2) - ROUND(taxfinal * taxpercent3, 2)-
ROUND(taxfinal * taxpercent4, 2) - ROUND(taxfinal * taxpercent5, 2)taxfinal,
a.levydataid, a.budgetitemcode, taxtypecode,
......
FROM tb_lvy_levydetaildata a, tb_lvy_levydata c,MV_LVY_LEVYTAXBGTDIV b
WHERE a.levydataid = c.levydataid
AND a.budgetdistrscalecode = b.budgetdistrscalecode
AND a.budgetitemcode = b.budgetitemcode
AND c.incomeresidecode = b.rcvfisccode
AND C.TAXSTATUSCODE='08'
AND C.NEGATIVEFLAG!='9'
删除物化视图日志
--删除物化视图:
--删除日志: DROP materialized view log on mv_lvy_levytaxbgtdiv;
DROP materialized view log on tb_lvy_levydetaildata;
DROP materialized view log on tb_lvy_levydata;
--删除物化视图 drop materialized view MV_LVY_LEVYDETAILDATA;
--基本和对表的操作一致 --物化视图由于是物理真实存在的,故可以创建索引。
创建方式和对普通表创建方式相同,就不在重复写了。
三、----------------------------------------------------------------------------------
物化视图对于前台数据库使用者来说如同一个实际的表,具有和一般表相通的如select等操作,而其实际上是一个视图,一个由系统实现定期刷新其数据的视图(具体刷新时间在定义物化视图的时候已有定义),使用物化视图更可以实现视图的所有功能,而物化视图确不是在使用时才读取,大大提高了读取速度,特别适用抽取大数据量表某些信息以及数据链连接表使用.具体语法如下:
物化视图对于前台数据库使用者来说如同一个实际的表,具有和表相通的一般select操作,而其实际上是一个视图,一个定期刷新数据的视图(具体刷新时间在定义物化视图的时候已有定义),使用物化视图可以实现视图的所有功能,而物化视图确不是在使用时才读取,大大提高了读取速度,特别适用抽取大数据量表某些信息以及数据链连接表使用.具体语法如下:
CREATE MATERIALIZED VIEW an_user_base_file_no_charge
select distinct user_no
from cw_arrearage t
where (t.mon = dbms_tianjin.getLastMonth or
drop materialized view an_user_base_file_no_charge;
第 13 章 物化视图
8.1.5企业版/个人版开始支持
需要权限:GRANT CREATE MATERIALIZED VIEW,还必须直接赋予GRANT QUERYREWRITE.为实现查询重写,必须使用CBO.
13.1 物化视图如何工作
设置
COMPATIBLE参数必须高于8.1.0
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRETY =
ENFORCED - 查询仅用Oracle强制与保证的约束、规则重写;
TRUSTED – 查询除用Oracle强制与保证的约束、规则,也可用用户设定的数据间的任何关系来重写;
STALE_TOLERATED – 即便Oracle知道物化视图中数据过期(与事实表等不同步),也重写查询。
创建物化视图的用户必须具有直接赋予的GRANT QUERY REWRITE权限,不能通过角色继承。
内部机制
全文匹配
部分匹配:从FROM子句开始,优化器比较之后的文本,然后比较SELECT列表
一般重写方法:
数据充分
关联兼容
分组兼容
聚集兼容
13.2 确保使用物化视图
约束
考虑到现实环境的数据量,可以将主键、外键、非空等约束置为NOVALIDATE,并调整QUERY_REWRITE_INTEGRITY为TRUSTED,这样可以达到“欺骗”数据库的目的,但必须注意如果无法保证此类约束的真实有效,查询改写后可能造成结果不精确。
维度
实际就是指明已存在的表中各列的归并关系,从而关联事实表后形成的物化视图可用于向“上”归并(相当于用表中代表更高归并关系的列关联事实表)。标准语法:
CREATE DIMENSION time_hierarchy_dim
LEVELday
LEVEL mmyyyy
LEVELyyyy
HIERARCHY time_rollup
(day CHILD OF mmyyyy CHILD OF yyyy)
ATTRIBUTE mmyyyy
DETERMINES mon_yyyy;
13.3 DBMS_OLAP
估计(物化视图)大小
DBMS_OLAP.ESTIMATE_SUMMARY_SIZE(视图名, 视图定义, 估计行数, 估计字节数);
其中后两个参数为NUMBER型输出参数。
维度有效性检查
DBMS_OLAP.VALIDATE_DIMENSION(视图名, 用户名, FALSE, FALSE);
SELECT * FROM 维度表名
WHERE ROWIN IN (SEELCT bad_rowid FROM MVIEW$_EXCEPTION);
所选出行即为不符合维度定义的行。
推荐物化视图
首先必须添加合适的外键,包通过外键来判定表之间的关系而不是维度。
DBMS_OLAP.RECOMMEND_MV(事实表名, 1000000000, ‘’);
第二个参数表示物化视图可用的空间大小,可传入一个较大的数。第三个参数传入需要保留的特定物化视图,传入空即为不考虑其他物化视图。
执行C:/oracle/RDBMS/demo/sadvdemo后执行:
DEMO_SUMADV.PRETTYPRINT_RECOMMENDATIONS
13.4 最后说明
物化视图不为OLTP系统设计
在事实表等更新时会导致物化视图行锁,从而影响系统并发性。
四、---------------------------------------------------------------------------------
但是快速刷新具有较多的约束,而且对于采用ONCOMMIT模式进行快速刷新的物化视图更是如此。对于包含聚集和包含连接的物化视图的快速刷新机制并不相同,而且对于多层嵌套的物化视图的快速刷新更是有额外的要求。
如此多的限制一般很难记全,当建立物化视图失败时,Oracle给出的错误信息又过于简单,有时无法使你准确定位到问题的原因。
Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW过程可以帮助你快速定位问题的原因。下面通过一个例子来说明,如果通过这个过程来解决问题。
建立一个快速刷新的嵌套物化视图:
SQL> CREATE TABLE B (ID NUMBER PRIMARY KEY, NAMEVARCHAR2(30));
表已创建。
SQL> CREATE TABLE C (ID NUMBER PRIMARY KEY, NAMEVARCHAR2(30));
表已创建。
SQL> CREATE TABLE A (ID NUMBER, BID NUMBER, CIDNUMBER, NUM NUMBER,
表已创建。
SQL> INSERT INTO B SELECT ROWNUM, 'B'||ROWNUM FROMUSER_TABLES WHERE ROWNUM <= 6;
已创建6行。
SQL> INSERT INTO C SELECT ROWNUM, 'C'||ROWNUM FROMUSER_TABLES WHERE ROWNUM <= 4;
已创建4行。
SQL> INSERT INTO A SELECT ROWNUM, TRUNC((ROWNUM -1)/2) + 1, TRUNC((ROWNUM - 1)/3) + 1,ROWNUM
已创建12行。
SQL> COMMIT;
提交完成。
上面建立好基表,下面建立第一层物化视图。
SQL> CREATE MATERIALIZED VIEW LOG ON A WITHROWID;
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW LOG ON B WITHROWID;
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW LOG ON C WITHROWID;
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST ONCOMMIT ENABLE QUERY REWRITE AS
实体化视图已创建。
第一次物化视图已经建立成功,下面建立嵌套物化视图:
SQL> CREATE MATERIALIZED VIEW LOG ON MV_ABC WITHROWID (BNAME, CNAME, NUM) INCLUDING NEW VALUES;
实体化视图日志已创建。
SQL> CREATE MATERIALIZED VIEW MV_MV_ABC REFRESH FASTON COMMIT ENABLE QUERY REWRITE AS
SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROMMV_ABC
ERROR 位于第 2 行:
ORA-12053: 这不是一个有效的嵌套实体化视图
错误出现了,不过错误的描述包含的信息量并不大。我们看看Oracle的文档上是如何描述这个错误的。
ORA-12053 this is not a valid nested materialized view
Cause: The list of objects in the FROM clause of the definition ofthis materialized view had some dependencies upon each other.
Action: Refer to the documentation to see which types of nestingare valid.
文档上的描述也是十分笼统的,并没有指出具体问题所在。
接下来,我们通过使用DBMS_MVIEW.EXPLAIN_MVIEW过程来定位错误。
使用EXPLAIN_MVIEW过程首先要建立MV_CAPABILITIES_TABLE表,建表的脚步是$ORACLE_HOME/rdbms/admin/utlxmv.sql。(EXPLAIN_MVIEW过程是两个过程的重载,一个输出到MV_CAPABILITIES_TABLE表,另一个以PL/SQL的VARRAY格式输出,为了简单起见,我们建立MV_CAPABILITIES_TABLE表)。
SQL> @?rdbmsadminutlxmv.sql
表已创建。
下面简单研究一下EXPLAIN_MVIEW过程。
DBMS_MVIEW.EXPLAIN_MVIEW(mv IN VARCHAR2, Statement_id IN VARCHAR2:=NULL);
该过程可以输入已经存在的物化视图名称(或USER_NAME.MV_NAME),也可输入建立物化视图的查询语句。另外一个参数STATEMENT_ID输入一个语句ID,为了标识出表中对应的记录。
SQL> BEGIN
PL/SQL 过程已成功完成。
SQL> SELECT CAPABILITY_NAME, RELATED_TEXT, MSGTXTFROM MV_CAPABILITIES_TABLE
CAPABILITY_NAME
------------------------------ -----------------------------------------------------------------
REFRESH_FAST_AFTER_ONETAB_DML
REFRESH_FAST_AFTER_ANY_DML
REFRESH_FAST_AFTER_ANY_DML
根据上面的信息,已经可以确定问题的原因了,对于聚集物化视图,使用了SUM(COLUMN),但是没有包括COUNT(COLUMN)。
修改物化视图,重新建立:
SQL> CREATE MATERIALIZED VIEW MV_MV_ABC REFRESH FASTON COMMIT ENABLE QUERY REWRITE AS
实体化视图已创建。
- Oracle物化视图 (转)
- oracle 物化视图(转)
- oracle物化视图【转】
- oracle 物化视图语法分析【转】
- oracle 物化视图语法分析【转】
- ORACLE物化视图-物化视图日志结构
- (转)oracle 物化视图 总结
- Oracle物化视图创建全过程(转)
- Oracle物化视图创建全过程(转)
- Oracle物化视图创建全过程(转)
- oracle的物化视图
- oracle的物化视图
- ORACLE中的物化视图
- Oracle的物化视图
- Oracle 物化视图
- Oracle 物化视图
- Oracle的物化视图
- ORACLE中的物化视图
- 动态SQL详解
- CSDN上的一位oracle大神
- 【女生皮肤必修课】如何美白,如何治疗脸色暗沉,如何对付眼肿,如何自制面膜。。。。。内容很全,留着慢慢看吧!!
- chr(13)和chr(10)
- 三星Note3水货/行货各版本区别 N900/N9002/N9005/N9006/N9008/N9009有什么不同
- oracle物化视图【转】
- 我爱的人
- 【原创】如何删除明细库中多余的数…
- 判断iOS的设备型号,比较全,几乎涵盖了现在能有的所有型号.
- Bootstrap学习笔记
- 【原创】如何找出明细库中与生产库…
- 实现接口 @override报错
- exists查询详解【转】
- 对C++沉思录中代理类实现的质疑和修改