Oracle PL\SQL操作（四）索引與約束

1.資料庫的索引

       可以將索引概念應用到資料庫表上。當一個表含有大量的記錄時，Oracle尋找該表中的特寫記錄要花很長的時間——就像花很長時間翻看全書來尋找某個主題一樣。Oracle有一個便於使用的功能，即可以建立一個次隱藏表，該表包含主表中的一個或多個重要的列，以及在主表中的指相應行的指標。在這裡，與書的頁碼相對應，該隱藏的次表（即索引表）中的指標就是行號。通過訪問索引，Oracle可以準確地知道要尋找的特定資料在哪一行上。由於索引比引用它的表要小得多，因此用索引尋找表中的資料比不用索引的表明赤地快。在一個擁有百萬條記錄的表中選擇與某值相匹配的記錄花了18.9秒，當建立該表的索引後，同樣的查詢只需要0.6秒就能完成。在一個大表中增加索引能使查詢速度快幾十倍。

         一旦建立了表的索引，Oracle自動使該表與表保持同步。對該表的任何INSERT、UPDATE或DELETE操作也自動修改索引，並且，如果索引中含有SELECT語句所需的列，則表的任何SELECT操作都將自動經過索引。增加或刪除索引不會影響表的操作——以前使用該表的任何程式現在仍將可以運行，但是操作速度可能變慢。如果刪除表，則相關的索引也將自動刪除，因為沒相關表的索引是沒有意義的。

刪除索引的文法如下：

DROP INDEX index_name

2.索引何時有用

       對於必須讀取表中內容的命令，索引改善了它們的回應時間。這意味著SELECT、UPDATE和DELETE命令都能夠更快地進行運行，如果該表有對應於這些命令的列的索引。但是增加表的索引並不能提高INSERT命令的輸入資料的速度，相反還要降低運行速度。因為索引本身實際上是一個表，因此當對錶添加一個記錄時，Oracle必須做兩次插入。這樣，組表增加一個索引將導致插入操作要兩倍多的時間（兩倍的時間用於兩次插入，另外還需要一點時間處理二表之間的同步）。增加兩個索引將使用插入操作花三倍時間，三個索引使插入操作花四倍的時間，等等。因此索引的使用要權衡利弊。它們會使資料輸入花費更長的時間，但卻使讀取資料的速度提高。所以，需要快速進行資料輸入的應用程式最好不要增加表的索引。例如，超市中的銷售系統，應使收很機儘可能快地圍繞銷售事務進行工作（即直接插入到資料庫中）。這時，若給儲存事務的表增加一個索引將是錯誤的，因為它會使插入變變慢。另一方面，在同一時刻可能會有經營管理員想要執行查詢來分析事務。

       記住：表越大，從表的索引中獲得的好處就越多。表2是在一個百萬條記錄的表上使用和不使用索引時，執行各種DML操作所花費的時間：

3.如何建立索引

建立索引很簡單，命令的文法如下：

CREATE INDEX index_name ON table_name(column_name); 

如果想要索引包含一個以上的表列，其文法如下：

CREATE INDEX index_name ON table_name( first_column_name, second_column_name ); 

用上面建立索引的命令建立索引：

create index code_index on person(person_code); create index person_name_index on person(last_name,first_name); 

請對建立的其它表建立索引進行測試。

說明：在標準Oracle索引中所能包含的最大的列數為32。

 

4.索引類型

1、B*—樹索引

Oracle組織記錄的預設索引類型稱為B*—樹。圖3示出B*—樹索引是如何組織記錄的。

當建立一個B*-樹索引時，Oracle分析被索引的列的值，確定如何將表分成記錄數量相等的葉塊，然後建立枝塊層，以便使葉塊層的記錄用儘可能少的步驟確定。

的例子中，枝塊是按字母順序均勻分割的。在實際中，分枝點由記錄的值確定。例如，如果一個表含有的以“A”開頭的記錄比其他字母的多得多，那麼可能一個完整的枝塊都是“A”的，下一個枝塊則從“B”開始。

B*-樹索引的優點是它可使Oracle快速地確定不需要讀取的記錄。通過使必須讀取的資料數量最小化，Oracle將更快地返回結果。

由於B*-樹索引通過根據內容將資料劃分成集合和子集合來工作，因此這種類型的索引適合於被索引的列中包含大量不同值的情況，則位元影像索引更適合。

2、位元影像索引

B*-樹索引結構最適合於檢索含有大量不同值的列，那麼，顯而易見，另一種索引結構對於只包含很少的不同值的列更有效。例如，性別的列將只含有三種可能值，即“M”（男）、“F”（女）或“U”（未知）。將數量很少的不同值放入B*-樹索引結構中沒有意義，因為B*-樹的“逐步劃分成子組”的方法對只有少量的不同值的情形幾乎無用。在這種情況下，使用位元影像索引更有意義。 圖4給出了對如何設計位元影像索引的一個簡單描述。

       在WHERE子句為一個低基數列的SELECT查詢中，預先建立該列的位元影像索引可大大減少檢索花費時間。該速度的提高是兩方面的結果：1）位元影像索引相當地小；2）儲存在位元影像索引中的“1”或“0”值可以被電腦很快地計算。

建立位元影像索引的文法如下：

CREATE BITMAP INDEX index_name ON table_name(column_name); 

5.保持資料的完整性：約束條件

(1)什麼是約束條件

約束條件（constraint)是定義一個或多個條件的一種方法，使用者的輸入在被Oracle接收進表之前必須滿足這些條件。

(2)怎樣建立約束條件

我們將學習如何建立三種不同的約束條件。當一起使用時，這些約束條件對於確保表不的資料是“乾淨”的將大有協助。

1）NOT NULL

非空，一旦使用該項約束條件，那麼被約束的列不可為空值。

在建立表的時候，使用“非空”約束。例如：

CREATE TABLE test_constraint( product_name varchar2(20) NOT NULL, product_price NUMBER(4,2) NOT NULL, purchase_date DATE ); 

如果表已經建立，則可以通過更改已有的表，使得當插入或更新記錄時，列不再接收空值。將已有列修改為NOT NULL狀態的文法如下：

ALTER TABLE table_name MODIFY (column_name NOT NULL); 例如：ALTER TABLE person MODIFY (last_name NOT NULL); ALTER TABLE person MODIFY (last_name NOT NULL); 

通過輸入代碼可以測試約束條件。

2）UNIQUE

唯一值約束條件。

添加唯一值約束條件的文法結構如下：

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_name); 例如：ALTER TABLE person ADD CONSTRAINT unique_test UNIQUE( first_name , last_name ); 

可通過使用插入記錄的語句進行測行。

3）Check

檢查（check）約束條件允許定義為了輸入的資料被Oracle數據庫接收，資料必須滿足的條件。可以給表的每個列定義一個檢查約束條件。

例如，可以要求價格列的資料必須為正值，日期列的值在某個範圍內。檢查約束條件是保證資料庫為乾淨資料的最有力的工具之一。

在一個已有表中建立某列檢查約束條件的文法如下：

ALTER TABLE table_name ADD CONSTRAINT [constraint_name] CHECK (column_name condition_to_satisfy) ; 

create table test(age number(2));       alter table test add(            constraint test_check  check(            age is not null and age>10)        );        insert into test values(12);        insert into test values(10);        insert into test values(9);        commit;

alter table purchase add(          constraint check_purchase_date check(          purchase_date is not null          and           to_char(purchase_date,’YYYYMMDD’)>’20020630’          )        );       insert into purchase valuse(‘Small Widget’,10,’28-5月-02‘,’GA’);       insert into purchase valuse(‘Small Widget’,9,’28-6月-02‘,’GA’);       insert into purchase valuse(‘Small Widget’,10,’28-7月-02‘,’GA’);

 

6.啟用和禁止已有的約束條件

約束條件可以禁止使用也可以重新載入，而不用永久地刪除它。文法如下：

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name; 

重新啟用約束條件的文法為：

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name; 例如：alter table test disable constraint test_check; insert into test values(7); insert into test values(9); alter table test enable constraint test_check;（為什麼出錯？如何插入下列資料？） insert into test values(12); insert into test values(13); 

 

7.更改和刪除已有的約束條件

生活不可預測，需求在不斷地改變，某些時候需要修改或刪除某個表的已有約束條件。

修改表的約束條件的文法如下：

ALTER TABLE table_name MODIFY (column_name NULL); 例如：alter table test modify (test_check null); alter table test modify (test_check not null); 

如果想要刪除整個約束條件，可使用以下文法完成：

ALTER TABLE table_name DROP CONSTRAINT constraint_name; 

注意：刪除約束條件是一個永久性的操作。如果你認為今後還會需要該約束條件可考慮禁用它來代替刪除它。

 

8.使用約束條件加強表之間的聯絡

為了使兩個表之間存在聯絡，必須使兩件事正確：

1）父表必須有一列（或一組列）唯一標識它所包含的每個記錄。（稱為主碼或主鍵）

2）子表必須有一個相同的列（或一組列）包含唯一標識父記錄的值。（你為外碼或外鍵）

（1）建立主鍵    如果表已存在用如下語句：       

 ALTER TABLE table_name           ADD PRIMARY KEY(column_name 1,column_name 2,……)            ;     例如：              create table st(st_no number(4),name char(10));             alter table st add primary key(st_no);

        當建立主鍵時，Oracle自動利用該主鍵在該列（或幾列）上建立一個索引。

主鍵也可以在建表時直接建立：如

create table xyqkb( xh number(4) primary key, xm char(10), dh char(15) ); 

（2）建立一個外鍵約束條件

        主鍵和外鍵是建立表之間聯絡的物理組成部分。但是，由它們自己並不能保證聯絡的完整性——也就是說，即使主鍵和外鍵的列都有完全相同的名字和資料類型，但是，Oracle並不認為它們之間有關係，除非你加以確定。即必須在子表中定義一個約束條件，使得它在接收資料進入其外鍵之前先檢查父表的主鍵。如果沒有這樣的約束條件，使用者可能會將父表中實際不存在的值輸入給子表的外鍵。

建立外鍵約束條件的文法如下：

ALTER TABLE child_table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name(s)_in_child_table) REFERENCES parent_table_name ; 

完成下面的操作建立一個學員成績表(xycjb)：

create table xycjb(xh number(4),kcbh number(4),cj number(3))； alter table xycjb add constraint xh_fk froeign key(xh) references xyqkb; 

 

9.串連運算子

1）UNION

用來返回前後兩個查詢的結果，如果有重複則只返回其中一個。串連運算子通常是在兩個表的結構類似或一致但表的內容不同的情況下使用。

用法如下：

select a,b from table_a union select a,b from table_b; [order by a] 

2）UNION ALL

基本上與UNION相同，但不去除重複和行；

select name from tst1 union all select name from tst2;

3）INTERSECT

用來返回前後兩個查詢相同的部分：

文法結構如下：

select a,b from table_a intersect select a,b from table_b ; 

4） MINUS

用來返回前面查詢減去後面查詢的部分，示:

select  name from tst1         minus        select name from tst2;

 

10.子查詢

select name from tst5 where age > (select avg(age) from tst5);