Oracle普通视图和物化视图的区别
介绍
物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高,并没有实质上的好处。
1、物化视图的类型:ON DEMAND、ON COMMIT
二者的区别在于刷新方法的不同,ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;而ON COMMIT是说,一旦基表有了COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。
2、ON DEMAND物化视图
物化视图的创建本身是很复杂和需要优化参数设置的,特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的,类似于普通视图的方式来做,所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。
物化视图的特点:
(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表),这通过其可以被user_tables查询出来,而得到佐证;
(2) 物化视图也是一种段(segment),所以其有自己的物理存储属性;
(3) 物化视图会占用数据库磁盘空间,这点从user_segment的查询结果,可以得到佐证;
创建语句:create materialized view mv_name as select * from table_name
默认情况下,如果没指定刷新方法和刷新模式,则Oracle默认为FORCE和DEMAND。
3、物化视图的数据怎么随着基表而更新?
Oracle提供了两种方式,手工刷新和自动刷新,默认为手工刷新。也就是说,通过我们手工的执行某个Oracle提供的系统级存储过程或包,来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新,其实也就是Oracle会建立一个job,通过这个job来调用相同的存储过程或包,加以实现。
4、ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别
前者不刷新(手工或自动)就不更新物化视图,而后者不刷新也会更新物化视图,——只要基表发生了COMMIT。
创建定时刷新的物化视图:
create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1 (指定物化视图每天刷新一次)
上述创建的物化视图每天刷新,但是没有指定刷新时间,如果要指定刷新时间(比如每天晚上10:00定时刷新一次):
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')
5、ON COMMIT物化视图
ON COMMIT物化视图的创建,和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的,所以ON COMMIT物化视图,需要再增加个参数即可。
需要注意的是,无法在定义时仅指定ON COMMIT,还得附带个参数才行。
创建ON COMMIT物化视图:
create materialized view mv_name refresh force on commit as select * from table_name
备注:实际创建过程中,基表需要有主键约束,否则会报错(ORA-12014)
6、物化视图的刷新
刷新(Refresh):指当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种:ON DEMAND和ON COMMIT。(如上所述)
刷新的方法有四种:FAST、COMPLETE、FORCE和NEVER。
FAST刷新采用增量刷新,只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式,则Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。
对于已经创建好的物化视图,可以修改其刷新方式,比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次:
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')
7、物化视图具有表一样的特征,所以可以像对表一样,我们可以为它创建索引,创建方法和对表一样。
8、物化视图的删除:
虽然物化视图是和表一起管理的,但是在经常使用的PLSQL工具中,并不能用删除表的方式来删除(在表上右键选择‘drop’并不能删除物化视图),可以使用语句来实现:
drop materialized view mv_name
其他参考
普通视图和物化视图的区别
普通视图和物化视图根本就不是一个东西,说区别都是硬拼到一起的,首先明白基本概念,普通视图是不存储任何数据的,他只有定义,在查询中是转换为对应的定义SQL去查询,而物化视图是将数据转换为一个表,实际存储着数据,这样查询数据,就不用关联一大堆表,如果表很大的话,会在临时表空间内做大量的操作。
普通视图的三个特征:
1、是简化设计,清晰编码的东西,他并不是提高性能的,他的存在只会降低性能(如一个视图7个表关联,另一个视图8个表,程序员不知道,觉得很方便,把两个视图关联再做一个视图,那就惨了),他的存在未了在设计上的方便性
2、其次,是安全,在授权给其他用户或者查看角度,多个表关联只允许查看,不允许修改,单表也可以同WITH READ ONLY来控制,当然有些项目基于视图做面向对象的开发,即在视图上去做INSTAND OF触发器,就我个人而言是不站同的,虽然开发上方便,但是未必是好事。
3、从不同的角度看不同的维度,视图可以划分维度和权限,并使多个维度的综合,也就是你要什么就可以从不同的角度看,而表是一个实体的而已,一般维度较少(如:人员表和身份表关联,从人员表可以查看人员的维度统计,从身份看,可以看不同种类的身份有那些人或者多少人),其次另一个如系统视图USER_TABLE、TAB、USER_OBJECTS这些视图,不同的用户下看到的肯定是不一样的,看的是自己的东西。
物化视图呢,用于OLAP系统中,当然部分OLTP系统的小部分功能未了提高性能会借鉴一点点,因为表关联的开销很大,所以在开发中很多人就像把这个代价交给定期转存来完成,ORACLE当然也提供了这个功能,就是将视图(或者一个大SQL)的信息转换为物理数据存储,然后提供不同的策略:定时刷还是及时刷、增量刷还是全局刷等等可以根据实际情况进行选择,总之你差的是表,不是视图。
关于在刷新和索引上的区别
他们两个没有联系吧,刷新我不清楚你是否指的是物化视图的刷新,因为刷新的概念很泛,你说到这里我就理解为物化视图的刷新了,上面也已经说了,这是一种策略和方法,其实它是通过对视图关联表上创建相应的LOG,根据日志信息的SQL同步到物化视图中的,一般来说:定时的一般是全局刷,及时的一般是局部刷。
而索引这个说起来就多了,可以说索引专门是一门课程,大概点来说,索引一般有普通索引、位图索引、唯一性索引(还有全文索引啥的,一般不用),其实仔细研究会发现无论是那一种索引都是B+树为基础,并起存放方式和表一样,是以段为单位,只是内部有树关系而已。
1、普通索引是根据B+树找到第一个(索引时有序的),然后以当前为基准,向后顺序找到不符合条件的健值为止。
2、位图是在叶子节点上根据位图种类对叶子节点的值进行01编码存放(如该字段有1、2、3三种值,就会在叶子节点上有三个位图,每个位图根据健值和ROWID顺序存放是否为1、是否为2、是否为3,所以在RBO下统计很快,CBO下一般会认为是普通索引)。
3、也是按照B+树找,只是找到就不再做任何操作,因为是唯一的。
因为B+查找是一个类似表的查询,而且获取到ROWID后还是要回表查询的,所以这个过程的开销要和全表扫描计算那个结果更加快,ORACLE才会选择是走索引还是走全表扫描,当然对于CBO和RBO选择的方式不一样,具体又是很多,CBO要依赖于表的统计信息,RBO是依赖于尝试。
有用过ORCLE 物化视图的朋友,请帮个忙,我想每天刷新两次,比如中午十二点,晚上七点,不知道怎么写,急用!
你这个需要,可以这样实现
建一个表,再写个存储过程
再用个SCHEDULE JOB来实现
用普通JOB,或者物化视图,对于时间间隔都需要有规律
或者写个储存过程来手工刷新物化视图,然后用SCHEDULE JOB来实现每天12点,19点刷新
对于oracle10g简单方法:
1. 先建立一个物化视图,不用指明刷新参数,只要create materialized view,如
CREATE MATERIALIZED VIEW mv_emp
AS SELECT * FROM scott.emp;
2. 建立一个用来刷新物化视图的存储过程,如
CREATE OR REPLACE PROCEDURE auto_refresh_mview_job_proc
AS
BEGIN
dbms_mview.REFRESH('mv_emp');
END;
/
3. 用ORACLE 10g的scheduler每天12:00和19:00定期刷新物化视图
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'auot_refresh_mview_job',
job_type => 'STORED_PROCEDURE',
job_action => 'scott.auto_refresh_mview_job_proc',
start_date => SYSDATE,
repeat_interval => 'FREQ=DAILY; BYHOUR=12,19',
enabled => TRUE,
comments => 'Refresh materialized view mv_emp'
);
END;
/
4. 删除scheduler对象的方法
BEGIN
DBMS_SCHEDULER.DROP_JOB(
job_name => 'auot_refresh_mview_job'
);
END;
/
dbms_mview.REFRESH('mv_emp'); 这个是我想要的,谢谢了,还有,如果我之前用alter materialized view '|| a.table_name||' refresh complete start with sysdate next sysdate+100/(24*60*60)'||';' 建的刷新,怎么取消啊。
把刷新物化视图的JOB 删除了就可以了,下面是查询物化视图及对应的jobno的语句:
SELECT job, what from user_jobs;
找到jobno后,用dbms_Job.remove就可以删除job了
exec dbms_job.remove(:jobno);
3. 用ORACLE 10g的scheduler每天12:00和19:00定期刷新物化视图
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'auot_refresh_mview_job',
job_type => 'STORED_PROCEDURE',
job_action => 'scott.auto_refresh_mview_job_proc',
start_date => SYSDATE,
repeat_interval => 'FREQ=DAILY; BYHOUR=12,19',
enabled => TRUE,
comments => 'Refresh materialized view mv_emp'
);
上面这种方式我不怎么明白。
物化视图的定时刷新其实是用Oracle的任务实现的,就是按照刷新的时间间隔定期的执行DBMS_MVIEW.REFRESH('mv_emp')的.由于Oracle10g增加了功能更为强大的scheduler功能,可以更加灵活的定义执行任务的时间,因此这里我给出的方法就是用10g的SCHEDULER,用scheduler要用到Oracle的内部包DBMS_SCHEDULER,其中DBMS_SCHEDULER.CREATE_JOB就是来创建一个实时执行的任务,下面是一个例子
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'auto_refresh_mview_job',
job_type => 'STORED_PROCEDURE',
job_action => 'scott.auto_refresh_mview_job_proc',
start_date => SYSDATE,
repeat_interval => 'FREQ=DAILY; BYHOUR=12,19',
enabled => TRUE,
comments => 'Refresh materialized view mv_emp'
);
job_name对应的参数是任务名,这个任务的名字就是'auto_refresh_mview_job'
job_type是任务的类型,这里是存储过程类型
job_action是任务的名称,这里就是存储过程'scott.auto_refresh_mview_job_proc',存储过程的功能就是执行dbms_mviw.refresh('mv_emp')
start_date是开始执行的时间,这里是从任务创建时就开始执行
repeat_interval是执行的时间间隔,FREQ表示执行频率,DAILY表示每天执行,BYHOUR定义了具体的执行时间为每天的12时和19时
enabled表示任务为启用状态,如果不加这个参数,为缺省的不启用
comments为任务的一个说明,这里面的内容可以根据实际情况自行填写。
定义了上面的任务后,可以从字典表dba_scheduler_jobs查询出这个任务来。
手动刷新
BEGIN DBMS_MVIEW.REFRESH ( list => 'viewname', Method =>'C', refresh_after_errors => True); END;
参数:
我们常常利用物化视图来同步数据,或者迁移数据。在dbms_mview中的刷新过程,可以使用并行刷新的特性,可以有效的减少完全刷新的时间,下面是一个过程示例:
create materialized view log on test; --创建物化视图日志
create table mv_test as select * from test@lnk_testdb where 1=2; --创建与源表相同的表结构
create materialized view mv_test on prebuilt table refresh fast as
select * from test@lnk_testdb; --创建物化视图
我们来看一下,我们常用的刷新过程参数含义:
PROCEDURE REFRESH
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TAB TABLE OF VARCHAR2(227) IN/OUT
METHOD VARCHAR2 IN DEFAULT
ROLLBACK_SEG VARCHAR2 IN DEFAULT
PUSH_DEFERRED_RPC BOOLEAN IN DEFAULT
REFRESH_AFTER_ERRORS BOOLEAN IN DEFAULT
PURGE_OPTION BINARY_INTEGER IN DEFAULT
PARALLELISM BINARY_INTEGER IN DEFAULT --这里是并行控制参数
HEAP_SIZE BINARY_INTEGER IN DEFAULT
ATOMIC_REFRESH BOOLEAN IN DEFAULT
NESTED BOOLEAN IN DEFAULT
比如说我们要全刷新一张mv_test物化视图:
begin
dbms_mview.refresh(TAB=>'MV_TEST',
METHOD=>'COMPLETE',
PARALLELISM=>8);
end;
/
dbms_mview.refresh('表名', 'F') --快速刷新,也就是增量刷新
dbms_mview.refresh('表名', 'C')--完全刷新
这个有什么问题
你只看前两个就行了,list是你要刷新的表名列表,method 就是你要执行刷新的方式
增量刷新就不需要使用什么并行了,通常情况下,是没有那个必要的。
begin
dbms_mview.refresh(TAB=>'MV_TEST',
METHOD=>'FAST',
PARALLELISM=>1);
end;
/
谢谢下面网友的提醒,对此文纠正一些错误,如果只是在刷新的时候,指定此parallelism并行参数,实际上并不能启用起并行,下面是文档中原文描述:
At the outset, it appears that the PARALLELISM parameter will invoke a parallel refresh of the materialized view. However, it doesn’t. The dynamic performance views V$PX_PROCESS and V$PX_SESSION provide information on the parallel execution processes and the sessions using parallel execution respectively. When the above refresh process is running, if we query these views, we will see that there are no parallel execution processes in use.
如果想在刷新的时候,启用并行,可以采用如下的三种办法:
1.修改master表的并行度属性
2.创建物化视图日志定义并行度属性
3.创建物化视图时,使用hint
有时间再对这个测试一下,后面补充的信息的参考文档为:
http://www.doug.org/newsletter/march/MV_Refresh_Parallel.pdf