Oracle物化視圖

標籤：force   實體   取數   儲存   時間   存在   comm   簡單   彙總   

/*
Oracle物化視圖
*/
/*
為什麼要使用物化視圖？
物化視圖作為sql調優的一種手段廣泛用在資料倉儲環境下。
大量的長查詢語句，處理的不好，就會引起嚴重的I/O問題。
*/
/*
什麼是物化視圖？
物化視圖和普通視圖相對應，在Oracle使用普通視圖時會重複執行建立視圖的所有語句。
物化視圖簡單來說就是具有實體儲存體的特殊視圖，占物理空間就像表一樣。
物化視圖基於表、物化視圖等建立，需要和源表進行同步，不斷重新整理新物化視圖中的資料。
兩個重要概念：查詢重寫和物化視圖同步
*/
/*
物化視圖的特點？
1、物化視圖可用於預先計算並儲存表串連或聚集等消耗較多的操作結果，這樣在這執行2查詢的時候就可以避免進行這些耗時的操作，快速得到結果。
2、使用物化視圖可以提高查詢效能。
3、物化視圖對應用透明，即增加和刪除物化視圖不會對應用程式中正執行的SQL語句帶來影響。
4、物化視圖需要佔用儲存空間，當基表發生變化時，物化視圖也需要重新整理。
5、物化視圖還支援通過資料庫鏈來做資料複製；
*/
/*
物化視圖可分為三種類型：
包含聚集的物化視圖
只包含串連的物化視圖
嵌套物化視圖
*/
/*
物化視圖的建立方式
BUILD IMMEDIATE 在建立物化視圖時產生資料
BUILD DEFERRED 在建立物化視圖時不產生資料以後根據需要產生資料
預設的建立方式是：BUILD IMMEDIATE
*/
/*
物化視圖的查詢重寫
什麼是查詢重寫？
對SQL語句進行重寫。當使用者使用SQL語句對基表進行查詢時，如果已經建立了基於這些基表的物化視圖，Oracle將自動計算和使用物化視圖來完成查詢。在某些情況下可以節約查詢時間，減少系統I/O，Oracle的這種查詢最佳化技術成為查詢重寫.
ENABLE QUERY REWRITE
DISABLE QUERY REWRITE
是否支援查詢重寫
查看命令：
show parameter query_rewrite_enabled;
*/
/*
重新整理
當基表發生了DML操作後即當基表發生變化時，物化視圖需要同步資料以更新物化視圖中的資料。
ON DEMAND:物化視圖可以再使用者需要的時候進行重新整理，可以手動通過DBMS_MVIEW.refresh，可以通過JOB定時重新整理。
ON COMMIT：物化視圖在對基表的DML操作事務提交的同時進行重新整理.
選擇重新整理方式後還需要選擇重新整理類型，重新整理的類型有四種：
FAST【增量重新整理，只重新整理上次以後的修改】,
COMPLETE【整個重新整理物化視圖】,
FORCE【Oracle在重新整理是會判斷是否可以快速重新整理，可以就使用FAST,不可以則使用COMPLETE】,
NEVE【不會對視圖進行任何重新整理】
預設值是 FORCE ON DEMAND即在需要的時候進行快速重新整理
*/
/*
物化視圖日誌
如果需要快速重新整理，則需要建立物化視圖日誌，物化視圖日誌根據不同物化視圖的快速重新整理的需要，可以建立為 ROWID或者PRIMARY KEY類型的，還可以選擇是否
包括SEQUENCE,INCLUDING NEW VALUE以及指定列的列表。
物化視圖日誌可以記錄主表被更新的記錄的主鍵、ROWID或者標識對象或者全部。
物化視圖也支援帶子查詢的物化視圖的快速重新整理。
物化視圖日誌表明為MLOG$_後面跟主體對象的名稱。
雖然主鍵物化視圖日誌和ROWID物化視圖日誌的差別很小,但是這些差別在實際使用中有著很大的影響。
*/
/*
在建立物化視圖時可以指定ORDER BY語句，使產生的資料按照一定的順序儲存,但是這個語句不會寫入物化視圖的定義中，而且對以後的重新整理也無效。
*/
/*
ONPREBUILDTABLE語句將物化視圖建立在一個已經存在的表上，
此時。物化視圖必須和表必須同名。當刪除物化視圖時不會刪除同名的表。
這種物化視圖的查詢重寫要求參數
QUERY_REWRITE_INTEGRITY必須設定為trusted或者stale_tolerated
*/
/*物化視圖的使用*/
--例子1
/*在使用者mv1查詢scott使用者的emp表，並在mv1下能擷取到scott.emp的即時更新*/
sqlplus / as sysdba
create user mv1 identified by oracle;--建立mv1使用者
grant connect,resource to mv1;--給mv1授權
grant select on scott.emp to mv1;--將查詢scott.emp
grant create materialized view to mv1;--把物化視圖的許可權授予mv1
grant execute on dbms_mview to mv1;
conn mv1/oracle
select * from scott.emp;--測試在mv1下是否能查詢scott的emp表
create materialized view emp as select * from scott.emp;--在mv1下建立物化視圖
select * from emp;--測試建立的物化試圖
--開啟新終端對scott的emp表進行更新
update emp set sal=sal-2;
commit;
--此時scott中emp表sal欄位已更新，但是mv1中的物化視圖沒有同步
--因為還沒有定義同步方式
conn mv1/oracle
--人工同步
exect dbms_mview.refresh(‘emp‘,‘c‘);--c表示採用COMPLETE完全重新整理
--完全重新整理相當於把原來表中的資料完全刪掉
--可以使用FAST快速重新整理，
exec dbms_mview.refresh(‘emp‘,‘f‘);
/*
提示如下錯誤
BEGIN dbms_mview.refresh(‘emp‘,‘f‘); END;
*
第 1 行出現錯誤:
ORA-23413: 表 "SCOTT"."EMP" 不帶實體化視圖日誌
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: 在 line 1
*/
--沒有建立物化視圖日誌
--物化視圖日誌是使用者選擇了FAST重新整理類型時要使用的，以增量同步處理基表的變化
conn / as sysdba
grant create materialized view to scott;
conn scott/123
create materialized view log on emp;
grant on commit refresh on scott.emp to mv1;
conn mv1/oracle
drop materialized view emp;
conn / as sysdba
grant select any table to mv1;--不做這個授權在mv1下建立重新整理視圖可能提示表或視圖不存在
create materialized view emp refresh fast on commit as select * from scott.emp;
--測試成功！修改scott使用者下的emp表，在mv1使用者下查詢檢視emp同步更新
--建立一個每個一分鐘重新整理的物化視圖
create materialized view empf refresh fast start with sysdate next sysdate+1/1440 as select * from scott.emp;
--建立一個可更新的物化視圖
create materialized view empu refresh fast for update as select * from scott.emp;
--例子2
/*
同步遠端資料庫某個表的資料
遠端資料庫名：username
密碼：password
SID:CPEES
需同步的表：table1
*/
--建立DB_LINK
create DATABASE LINK to_cpees
CONNECT TP "username" indentified by "password"
using "CPEES"
--在遠端資料庫上表上建快速重新整理日誌
create materialized view log on table1 with primary key including new values;
--在本地建立物化視圖
create materialized view table1
build immediate--建立物化視圖時產生資料
refresh fast with primary key--根據主表主鍵增量快速重新整理
on demand--在使用者需要時由使用者重新整理
enable query rewrite --可讀寫
as
select * from [email protected]_cpees;--查詢語句
--每月不定時重新整理
create or replace procdure p_mview_refresh as
begin
dbms_mview.refresh(‘table1‘,‘f‘);
end p_mview_refresh;
--注意：1、如果需要同時重新整理多個物化視圖，必須用逗號把各個物化視圖名稱串連起來，並對每個視圖都要指明重新整理方式（f、增量重新整理，c、完全重新整理，?、強制重新整理）。例如：dbms_mview.refresh(‘table1,table2‘,‘ff‘);
-- 2、當日誌和物化視圖建立好後，刪除日誌，則需要重新建立新的物化視圖，否則無法使用增量重新整理
-- 3、主鍵更新，表必須有主鍵
--關於刪除
--刪除是日誌和物化視圖要分開刪除
DROP MATERIALIZED VIEW LOG ON [email protected];
DROP MATERIALIZED VIEW table1;
/*
其他概念：
物化視圖可以進行分區。
物化視圖是包括一個查詢結果的資料庫物件，是遠端資料的本機複本，或者用來產生基於資料表求和的匯總表。
物化視圖儲存基於遠端資料表的資料也可以稱為快照。
物化視圖可以基於表、視圖和其他物化視圖查詢。
關於複製：物化視圖允許你在本地維護遠端資料的副本，這些副本是唯讀。如果想修改本機複本必須使用進階複製功能。
當你像從一個表或視圖中抽取資料時，可以從物化視圖中抽取。對於資料倉儲，建立物化視圖通常情況下是彙總檢視，單一表彙總和串連視圖。
*/

Oracle物化視圖