1、oracle物化視圖學(xué)習(xí)筆記物化視圖是一種特別的物理表，“物化”(materializ)視圖是相對(duì)一般視圖而言的。一般視圖是虛擬表，應(yīng)用的局限性大，任何對(duì)視圖的查詢(xún)，oracle都事實(shí)上轉(zhuǎn)換為視圖sql語(yǔ)句的查詢(xún)。這樣對(duì)整體查詢(xún)性能的提高，并沒(méi)有實(shí)質(zhì)上的益處。 挺直刷新 在scott用戶(hù)下創(chuàng)建源數(shù)據(jù)表 create table scott.emp_ori as select empno, deptno,sal from scott.emp 在ceno用戶(hù)下創(chuàng)建mv create materialized view ceno._emp build immediate refresh on com

2、mit - query re as select empno, deptno,sal from scott.emp 現(xiàn)在在源頭數(shù)據(jù)表scott.ori作dml操作 drop table scott.emp_ori purge create table scott.emp_ori as select empno, deptno,sal from scott.emp where empno 2000 select * from scott.emp_ori drop materialized view ceno.mv_emp create materialized view ceno.mv_emp

3、build immediate refresh on commit enable query rewrite as select empno, deptno,sal from scott.emp_ori ora-12054: 無(wú)法為實(shí)體化視圖設(shè)置 on commit 刷新屬性 alter table scott.emp_ori a consaintpk_emp_ori primary key (empno) create materialized view ceno.mv_emp build immediate refresh on commit enable query rewrite as

4、 select empno, deptno,sal from scott.emp_ori ok -勝利創(chuàng)建 建立refresh on commit 需要源表上有主鍵存在？ 等一會(huì)兒再舉行研究 看看原表與mv的數(shù)據(jù) select empno,deptno,sal, '源數(shù)據(jù)' _source from scott.emp_ori union all select empno,deptno,sal, '目標(biāo)數(shù)據(jù)' date_source froeno.mv_emp 很多據(jù) insert into scott.emp_ori select 對(duì)源表舉行dml操作 ins

5、ert into scott.emp_ori select empno, deptno,sal from scott.emp where empno 7400 select empno,deptno,sal, '源數(shù)據(jù)' date_source from scott.emp_ori union all select empno,deptno,sal, '目標(biāo)數(shù)據(jù)' date_source fromceno.mv_emp commit; select empno,deptno,sal, '源數(shù)據(jù)' date_source from scott.em

6、p_ori union all select empno,deptno,sal, '目標(biāo)數(shù)據(jù)' date_source fromceno.mv_emp update scott.emp_ori sal=600 select empno,deptno,sal, '源數(shù)據(jù)' date_source from scott.emp_ori union all select empno,deptno,sal, '目標(biāo)數(shù)據(jù)' date_source fromceno.mv_emp commit; delete from scott.emp_ori selec

7、t empno,deptno,sal, '源數(shù)據(jù)' date_source from scott.emp_ori union all select empno,deptno,sal, '目標(biāo)數(shù)據(jù)' date_source fromceno.mv_emp commit; select empno,deptno,sal, '源數(shù)據(jù)' date_source from scott.emp_ori union all select empno,deptno,sal, '目標(biāo)數(shù)據(jù)' date_source fromceno.mv_emp m

8、erge into scott.emp_ori t using (select empno,deptno,sal from scott.emp where empno 7600) s on (t.empno=s.empno) when matched then update set t.deptno=s.deptno, t.sal=s.sal when not matched then insert (t.empno,t.deptno,t.sal) values(s.empno,s.deptno,s.sal) select empno,deptno,sal, '源數(shù)據(jù)' dat

9、e_source from scott.emp_ori union all select empno,deptno,sal, '目標(biāo)數(shù)據(jù)' date_source fromceno.mv_emp commit; select empno,deptno,sal, '源數(shù)據(jù)' date_source from scott.emp_ori union all select empno,deptno,sal, '目標(biāo)數(shù)據(jù)' date_source fromceno.mv_emp 看到源數(shù)據(jù)與目標(biāo)數(shù)據(jù)在提交后，立刻被全都化。 之前，我們說(shuō)了，數(shù)據(jù)庫(kù)comm

10、it事實(shí)上并沒(méi)有多少工作要做，那怕之前的事物是一個(gè)很大的事物，commit也是眨眼完成工作。 但是有時(shí)候，提交卻要需要很長(zhǎng)時(shí)光，最有可能的緣由是可能有一個(gè)mv建立在該表上，需要較長(zhǎng)的時(shí)光將變幻數(shù)據(jù)刷新到目標(biāo)物化視圖。 檢查一張表上是否有物化視圖 由于query是long類(lèi)型的字段，所以不能用以下辦法來(lái)檢查 select * from all_mviews wherelower(to_lob(query) like '%scott.emp_ori%' 采納以下辦法 create table mv_info ( owner varchar2(32), mview_name varc

11、har2(30), query clob ) insert into mv_info select owner,mview_name,to_lob(query) from all_mviews select * from mv_info where lower(query)like '%scott.emp_ori%' 疑問(wèn) 這個(gè)選項(xiàng)如何能夠影響到源表的行為 看看這個(gè)物化視圖的完整腳本 create materialized view ceno . mv_emp organization heap pctfree 10 pctused 40 initrans 1 maxtrans

12、 255 nocompress logging storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace users build immediate using index pctfree 10 initrans 2 maxtrans 255 storage(initial 65536 next 1048576 minextents 1 maxextents 21

13、47483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace users refresh force on commit with primary key using default local rollback segment using enforced constraints enable query rewrite asselect empno, deptno,sal from scott.emp_ori 躲藏在mv之后的一些問(wèn)題 sql desc mv_emp name typ

14、e nullable defaultcomments - - - - empno number(4) deptno number(2) y sal number(7,2) y 現(xiàn)在我們轉(zhuǎn)變表名 conn scott/tigergq rename emp_ori to emp_ori1 再查看mv_emp的腳本，發(fā)覺(jué)依舊還是用法emp_ori表，這和依靠于emp_ori其它的對(duì)象不一樣。 plain plan for select * from mv_emp where empno=7369 select * from table(dbms_xplan.display() - | id |ope

15、ration |name | rows | bytes | cost (%cpu)| time | - | 0 | selectstatement | | 1 | 39 | 1 (0)| 00:00:01| | 1 | mat_view access by index rowid| mv_emp | 1 | 39 | 1 (0)| 00:00:01 | |* 2 | index unique scan | pk_emp_ori | 1 | | 1 (0)| 00:00:01| - selectdbms_metadata.get_ddl('index','pk_emp_o

16、ri') from al create unique index ceno . pk_emp_ori on ceno . mv_emp ( empno ) pctfree 10 initrans 2 maxtrans 255 storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace users 我們并沒(méi)有在mv_emp的empno上建立唯一性索引，它是建立mv的時(shí)候自動(dòng)建立上的。 修改原表的名稱(chēng) rename emp_ori to emp_ori1 create table emp_ori as select * from emp_ori1 select * from emp_ori insert into emp_ori values(1000,20,12) commit; 沒(méi)有看到mv_emp變幻 alter table scott.emp_ori add constraint pk_emp_ori1primary key (empno) ; insert