linux view 刷新,建立快速刷新的物化视图（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_namevarchar(40);

my_sqlvarchar(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';