Oracle-day03 中

標籤：Oracle

二、物化視圖
（一）什麼是物化視圖
視圖是一個虛擬表（也可以認為是一條語句），基於它建立時指定的查詢語句返回的結果集。每次訪問它都會導致這個查詢語句被執行一次。為了避免每次訪問都執行這個查詢，可以將這個查詢結果集儲存到一個物化視圖（也叫實體化視圖）。
物化視圖與普通的視圖相比的區別是物化視圖是建立的副本，它類似於一張表，需要佔用儲存空間。而對一個物化視圖查詢的執行效率與查詢一個表是一樣的。
（二）建立物化視圖文法

CREATE METERIALIZED VIEW view_name[BUILD IMMEDIATE | BUILD DEFERRED ]REFRESH [FAST|COMPLETE|FORCE][ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT(next_time)ASsubquery

BUILD IMMEDIATE 是在建立物化視圖的時候就產生資料
BUILD DEFERRED 則在建立時不產生資料，以後根據需要再產生資料。
預設為 BUILD IMMEDIATE。
重新整理（REFRESH）：指當基表發生了 DML 操作後，物化視圖何時採用哪種方式和基表進行同步。
REFRESH 後跟著指定的重新整理方法有三種：FAST、COMPLETE、FORCE。FAST重新整理採用增量重新整理，只重新整理自上次重新整理以後進行的修改。COMPLETE 重新整理對整個物化視圖進行完全的重新整理。如果選擇 FORCE 方式，則 Oracle 在重新整理時會去判斷是否可以進行快速重新整理，如果可以則採用 FAST 方式，否則採用 COMPLETE的方式。FORCE 是預設的方式。
重新整理的模式有兩種：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要
手動重新整理物化視圖（預設）。ON COMMIT 指在基表發生 COMMIT 操作時自動重新整理。
（三）案例
1.建立手動重新整理的物化視圖
需求：查詢地址 ID,位址名稱和所屬地區名稱, 結果如下：

語句：

create materialized view mv_addressasselect ad.id,ad.name adname,ar.name ar_namefrom t_address ad,t_area arwhere ad.areaid=ar.id

執行上邊的語句後查詢

select * from mv_address;
查詢結果如下：

這時，我們向地址表（T_ADDRESS）中插入一條新記錄，

insert into t_address values(8,‘宏福苑小區‘,1,1);
再次執行上邊的語句進行查詢，會發現新插入的語句並沒有出現在物化視圖中。
我們需要通過下面的語句（PL/SQL），手動重新整理物化視圖：

beginDBMS_MVIEW.refresh(‘MV_ADDRESS‘,‘C‘);end;

或者通過下面的命令手動重新整理物化視圖：

EXEC DBMS_MVIEW.refresh(‘MV_ADDRESS‘,‘C‘);
注意：此語句需要在命令視窗中執行。
執行此命令後重新查詢物化視圖，就可以查詢到最新的資料了。
DBMS_MVIEW.refresh 實際上是系統內建的預存程序，關於預存程序我們在第 4章會詳細講解。
2.建立自動重新整理的物化視圖，和上例一樣的結果集
語句如下：

create materialized view mv_address2refreshon commitasselect ad.id,ad.name adname,ar.name ar_namefrom t_address ad,t_area arwhere ad.areaid=ar.id

建立此物化視圖後，當 T_ADDRESS 表發生變化時，MV_ADDRESS2 自動跟著改變。
3.建立時不產生資料的物化視圖

create materialized view mv_address3build deferredrefreshon commitasselect ad.id,ad.name adname,ar.name ar_namefrom t_address ad,t_area arwhere ad.areaid=ar.id;

建立後執行下列語句查詢物化視圖

select * from mv_address3
查詢結果：

執行下列語句產生資料

beginDBMS_MVIEW.refresh(‘MV_ADDRESS3‘,‘C‘);end;

重新查詢，得到結果：

由於我們建立時指定的 on commit ,所以在修改資料後能立刻看到最新資料，無須再次執行 refresh
4.建立增量重新整理的物化視圖
如果建立增量重新整理的物化視圖，必須首先建立物化視圖日誌

create materialized view log on t_address with rowid；create materialized view log on t_area with rowid

建立的物化視圖日誌名稱為 MLOG$_表名稱
建立物化視圖

create materialized view mv_address4refresh fastasselect ad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.nameadname,ar.name ar_namefrom t_address ad,t_area arwhere ad.areaid=ar.id;

注意：建立增量重新整理的物化視圖，必須：

1. 建立物化視圖中涉及表的物化視圖日誌。
2. 在查詢語句中，必須包含所有表的 rowid ( 以 rowid 方式建立物化視圖日誌 )
   當我們向地址表插入資料後，物化視圖日誌的內容：
   
   SNAPTIME$$：用於表示重新整理時間。
   DMLTYPE$$：用於表示 DML 操作類型，I 表示 INSERT，D 表示 DELETE，U表示 UPDATE。
   OLD_NEW$$：用於表示這個值是新值還是舊值。N（EW）表示新值，O（LD）表示舊值，U 表示 UPDATE 操作。
   CHANGE_VECTOR$$：表示修改向量，用來表示被修改的是哪個或哪幾個欄位。
   此列是 RAW 類型，其實 Oracle 採用的方式就是用每個 BIT 位去映射一個列。插入操作顯示為：FE, 刪除顯示為：OO 更新操作則根據更新欄位的位置而顯示
   不同的值。
   當我們手動重新整理物化視圖後，物化視圖日誌被清空，物化視圖更新。

beginDBMS_MVIEW.refresh(‘MV_ADDRESS4‘,‘C‘);end;

三、序列
（一）什麼是序列
序列是 ORACLE 提供的用於產生一系列唯一數位資料庫物件。
（二）建立與使用簡單序列
建立序列文法：

create sequence 序列名稱
通過序列的偽列來訪問序列的值
NEXTVAL 返回序列的下一個值
CURRVAL 返回序列的當前值
注意：我們在剛建立序列後，無法提取當前值，只有先提取下一個值時才能再次提取當前值。
提取下一個值

select 序列名稱.nextval from dual
提取當前值

select 序列名稱.currval from dual
（三）建立複雜序列
文法：

CREATESEQUENCEsequence //建立序列名稱[INCREMENT BYn]//遞增的序列值是 n如果 n是正數就遞增,如果是負數就遞減 預設是 1[STARTWITHn]//開始的值,遞增預設是 minvalue遞減是 maxvalue[{MAXVALUEn |NOMAXVALUE}]//最大值[{MINVALUEn |NOMINVALUE}]//最小值[{CYCLE| NOCYCLE}]//迴圈/不迴圈[{CACHEn |NOCACHE}];//分配並存入到記憶體中

（四）案例
1.有最大值的非迴圈序列
建立序列的語句：

create sequence seq_test1increment by 10start with 10maxvalue 300minvalue 20

以上的錯誤，是由於我們的開始值小於最小值 。開始值不能小於最小值，修改
以上語句：

create sequence seq_test1increment by 10start with 10maxvalue 300minvalue 5

我們執行下列語句提取序列值，當序列值為 300（最大值）的時候再次提取值，
系統會報異常資訊。

2.有最大值的迴圈序列

create sequence seq_test2increment by 10start with 10maxvalue 300minvalue 5cycle ;

當序列當前值為 300（最大值），再次提取序列的值

select seq_test2.nextval from dual
提取的值為：

由此我們得出結論，迴圈的序列，第一次迴圈是從開始值開始迴圈，而第二次循
環是從最小值開始迴圈。
思考問題：
下列語句是否會報錯？為什嗎？

create sequence seq_test3increment by 10start with 10minvalue 5cycle ;

答：此為錯誤的語句。因為你建立的是一個迴圈的序列，所以必須指定最大值，
否則會報錯。
3.帶緩衝的序列
我們執行下列語句：

create sequence seq_test3increment by 10start with 10maxvalue 300minvalue 5cyclecache 50;

我們執行上邊語句的意思是每次取出 50 個緩衝值，但是執行會提示錯誤

上邊錯誤提示的意思是：緩衝設定的數必須小於每次迴圈的數。
我們緩衝設定的值是 50，而最大值是 300，那麼為什麼還會提示這樣的資訊呢？
其實我們的 cache 雖然是 50，但是我們每次增長值是 10。這樣 50 次緩衝提取出
的數是 500 （50*10）
我們更改為下列的語句：

create sequence seq_test4increment by 10start with 10maxvalue 500minvalue 10cyclecache 50;

下列語句依然會提示上邊的錯誤，這是因為還存在一個 minvalue ，minvalue 和
maxvalue 之間是 490 個數，也就是一次迴圈可以提取 490，但是我們的緩衝是
500。
我們再次修改語句：

create sequence seq_test5increment by 10start with 10maxvalue 500minvalue 9cyclecache 50;

把最小值減 1，或把最大值加 1，都可以通過。
（五）修改和刪除序列
修改序列：使用 ALTER SEQUENCE 語句修改序列，不能更改序列的 START
WITH 參數

ALTER SEQUENCE 序列名稱 MAXVALUE 5000 CYCLE;
刪除序列：

DROP SEQUENCE 序列名稱;

Oracle-day03 中