Oracle物化视图创建-使用-维护

晟数学院 2021-04-16

2775

点击“蓝字”关注我们

使用原则

物化视图使用时基本等同于数据库表，主键、索引均可创建，如使用增量刷新，可能导致基表进行DML时效率降低。在子查询关联逻辑复杂，查询非常慢时，可将子查询单独拿出来，建一个物化视图，再由原查询关联此物化视图提升效率，不建议使用物化视图完全替代普通视图。

创建

create materialized view [view_name] 
refresh [fast|complete|force] [ 
on [commit|demand] 
| start with (start_time) next (next_time) ] as 
{创建物化视图用的查询语句}

刷新模式：

demand表示视图在被需要的时候刷新

commit表示一旦有了事务提交即更新

刷新方法：

fast采用增量刷新，只刷新自上次刷新以后进行的修改

complete对整个物化视图进行完全的刷新

force在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式

never从不刷新

（可选）刷新时间：

start_time指定起始时间

next_time下次开始时间，起始到下次的间隔时间即改表的循环时间

例子：

create materialized view test_mview
    refresh force on demand
   start with sysdate 
   next TO_DATE(CONCAT(TO_CHAR(SYSDATE+1,'DD-MM-YYYY'),'02:00:00'),'DD-MM-YYYY HH24:MI:SS')
as 
select v.*,rownum id from sa_plan_excute_view v;

小记：上例表示，从n天开始，下次运行时间为n+1凌晨两点。当n+1天运行后，再次检测n+2天运行时间。开始时间不能晚于当前时间；开始时间不能为固定时间如to_date(‘07-03-2021 22:00:00’,‘dd-MM-yyyy HH24:Mi:SS’)，否则将导致第二次更新视图失败；start和next时间格式必须一致。物化视图创建完成，会自动在dba_jobs中生成一个job定时任务，下文会说到。

删除

drop materialized view test_mview;

小记：删除物化视图时会自动删除物化视图日志。

创建物化视图日志

create materialized view log on test_sa_plan_excute_view with primary key;

小记：创建物化视图日志必须要有可作为主键的列(值不为空且不重复)，由于Oracle中并没有自增主键，所以我们在创建物化视图时，在select语句用rownum做自增主键。如：

select rownum primary_id,d.* from dept d

物化视图日志用于记录物化视图语句相关基表的DML操作，并将此DML操作用于物化视图中，使得数据同步更新，这也是fast增量刷新速度最快的原因。但创建物化视图时使用了聚合函数，会导致物化视图日志不可用，只能使用complete完全刷新，也就是仅使用完全刷新时，可以不用创建物化视图日志。

查看物化视图日志

select * from mlog$_test_mview;

小记：mlog$是每个物化视图日志必有的前缀，第一个_后面是物化视图名，命名方式由数据库决定。

手动刷新物化视图

begin
    dbms_mview.refresh('test_mview');
end;

job定时刷新物化视图

小记：使用了聚合函数的物化视图，除了可以用…complete on demand start with…next…还能直接自定义job定时任务，当有多个物化视图时，且更新时间一致，可以统一写在一个job定时任务内，避免生成过多job，物化视图的完全刷新应间隔半小时以上。

创建存储函数

create procedure REFRESH_TEST_MVIEW as
begin
    dbms_mview.refresh('test_mview');
end;

创建job自动执行存储函数

declare
    job number;
BEGIN
    DBMS_JOB.SUBMIT(
            JOB => job,  /*自动生成JOB_ID*/
            WHAT => 'TEST_MVIEW;',  /*需要执行的存储过程名称或SQL语句*/
            NEXT_DATE => TRUNC(sysdate,'mi'),  /*初次执行时间*/
            INTERVAL =>
                'TRUNC(sysdate,''mi'') + 1  (24*60)' /*每隔1分钟执行一次;*/
        );
    commit;
end;

查看运行job

select job, next_date, next_sec, failures, broken from dba_jobs;

小记：生成物化视图，或自定义物化视图后，都应查看dba_jobs中该job是否正常运行。failures为失败次数，若大于0，请检查定义视图或自定义job。

查看运行job的id

select job from user_jobs where what in ('TEST_MVIEW;')

删除job

begin
    for v in (select job
              from user_jobs
              where what in ('TEST_MVIEW;')) loop
            dbms_job.remove(v.job);
        end loop;
    commit;
end;