Oracle 11g R2 視圖

視圖是一個(gè)虛表，不占用物理空間，因?yàn)橐晥D本身的定義語句存儲(chǔ)在數(shù)據(jù)字典里。視圖中的數(shù)據(jù)是從一個(gè)或多個(gè)實(shí)際的表中獲得。

物化視圖：也成實(shí)體化視圖，含有實(shí)際數(shù)據(jù)，占用存儲(chǔ)空間，在數(shù)據(jù)倉庫中經(jīng)常應(yīng)用物化視圖

創(chuàng)建視圖的語法

CREATE [OR REPLACE] [FORCE |NO FORCE] VIEWview_name [(alias [,alias]..)] as select_statement [WITH CHECK_OPTION[CONSTRAINT constraint]] [WITH READ ONLY];

在語法中

OR REPLACE:如果視圖已經(jīng)存在，此選項(xiàng)將重新創(chuàng)建該視圖。

FORC:如果使用此關(guān)鍵字，則無論基表是否存在，都將創(chuàng)建視圖

NO FORCE:這是默認(rèn)值，如果使用此關(guān)鍵字，則僅當(dāng)基表存在時(shí)才創(chuàng)建視圖

VIEW_NAME:要?jiǎng)?chuàng)建的視圖名

ALIAS:指定由視圖的查詢所選擇的的表達(dá)式或列的別名。別名的數(shù)目必須與視圖所選擇的的表達(dá)式的數(shù)據(jù)相匹配。

select_statement:SELECT 語句

WITH CHECK_OPTION：此選項(xiàng)指定只能插入或更新視圖可以訪問的行，constraint標(biāo)識CHECK OPTION約束指定的名稱

WITH READ ONLY:此選項(xiàng)保證不能再視圖上執(zhí)行任何修改操作。

創(chuàng)建帶有錯(cuò)誤的視圖

如果在CREATE VIEW語法中使用FORCE選項(xiàng)，即使存在以下情況，也會(huì)創(chuàng)建視圖

視圖定義的查詢引用了一個(gè)不存在的表

視圖定義的查詢引用了現(xiàn)有表中無效的列。

視圖的所有者沒有所需的權(quán)限。

在這些情況下，oracle僅檢查CREATE VIEW語句中語法錯(cuò)誤，如果語法正確，將會(huì)創(chuàng)建視圖，并將視圖的定義存儲(chǔ)在數(shù)據(jù)字典中，但是該視圖卻不能使用。這種視圖被認(rèn)為是帶有錯(cuò)誤創(chuàng)建的。可以用SHOW ERRORS VIEW視圖名來查看錯(cuò)誤

對單表的視圖操作
SQL> create table order_master (ordernonumber(5) CONSTRAINT p_ord PRIMARY KEY,

2 odate DATE,vencode number(5),

3 o_status char(1));

插入數(shù)據(jù)

SQL> insert into order_master values (1,to_date('2010-01-01','yyyy-mm-dd'),1,'a');

SQL> insert into order_master values(2,to_date('2011-01-01','yyyy-mm-dd'),2,'p');

創(chuàng)建訂單狀態(tài)為"p"的視圖，提示沒有創(chuàng)建視圖的權(quán)限

授予SCOTT用戶創(chuàng)建視圖的權(quán)限

創(chuàng)建視圖

SQL> create view pen_view as select *from order_master where o_status = 'p';

查詢視圖

通過視圖修改數(shù)據(jù)，將狀態(tài)為“p”的訂單修改為“d”

SQL> update pen_view SET o_status='d'where o_status='p';

如果修改成功，在查詢視圖將查詢不出任何記錄，因?yàn)樾薷牧藙?chuàng)建視圖是作為條件的列

為了避免修改視圖后查詢不到記錄的現(xiàn)象，使用with check option語句創(chuàng)建檢查約束以防止上述情況的發(fā)生，同時(shí)可以使用CONSTRAINT指定約束名稱

SQL> create or replace view pen_view asselect * from order_master where o_status='p'

2 with check option constraintpenv;

更新視圖

SQL> update pen_view set o_status='d'where o_status='p';

提示with check option違反where子句

創(chuàng)建只讀視圖
SQL> create or replace view pen_view asselect * from order_master with read only;

查看視圖

為視圖插入記錄

創(chuàng)建帶有錯(cuò)誤的視圖

因?yàn)椴淮嬖趘enmast表

創(chuàng)建表venmast

SQL> create table venmast (id int);

手動(dòng)編譯剛才創(chuàng)建的錯(cuò)誤視圖

查看視圖

創(chuàng)建帶ORDER BY子句的視圖
SQL> create or replace view pen_view asselect * from order_master order by orderno;

復(fù)雜視圖
DML語句是指用于修改數(shù)據(jù)的INSERT，DELETE，UPDATE語句。因?yàn)橐晥D是一個(gè)虛表，所以這些語句也可以與視圖一同使用。一般情況下不通過視圖修改數(shù)據(jù)，而是直接修改基本表，因?yàn)檫@樣調(diào)理更清晰。在視圖上使用DML語句有如下限制：（相對于表）

DML語句只能修改視圖中的一個(gè)基表

如果對記錄的修改違反了基表的約束條件，則將無法更新視圖

如果創(chuàng)建的視圖包含連接運(yùn)算符，DISTINCT運(yùn)算符，集合運(yùn)算符，聚合函數(shù)和group BY子句，則將無法更新視圖。

如果創(chuàng)建的視圖包含偽列或表達(dá)式，則將無法更新視圖。

簡單視圖基于單個(gè)基表，不包括函數(shù)和分組函數(shù)，那么可以在此視圖中進(jìn)行INSERT，UPDATE,DELETE操作。這些操作實(shí)際上是在基表中插入、更新和刪除行。

復(fù)雜視圖從多個(gè)提取數(shù)據(jù)，包括函數(shù)和分組函數(shù)，復(fù)雜視圖不一定能進(jìn)行DML操作。

刪除視圖可以使用

物化視圖
物化視圖是和普通視圖相對應(yīng)的，在oracle使用普通視圖時(shí)，它會(huì)重復(fù)執(zhí)行創(chuàng)建視圖的所有sql語句，如果這樣的SQL語句含有多張表的連接或者ORDER BY子句，而且表的數(shù)據(jù)量很大，則會(huì)非常耗時(shí)，效率非常低下。為了解決這個(gè)問題，oracle提出了物化視圖的概念

物化視圖就是具有物理存儲(chǔ)的特殊視圖，占用物理空間，就象表一樣，物化視圖是基于表，物化視圖等創(chuàng)建的。它需要和源表進(jìn)行同步，不斷的刷新物化視圖中的數(shù)據(jù)。物化視圖有兩個(gè)重要概念：查詢重寫和物化視圖的同步

查詢重寫：

對SQL語句進(jìn)行重寫。當(dāng)用戶使用SQL語句對基表進(jìn)行查詢時(shí)，如果已經(jīng)建立了基于這些基表的物化視圖，oracle將自動(dòng)計(jì)算和使用物化視圖來完成查詢，在某些情況下可以節(jié)約查詢時(shí)間，減少系統(tǒng)I/O。這種查詢優(yōu)化技術(shù)成為查詢重寫。參數(shù)QUERY_REWRITE_ENABLED決定是否使用重寫查詢。在創(chuàng)建物化視圖時(shí)需要使用ENABLE QUERY REWRITE來啟動(dòng)查詢重寫功能

可通過SHOW命令查看該參數(shù)的值

物化視圖的同步：

物化視圖是基于表創(chuàng)建的，所以當(dāng)基表發(fā)生變化時(shí)，需要同步數(shù)據(jù)以更新物化視圖中的數(shù)據(jù)，這樣保持無話視圖中的數(shù)據(jù)和基表的數(shù)據(jù)的一致性。oracle提供了兩種物化視圖刷新方式

ON COMMIT:指物化視圖在對基表的DML操作事物提交的通行進(jìn)行刷新

ON DEMAND：指物化視圖在用戶需要的時(shí)候進(jìn)行刷新，可以手工通過DBMS_IVIEW.refresh等方法來進(jìn)行刷新，也可以通過JOB定時(shí)刷新

選擇刷新方式后，還需要選擇一種刷新類型，刷新類型值刷新時(shí)基表與物化視圖如何實(shí)現(xiàn)數(shù)據(jù)同步，oracle提供了一下4種刷新類型：

COMPLETE:對整個(gè)物化視圖進(jìn)行完全刷新。

FAST:采用增量刷新，只刷新自上次刷新以后進(jìn)行的修改

FORCE：oracle在刷新會(huì)判斷是否可以進(jìn)行快速刷新，如果可以則采用FAST刷新方式，否則使用COMPLETE方式。

NEVER:物化視圖不進(jìn)行任何刷新

創(chuàng)建物化視圖
創(chuàng)建物化視圖的前提條件：

具備創(chuàng)建物化視圖的權(quán)限，QUERY REWRITE的權(quán)限，以及對創(chuàng)建物化視圖所涉及的表的訪問權(quán)限和創(chuàng)建表的權(quán)限

使用SCOTT用戶來舉例說明

1.授予相應(yīng)的權(quán)限

SQL> show user;

USER is "SYS"

SQL> grant create materialized view toscott;

SQL> grant query rewrite to scott;

SQL> grant create any table to scott;

SQL> grant select any table to scott;

2.創(chuàng)建物化視圖日志

物化視圖日志是用戶選擇了FAST刷新類型時(shí)需要使用的，以增量同步基表的變化。

對SCOTT用戶的EMP表和DEPT表創(chuàng)建物化視圖，所以對這兩個(gè)基表創(chuàng)建物化視圖日志

SQL> create materialized view log ondept with rowid;

SQL> create materialized view log on empwith rowid;

創(chuàng)建物化視圖

通過CREATE MATERIALIEZED VIEW語句來創(chuàng)建物化視圖，

SQL> create materialized viewmtrlview_test

2 build immediate

3 refresh fast

4 on commit

5 enable query rewrite as

6 selectd.dname,d.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,d.rowid d_rowid,e.rowide_rowid

7 from dept d,emp e whered.deptno=e.deptno;

其中：

BUILD IMMEDIATE:該參數(shù)的意思是立即創(chuàng)建物化視圖；也可以選擇BUILD DEFFERED,該參數(shù)說明在物化視圖定義以后不會(huì)立即執(zhí)行，而是延遲執(zhí)行，在使用該視圖在創(chuàng)建。

REFRESH FAST：刷新數(shù)據(jù)的類型選擇FAST類型

ON COMMIT：在基表有更新時(shí)提交后立即更新物化視圖

ENABLE QUERY REWRITE :啟動(dòng)查詢重寫功能，在創(chuàng)建物化視圖是明確說明啟用查詢重寫功能。

AS：定義后面的查詢語句

查詢體：物化視圖的查詢內(nèi)容。該SQL語句的查詢結(jié)果集輸出到物化視圖中，保存在由oracle自動(dòng)創(chuàng)建的表中。

刪除物化視圖

如果對此有興趣，請掃下面二維碼免費(fèi)獲取更多詳情