确认当前操作的实例名
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的调试才能知道~~~
