建立快速刷新的物化视图（materialized view)

1.建表

create table t(
    code    varchar(10),
    name    varchar(20),
    id        varchar(40) primary key);

2.插入数据：

insert into t(code, name, id) values('001', 'test1', '001');
insert into t(code, name, id) values('002', 'test2', '002');
commit;

 

3.创建materialized view log

create materialized view log on t with primary key,rowid, sequence (name) including new values;

4.创建materialized view

create materialized view t_mv
    build immediate 
    refresh fast on commit

    enable query rewrite
    as
    select name, id
    from t;

 

5.测试：

update t set name=upper(name);

commit;

select * from t;

select * from t_mv;

对于含有聚合函数的物化视图,必须含有count(*)作为快速刷新的保证,同时在物化视对应的基表上创建物化视图日志，

日志至少包含所有在物化视图出现的字段:

create materialized view test_mv

build immediate

refresh fast on commit

enable query rewrite

as

select 

sum(amount), count(*), count(amount), prod_id

from sales

group by prod_id;

其中count(*), count(amount)用于快速刷新的重要条件,

用dbms_advisor.tune_mview生成创建物化视图的语法

例如：select distinct code, name from t;

set serveroutput on;

declare

task_name varchar(40);

my_sql varchar(1000);

begin

my_sql := ' create materialized view t_mv ' ||

'  refresh fast ' ||

'  as ' ||

'  select distinct code, name from t ' ;

dbms_advisor.tune_mview(task_name, my_sql);

dbms_output.put_line(task_name);

end;

/

set long 9999999;

select statement     

from dba_tune_mview 

where  task_name ='XXX';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12554513/viewspace-667033/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12554513/viewspace-667033/