5.6 索引

索引具有兩個功能：一是強制實施主鍵約束和唯一約束，二是提高性能。應用程序的索引策略對于性能至關重要。索引管理所屬的范圍沒有明確界限。當業務分析師指定將要實施為約束的業務規則時，他們實際在指定索引。數據庫管理員將監視在數據庫中運行的代碼的執行，并將提出有關索引的建議。開發人員最了解代碼內容和數據特點，也將參與到索引策略的開發中。

5.6.1 為什么索引是必需的

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

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

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

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

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

5.6.2 索引類型

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

1．B*樹索引

B*樹索引(B代表“平衡(balanced)”)是一個樹結構。樹的根節點指向第二級別的多個節點，第二級別的節點又指向第三級別的多個節點，以此類推。樹的所需深度主要取決于表中的行數，以及索引鍵值的長度。

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

行的指針是rowid。rowid是Oracle專用的虛擬列，每個表的每一行都有此虛擬列。其中的加密項是行的物理地址。因為rowid不是SQL標準的一部分，所以普通的SQL語句永遠都看不到它們，但可以根據需要查看和使用它們。如圖5-9所示。

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

如果需要的行數只占表的總行數的一小部分，而且表很大，則B*樹是十分有效的檢索行的方式。考慮以下語句：

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

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

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

        select * from employees
        where last_name is null;

將始終導致全表掃描。如果一個列中包含的唯一值很少，那么，在此列上創建B*樹索引就沒有太大價值，因為其選擇性不充分：為了找到每個不同鍵值而檢索的表的部分過多。一般而言，如果遇到以下情況，應該使用B*樹索引：

● 列的基數(不同值的個數)很大

● 表的行數多

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

2．位圖索引

在很多業務應用程序中，數據和查詢的特點致使B*樹索引用處不大?？紤]連鎖超市的銷售表，它存儲一年的歷史數據，可從幾個維度進行分析。圖5-10顯示了一個僅有4個維度的簡單實體-關系圖。

每個維度的基數可能很少。做如表5-1的假設。

假設數據分布均勻，則只有兩個維度(PRODUCT和DATE)具有的選擇性優于2%～4%的常用標準，這使索引變得很有價值。但是，如果查詢使用范圍謂詞(如計算一個月的銷售額， 10個或更多產品類別的銷售額)，那么，這些也都不符合條件。這是一個簡單事實：B*樹索引在數據倉庫環境中通常無用。一個典型的查詢是針對一個月內自購客戶對某類產品的購買額來比較兩個商店。雖然可以在相關列上建立B*樹索引，但Oracle 將會忽略它們，因為其選擇性不充分。正因為如此，設計了位圖索引。

位圖索引將與每個鍵值關聯的rowid存儲為位圖。CHANNEL索引的位圖可能如下：

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

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

SHOP索引的位圖可能為：

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

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

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

Oracle可以檢索兩個相關位圖，并使用布爾“與”操作將它們結合在一起：

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

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

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

● 列的基數(不同值的個數)小

● 表中的行數多

● 列用于布爾代數運算

3．索引類型選項

在創建索引時，可以應用6個常用選項：

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

● 反向鍵(Reverse key)

● 壓縮(Compressed)

● 復合(Composite)

● 基于函數(Function based)

● 升序或降序(Ascending or descending)

所有這6個選項都可應用于B*樹索引，只有后三個選項可用于位圖索引。

● 唯一索引不允許重復值。非唯一索引是默認設置。索引的唯一特性獨立于唯一約束或主鍵約束：唯一索引不允許插入重復值，即使沒有定義此類約束，也同樣如此。唯一約束或主鍵約束可以使用非唯一索引；它正好沒有重復值。實際上，這是可延遲約束的一項要求，因為在某個階段(提交事務之前)可能存在重復值。下一節將討論約束。

● 反向鍵索引基于將字節倒置的鍵列版本，例如，將針對“nhoJ”而非“John”來設置索引。在完成SELECT時，Oracle將自動反轉搜索字符串的值。這是在多用戶系統中避免爭用的功能強大的技術。例如，如果多位用戶正在并發地插入具有主鍵(基于連續增長的編號)的行，那么，所有索引插入將聚集在索引的高端。通過反轉鍵，連貫的索引鍵插入將分布在整個索引范圍內。即使“John”和“Jules”很是接近，“nhoJ”和“seluJ”也相距甚遠。

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

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

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

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

5.6.3 創建和使用索引

在定義主鍵約束和唯一約束時，如果相關列上的索引并不存在，則將隱式創建索引。顯式創建索引的基本語法如下：

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

索引的默認類型是非唯一、非壓縮、非反向鍵B*樹索引。無法創建唯一的位圖索引(即便允許，也不會這么做，因為這會導致基數問題)。索引是模式對象，可能在一個模式中創建另一個模式的表上的索引，但大多數人認為這會造成混淆。復合索引(composite index)是多個列上的索引。復合索引可以在不同數據類型的列上，而且列不必在表中相互鄰近。

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

        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);

創建的前兩個索引被標記為UNIQUE，意即不允許插入重復值。此處未將其定義為約束，但實際上就是這樣。第三個索引未定義為UNIQUE，即可以接受重復值；這是兩個列上的復合索引。第四個索引定義為位圖索引，因為與表中的行數相比，列的基數占的比例可能很小。

在定義兩個主鍵約束時，Oracle將檢測預先創建的索引，并使用它們來實施約束。注意， DEPT.DEPTNO上的索引并非用于提高性能，因為此表可能很小，以至于從不會使用索引來檢索行(掃描速度更快)，但使用索引來實施主鍵約束依然至關重要。

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

5.6.4 修改和刪除索引

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

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

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

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

練習5-5 創建索引

本練習將為CUSTOMERS表創建、填充、添加一些索引。

(1) 連接到HR模式，創建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) 在客戶名稱和客戶狀態上創建復合B*樹索引：

        create index cust_name_i on customers (customer_name, customer_status);

(3) 在一些低基數列上創建位圖索引：

        create bitmap index creditrating_i on customers(creditrating);

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

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

5.7 約束

表約束是數據庫能夠實施業務規則以及保證數據遵循實體-關系模型的一種手段，其中，實體-關系模型由定義應用程序數據結構的系統分析所確定。例如，機構中的商業分析師決定使用數字來唯一標識所有客戶以及所有訂單，在創建客戶之后才能將訂單發給客戶，而且每張訂單必須包含有效日期和一個大于零的值。要實現這個功能，需要進行下列操作：在CUSTOMERS表的CUSTOMER_ID列上和ORDERS表的ORDER_ID列上創建主鍵約束，在引用CUSTOMERS表的ORDERS表上創建外鍵約束，在ORDERS表的DATE列上創建一個NOT NULL約束(DATE數據類型本身能夠自動確保所有日期有效，這種數據類型并不接受無效的日期)，在ORDERS表的ORDER_ AMOUNT列上創建一個CHECK約束。

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

5.7.1 約束類型

Oracle數據庫支持的約束類型如下：

● UNIQUE

● NOT NULL

● PRIMARY KEY

● FOREIGN KEY

● CHECK

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

1．UNIQUE約束

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

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

UNIQUE約束通過索引來實施。在定義UNIQUE約束時，Oracle將查看鍵列上的索引，如果不存在，就創建一個。此后，每次插入行時，Oracle都將查看索引，了解鍵列的值是否已經存在。如果已存在，則將拒絕插入。這些索引(稱為B*樹索引)的結構不包含NULL值，正因為如此，才允許出現多個包含NULL的行：索引中根本不存在NULL。雖然索引的第一要務是實施約束，但也有次生效應：如果在SQL語句的WHERE子句中使用鍵列，性能將提高。但是，選擇WHERE key_column IS NULL則不使用索引(因為它不包括NULL)，因此總是導致掃描整個表。

2．NOT NULL約束

NOT NULL約束強制在鍵列中輸入值。它針對每個列進行定義，有時被稱為強制列(mandatory column)。如果業務要求一組列都具有值，則不能為整個組定義NOT NULL約束，而必須針對每列定義NOT NULL約束。

如果嘗試插入沒有為具有NOT NULL約束的列指定值的行，將導致錯誤。如3.1節所述，在創建表時通過在列上包含DEFAULT子句，可以不指定值。

3．PRIMARY KEY約束

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

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

4．FOREIGN KEY約束

在父子關系的子表中定義FOREIGN KEY約束。此約束使子表中的列(或列組合)對應父表的主鍵列。這些列不必同名，但數據類型必須相同。FOREIGN KEY約束定義數據庫的關系結構：連接第三范式的表的多對一關系。

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

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

嘗試在子表中插入父表中沒有匹配行的行，將生成錯誤。同樣，如果父表中的某行在子表中已有引用它的行，則刪除相應的行將引發錯誤?？梢允褂脙煞N技術來更改此行為。首先，可將此約束創建為ON DELETE CASCADE。這意味著，如果刪除父表中的行，那么Oracle將在子表中搜索所有匹配行，并刪除它們。這將自動發生。一個作用較溫和的技術是將約束創建為ON DELETE SET NULL。在此情況下，如果刪除父表中的行，Oracle將在子表中搜索所有的匹配行，并將外鍵列設為空。這意味著，子行將成為孤行，但依然存在。如果子表中的列也有NOT NULL約束，則父表上的刪除操作將失敗。

即使子表中沒有行，也不能刪除或截斷外鍵關系中的父表。如果使用ON DELETE SET NULL或ON DELETE CASCADE子句，這依然適用。

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

5．CHECK約束

CHECK約束可用來實施簡單規則，如列中輸入的值必須在一個值域內。規則必須是一個結果為TRUE或FALSE的表達式。規則可以引用作為字面值輸入的絕對值，也可以引用同一行中的其他列，也可以使用一些函數?？梢愿鶕枰獮橐粋€列應用足夠多的CHECK約束，但無法使用子查詢來計算值是否被允許，也無法使用諸如SYSDATE的函數。

5.7.2 定義約束

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

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

請考慮下面兩個創建表的語句(已為其添加了行號)：

        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) 創建的第一個表稱為DEPT，每個部門占用一行。

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

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

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

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

(6) EMPNO是數值，最多4位數字，沒有小數點。EMP_EMPNO_PK約束將此標記為表的主鍵。

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

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

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

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

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

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

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

(14) 這定義一個附加的表級約束EMP_HIREDATE_CK。此約束用于檢查童工的使用情況，對于雇傭日期不超過出生日期16年的任何行，將予以拒絕。此約束不可以與HIREDATE一起定義，因為此語法不允許在那時引用其他列。

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

上例顯示了在創建表時定義約束的幾種可能性。其他可能如下：

● 控制UNIQUE和PRIMARY KEY約束的索引創建。

● 定義在插入時(默認方式)還是在后面(在提交事務時)檢查約束。

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

可以創建一個無約束的表，此后使用ALTER TABLE命令添加約束。最終結果沒有差別，但此技術的使用降低了代碼的自我記錄性，因為完整的表定義將分散在多個語句中(而并非在一個語句中)。

5.7.3 約束狀態

任何時候，每個約束都處于啟用或禁用狀態，驗證或非驗證狀態。從語法上講，它們的任何組合都可能出現：

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

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

● ENABLE NOVALIDATE 表中可以已經存在不合乎要求的數據，但現在輸入的所有數據必須符合要求。

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

理想狀況是ENABLE VALIDATE(定義約束時的默認設置)。這將確保所有數據都是有效的，而且不能輸入無效數據。另一方面，將大量數據上傳到表時，DISABLE NOVALIDATE可能非常有用。正在上傳的數據完全有可能不符合業務規則，但為了避免因為幾個錯誤行而導致大型上傳的失敗，將約束設置為此狀態將使上傳成功執行。在上傳完畢后，立即將約束的狀態轉換為ENABLE NOVALIDATE。在將約束轉換為理想狀態之前，這將防止事態的進一步惡化，同時會檢查數據的合規性。

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

        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 檢查約束

可以在執行語句時檢查約束(IMMEDIATE約束)或提交事務時檢查約束(DEFERRED約束)。默認方式下，所有約束都是IMMEDIATE (立即)約束，不能延遲。上例的一個替代方法是將約束創建為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;

要使約束成為延遲約束，必須使用適當的方法予以創建：

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

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

在ENABLED/DISABLED和VALIDATE/NOVALIDATE之間更改約束狀態的操作會影響所有會話。此狀態更改是一個數據字典更新。在IMMEDIATE和DEFERRED之間切換可延遲約束與會話有關，但初始狀態將應用于所有會話。

練習5-6 管理約束

在本練習中，定義和調整在練習5-3中創建的表上的一些約束。

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

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

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

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

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

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

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

        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 視圖

對于用戶來說，視圖看起來與表相似：兩者都是二維行列結構，并且用戶可以在視圖上運行SELECT語句和DML語句。程序員知道視圖的真相：視圖只不過是命名的SELECT語句。任何SELECT語句都返回二維行集。如果將SELECT語句保存為視圖，那么每當用戶查詢或更新視圖(給人的印象就是表)中的行時，就會像對待表一樣運行語句并向用戶顯示結果。視圖可以基于任何SELECT語句?？梢允锹摻颖怼绦芯酆匣蜻M行排序的語句，可以對視圖使用在SELECT命令中合法的任何內容。

5.8.1 使用視圖的原因

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

1．用來實施安全性的視圖

有時可能只應當允許用戶看到表的部分行或列。雖然有幾種方式可以做到這一點，但是視圖往往是最簡單的方式。以HR.EMPLOYEES表為例，這個表中包括不應讓除人事部門之外的員工看到的個人詳細信息。但是財務工作人員需要能夠看到成本核算信息。該視圖將對數據做客觀處理：

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

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

然后可以授予財務工作人員查看視圖而不是查看表的權限，并且可以執行如下語句：

        select * from emp_fin where department_id=50;

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

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

構造良好的視圖集可以在數據庫中實現完整的安全結構，給予用戶訪問他們需要查看的數據的權限，而隱藏他們不需要查看的數據。

2．用來簡化用戶SQL的視圖

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

        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中選擇，而不需要知道關于聯接的任何情況，甚至不需要知道如何排序結果：

        select * from dept_sal;

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

3．用來防止錯誤的視圖

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

視圖有助于按照沒有歧義的方式提供數據。例如，很多應用程序永遠不會真正刪除行。分析下面這個表：

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

列ACTIVE是一個標志，表示該員工當前被雇用，當插入一行時，這個標志的默認值為'Y'。當用戶通過用戶界面“刪除”員工時，底層SQL語句將更新為把ACTIVE設置為’N'。如果不了解這一點的用戶對表進行查詢時，他們可能會嚴重曲解結果。因此，授予他們對視圖的訪問權限往往會更好：

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

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

4．使數據易于理解的視圖

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

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

5．用來提升性能的視圖

程序員可以優化視圖背后的SELECT語句，這樣用戶就不需要關心代碼的調整。得到同一個結果可能有很多方式，但是有些技術也許比其他技術慢很多。例如，當聯接兩個表時，通?？梢赃x擇嵌套循環聯接，也可以選擇哈希聯接。嵌套循環聯接使用索引找到單個行；哈希聯接將整個表讀入內存中。在這兩種方法之間如何選擇取決于數據的狀態和可用的硬件資源。

從理論上講，人們總是能依靠Oracle優化器來找出運行SQL語句的最佳方式，但是有時也會出錯。如果程序員知道哪種技術最好，可以通知優化器采用這種技術。本例強制使用哈希技術：

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

每當用戶查詢DEPT_EMP視圖時，都會通過將明細表掃描到內存中來進行聯接。用戶不需要知道強制使用這種聯接方法的語法。也不需要知道這種語法：它不在OCP SQL的考試范圍之內，但是應該知道使用視圖設計進行調整的概念。

5.8.2 簡單視圖和復雜視圖

出于實用的目的，簡單視圖和復雜視圖的劃分與是否能對視圖執行DML語句有關：簡單視圖通常能接受DML語句，復雜視圖則不能。嚴格的定義如下所示：

● 簡單視圖從明細表中取得數據，不使用函數，不進行聚合。

● 復雜視圖可以聯接明細表，使用函數，進行聚合。

對上一節中作為示例的4個視圖應用這些定義可知，第一個和第三個視圖是簡單視圖，第二個和第四個視圖是復雜視圖。

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

圖5-10中的第一個視圖RNAME_V確實符合簡單視圖的定義，但是不能通過該視圖執行INSERT操作，因為這個視圖缺少強制列。第二個視圖RUPPERNAME_V是復雜視圖，因為它包括一個函數。這使得INSERT無法執行，因為數據庫無法計算出到底應插入什么數據：它不能以確定性的方式反向工程UPPER函數的效果。但是DELETE操作成功執行，因為這個操作不依賴于該函數。

5.8.3 創建、更改和刪除視圖

創建視圖的語法如下：

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

注意，視圖是模式對象。沒有理由不允許一名用戶擁有的視圖引用另一名用戶擁有的明細表。默認情況下，在當前模式中創建視圖?？蛇x關鍵字如下所示，到目前為止的示例中還沒有用到這些關鍵字：

● OR REPLACE 如果視圖已經存在，則在創建之前刪除它。

● FORCE或NOFORCE 即使子查詢中不存在明細表，FORCE關鍵字也會創建視圖。NOFORCE是默認值，如果明細表不存在，則引發一個錯誤。

● WITH CHECK OPTION 這是用來處理DML的選項。如果子查詢包括WHERE子句，那么這個選項會防止插入視圖中不可見的行，或者防止導致行從視圖中消失的更新。默認情況下不啟用這個選項，它會產生令人不安的結果。

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

● CONSTRAINT constraintname 這個選項可以用來命名WITH CHECK OPTION和WITH READ ONLY限制，這樣，當這些限制導致語句失敗時，出現的錯誤消息更易于理解。

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

ALTER VIEW命令的主要用途是編譯視圖，在成功地編譯視圖后才能使用該命令。在創建視圖時，Oracle會檢查該視圖所基于的明細表和必要的列是否存在。如果明細表和必要的列不存在，編譯就會失敗，也不會創建視圖——除非使用FORCE選項。如果使用了FORCE選項，則會創建視圖，但是直到創建視圖引用的表或列時才能成功地編譯該視圖。當查詢無效視圖時，Oracle會試圖自動編譯該視圖。如果因為修復問題而成功地編譯了視圖，那么用戶不會知道曾經存在問題，只是用戶的查詢會比平常花費的時間稍微長一些。一般而言，應當手動編譯視圖來確保成功編譯，而不是讓用戶發現錯誤。

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

        DROP VIEW [schema.]viewname ;

在CREATE VIEW命令中使用OR REPLACE關鍵字，則會在創建視圖前自動刪除該視圖(如果它已經存在)。

練習5-7 創建視圖

在此練習中，使用處于HR模式的數據創建一些簡單視圖和復雜視圖?？梢允褂肧QL*Plus，也可以使用SQL Developer。

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

(2) 對EMPLOYEES和DEPARTMENT表創建視圖，刪除其中的所有個人信息：

        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) 創建聯接與聚合這兩個簡單視圖的復雜視圖。注意，可以在視圖的基礎上再創建視圖。

        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) 通過查詢確認視圖有效。