5.6 索引

索引具有兩個(gè)功能：一是強(qiáng)制實(shí)施主鍵約束和唯一約束，二是提高性能。應(yīng)用程序的索引策略對(duì)于性能至關(guān)重要。索引管理所屬的范圍沒有明確界限。當(dāng)業(yè)務(wù)分析師指定將要實(shí)施為約束的業(yè)務(wù)規(guī)則時(shí)，他們實(shí)際在指定索引。數(shù)據(jù)庫管理員將監(jiān)視在數(shù)據(jù)庫中運(yùn)行的代碼的執(zhí)行，并將提出有關(guān)索引的建議。開發(fā)人員最了解代碼內(nèi)容和數(shù)據(jù)特點(diǎn)，也將參與到索引策略的開發(fā)中。

5.6.1 為什么索引是必需的

索引是約束機(jī)制的一部分。如果將某列(或一組列)標(biāo)記為表的主鍵，那么，每次在表中插入行時(shí)，Oracle必須檢查是否已經(jīng)存在具有同一主鍵值的行。如果表的列上不具有索引，那么唯一的辦法是掃描整個(gè)表，檢查每一行。如果表僅有數(shù)行，這種做法可以接受，但如果表有數(shù)千行或數(shù)百萬行(或數(shù)十億行)，則不可行。通過索引，可以立即(或近乎立即)訪問鍵值，因此，檢查存在性幾乎可以一揮而就。如果定義了主鍵約束，而主鍵列上尚不存在索引，Oracle將自動(dòng)創(chuàng)建一個(gè)。

唯一約束也需要索引。與主鍵約束的區(qū)別在于，唯一約束的列可以留空。這不影響索引的創(chuàng)建和使用。外鍵約束通過索引來實(shí)施，但索引必須存在于父表中，而并非一定在為其定義約束的表中。外鍵約束將子表中的列與父表中的主鍵或唯一鍵關(guān)聯(lián)起來。在子表中插入行時(shí)， Oracle將在父表中查找索引，在確認(rèn)存在匹配的行后才允許執(zhí)行插入。但是，為了提高性能，始終應(yīng)在子表的外鍵列上創(chuàng)建索引：如果Oracle可以使用索引來確定子表中是否存在引用被刪除行的行，那么在父表上執(zhí)行DELETE操作的速度將大大加快。

索引對(duì)于性能至關(guān)重要。在執(zhí)行包含WHERE子句的任何SQL語句時(shí)，Oracle必須確定要選擇或修改的行。如果WHERE子句中引用的列上沒有任何索引，唯一的途徑是掃描整個(gè)表。全表掃描將讀取表中的每一行，以便找到相關(guān)行。如果表有許多行，那么將耗用很長(zhǎng)時(shí)間。如果相關(guān)的列上存在索引，Oracle將改為搜索索引。索引是有序鍵值列表，其排列方式可使搜索變得更有效。每個(gè)鍵值是指向表行的指針。如果表超過一定的大小，而且要檢索的行的比例低于特定的值，那么，與掃描全表相比，通過索引查找來定位相關(guān)行將快得多。如果表較小，或WHERE子句將檢索表行的大部分，則全表掃描來得更快：通?？梢赃x擇信任Oracle，因?yàn)镺racle可以根據(jù)數(shù)據(jù)庫收集的有關(guān)表和表行的統(tǒng)計(jì)信息，做出是否使用索引的正確決策。

第二種可以使用索引的情況是排序。如果SELECT語句包括ORDER BY、GROUP BY、UNION或其他一些關(guān)鍵詞，則必須按順序排列行。如果有了索引，則可以按正確順序返回行，而無須首先對(duì)它們進(jìn)行排序。

索引有助于提高性能的第三種情況是在聯(lián)接表時(shí)。不過此時(shí)，Oracle仍會(huì)根據(jù)表的大小和可用的內(nèi)存資源做出選擇：將表掃描到內(nèi)存中并在那里將它們聯(lián)接在一起可能比使用索引的速度更快。嵌套循環(huán)聯(lián)接(nested loop join)技術(shù)使用另一個(gè)表上的索引遍歷一個(gè)表來定位匹配行，這通常是一個(gè)磁盤密集型操作。哈希聯(lián)接(hash join)技術(shù)將整個(gè)表讀入內(nèi)存，將其轉(zhuǎn)換為哈希表，然后使用哈希算法定位匹配的行，這是一個(gè)內(nèi)存和CPU更為密集型的操作。排序合并聯(lián)接(sort merge join)在聯(lián)接列上排序表，然后將它們合并在一起：這通常是磁盤、內(nèi)存和CPU資源之間的折中。如果沒有索引，Oracle將在可用的聯(lián)接技術(shù)方面受到嚴(yán)重限制。

5.6.2 索引類型

Oracle 支持多類索引，這些索引有多個(gè)變體。此處描述的兩類索引是B*樹索引(默認(rèn)索引類型)和位圖索引。一般而言，索引將提高檢索數(shù)據(jù)的性能，但會(huì)降低DML操作的性能(原因是必須維護(hù)索引)。每次在表中插入一行時(shí)，必須在表的每個(gè)索引中插入一個(gè)新鍵，這會(huì)給數(shù)據(jù)庫造成更大負(fù)擔(dān)。為此，在事務(wù)處理系統(tǒng)中，通常會(huì)盡量減少索引數(shù)量(可能不超過約束需要的數(shù)量)，而在查詢密集系統(tǒng)(如數(shù)據(jù)倉庫)中，創(chuàng)建足夠多的索引會(huì)起到幫助作用。

1．B*樹索引

B*樹索引(B代表“平衡(balanced)”)是一個(gè)樹結(jié)構(gòu)。樹的根節(jié)點(diǎn)指向第二級(jí)別的多個(gè)節(jié)點(diǎn)，第二級(jí)別的節(jié)點(diǎn)又指向第三級(jí)別的多個(gè)節(jié)點(diǎn)，以此類推。樹的所需深度主要取決于表中的行數(shù)，以及索引鍵值的長(zhǎng)度。

索引樹的葉節(jié)點(diǎn)按順序存儲(chǔ)行鍵，每個(gè)鍵有一個(gè)指針，用來確定行的物理位置。因此，如果要使用索引查找檢索行，而WHERE子句在索引列上使用了相等(equality)謂詞，那么Oracle將沿著樹向下導(dǎo)航，直至找到包含期望鍵值的葉節(jié)點(diǎn)，然后使用指針查找行。如果WHERE子句使用了不等(nonequality)謂詞(如LIKE、BETWEEN、>或<等任何操作符)，則Oracle 可以在樹中向下導(dǎo)航，找到第一個(gè)匹配的值鍵，然后導(dǎo)航索引的葉子節(jié)點(diǎn)來查找其他所有匹配的值。此時(shí)，它將按順序從表中檢索行。

行的指針是rowid。rowid是Oracle專用的虛擬列，每個(gè)表的每一行都有此虛擬列。其中的加密項(xiàng)是行的物理地址。因?yàn)閞owid不是SQL標(biāo)準(zhǔn)的一部分，所以普通的SQL語句永遠(yuǎn)都看不到它們，但可以根據(jù)需要查看和使用它們。如圖5-9所示。

每一行的 rowid 在全局上是唯一的。整個(gè)數(shù)據(jù)庫的每個(gè)表的每一行都有不同的 rowid。rowid 加密項(xiàng)給出了行的物理地址，Oracle 可以基于此地址計(jì)算行在哪個(gè)操作系統(tǒng)文件的哪個(gè)位置，并直接找到它。

如果需要的行數(shù)只占表的總行數(shù)的一小部分，而且表很大，則B*樹是十分有效的檢索行的方式?？紤]以下語句：

        select count(*) from employees
        where last_name between 'A%' and 'Z%';

此WHERE子句涉及廣泛的范圍，將包括表中的每一行。如果通過搜索索引來查找rowid，然后使用rowid來查找行，那么，與掃描全表相比，速度將慢得多。畢竟需要的是整個(gè)表。另外，如果表非常小，一次磁盤讀取就可以對(duì)其進(jìn)行完整掃描，則沒必要首先讀取索引。

一般認(rèn)為，如果查詢要檢索超過2%～4%的行，則全表掃描速度更快。如果在WHERE子句中指定的值是NULL，則會(huì)是一個(gè)例外情況。NULL不會(huì)參與到B*樹索引中，因此，如下查詢：

        select * from employees
        where last_name is null;

將始終導(dǎo)致全表掃描。如果一個(gè)列中包含的唯一值很少，那么，在此列上創(chuàng)建B*樹索引就沒有太大價(jià)值，因?yàn)槠溥x擇性不充分：為了找到每個(gè)不同鍵值而檢索的表的部分過多。一般而言，如果遇到以下情況，應(yīng)該使用B*樹索引：

● 列的基數(shù)(不同值的個(gè)數(shù))很大

● 表的行數(shù)多

● 列用在WHERE子句或JOIN條件中

2．位圖索引

在很多業(yè)務(wù)應(yīng)用程序中，數(shù)據(jù)和查詢的特點(diǎn)致使B*樹索引用處不大。考慮連鎖超市的銷售表，它存儲(chǔ)一年的歷史數(shù)據(jù)，可從幾個(gè)維度進(jìn)行分析。圖5-10顯示了一個(gè)僅有4個(gè)維度的簡(jiǎn)單實(shí)體-關(guān)系圖。

每個(gè)維度的基數(shù)可能很少。做如表5-1的假設(shè)。

假設(shè)數(shù)據(jù)分布均勻，則只有兩個(gè)維度(PRODUCT和DATE)具有的選擇性優(yōu)于2%～4%的常用標(biāo)準(zhǔn)，這使索引變得很有價(jià)值。但是，如果查詢使用范圍謂詞(如計(jì)算一個(gè)月的銷售額， 10個(gè)或更多產(chǎn)品類別的銷售額)，那么，這些也都不符合條件。這是一個(gè)簡(jiǎn)單事實(shí)：B*樹索引在數(shù)據(jù)倉庫環(huán)境中通常無用。一個(gè)典型的查詢是針對(duì)一個(gè)月內(nèi)自購(gòu)客戶對(duì)某類產(chǎn)品的購(gòu)買額來比較兩個(gè)商店。雖然可以在相關(guān)列上建立B*樹索引，但Oracle 將會(huì)忽略它們，因?yàn)槠溥x擇性不充分。正因?yàn)槿绱耍O(shè)計(jì)了位圖索引。

位圖索引將與每個(gè)鍵值關(guān)聯(lián)的rowid存儲(chǔ)為位圖。CHANNEL索引的位圖可能如下：

        WALK-IN     11010111000101011100010101.....
        DELIVERY    00101000111010100010100010.....

這指示前兩行銷售給自購(gòu)客戶，第三次銷售是遞送，第四次銷售是自購(gòu)，以此類推。

SHOP索引的位圖可能為：

        LONDON     11001001001001101000010000.....
        OXFORD     00100010010000010001001000.....
        READING    00010000000100000100100010.....
        GLASGOW    00000100100010000010000101.....

這表示前兩次銷售發(fā)生在London的商店，第三次在Oxford，第四次在Reading，以此類推。如果接收到此查詢：

        select count(*) from sales
        where channel='WALK-IN'
        and shop='OXFORD';

Oracle可以檢索兩個(gè)相關(guān)位圖，并使用布爾“與”操作將它們結(jié)合在一起：

        WALK-IN             11010111000101011100010101.....
        OXFORD              00100010010000010001001000.....
        WALK-IN & OXFORD    00000010000000010000000000.....

按位“與”操作的結(jié)果顯示，只有第7行和第16行符合選擇性標(biāo)準(zhǔn)。位圖的合并速度極快，可使用AND、OR和NOT操作符的任意組合，基于很多列上的很多條件，實(shí)現(xiàn)復(fù)雜的布爾操作。與B*樹索引相比，位圖索引的一個(gè)特別好處在于它們包含NULL。就位圖索引而言，NULL只不過是另一個(gè)具有自己的位圖的不同值而已。

一般而言，在具備以下條件時(shí)使用位圖索引：

● 列的基數(shù)(不同值的個(gè)數(shù))小

● 表中的行數(shù)多

● 列用于布爾代數(shù)運(yùn)算

3．索引類型選項(xiàng)

在創(chuàng)建索引時(shí)，可以應(yīng)用6個(gè)常用選項(xiàng)：

● 唯一或非唯一(Unique or non-unique)

● 反向鍵(Reverse key)

● 壓縮(Compressed)

● 復(fù)合(Composite)

● 基于函數(shù)(Function based)

● 升序或降序(Ascending or descending)

所有這6個(gè)選項(xiàng)都可應(yīng)用于B*樹索引，只有后三個(gè)選項(xiàng)可用于位圖索引。

● 唯一索引不允許重復(fù)值。非唯一索引是默認(rèn)設(shè)置。索引的唯一特性獨(dú)立于唯一約束或主鍵約束：唯一索引不允許插入重復(fù)值，即使沒有定義此類約束，也同樣如此。唯一約束或主鍵約束可以使用非唯一索引；它正好沒有重復(fù)值。實(shí)際上，這是可延遲約束的一項(xiàng)要求，因?yàn)樵谀硞€(gè)階段(提交事務(wù)之前)可能存在重復(fù)值。下一節(jié)將討論約束。

● 反向鍵索引基于將字節(jié)倒置的鍵列版本，例如，將針對(duì)“nhoJ”而非“John”來設(shè)置索引。在完成SELECT時(shí)，Oracle將自動(dòng)反轉(zhuǎn)搜索字符串的值。這是在多用戶系統(tǒng)中避免爭(zhēng)用的功能強(qiáng)大的技術(shù)。例如，如果多位用戶正在并發(fā)地插入具有主鍵(基于連續(xù)增長(zhǎng)的編號(hào))的行，那么，所有索引插入將聚集在索引的高端。通過反轉(zhuǎn)鍵，連貫的索引鍵插入將分布在整個(gè)索引范圍內(nèi)。即使“John”和“Jules”很是接近，“nhoJ”和“seluJ”也相距甚遠(yuǎn)。

● 壓縮索引僅將重復(fù)鍵值存儲(chǔ)一次。默認(rèn)方式是不壓縮，這意味著，如果鍵值不唯一，將為每個(gè)出現(xiàn)的鍵值存儲(chǔ)一次，每次存儲(chǔ)都有一個(gè)rowid 指針。壓縮索引僅將鍵存儲(chǔ)一次，后跟所有匹配rowid的字符串。

● 復(fù)合索引建立在連接兩個(gè)或多個(gè)列的基礎(chǔ)之上。對(duì)于混合的數(shù)據(jù)類型沒有限制。如果搜索字符串不包含所有列，則仍然可以使用索引。但是，如果它不包括最左邊的列，Oracle 將必須使用跳過-掃描(skip-scanning)方法，與包括最左邊的列相比，這種方法的效率會(huì)低得多。

● 基于函數(shù)的索引則基于應(yīng)用于一列或多列的函數(shù)(如UPPER(last_name)或TO_CHAR(startdate, 'ccyy-mm-dd'))的結(jié)果。查詢必須將同一函數(shù)應(yīng)用于搜索字符串，否則Oracle不能使用索引。

默認(rèn)情況下，索引是升序索引，即鍵是按從最低值到最高值的順序排列的。而降序索引與此相反。事實(shí)上，區(qū)別通常并不重要：索引中的條目以雙鏈表的形式存儲(chǔ)，因此，可按相同的速度向上或向下導(dǎo)航，但是，如果使用索引范圍掃描進(jìn)行檢索，那么這將影響行的返回順序。

5.6.3 創(chuàng)建和使用索引

在定義主鍵約束和唯一約束時(shí)，如果相關(guān)列上的索引并不存在，則將隱式創(chuàng)建索引。顯式創(chuàng)建索引的基本語法如下：

        CREATE [UNIQUE | BITMAP] INDEX [ schema.]indexname
        ON [schema.]tablename (column [, column...] ) ;

索引的默認(rèn)類型是非唯一、非壓縮、非反向鍵B*樹索引。無法創(chuàng)建唯一的位圖索引(即便允許，也不會(huì)這么做，因?yàn)檫@會(huì)導(dǎo)致基數(shù)問題)。索引是模式對(duì)象，可能在一個(gè)模式中創(chuàng)建另一個(gè)模式的表上的索引，但大多數(shù)人認(rèn)為這會(huì)造成混淆。復(fù)合索引(composite index)是多個(gè)列上的索引。復(fù)合索引可以在不同數(shù)據(jù)類型的列上，而且列不必在表中相互鄰近。

下面的例子創(chuàng)建了表和索引，然后定義了約束：

        create table dept(deptno number,  dname varchar2(10));
        create table emp (empno number, surname varchar2 (10),
         forename varchar2(10), dob date, deptno number);
        create unique index dept_i1 on dept(deptno);
        create unique index emp_i1 on emp(empno);
        create index emp_i2 on emp(surname, forename);
        create bitmap index emp_i3 on emp(deptno);
        alter table dept add constraint dept_pk primary key (deptno);
        alter table emp add constraint emp_pk primary key (empno);
        alter table emp add constraint emp_fk
        foreign key (deptno) references dept(deptno);

創(chuàng)建的前兩個(gè)索引被標(biāo)記為UNIQUE，意即不允許插入重復(fù)值。此處未將其定義為約束，但實(shí)際上就是這樣。第三個(gè)索引未定義為UNIQUE，即可以接受重復(fù)值；這是兩個(gè)列上的復(fù)合索引。第四個(gè)索引定義為位圖索引，因?yàn)榕c表中的行數(shù)相比，列的基數(shù)占的比例可能很小。

在定義兩個(gè)主鍵約束時(shí)，Oracle將檢測(cè)預(yù)先創(chuàng)建的索引，并使用它們來實(shí)施約束。注意， DEPT.DEPTNO上的索引并非用于提高性能，因?yàn)榇吮砜赡芎苄?，以至于從不?huì)使用索引來檢索行(掃描速度更快)，但使用索引來實(shí)施主鍵約束依然至關(guān)重要。

在創(chuàng)建索引后，索引的使用完全透明化和自動(dòng)化。在執(zhí)行SQL語句之前，Oracle服務(wù)器將評(píng)估所有可能的執(zhí)行方式。其中一些方法涉及使用可用的任何索引，其他方法與此無關(guān)。Oracle將使用在表和環(huán)境上收集的信息，來智能化地確定要使用哪些索引(如果有)。

5.6.4 修改和刪除索引

ALTER INDEX命令不能用于更改本章描述的任何特性：索引類型(B*樹或位圖)，列，以及索引是唯一索引還是非唯一索引。ALTER INDEX命令屬于數(shù)據(jù)庫管理范疇，通常用于調(diào)整索引的物理屬性，不用于調(diào)整開發(fā)人員關(guān)注的邏輯屬性。如果有必要更改其中一些屬性，則必須刪除和重新創(chuàng)建索引。接著前一節(jié)的例子，將EMP_I2更改為包含員工的生日：

        drop index emp_i2;
        create index emp_i2 on emp(surname, forename, dob);

此復(fù)合索引現(xiàn)在包含具有不同數(shù)據(jù)類型的列。列的出現(xiàn)順序正好與表中的定義順序相同，但是，這毫無必要。

在刪除表時(shí)，也將刪除為表定義的所有索引和約束。如果通過創(chuàng)建約束隱式創(chuàng)建了索引，那么，在刪除約束時(shí)也將刪除索引。如果顯式創(chuàng)建了索引，并在后來創(chuàng)建了約束，那么，如果刪除約束，索引將留存下來。

練習(xí)5-5 創(chuàng)建索引

本練習(xí)將為CUSTOMERS表創(chuàng)建、填充、添加一些索引。

(1) 連接到HR模式，創(chuàng)建CUSTOMERS、ORDERS、ORDER_ ITEMS和PRODUCTS表，如下：

        create table customers(customer_id number(8,0) not null,
          join_date date not null, customer_status varchar2(8) not null,
          customer_name varchar2(20) not null, creditrating varchar2(10));

       create table orders (order_id number(8), order_date date, order_status
        varchar2(8), order_amount number(10,2),
        customer_id number(8));

       create table order_items (order_item_id number(8),
        order_id number(8), product_id number(8));

       create table products(product_id number(8),
        product_description varchar2(20), product_status varchar2(8),
        price number (10,2), price_date date, stock_count number(8));

(2) 在客戶名稱和客戶狀態(tài)上創(chuàng)建復(fù)合B*樹索引：

        create index cust_name_i on customers (customer_name, customer_status);

(3) 在一些低基數(shù)列上創(chuàng)建位圖索引：

        create bitmap index creditrating_i on customers(creditrating);

(4) 運(yùn)行下列查詢確定剛創(chuàng)建的索引的名稱及其他一些特性。

        select index_name, column_name, index_type, uniqueness
        from user_indexes natural join user_ind_columns
        where table_name='CUSTOMERS';

5.7 約束

表約束是數(shù)據(jù)庫能夠?qū)嵤I(yè)務(wù)規(guī)則以及保證數(shù)據(jù)遵循實(shí)體-關(guān)系模型的一種手段，其中，實(shí)體-關(guān)系模型由定義應(yīng)用程序數(shù)據(jù)結(jié)構(gòu)的系統(tǒng)分析所確定。例如，機(jī)構(gòu)中的商業(yè)分析師決定使用數(shù)字來唯一標(biāo)識(shí)所有客戶以及所有訂單，在創(chuàng)建客戶之后才能將訂單發(fā)給客戶，而且每張訂單必須包含有效日期和一個(gè)大于零的值。要實(shí)現(xiàn)這個(gè)功能，需要進(jìn)行下列操作：在CUSTOMERS表的CUSTOMER_ID列上和ORDERS表的ORDER_ID列上創(chuàng)建主鍵約束，在引用CUSTOMERS表的ORDERS表上創(chuàng)建外鍵約束，在ORDERS表的DATE列上創(chuàng)建一個(gè)NOT NULL約束(DATE數(shù)據(jù)類型本身能夠自動(dòng)確保所有日期有效，這種數(shù)據(jù)類型并不接受無效的日期)，在ORDERS表的ORDER_ AMOUNT列上創(chuàng)建一個(gè)CHECK約束。

在針對(duì)定義了約束的表執(zhí)行任何DML時(shí)，如果DML違反了約束，則將自動(dòng)回滾整個(gè)語句。注意，如果一個(gè)DML語句影響到多個(gè)行，那么，在特定行遇到約束問題前，此語句可能已經(jīng)局部成功。如果此語句是多語句事務(wù)的一部分，那么，事務(wù)中已經(jīng)成功的語句將保持完好，但不提交。

5.7.1 約束類型

Oracle數(shù)據(jù)庫支持的約束類型如下：

● UNIQUE

● NOT NULL

● PRIMARY KEY

● FOREIGN KEY

● CHECK

約束具有名稱。最好使用標(biāo)準(zhǔn)命名約定指定名稱，如果未顯式指定名稱，Oracle將為其生成名稱。一般用于對(duì)象-關(guān)系交互式操作的REF約束超出了本書的討論范圍。

1．UNIQUE約束

UNIQUE約束要求，對(duì)于列或列組合而言，表中每行的值必須是不同的。如果此約束針對(duì)單個(gè)列，則相應(yīng)的列稱為鍵(key)列。如果約束由多列組成(稱為組合鍵唯一約束)，這些列并不必是相同的數(shù)據(jù)類型，也不必在表定義中相互鄰近。

UNIQUE約束的怪異之處在于，可以在鍵列中輸入NULL值。在鍵列中，可能有任意數(shù)量的包含NULL值的行。這是因?yàn)镹ULL不等于任何東西，甚至不等于另一個(gè)NULL。因此，如果不搜索NULL，則可以確保在鍵列上選擇行時(shí)將僅返回一行；如果搜索NULL，那么，鍵列為NULL的所有行都將返回。

UNIQUE約束通過索引來實(shí)施。在定義UNIQUE約束時(shí)，Oracle將查看鍵列上的索引，如果不存在，就創(chuàng)建一個(gè)。此后，每次插入行時(shí)，Oracle都將查看索引，了解鍵列的值是否已經(jīng)存在。如果已存在，則將拒絕插入。這些索引(稱為B*樹索引)的結(jié)構(gòu)不包含NULL值，正因?yàn)槿绱?，才允許出現(xiàn)多個(gè)包含NULL的行：索引中根本不存在NULL。雖然索引的第一要?jiǎng)?wù)是實(shí)施約束，但也有次生效應(yīng)：如果在SQL語句的WHERE子句中使用鍵列，性能將提高。但是，選擇WHERE key_column IS NULL則不使用索引(因?yàn)樗话∟ULL)，因此總是導(dǎo)致掃描整個(gè)表。

2．NOT NULL約束

NOT NULL約束強(qiáng)制在鍵列中輸入值。它針對(duì)每個(gè)列進(jìn)行定義，有時(shí)被稱為強(qiáng)制列(mandatory column)。如果業(yè)務(wù)要求一組列都具有值，則不能為整個(gè)組定義NOT NULL約束，而必須針對(duì)每列定義NOT NULL約束。

如果嘗試插入沒有為具有NOT NULL約束的列指定值的行，將導(dǎo)致錯(cuò)誤。如3.1節(jié)所述，在創(chuàng)建表時(shí)通過在列上包含DEFAULT子句，可以不指定值。

3．PRIMARY KEY約束

主鍵(primary key)是定位表中單個(gè)行的方式。關(guān)系數(shù)據(jù)庫范例要求每個(gè)表都必須有主鍵，主鍵是用于區(qū)分每行的列或列組合。Oracle數(shù)據(jù)庫的定義與此范例(及其他一些RDBMS實(shí)現(xiàn))有所不同，它允許存在不包含主鍵的表。

主鍵約束的實(shí)現(xiàn)實(shí)際上是UNIQUE和NOT NULL約束的組合。鍵列必須具有唯一值，而且不得為空。與UNIQUE約束一樣，約束列上必須存在索引。如果不存在，將在定義約束時(shí)創(chuàng)建索引。一個(gè)表只能有一個(gè)主鍵，試著創(chuàng)建第二個(gè)，將出現(xiàn)錯(cuò)誤。但是，表可以有任意數(shù)量的UNIQUE和NOT NULL約束列，因此，如果業(yè)務(wù)分析師認(rèn)定多個(gè)列必須是唯一的而且必須填充，則可將其中一個(gè)指定為主鍵，將其他的列指定為UNIQUE和NOT NULL。例如EMPLOYEE表，其中電子郵件地址、社會(huì)保險(xiǎn)號(hào)和員工編號(hào)都是必需的，而且是唯一的。

4．FOREIGN KEY約束

在父子關(guān)系的子表中定義FOREIGN KEY約束。此約束使子表中的列(或列組合)對(duì)應(yīng)父表的主鍵列。這些列不必同名，但數(shù)據(jù)類型必須相同。FOREIGN KEY約束定義數(shù)據(jù)庫的關(guān)系結(jié)構(gòu)：連接第三范式的表的多對(duì)一關(guān)系。

如果父表具有UNIQUE和/或PRIMARY KEY約束，則這些列可用作FOREIGN KEY約束的基礎(chǔ)，即使允許空值，也是如此。

UNIQUE約束允許約束列中出現(xiàn)NULL值，F(xiàn)OREIGN KEY約束也同樣如此。即使父表的行中不存在NULL，也可將行插入包含NULL外鍵列的子表中。這會(huì)創(chuàng)建孤行，并產(chǎn)生令人不快的混亂。一般而言，UNIQUE約束中的所有列以及FOREIGN KEY約束中的所有列最好也定義NOT NULL約束，這往往是業(yè)務(wù)要求。

嘗試在子表中插入父表中沒有匹配行的行，將生成錯(cuò)誤。同樣，如果父表中的某行在子表中已有引用它的行，則刪除相應(yīng)的行將引發(fā)錯(cuò)誤。可以使用兩種技術(shù)來更改此行為。首先，可將此約束創(chuàng)建為ON DELETE CASCADE。這意味著，如果刪除父表中的行，那么Oracle將在子表中搜索所有匹配行，并刪除它們。這將自動(dòng)發(fā)生。一個(gè)作用較溫和的技術(shù)是將約束創(chuàng)建為ON DELETE SET NULL。在此情況下，如果刪除父表中的行，Oracle將在子表中搜索所有的匹配行，并將外鍵列設(shè)為空。這意味著，子行將成為孤行，但依然存在。如果子表中的列也有NOT NULL約束，則父表上的刪除操作將失敗。

即使子表中沒有行，也不能刪除或截?cái)嗤怄I關(guān)系中的父表。如果使用ON DELETE SET NULL或ON DELETE CASCADE子句，這依然適用。

FOREIGN KEY約束的一個(gè)變體是自引用FOREIGN KEY約束。這將定義一個(gè)條件，其中的父行和子行存在于同一個(gè)表中。如EMPLOYEE表，其中包括員工經(jīng)理的列。經(jīng)理本身也是一名員工，必須存在于此表中。因此，如果主鍵是EMPLOYEE_ID列，并由MANAGER_ID列來確定經(jīng)理，則FOREIGN KEY約束表明，MANAGER_ID列的值必須反過來引用有效的EMPLOYEE_ID。如果員工就是經(jīng)理本人，則此行將引用自身。

5．CHECK約束

CHECK約束可用來實(shí)施簡(jiǎn)單規(guī)則，如列中輸入的值必須在一個(gè)值域內(nèi)。規(guī)則必須是一個(gè)結(jié)果為TRUE或FALSE的表達(dá)式。規(guī)則可以引用作為字面值輸入的絕對(duì)值，也可以引用同一行中的其他列，也可以使用一些函數(shù)。可以根據(jù)需要為一個(gè)列應(yīng)用足夠多的CHECK約束，但無法使用子查詢來計(jì)算值是否被允許，也無法使用諸如SYSDATE的函數(shù)。

5.7.2 定義約束

可在創(chuàng)建表時(shí)定義約束，也可在后期將約束添加到表中。如果在創(chuàng)建表時(shí)定義約束，則可以與引用的列一起定義約束，也可以在表定義結(jié)束階段定義約束。使用后一種技術(shù)會(huì)獲得更大的靈活性。例如，如果一起定義約束，將無法定義引用兩列的FOREIGN KEY約束，或引用除約束列以外的任意列的CHECK約束；而如果在表定義結(jié)束階段定義約束，則這些都可能完成。

如果在創(chuàng)建表時(shí)定義約束，而約束需要索引(UNIQUE或PRIMARY KEY約束)，則索引將與表一起創(chuàng)建。

請(qǐng)考慮下面兩個(gè)創(chuàng)建表的語句(已為其添加了行號(hào))：

        1    create table dept(
        2    deptno number(2,0) constraint dept_deptno_pk primary key
        3    constraint dept_deptno_ck check (deptno between 10 and 90),
        4    dname  varchar2(20) constraint dept_dname_nn not null
        5    create table emp  (
        6    empno    number(4,0) constraint emp_empno_pk primary key,
        7    ename    varchar2(20) constraint emp_ename_nn not null,
        8    mgr      number (4,0) constraint emp_mgr_fk references emp (empno),
        9    dob      date,
        10    hiredate date,
        11    deptno   number(2,0) constraint emp_deptno_fk references dept(deptno)
        12    on delete set null,
        13    email varchar2(30) constraint emp_email_uk unique,
        14    constraint emp_hiredate_ck check (hiredate >= dob + 365*16),
        15    constraint emp_email_ck
        16    check ((instr(email, '@') > 0) and (instr(email, '.') > 0)));

下面逐行分析這些語句：

(1) 創(chuàng)建的第一個(gè)表稱為DEPT，每個(gè)部門占用一行。

(2) DEPTNO是數(shù)值，兩位數(shù)字，沒有小數(shù)點(diǎn)。這是表的主鍵，約束名為DEPT_DEPTNO_PK。

(3) 應(yīng)用于DEPTNO的第二個(gè)約束是CHECK約束，將其數(shù)字限制為10～90。此約束名為DEPT_ DEPTNO_CK。

(4) DNAME列是變長(zhǎng)字符，包含使其不為空的DEPT_DNAME_NN約束。

(5) 創(chuàng)建的第二個(gè)表是EMP，每個(gè)員工占用一行。

(6) EMPNO是數(shù)值，最多4位數(shù)字，沒有小數(shù)點(diǎn)。EMP_EMPNO_PK約束將此標(biāo)記為表的主鍵。

(7) ENAME是變長(zhǎng)字符，包含使其不為空的EMP_ENAME_NN約束。

(8) MGR是經(jīng)理，其本身必須是員工。此列的定義方式與表的主鍵列EMPNO的定義方式相同。EMP_MGR_FK約束將此列定義為自引用的外鍵，因此輸入的任何值必須引用EMP中已經(jīng)存在的行(但未使用NOT NULL約束，所以可以保留為空)。

(9) DOB是員工的生日，是日期，不受約束。

(10) HIREDATE是員工雇傭日期，不受約束。起碼當(dāng)前不受約束。

(11) DEPTNO是員工關(guān)聯(lián)的部門。此列的定義方式與DEPT表的主鍵列DEPTNO的定義方式相同，而EMP_DEPTNO_FK約束實(shí)施外鍵關(guān)聯(lián)。不能將員工分配給不存在的部門。但這可以為空。

(12) EMP_DEPTO_FK約束進(jìn)一步定義為ON DELETE SET NULL，因此如果刪除DEPT中的父行，EMPNO中所有匹配的子行將DEPTNO設(shè)置為NULL。

(13) EMAIL是變長(zhǎng)字符數(shù)據(jù)，如果輸入就必須是唯一的(但可以保留為空)。

(14) 這定義一個(gè)附加的表級(jí)約束EMP_HIREDATE_CK。此約束用于檢查童工的使用情況，對(duì)于雇傭日期不超過出生日期16年的任何行，將予以拒絕。此約束不可以與HIREDATE一起定義，因?yàn)榇苏Z法不允許在那時(shí)引用其他列。

(15) 添加到EMAIL列的附加約束EMP_EMAIL_CK對(duì)電子郵件地址進(jìn)行兩個(gè)檢查。INSTR函數(shù)查找“@”和“.”字符(這些總是出現(xiàn)在有效的電子郵件地址中)，如果找不到這兩個(gè)字符，CHECK條件將返回FALSE，行將被拒絕。

上例顯示了在創(chuàng)建表時(shí)定義約束的幾種可能性。其他可能如下：

● 控制UNIQUE和PRIMARY KEY約束的索引創(chuàng)建。

● 定義在插入時(shí)(默認(rèn)方式)還是在后面(在提交事務(wù)時(shí))檢查約束。

● 說明此約束真正實(shí)施(默認(rèn)方式)還是被禁用。

可以創(chuàng)建一個(gè)無約束的表，此后使用ALTER TABLE命令添加約束。最終結(jié)果沒有差別，但此技術(shù)的使用降低了代碼的自我記錄性，因?yàn)橥暾谋矶x將分散在多個(gè)語句中(而并非在一個(gè)語句中)。

5.7.3 約束狀態(tài)

任何時(shí)候，每個(gè)約束都處于啟用或禁用狀態(tài)，驗(yàn)證或非驗(yàn)證狀態(tài)。從語法上講，它們的任何組合都可能出現(xiàn)：

● ENABLE VALIDATE 無法輸入違反約束的行，而且表中的所有行都符合約束。

● DISABLE NOVALIDATE 可以輸入任何數(shù)據(jù)(無論是否符合要求)，表中可能已經(jīng)存在不合乎要求的數(shù)據(jù)。

● ENABLE NOVALIDATE 表中可以已經(jīng)存在不合乎要求的數(shù)據(jù)，但現(xiàn)在輸入的所有數(shù)據(jù)必須符合要求。

● DISABLE VALIDATE 表中所有數(shù)據(jù)都符合約束，但新行未必如此。約束上的索引也會(huì)刪除。

理想狀況是ENABLE VALIDATE(定義約束時(shí)的默認(rèn)設(shè)置)。這將確保所有數(shù)據(jù)都是有效的，而且不能輸入無效數(shù)據(jù)。另一方面，將大量數(shù)據(jù)上傳到表時(shí)，DISABLE NOVALIDATE可能非常有用。正在上傳的數(shù)據(jù)完全有可能不符合業(yè)務(wù)規(guī)則，但為了避免因?yàn)閹讉€(gè)錯(cuò)誤行而導(dǎo)致大型上傳的失敗，將約束設(shè)置為此狀態(tài)將使上傳成功執(zhí)行。在上傳完畢后，立即將約束的狀態(tài)轉(zhuǎn)換為ENABLE NOVALIDATE。在將約束轉(zhuǎn)換為理想狀態(tài)之前，這將防止事態(tài)的進(jìn)一步惡化，同時(shí)會(huì)檢查數(shù)據(jù)的合規(guī)性。

例如，考慮下面的腳本，它將實(shí)時(shí)數(shù)據(jù)源表中的數(shù)據(jù)讀入歸檔數(shù)據(jù)表。假設(shè)目標(biāo)表的列存在NOT NULL約束，而源表上未實(shí)施此約束：

        alter table sales_archive modify constraint sa_nn1 disable novalidate;
        insert into sales_archive
        select * from sales_current;
        alter table sales_archive modify constraint sa_nn1 enable novalidate;
        update sales_archive set channel='NOT KNOWN' where channel is null;
        alter table sales_archive modify constraint sa_nn1 enable validate;

5.7.4 檢查約束

可以在執(zhí)行語句時(shí)檢查約束(IMMEDIATE約束)或提交事務(wù)時(shí)檢查約束(DEFERRED約束)。默認(rèn)方式下，所有約束都是IMMEDIATE (立即)約束，不能延遲。上例的一個(gè)替代方法是將約束創(chuàng)建為DEFERRED(延遲)約束：

        set constraint sa_nn1 deferred;
        insert into sales_archive
        select * from sales_current;
        update sales_archive set channel='NOT KNOWN' where channel is null;
        commit;
        set constraint sa_nn1 immediate;

要使約束成為延遲約束，必須使用適當(dāng)?shù)姆椒ㄓ枰詣?chuàng)建：

        alter table sales_archive add constraint sa_nn1
        check (channel is not null) deferrable initially immediate;

如果創(chuàng)建時(shí)沒這么做，則無法在后來使約束成為延遲約束。在插入或更新行時(shí)將默認(rèn)實(shí)施SA_NN1約束，但檢查可以延遲到提交事務(wù)之時(shí)?？裳舆t約束通常用于外鍵。如果進(jìn)程在父表和子表中插入或更新行，而未按正確順序處理行，那么，在外鍵約束不延遲的情況下，進(jìn)程將失敗。

在ENABLED/DISABLED和VALIDATE/NOVALIDATE之間更改約束狀態(tài)的操作會(huì)影響所有會(huì)話。此狀態(tài)更改是一個(gè)數(shù)據(jù)字典更新。在IMMEDIATE和DEFERRED之間切換可延遲約束與會(huì)話有關(guān)，但初始狀態(tài)將應(yīng)用于所有會(huì)話。

練習(xí)5-6 管理約束

在本練習(xí)中，定義和調(diào)整在練習(xí)5-3中創(chuàng)建的表上的一些約束。

(1) 在SQL Developer中，導(dǎo)航至HR模式，并單擊CUSTOMERS表。

(2) 通過Constraints選項(xiàng)卡，查看為該表創(chuàng)建的4個(gè)NOT NULL約束。注意，它們的名稱起不到什么幫助作用，將在步驟(8)中確定名稱。

(3) 單擊Actions按鈕，選擇Constraints: Add Primary Key。

(4) 在Add Primary Constraint窗口中，命名約束為PK_CUSTOMER_ID，選擇CUSTOMER_ID列，然后單擊Apply按鈕。

(5) 選擇Show SQL選項(xiàng)卡來查看創(chuàng)建約束的語句，然后單擊Apply按鈕運(yùn)行該語句。

(6) 使用SQL*Plus，以用戶HR的身份連接到數(shù)據(jù)庫。

(7) 運(yùn)行此查詢來查找約束的名稱：

        select constraint_name, constraint_type, column_name
        from user_constraints natural join user_cons_columns
        where table_name='CUSTOMERS';

(8) 使用ALTER TABLE命令，基于步驟(7)檢索到的最初約束名，將約束重命名為更有意義的名稱：

        ALTER TABLE CUSTOMERS RENAME CONSTRAINT old_name TO new_name ;

(9) 向HR模式中添加下列約束：

        alter table orders add constraint pk_order_id primary key(order_id);
        alter table products add constraint pk_product_id primary key(product_id);
        alter table order_items add constraint fk_product_id foreign key(product_id)
          references products(product_id);
        alter table order_items add constraint fk_order_id foreign key(order_id)
          references orders(order_id);
        alter table orders add constraint fk_customer_id foreign key(customer_id)
          references customers(customer_id);

5.8 視圖

對(duì)于用戶來說，視圖看起來與表相似：兩者都是二維行列結(jié)構(gòu)，并且用戶可以在視圖上運(yùn)行SELECT語句和DML語句。程序員知道視圖的真相：視圖只不過是命名的SELECT語句。任何SELECT語句都返回二維行集。如果將SELECT語句保存為視圖，那么每當(dāng)用戶查詢或更新視圖(給人的印象就是表)中的行時(shí)，就會(huì)像對(duì)待表一樣運(yùn)行語句并向用戶顯示結(jié)果。視圖可以基于任何SELECT語句?？梢允锹?lián)接表、執(zhí)行聚合或進(jìn)行排序的語句，可以對(duì)視圖使用在SELECT命令中合法的任何內(nèi)容。

5.8.1 使用視圖的原因

使用視圖可能的原因包括：安全性、簡(jiǎn)化用戶SQL語句、防止錯(cuò)誤、提高性能和使數(shù)據(jù)易于理解。表和列名往往太長(zhǎng)，而且相當(dāng)無意義。視圖和其中的列則更為一目了然。

1．用來實(shí)施安全性的視圖

有時(shí)可能只應(yīng)當(dāng)允許用戶看到表的部分行或列。雖然有幾種方式可以做到這一點(diǎn)，但是視圖往往是最簡(jiǎn)單的方式。以HR.EMPLOYEES表為例，這個(gè)表中包括不應(yīng)讓除人事部門之外的員工看到的個(gè)人詳細(xì)信息。但是財(cái)務(wù)工作人員需要能夠看到成本核算信息。該視圖將對(duì)數(shù)據(jù)做客觀處理：

        create view hr.emp_fin as
        select hire_date, job_id, salary, commission_pct, department_id from
          hr.employees;

注意，上面對(duì)表使用了模式限定符作為數(shù)據(jù)(常常是指基表或明細(xì)表)和視圖的來源：視圖是模式對(duì)象，可以從相同模式或其他模式的表中取得所需的數(shù)據(jù)。如果沒有指定模式，則就是當(dāng)前模式。

然后可以授予財(cái)務(wù)工作人員查看視圖而不是查看表的權(quán)限，并且可以執(zhí)行如下語句：

        select * from emp_fin where department_id=50;

他們只能看到組成該視圖的5列，而看不到EMPLOYEES中其余包含個(gè)人信息的列。可以像表一樣將該視圖與其他表聯(lián)接或執(zhí)行聚合操作：

        select department_name, sum(salary) dept_sal
        from departments natural join emp_fin
        group by department_name;

構(gòu)造良好的視圖集可以在數(shù)據(jù)庫中實(shí)現(xiàn)完整的安全結(jié)構(gòu)，給予用戶訪問他們需要查看的數(shù)據(jù)的權(quán)限，而隱藏他們不需要查看的數(shù)據(jù)。

2．用來簡(jiǎn)化用戶SQL的視圖

對(duì)用戶來說，如果復(fù)雜的工作(如聯(lián)接或聚合)都由定義視圖的代碼完成，那么用戶查詢數(shù)據(jù)時(shí)就會(huì)輕松很多。在上面的示例中，用戶必須編寫將EMP_FIN視圖聯(lián)接到DEPARTMENTS表的代碼，并計(jì)算每個(gè)部門的薪水總和?？梢栽谝粋€(gè)視圖中完成全部這些工作：

        create view dept_sal as
        select d.department_name, sum(e.salary) dept_sal
        from departments d left outer join employees e on
          d.department_id=e.department_id
        group by department_name order by department_name;

然后用戶可以從DEPT_SAL中選擇，而不需要知道關(guān)于聯(lián)接的任何情況，甚至不需要知道如何排序結(jié)果：

        select * from dept_sal;

特別地，用戶不需要知道如何確保列出了所有部門，甚至是沒有員工的部門。上一節(jié)中的示例未能實(shí)現(xiàn)這些功能。

3．用來防止錯(cuò)誤的視圖

雖然不可能防止用戶犯錯(cuò)誤，但是構(gòu)造良好的視圖可以防止一些由于不了解應(yīng)如何解釋數(shù)據(jù)而產(chǎn)生的錯(cuò)誤。上一節(jié)已經(jīng)通過構(gòu)造一個(gè)列出所有部門(不管這些部門目前是否有員工)的視圖介紹了此概念。

視圖有助于按照沒有歧義的方式提供數(shù)據(jù)。例如，很多應(yīng)用程序永遠(yuǎn)不會(huì)真正刪除行。分析下面這個(gè)表：

        create table emp  ( empno number constraint emp_empno_pk primary key,
        ename  varchar2(10), deptno number, active varchar2(1) default 'Y');

列ACTIVE是一個(gè)標(biāo)志，表示該員工當(dāng)前被雇用，當(dāng)插入一行時(shí)，這個(gè)標(biāo)志的默認(rèn)值為'Y'。當(dāng)用戶通過用戶界面“刪除”員工時(shí)，底層SQL語句將更新為把ACTIVE設(shè)置為’N'。如果不了解這一點(diǎn)的用戶對(duì)表進(jìn)行查詢時(shí)，他們可能會(huì)嚴(yán)重曲解結(jié)果。因此，授予他們對(duì)視圖的訪問權(quán)限往往會(huì)更好：

        create view current_staff as select * from emp where active='Y';

訪問這個(gè)視圖的查詢不可能看到“已刪除的”員工成員。

4．使數(shù)據(jù)易于理解的視圖

數(shù)據(jù)庫中的數(shù)據(jù)結(jié)構(gòu)是規(guī)范化表。期望用戶理解規(guī)范化結(jié)構(gòu)是不合理的要求。以O(shè)racle E-Business套件為例，Accounts Receivable模塊中的customer實(shí)際上是一個(gè)整體，由分布在HZ_ PARTIES、HZ_PARTY_SITES及HZ_CUST_ACCTS_ALL等表中的信息組成。所有這些表用主鍵到外鍵的關(guān)系連接，但是這些關(guān)系沒有在任何對(duì)用戶可見的標(biāo)識(shí)符(如客戶編號(hào))上定義：它們基于用戶永遠(yuǎn)見不到的列，其中包含按照順序在內(nèi)部生成的值。用來檢索客戶信息的表單和報(bào)表永遠(yuǎn)不會(huì)直接訪問這些表，它們都是通過視圖工作的。

視圖除了可以通過易于理解的形式向用戶提供數(shù)據(jù)之外，也提供用戶看到的對(duì)象和存儲(chǔ)在數(shù)據(jù)庫中的對(duì)象之間的抽象層，對(duì)于維護(hù)工作相當(dāng)有價(jià)值。視圖允許在不需要重新編碼應(yīng)用程序的情況下重新設(shè)計(jì)數(shù)據(jù)結(jié)構(gòu)。如果修改了表，那么調(diào)整視圖定義可能對(duì)SQL和PL/SQL代碼造成一些不必要的改動(dòng)。視圖是使應(yīng)用程序能夠在不同數(shù)據(jù)庫之間移植的重要技術(shù)。

5．用來提升性能的視圖

程序員可以優(yōu)化視圖背后的SELECT語句，這樣用戶就不需要關(guān)心代碼的調(diào)整。得到同一個(gè)結(jié)果可能有很多方式，但是有些技術(shù)也許比其他技術(shù)慢很多。例如，當(dāng)聯(lián)接兩個(gè)表時(shí)，通常可以選擇嵌套循環(huán)聯(lián)接，也可以選擇哈希聯(lián)接。嵌套循環(huán)聯(lián)接使用索引找到單個(gè)行；哈希聯(lián)接將整個(gè)表讀入內(nèi)存中。在這兩種方法之間如何選擇取決于數(shù)據(jù)的狀態(tài)和可用的硬件資源。

從理論上講，人們總是能依靠Oracle優(yōu)化器來找出運(yùn)行SQL語句的最佳方式，但是有時(shí)也會(huì)出錯(cuò)。如果程序員知道哪種技術(shù)最好，可以通知優(yōu)化器采用這種技術(shù)。本例強(qiáng)制使用哈希技術(shù)：

        create view dept_emp as
        select /*+USE_HASH (employees departments)*/ department_name, last_name
        from departments natural join employees;

每當(dāng)用戶查詢DEPT_EMP視圖時(shí)，都會(huì)通過將明細(xì)表掃描到內(nèi)存中來進(jìn)行聯(lián)接。用戶不需要知道強(qiáng)制使用這種聯(lián)接方法的語法。也不需要知道這種語法：它不在OCP SQL的考試范圍之內(nèi)，但是應(yīng)該知道使用視圖設(shè)計(jì)進(jìn)行調(diào)整的概念。

5.8.2 簡(jiǎn)單視圖和復(fù)雜視圖

出于實(shí)用的目的，簡(jiǎn)單視圖和復(fù)雜視圖的劃分與是否能對(duì)視圖執(zhí)行DML語句有關(guān)：簡(jiǎn)單視圖通常能接受DML語句，復(fù)雜視圖則不能。嚴(yán)格的定義如下所示：

● 簡(jiǎn)單視圖從明細(xì)表中取得數(shù)據(jù)，不使用函數(shù)，不進(jìn)行聚合。

● 復(fù)雜視圖可以聯(lián)接明細(xì)表，使用函數(shù)，進(jìn)行聚合。

對(duì)上一節(jié)中作為示例的4個(gè)視圖應(yīng)用這些定義可知，第一個(gè)和第三個(gè)視圖是簡(jiǎn)單視圖，第二個(gè)和第四個(gè)視圖是復(fù)雜視圖。

一般不能對(duì)復(fù)雜視圖執(zhí)行INSERT、UPDATE或DELETE命令。視圖中的行對(duì)明細(xì)表中的行進(jìn)行反向映射，但是這種映射并不總是能夠一對(duì)一地建立，而這對(duì)于DML操作是必需的。一般來說可以對(duì)簡(jiǎn)單視圖執(zhí)行DML，但并非總是如此。例如，如果視圖不包括具有NOT NULL約束的列，那么通過視圖進(jìn)行INSERT操作就不會(huì)成功(除非該列有默認(rèn)值)。這樣會(huì)產(chǎn)生令人不安的影響，因?yàn)殄e(cuò)誤消息會(huì)引用語句中沒有提到的表和列，如圖5-11中的第一個(gè)示例所示。

圖5-10中的第一個(gè)視圖RNAME_V確實(shí)符合簡(jiǎn)單視圖的定義，但是不能通過該視圖執(zhí)行INSERT操作，因?yàn)檫@個(gè)視圖缺少?gòu)?qiáng)制列。第二個(gè)視圖RUPPERNAME_V是復(fù)雜視圖，因?yàn)樗ㄒ粋€(gè)函數(shù)。這使得INSERT無法執(zhí)行，因?yàn)閿?shù)據(jù)庫無法計(jì)算出到底應(yīng)插入什么數(shù)據(jù)：它不能以確定性的方式反向工程UPPER函數(shù)的效果。但是DELETE操作成功執(zhí)行，因?yàn)檫@個(gè)操作不依賴于該函數(shù)。

5.8.3 創(chuàng)建、更改和刪除視圖

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

        CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW
        [schema.]viewname [(alias [, alias]...)]
        AS subquery
        [WITH CHECK OPTION [CONSTRAINT constraintname]]
        [WITH READ ONLY [CONSTRAINT constraintname]] ;

注意，視圖是模式對(duì)象。沒有理由不允許一名用戶擁有的視圖引用另一名用戶擁有的明細(xì)表。默認(rèn)情況下，在當(dāng)前模式中創(chuàng)建視圖。可選關(guān)鍵字如下所示，到目前為止的示例中還沒有用到這些關(guān)鍵字：

● OR REPLACE 如果視圖已經(jīng)存在，則在創(chuàng)建之前刪除它。

● FORCE或NOFORCE 即使子查詢中不存在明細(xì)表，F(xiàn)ORCE關(guān)鍵字也會(huì)創(chuàng)建視圖。NOFORCE是默認(rèn)值，如果明細(xì)表不存在，則引發(fā)一個(gè)錯(cuò)誤。

● WITH CHECK OPTION 這是用來處理DML的選項(xiàng)。如果子查詢包括WHERE子句，那么這個(gè)選項(xiàng)會(huì)防止插入視圖中不可見的行，或者防止導(dǎo)致行從視圖中消失的更新。默認(rèn)情況下不啟用這個(gè)選項(xiàng)，它會(huì)產(chǎn)生令人不安的結(jié)果。

● WITH READ ONLY防止任何DML通過視圖。

● CONSTRAINT constraintname 這個(gè)選項(xiàng)可以用來命名WITH CHECK OPTION和WITH READ ONLY限制，這樣，當(dāng)這些限制導(dǎo)致語句失敗時(shí)，出現(xiàn)的錯(cuò)誤消息更易于理解。

此外，可以為視圖的列名提供一組別名。如果沒有提供別名，列就會(huì)根據(jù)表的列命名，或者用子查詢中指定的別名來命名。

ALTER VIEW命令的主要用途是編譯視圖，在成功地編譯視圖后才能使用該命令。在創(chuàng)建視圖時(shí)，Oracle會(huì)檢查該視圖所基于的明細(xì)表和必要的列是否存在。如果明細(xì)表和必要的列不存在，編譯就會(huì)失敗，也不會(huì)創(chuàng)建視圖——除非使用FORCE選項(xiàng)。如果使用了FORCE選項(xiàng)，則會(huì)創(chuàng)建視圖，但是直到創(chuàng)建視圖引用的表或列時(shí)才能成功地編譯該視圖。當(dāng)查詢無效視圖時(shí)，Oracle會(huì)試圖自動(dòng)編譯該視圖。如果因?yàn)樾迯?fù)問題而成功地編譯了視圖，那么用戶不會(huì)知道曾經(jīng)存在問題，只是用戶的查詢會(huì)比平?；ㄙM(fèi)的時(shí)間稍微長(zhǎng)一些。一般而言，應(yīng)當(dāng)手動(dòng)編譯視圖來確保成功編譯，而不是讓用戶發(fā)現(xiàn)錯(cuò)誤。

創(chuàng)建視圖的列定義后，不能使用修改表列的方式調(diào)整視圖的列。要調(diào)整視圖的列，必須先刪除視圖再重新創(chuàng)建。DROP命令如下：

        DROP VIEW [schema.]viewname ;

在CREATE VIEW命令中使用OR REPLACE關(guān)鍵字，則會(huì)在創(chuàng)建視圖前自動(dòng)刪除該視圖(如果它已經(jīng)存在)。

練習(xí)5-7 創(chuàng)建視圖

在此練習(xí)中，使用處于HR模式的數(shù)據(jù)創(chuàng)建一些簡(jiǎn)單視圖和復(fù)雜視圖。可以使用SQL*Plus，也可以使用SQL Developer。

(1) 作為用戶HR連接到數(shù)據(jù)庫。

(2) 對(duì)EMPLOYEES和DEPARTMENT表創(chuàng)建視圖，刪除其中的所有個(gè)人信息：

        create view emp_anon_v as
        select hire_date, job_id, salary, commission_pct, department_id from employees;
        create view dept_anon_v as
        select department_id, department_name, location_id from departments;

(3) 創(chuàng)建聯(lián)接與聚合這兩個(gè)簡(jiǎn)單視圖的復(fù)雜視圖。注意，可以在視圖的基礎(chǔ)上再創(chuàng)建視圖。

        create view dep_sum_v as
        select e.department_id, count(1) staff, sum(e.salary) salaries,
        d.department_name from emp_anon_v e join dept_anon_v d
        on e.department_id=d.department_id
        group by e.department_id, d.department_name;

(4) 通過查詢確認(rèn)視圖有效。