- 浏览: 439555 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
q12344566789:
...
如何查看表的并行度并设置表的并行度 -
chruan:
刚好遇到这个问题,谢谢了。
使用Spring的CharacterEncodingFilter应注意的问题 -
nwpucyp:
还需要修改shutdown.bat里的CATALINA_HOM ...
一台机器上同时部署多个tomcat服务 -
ronghua_liu:
dom4j比较奇葩,其他开源项目没看到这样的
dom4j下载地址 -
mc90716:
引用引用引用引用
数据库查询时对多个字段group by 有什么作用
一、物化视图的一般用法物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并没有实质上的好处。
1、物化视图的类型ON DEMAND、ON COMMIT。二者的区别在于刷新方法的不同,ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;而ON COMMIT是说,一旦基表有了COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。
物化视图可以分为以下三种类型:包含聚集的物化视图;只包含连接的物化视图;嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别,而对于其他方面则区别不大。创建物化视图时可以指定多种选项,下面对几种主要的选择进行简单说明:
创建方式(Build Methods):包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据,而BUILD DEFERRED则在创建时不生成数据,以后根据需要在生成数据。默认为BUILD IMMEDIATE。
查询重写(Query Rewrite):包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。
在建立物化视图的时候可以指定ORDER BY语句,使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中,而且对以后的刷新也无效。
2、ON DEMAND物化视图物化视图的创建本身是很复杂和需要优化参数设置的,特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的,类似于普通视图的方式来做,所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。物化视图的特点:
(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表),这通过其可以被user_tables查询出来,而得到佐证;
(2) 物化视图也是一种段(segment),所以其有自己的物理存储属性;
(3) 物化视图会占用数据库磁盘空间,这点从user_segment的查询结果,可以得到佐证;
创建语句:
SQL> create materialized view mv_name as select * from table_name;
默认情况下,如果没指定刷新方法和刷新模式,则Oracle默认为FORCE和DEMAND。
物化视图的数据怎么随着基表而更新?
Oracle提供了两种方式,手工刷新和自动刷新,默认为手工刷新。也就是说,通过我们手工的执行某个Oracle提供的系统级存储过程或包,来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新,其实也就是Oracle会建立一个job,通过这个job来调用相同的存储过程或包,加以实现。
ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别,即前者不刷新(手工或自动)就不更新物化视图,而后者不刷新也会更新物化视图,——只要基表发生了COMMIT。
创建定时刷新的物化视图(指定物化视图每天刷新一次):
SQL> create materialized view mv_name refresh force on demand start with sysdate next sysdate+1;
上述创建的物化视图每天刷新,但是没有指定刷新时间,如果要指定刷新时间(比如每天晚上10:00定时刷新一次):
SQL> create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');
3、ON COMMIT物化视图ON COMMIT物化视图的创建,和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的,所以ON COMMIT物化视图,需要再增加个参数即可。
需要注意的是,无法在定义时仅指定ON COMMIT,还得附带个参数才行。创建ON COMMIT物化视图:
SQL> create materialized view mv_name refresh force on commit as select * from table_name;
备注:实际创建过程中,基表需要有主键约束,否则会报错(ORA-12014)。
4、物化视图的刷新刷新(Refresh):指当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步。
刷新的模式有两种:ON DEMAND和ON COMMIT。
刷新的方法有四种:FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新,只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式,则Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。
对于已经创建好的物化视图,可以修改其刷新方式,比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次:
SQL> alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');
5、物化视图日志如果需要进行快速刷新,则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要,可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
可以指明ON PREBUILD TABLE语句将物化视图建立在一个已经存在的表上。这种情况下,物化视图和表必须同名。当删除物化视图时,不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。
6、物化视图分区而且基于分区的物化视图可以支持分区变化跟踪(PCT)。具有这种特性的物化视图,当基表进行了分区维护操作后,仍然可以进行快速刷新操作。对于聚集物化视图,可以在GROUP BY列表中使用CUBE或ROLLUP,来建立不同等级的聚集物化视图。
二、物化视图与数据迁移Oracle 的物化视图提供了强大的功能,可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能;物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性;物化视图需要占用存储空间;当基表发生变化时,物化视图也应当刷新。
如如何建立在特定的表空间上,这些在其他的物化视图上面几乎都没有任何介绍的。主要以我做的一个例子来操作,如果对物化视图的基本概念清楚了就比较明白在那里写特定的表空间存储了。
1、简单试验在master site上创建表和mview log
SQL> create table stu (id varchar2(10) primary key ,name varchar2(20));
Table created.
SQL> create materialized view log on stu;
Materialized view log created.
在mv site上创建mview
SQL> create materialized view stu_mv refresh fast start with sysdate next sysdate+1/1440 with primary key as select * from stu@to_vm9;
Materialized view created.
SQL> select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;
JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT
--- --------- ----------- --------- ----------- ---------- -------------- -----------------------------------------
21 SEAGULL 2008-2-18 1 14:41:43 2008-2-18 1 14:42:43 sysdate+1/1440 dbms_refresh.refresh('"SEAGULL"."STU_MV"');
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STU_MV TABLE
在master site上对master table做更新:
SQL> INSERT INTO STU(ID,NAME) VALUES('56','555555555555');
1 row created.
SQL> commit;
等1分钟后在mv site上检查
SQL> select * from stu_mv;
ID NAME
---------- --------------------
56 555555555555
2、跨版本数据迁移利用prebuilt mv实现跨平台,跨版本数据迁移。该方法的实现原理是对于要迁移的表对象,需要有一个主键,用于mv的刷新,对于符合该要求的表,在源表上创建mv日志,再在目标数据库上创建结构一样的表,然后在目标表上采用prebuilt方式创建mv,第一次采用完全刷新,之后采用增量刷新,等真正要切换的时候,只需要刷新完增量的日志,删除mv,保留目标表即可。基本思路的例子:
在源库上创建表和mview log
SQL> create table big_t1 as select * from dba_objects;
Table created.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6170
SQL> create materialized view log on big_t1;
Materialized view log created.
在目标数据库上创建与该表一样的表,并在该表上创建prebuilt mv:
SQL> create table big_t1 as select * from big_t1@to_vm9 where 1=2;
Table created.
SQL> select count(1) from big_t1;
COUNT(1)
----------
0
SQL> create materialized view big_t1 on prebuilt table refresh fast as select * from big_t1@to_vm9;
Materialized view created.
做完全刷新和增量刷新
SQL> exec dbms_mview.refresh('BIG_T1','Complete');
PL/SQL procedure successfully completed.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6170
此时模拟在做完全刷新过程中,源库的表又发生了变化
SQL> insert into big_t1(object_id,owner) values(99991,'test');
1 row created.
SQL> commit;
Commit complete.
再做增量刷新
SQL> select count(1) from big_t1;
COUNT(1)
----------
6170
SQL> exec dbms_mview.refresh('BIG_T1');
PL/SQL procedure successfully completed.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6171
停机切换,做最后一次刷新,然后删除源库的mview log和目标库的mview:
SQL> exec dbms_mview.refresh('BIG_T1');
PL/SQL procedure successfully completed.
SQL> drop materialized view big_t1;
Materialized view dropped.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6171
这里删除的mview(big_t1)是prebuilt mv,所以删除该mview,并不删除相应的表。如果删除了mvnew(stu_mv),由于是普通mv,则删除了该mview,就没有对应的表了。
SQL> drop materialized view stu_mv;
Materialized view dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIG_T1 TABLE
3、创建存储的日志空间 SQL> CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID ;
SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID,sequence(LEVYDETAILDATAID);
SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH rowid,sequence(LEVYDATAID);
4、然后创建物化视图SQL> 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'
5、删除物化视图日志物化视图日志经常会由于物化视图长时间没有刷新,或者基表的一次批量数据更改而变得很大,这会影响物化视图的刷新性能,因此对于这种情况需要对物化视图日志进行处理,降低物化视图日志表的高水位线。
物化视图日志会记录下基表所有的增、删、改操作,而物化视图执行完快速刷新操作后,会从物化视图日志中将本物化视图刷新过且其他物化视图所不需要刷新的记录删除掉。如果其中一个物化视图一直不刷新,那么物化视图日志就会变得越来越大。
还有一种情况,比如表中插入了大量的数据,或者删除了大量的数据,或者将表中的某一列统一更新为一个值,这种操作都会在物化视图日志中产生大量的记录。
而物化视图日志的增大必然影响物化视图的刷新速度。一方面,物化视图在刷新的时候要扫描物化视图日志,另一方面,物化视图在刷新介绍后,也要清除物化视图日志中的记录,仍然要扫描物化视图日志,因此物化视图日志的大小直接会影响物化视图快速刷新的速度。更重要的是,物化视图日志的高水位一旦增长到一个很高的位置,即使以后物化视图日志中记录很少,甚至没有记录存在,物化视图在刷新的时候仍然需要较长的时间。
SQL> DROP materialized view log on mv_lvy_levytaxbgtdiv;
SQL> DROP materialized view log on tb_lvy_levydetaildata;
SQL> DROP materialized view log on tb_lvy_levydata;
6、删除物化视图SQL> drop materialized view MV_LVY_LEVYDETAILDATA;
基本和对表的操作一致,物化视图由于是物理真实存在的,故可以创建索引,创建方式和对普通表创建方式相同。
三、ORACLE物化视图总结物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。物化视图可以查询表,视图和其它的物化视图。主要用在数据仓库和决策支持系统。
通常情况下,物化视图被称为主表(在复制期间)或明细表(在数据仓库中)。对于复制,物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本,必须用高级复制的功能。当你想从一个表或视图中抽取数据时,你可以用从物化视图中抽取。对于数据仓库,创建的物化视图通常情况下是聚合视图,单一表聚合视图和连接视图。
物化视图把他的物理结构存储在自己的段中,该段可以被索引和分区。查询不必完全匹配用来创建物化视图的SQL语句,优化程序可以动态重写一个与原定义相近的查询,以便物化视图用来代替实际的表,这种查询重写自动发生,对用户是透明的。
1、使用物化视图前的几个配置步骤(1) 确定那些语句要创建物化视图。
(2) 决定是否要保持视图与基础表数据同步。
如果不同步,可选择如下三种刷新方式:
COMPLETE:刷新启动时,先truncate物化视图,再从基础表重新插入填充数据。
FAST:只刷新基础表上次刷新后改变的数据。使用视图的日志数据或ROWID完成。
FORCE:默认的方式。先使用FAST,不行就使用COMPLETE方式。
(3) 设置init.ora的参数:
JOB_QUEUE_PROCESSES,必须设置大于 1。
QUERY_REWRITE_ENABLED,设置为TRUE时,允许动态重写查询。
QUERY_REWRITE_INTEGRITY,确定访问物化视图时数据一致性要遵守的程度。
OPTIMIZER_MODE,必须设置成CBO的某种方式。
使用一个物化视图,用户只需在基础表上拥有权限即可。
2、创建物化视图SQL>create materialized view emp_by_district
Tablespace mview_data
Build immediate
Refresh fast
Enable query rewrite
As
Select d.id,count(e.last_name) from distributor dist,district d,employee e
Where e.id = dist.manager_id
And d.id dist.district_id
Group by d.id;
以下是Oracle创建物化视图时的常用语法,各参数的含义如下:
1、refresh [fast|complete|force] 视图刷新的方式
fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化,建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on (主表名)。
complete:全部刷新。相当于重新执行一次创建视图的查询语句。
force: 这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。
2、MV数据刷新的时间
on demand:在用户需要刷新的时候刷新,这里就要求用户自己动手去刷新数据了(也可以使用job定时刷新)
on commit:当主表中有数据提交的时候,立即刷新MV中的数据;
start ……:从指定的时间开始,每隔一段时间(由next指定)就刷新一次;
3、Build immediate一共有三个选项
(1) Build immediate:建立物化视图,并使用当前命令执行的数据马上填充视图数据。
(2) Build deferred:只建立物化视图,在第一次刷新之间不填充数据。
(3) No prebuilt table,使用事先已存在的,已含有视图定义中有现有数据的表,而不是建立一个新结构来保存数据。
如果是refresh fast on commit或refresh complete on commit创建的,则在基础表提交的时候都会得到刷新。启用或禁用物化视图,需要有query rewrite或global query rewrite权限。
3、刷新物化视图自动刷新:
(1) 使用commit选项。
(2) 使用dbms_mview安排自动刷新时间。
手工刷新:
SQL>execute dbms_mview.refresh(‘EMP_BY_DISTRICT’); --刷新指定的物化视图
SQL>execute dbms_mview.refresh_defresh_dependent(‘EMPLOYEE’); ――刷新利用了该表的所有物化视图
SQL>execute dbms_mview.refresh_all_mviews; ――刷新该模式中,自上次刷新以来,未得到刷新的所有物化视图。
4、禁用物化视图- 修改init.ora参数的query_rewrite_enabled参数设置成flase,重启实例。
- 使用alter system set query_rewrite_enabled = flase;动态修改。
- 使用alter session set query_rewrite_enabled = flash;修改会话内。
- 使用 norewrite提示。
5、删除物化视图SQL>drop materialized view emp_by_district;
1、物化视图的类型ON DEMAND、ON COMMIT。二者的区别在于刷新方法的不同,ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;而ON COMMIT是说,一旦基表有了COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。
物化视图可以分为以下三种类型:包含聚集的物化视图;只包含连接的物化视图;嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别,而对于其他方面则区别不大。创建物化视图时可以指定多种选项,下面对几种主要的选择进行简单说明:
创建方式(Build Methods):包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据,而BUILD DEFERRED则在创建时不生成数据,以后根据需要在生成数据。默认为BUILD IMMEDIATE。
查询重写(Query Rewrite):包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。
在建立物化视图的时候可以指定ORDER BY语句,使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中,而且对以后的刷新也无效。
2、ON DEMAND物化视图物化视图的创建本身是很复杂和需要优化参数设置的,特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的,类似于普通视图的方式来做,所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。物化视图的特点:
(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表),这通过其可以被user_tables查询出来,而得到佐证;
(2) 物化视图也是一种段(segment),所以其有自己的物理存储属性;
(3) 物化视图会占用数据库磁盘空间,这点从user_segment的查询结果,可以得到佐证;
创建语句:
SQL> create materialized view mv_name as select * from table_name;
默认情况下,如果没指定刷新方法和刷新模式,则Oracle默认为FORCE和DEMAND。
物化视图的数据怎么随着基表而更新?
Oracle提供了两种方式,手工刷新和自动刷新,默认为手工刷新。也就是说,通过我们手工的执行某个Oracle提供的系统级存储过程或包,来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新,其实也就是Oracle会建立一个job,通过这个job来调用相同的存储过程或包,加以实现。
ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别,即前者不刷新(手工或自动)就不更新物化视图,而后者不刷新也会更新物化视图,——只要基表发生了COMMIT。
创建定时刷新的物化视图(指定物化视图每天刷新一次):
SQL> create materialized view mv_name refresh force on demand start with sysdate next sysdate+1;
上述创建的物化视图每天刷新,但是没有指定刷新时间,如果要指定刷新时间(比如每天晚上10:00定时刷新一次):
SQL> create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');
3、ON COMMIT物化视图ON COMMIT物化视图的创建,和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的,所以ON COMMIT物化视图,需要再增加个参数即可。
需要注意的是,无法在定义时仅指定ON COMMIT,还得附带个参数才行。创建ON COMMIT物化视图:
SQL> create materialized view mv_name refresh force on commit as select * from table_name;
备注:实际创建过程中,基表需要有主键约束,否则会报错(ORA-12014)。
4、物化视图的刷新刷新(Refresh):指当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步。
刷新的模式有两种:ON DEMAND和ON COMMIT。
刷新的方法有四种:FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新,只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式,则Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。
对于已经创建好的物化视图,可以修改其刷新方式,比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次:
SQL> alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');
5、物化视图日志如果需要进行快速刷新,则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要,可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
可以指明ON PREBUILD TABLE语句将物化视图建立在一个已经存在的表上。这种情况下,物化视图和表必须同名。当删除物化视图时,不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。
6、物化视图分区而且基于分区的物化视图可以支持分区变化跟踪(PCT)。具有这种特性的物化视图,当基表进行了分区维护操作后,仍然可以进行快速刷新操作。对于聚集物化视图,可以在GROUP BY列表中使用CUBE或ROLLUP,来建立不同等级的聚集物化视图。
二、物化视图与数据迁移Oracle 的物化视图提供了强大的功能,可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。物化视图有很多方面和索引很相似:使用物化视图的目的是为了提高查询性能;物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性;物化视图需要占用存储空间;当基表发生变化时,物化视图也应当刷新。
如如何建立在特定的表空间上,这些在其他的物化视图上面几乎都没有任何介绍的。主要以我做的一个例子来操作,如果对物化视图的基本概念清楚了就比较明白在那里写特定的表空间存储了。
1、简单试验在master site上创建表和mview log
SQL> create table stu (id varchar2(10) primary key ,name varchar2(20));
Table created.
SQL> create materialized view log on stu;
Materialized view log created.
在mv site上创建mview
SQL> create materialized view stu_mv refresh fast start with sysdate next sysdate+1/1440 with primary key as select * from stu@to_vm9;
Materialized view created.
SQL> select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;
JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT
--- --------- ----------- --------- ----------- ---------- -------------- -----------------------------------------
21 SEAGULL 2008-2-18 1 14:41:43 2008-2-18 1 14:42:43 sysdate+1/1440 dbms_refresh.refresh('"SEAGULL"."STU_MV"');
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STU_MV TABLE
在master site上对master table做更新:
SQL> INSERT INTO STU(ID,NAME) VALUES('56','555555555555');
1 row created.
SQL> commit;
等1分钟后在mv site上检查
SQL> select * from stu_mv;
ID NAME
---------- --------------------
56 555555555555
2、跨版本数据迁移利用prebuilt mv实现跨平台,跨版本数据迁移。该方法的实现原理是对于要迁移的表对象,需要有一个主键,用于mv的刷新,对于符合该要求的表,在源表上创建mv日志,再在目标数据库上创建结构一样的表,然后在目标表上采用prebuilt方式创建mv,第一次采用完全刷新,之后采用增量刷新,等真正要切换的时候,只需要刷新完增量的日志,删除mv,保留目标表即可。基本思路的例子:
在源库上创建表和mview log
SQL> create table big_t1 as select * from dba_objects;
Table created.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6170
SQL> create materialized view log on big_t1;
Materialized view log created.
在目标数据库上创建与该表一样的表,并在该表上创建prebuilt mv:
SQL> create table big_t1 as select * from big_t1@to_vm9 where 1=2;
Table created.
SQL> select count(1) from big_t1;
COUNT(1)
----------
0
SQL> create materialized view big_t1 on prebuilt table refresh fast as select * from big_t1@to_vm9;
Materialized view created.
做完全刷新和增量刷新
SQL> exec dbms_mview.refresh('BIG_T1','Complete');
PL/SQL procedure successfully completed.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6170
此时模拟在做完全刷新过程中,源库的表又发生了变化
SQL> insert into big_t1(object_id,owner) values(99991,'test');
1 row created.
SQL> commit;
Commit complete.
再做增量刷新
SQL> select count(1) from big_t1;
COUNT(1)
----------
6170
SQL> exec dbms_mview.refresh('BIG_T1');
PL/SQL procedure successfully completed.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6171
停机切换,做最后一次刷新,然后删除源库的mview log和目标库的mview:
SQL> exec dbms_mview.refresh('BIG_T1');
PL/SQL procedure successfully completed.
SQL> drop materialized view big_t1;
Materialized view dropped.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6171
这里删除的mview(big_t1)是prebuilt mv,所以删除该mview,并不删除相应的表。如果删除了mvnew(stu_mv),由于是普通mv,则删除了该mview,就没有对应的表了。
SQL> drop materialized view stu_mv;
Materialized view dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIG_T1 TABLE
3、创建存储的日志空间 SQL> CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID ;
SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID,sequence(LEVYDETAILDATAID);
SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH rowid,sequence(LEVYDATAID);
4、然后创建物化视图SQL> 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'
5、删除物化视图日志物化视图日志经常会由于物化视图长时间没有刷新,或者基表的一次批量数据更改而变得很大,这会影响物化视图的刷新性能,因此对于这种情况需要对物化视图日志进行处理,降低物化视图日志表的高水位线。
物化视图日志会记录下基表所有的增、删、改操作,而物化视图执行完快速刷新操作后,会从物化视图日志中将本物化视图刷新过且其他物化视图所不需要刷新的记录删除掉。如果其中一个物化视图一直不刷新,那么物化视图日志就会变得越来越大。
还有一种情况,比如表中插入了大量的数据,或者删除了大量的数据,或者将表中的某一列统一更新为一个值,这种操作都会在物化视图日志中产生大量的记录。
而物化视图日志的增大必然影响物化视图的刷新速度。一方面,物化视图在刷新的时候要扫描物化视图日志,另一方面,物化视图在刷新介绍后,也要清除物化视图日志中的记录,仍然要扫描物化视图日志,因此物化视图日志的大小直接会影响物化视图快速刷新的速度。更重要的是,物化视图日志的高水位一旦增长到一个很高的位置,即使以后物化视图日志中记录很少,甚至没有记录存在,物化视图在刷新的时候仍然需要较长的时间。
SQL> DROP materialized view log on mv_lvy_levytaxbgtdiv;
SQL> DROP materialized view log on tb_lvy_levydetaildata;
SQL> DROP materialized view log on tb_lvy_levydata;
6、删除物化视图SQL> drop materialized view MV_LVY_LEVYDETAILDATA;
基本和对表的操作一致,物化视图由于是物理真实存在的,故可以创建索引,创建方式和对普通表创建方式相同。
三、ORACLE物化视图总结物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。物化视图可以查询表,视图和其它的物化视图。主要用在数据仓库和决策支持系统。
通常情况下,物化视图被称为主表(在复制期间)或明细表(在数据仓库中)。对于复制,物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本,必须用高级复制的功能。当你想从一个表或视图中抽取数据时,你可以用从物化视图中抽取。对于数据仓库,创建的物化视图通常情况下是聚合视图,单一表聚合视图和连接视图。
物化视图把他的物理结构存储在自己的段中,该段可以被索引和分区。查询不必完全匹配用来创建物化视图的SQL语句,优化程序可以动态重写一个与原定义相近的查询,以便物化视图用来代替实际的表,这种查询重写自动发生,对用户是透明的。
1、使用物化视图前的几个配置步骤(1) 确定那些语句要创建物化视图。
(2) 决定是否要保持视图与基础表数据同步。
如果不同步,可选择如下三种刷新方式:
COMPLETE:刷新启动时,先truncate物化视图,再从基础表重新插入填充数据。
FAST:只刷新基础表上次刷新后改变的数据。使用视图的日志数据或ROWID完成。
FORCE:默认的方式。先使用FAST,不行就使用COMPLETE方式。
(3) 设置init.ora的参数:
JOB_QUEUE_PROCESSES,必须设置大于 1。
QUERY_REWRITE_ENABLED,设置为TRUE时,允许动态重写查询。
QUERY_REWRITE_INTEGRITY,确定访问物化视图时数据一致性要遵守的程度。
OPTIMIZER_MODE,必须设置成CBO的某种方式。
使用一个物化视图,用户只需在基础表上拥有权限即可。
2、创建物化视图SQL>create materialized view emp_by_district
Tablespace mview_data
Build immediate
Refresh fast
Enable query rewrite
As
Select d.id,count(e.last_name) from distributor dist,district d,employee e
Where e.id = dist.manager_id
And d.id dist.district_id
Group by d.id;
以下是Oracle创建物化视图时的常用语法,各参数的含义如下:
1、refresh [fast|complete|force] 视图刷新的方式
fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化,建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on (主表名)。
complete:全部刷新。相当于重新执行一次创建视图的查询语句。
force: 这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。
2、MV数据刷新的时间
on demand:在用户需要刷新的时候刷新,这里就要求用户自己动手去刷新数据了(也可以使用job定时刷新)
on commit:当主表中有数据提交的时候,立即刷新MV中的数据;
start ……:从指定的时间开始,每隔一段时间(由next指定)就刷新一次;
3、Build immediate一共有三个选项
(1) Build immediate:建立物化视图,并使用当前命令执行的数据马上填充视图数据。
(2) Build deferred:只建立物化视图,在第一次刷新之间不填充数据。
(3) No prebuilt table,使用事先已存在的,已含有视图定义中有现有数据的表,而不是建立一个新结构来保存数据。
如果是refresh fast on commit或refresh complete on commit创建的,则在基础表提交的时候都会得到刷新。启用或禁用物化视图,需要有query rewrite或global query rewrite权限。
3、刷新物化视图自动刷新:
(1) 使用commit选项。
(2) 使用dbms_mview安排自动刷新时间。
手工刷新:
SQL>execute dbms_mview.refresh(‘EMP_BY_DISTRICT’); --刷新指定的物化视图
SQL>execute dbms_mview.refresh_defresh_dependent(‘EMPLOYEE’); ――刷新利用了该表的所有物化视图
SQL>execute dbms_mview.refresh_all_mviews; ――刷新该模式中,自上次刷新以来,未得到刷新的所有物化视图。
4、禁用物化视图- 修改init.ora参数的query_rewrite_enabled参数设置成flase,重启实例。
- 使用alter system set query_rewrite_enabled = flase;动态修改。
- 使用alter session set query_rewrite_enabled = flash;修改会话内。
- 使用 norewrite提示。
5、删除物化视图SQL>drop materialized view emp_by_district;
发表评论
-
深入理解Oracle索引(5):反向索引的定义、缺点和适用场景
2014-02-20 13:26 808http://blog.csdn.net/dba_waterb ... -
java.sql.SQLException: ORA-01008: 并非所有变量都已绑定
2013-10-17 19:21 3506java.sql.SQLException: ORA-0100 ... -
linux下mysql的root密码忘记解决方
2013-10-08 10:02 8451.首先确认服务器出于安全的状态,也就是没有人能够任意地连接 ... -
centos6 修改profile后,无法登录系统(profile修改错误)
2013-09-29 15:41 808使用单用户模式登陆去修改profile 一、单用户模式; ... -
centos6 修改profile后,无法登录系统(profile修改错误)
2013-09-29 15:41 1740使用单用户模式登陆去修改profile 一、单用户模式; ... -
JavaScript trim函数大赏
2013-09-18 17:41 885http://www.cnblogs.com/rubylouv ... -
java 线程
2013-07-17 17:57 1011http://www.cnblogs.com/devinzha ... -
AIX 操作系统查看文件夹及文件大小的命令
2013-07-03 18:51 75361、df -sg 说明:查看各分区的使用情况 2、du ... -
oracle 多个例程,启动具体例程 startup pfile
2013-07-03 13:46 3736如果环境是AIX系统,安装了oracle,具有多个例程(实例i ... -
解决ORA-30036:无法按8扩展段(在还原表空间‘XXXX’中)
2013-07-01 18:03 1793http://blog.sina.com.cn/s/blog_ ... -
oracle 并行度--转载
2013-06-28 15:51 1913从巴乔博客中看到: ‘并行度为DEFAULT的表进行PDML时 ... -
oracle并行查询常见问题 --转载
2013-06-28 15:46 1150在OLAP环境,以利用多的 ... -
多核技术与并发多线程技术介绍(转载)
2013-06-28 15:45 1513看多很多人在这个上面 ... -
AIX如何查看cpu个数(转载)
2013-06-28 15:19 1304http://hi.baidu.com/fgvibxjaneb ... -
如何查看表的并行度并设置表的并行度
2013-06-28 15:17 7609查看表的并行度语句: select table_name,de ... -
Oracle--optimizer_mode
2013-06-24 17:00 1522Oracle--optimizer_mode O ... -
jtable单元格的悬浮提示和表头标题的悬浮提示
2013-06-19 18:14 1526http://blog.csdn.net/yufaw/arti ... -
servlet2.4 和servlet2.5中配置taglib的区别
2013-06-17 15:54 15012.4写法: <jsp-config> & ... -
win7 SP2-1503: 无法初始化 Oracle 调用界面
2013-06-09 15:01 1445win7 下 cmd 运行 sqlplus 报 ... -
触发Full GC执行的情况
2013-06-06 18:38 970http://blog.sina.com.cn/s/blog_ ...
相关推荐
Oracle物化视图是一种数据库对象,它存储了查询结果,以提供快速的数据访问,特别适合于需要频繁查询但计算过程复杂或涉及大量数据连接的场景。物化视图的使用可以显著提高查询性能,因为它避免了每次查询时的计算...
Oracle 物化视图创建和使用 Oracle 物化视图是一种预先计算并保存表连接或聚集等耗时较多的操作的结果,以提高查询性能。物化视图对应用程序透明,不会影响应用程序的正确性和有效性,但需要占用存储空间。基表发生...
"Oracle 物化视图增量刷新的应用研究" Oracle 物化视图是 Oracle 公司提供的一种新技术,可以解决很多普通逻辑视图无法完成的功能。物化视图操作简单,支持增量刷新及全量刷新,可以支持复杂的表连接、聚合函数等...
"利用ORACLE物化视图建立报表数据库.pdf" 本文主要介绍了利用ORACLE物化视图建立报表数据库的方法和原理。报表数据库是指独立于生产数据库的数据库,用于存储和管理报表数据。通过建立报表数据库,可以实现工作负荷...
### Oracle 物化视图详解 #### 一、物化视图的概念与作用 物化视图是Oracle数据库中一种特殊的数据对象,它保存的是基于一个或多个表(称为基表)的查询结果集,并且这些结果集是物理上存在的。与普通的视图不同,...
Oracle物化视图是一种在数据库中预先计算并存储视图查询结果的数据对象,它与普通的视图不同,普通视图在查询时动态地基于基表数据生成结果,而物化视图则拥有自己的物理存储,提供了对数据的快速访问。在本篇循序渐...
Oracle物化视图是数据库管理系统中的一个重要特性,尤其在处理大量数据和复杂查询的场景下,它可以极大地提升查询性能和数据一致性。物化视图与普通的视图不同,后者是逻辑上的虚表,其内容在查询时动态计算,而物化...
Oracle 物化视图 Oracle 物化视图是数据库对象,存储远程表的数据副本,也可以称为快照。物化视图可以查询表、视图和其他物化视图。通常情况下,物化视图被称为主表(在复制期间)或明细表(在数据仓库中)。 创建...
### Oracle物化视图介绍及应用详解 #### 一、物化视图概念与作用 在Oracle数据库中,物化视图(Materialized View, MV)是一种预计算并存储的查询结果,它能够显著提高复杂查询的性能。通常情况下,复杂的查询涉及到...
Oracle数据库中的物化视图(Materialized View,简称MV)是一种强大的优化工具,它通过预先计算并存储查询结果,提供了一种快速访问复杂查询数据的方式。这种技术在数据仓库环境中尤其有用,因为数据仓库通常涉及...
Oracle物化视图日志是一种强大的数据同步技术,尤其适用于分布式数据库和分布式应用系统之间的数据一致性维护。在当今信息化系统中,随着技术的快速发展,数据的分布性和实时性需求日益增强,数据同步成为了一个关键...
【Oracle物化视图】是Oracle数据库中一种特殊的数据对象,它存储了查询结果集的物理副本,可以提供对远程数据的快速访问和一致性保证。物化视图的主要优点在于,它可以减少对远程数据源的访问,提高数据的本地可用性...
### ORACLE 物化视图详解 #### 一、物化视图概述 在Oracle数据库中,物化视图(Materialized View)是一种特殊的数据库对象,它存储的是一个查询的结果集,可以理解为一个预计算的快照。物化视图主要用于提高报表...
### Oracle物化视图详解 #### 一、物化视图概述 Oracle物化视图是一种特殊类型的数据库对象,其核心功能在于预先计算并存储基于一个或多个表的查询结果,以此来加速后续的查询操作。与普通视图不同,普通视图在...
Oracle物化视图是Oracle数据库中一种非常重要的特性,它提供了数据的一致性视图,通常用于实现数据的异步复制。本配置指导书主要针对Oracle高级复制中的物化视图,旨在帮助用户理解并成功配置物化视图,以满足在实际...
### ORACLE物化视图详解 #### 执行概要与简介 在当今的数据密集型世界中,无论是数据仓库、数据中心还是在线事务处理(OLTP)系统,都面临着一个共同的挑战:如何高效地检索和展示庞大的信息量。Oracle物化视图作为...
Oracle物化视图是数据库性能优化的重要工具,尤其在处理大量数据查询和统计工作中显得尤为重要。物化视图,也称作快照,是数据库在某一时间点对目标表(主控)的副本,可以是主站点上的主表,也可以是物化视图站点上...
Oracle物化视图是一种在数据库管理系统中用于提升性能和数据整合的重要工具,尤其适用于处理大量数据和复杂的查询。物化视图本质上是一个预先计算并存储的查询结果集,它不仅提供了一种数据缓存的方式,还可以实现...
【Oracle物化视图实现数据同步复制】 Oracle物化视图是一种高级的数据管理工具,用于在数据库间实现数据的同步复制。物化视图的概念首次出现在Oracle8i中,它是一个实际存储在数据库中的预计算结果集,可以提高查询...