刷新物化视图很慢_快速刷新物化视图

确认当前操作的实例名

select instance_name,status from v$instance;

select instance_name,status from gv$instance;

确定当前用户的索引类型以及分布情况

select index_name,index_type,status,table_name from user_indexes;

select sid,event,p1,p2 from v$session_wait;

oracle 创建可快速刷新的物化视图

1.创建测试基表

drop table lgc_mv;

create table lgc_mv (id number,

name varchar2(30),

loc varchar2(30),

dep_id number,

sal number);

insert into lgc_mv values(1,’luda’,’hz’,101,5555);

insert into lgc_mv values(2,’luya’,’hz’,102,6666);

insert into lgc_mv values(3,’shaoshan’,’bj’,103,7777);

insert into lgc_mv values(4,’xiaxian’,’xm’,104,5355);

insert into lgc_mv values(5,’langyu’,’hz’,105,5556);

insert into lgc_mv values(6,’sushan’,’wrmj’,106,5457);

insert into lgc_mv values(7,’hens’,’bj’,107,5337);

insert into lgc_mv values(8,’join’,’bj’,107,10240);

drop table sales

create table sales (id number,

name varchar2(30),

dep_id number,

salary number);

insert into sales values(1,’luda’,101,5555);

insert into sales values(2,’luya’,102,6666);

insert into sales values(3,’shaoshan’,103,7777);

insert into sales values(4,’xiaxian’,104,5355);

insert into sales values(5,’langyu’,105,5556);

insert into sales values(6,’sushan’,106,5457);

insert into sales values(7,’hens’,107,5337);

insert into sales values(8,’hludss’,107,6889);

insert into sales values(9,’yasil’,105,7993);

oracle

alter table lgc_mv rename cloumn hob_id to sal;

explain plan set statement_id=’sales’ for select * from sales;

select * from table(dbms_xplan.display);

explain plan set statement_id=’lgc_mv’ for select * from lgc_mv;

2。创建物化视图

alter system set query_rewrite_enabled=true scope=spfile;

alter table lgc_mv noparallel;

查找当前用户拥有的物化视图和状态

SELECT mview_name, refresh_mode, refresh_method,

last_refresh_type, last_refresh_date

FROM user_mviews

drop materialized view lgc_info_mv;

————-测试时候就这样的：

create materialized view lgc_info_mv

parallel

build immediate

refresh complete

enable query rewrite as

select a.dep_id,sum(sal) sum_salary

from lgc_mv a,sales b where a.id=b.id

group by a.dep_id;

————————————-

create materialized view lgc_info_mv

parallel

build immediate

refresh complete

enable query rewrite as

select a.dep_id,sum(sal) sum_salary,count(sal),count(*)

from lgc_mv a,sales b where a.id=b.id

group by a.dep_id;

创建dbms_mview.explain_mview包所需求的表

@?/rdbms/admin/utlxmv.sql

分析物化视图

exec dbms_mview.explain_mview(‘lgc.lgc_info_mv’);

查找分析物化视图分析结果

SELECT capability_name, possible, SUBSTR(related_text,1,8)

AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt

FROM MV_CAPABILITIES_TABLE

ORDER BY seq;

清除数据

TRUNCATE TABLE mv_capabilities_table

分析后创建基物化视图在对应的表上。

DROP materialized view log on sales;

DROP materialized view log on lgc_mv;

CREATE MATERIALIZED VIEW LOG ON SALES WITH ROWID,SEQUENCE

(id,name,dep_id,salary) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON LGC_MV WITH ROWID,SEQUENCE

(id,name,loc,dep_id,sal) INCLUDING NEW VALUES;

这时候就要根据里面的东西调试了~一个快速刷新的视图需要根据explain的调试才能知道~~~