第5章索引與數據完整性索

引索引在Oracle11g數據庫中，索引是一種供服務器在表中快速查找每一行的數據庫結構。在數據庫中建立索引主要有以下作用：（1）快速存取數據；（2）既可以改善數據庫性能，又可以保證列值的唯一性；（3）實現表與表之間的參照完整性；（4）在使用ORDERBY子句、GROUPBY子句進行數據檢索時，利用索引可以減少排序和分組的時間。01索引的分類索引的分類（1）索引按存儲方法可以分為兩類：B*樹索引和位圖索引。①B*樹索引。B*樹索引的存儲結構類似圖書的索引結構，有分支和葉塊兩種類型，分支塊相當于圖書的大目錄，葉塊相當于索引到具體的頁。Oracle系統用B*樹（B*-tree）機制存儲索引條目，以保證用最短路徑訪問鍵值。②位圖索引。位圖索引主要用來節省空間，減少Oracle系統對數據塊的訪問。它采用位圖偏移方式來與表的行ID號對應。采用位圖索引一般是在重復值太多的表字段情況下。索引的分類①②③④⑤（2）索引按功能和對象可分為以下六種類型。非唯一索引。非唯一索引是指不對索引列的值進行唯一性限制。分區索引。分區索引是指索引可以分散存在于多個不同的表空間中，其優點是能夠提高數據查詢的效率。唯一索引。唯一索引意味著不會有兩行記錄相同的索引鍵值。唯一索引表中的記錄沒有RowID，所以不能再對其建立其他索引。未排序索引。未排序索引也稱為正向索引。⑥逆序索引。逆序索引也稱為反向索引。該索引同樣保持索引列按順序排列，但顛倒了已索引的每列字節。基于函數的索引。基于函數的索引是指索引中的一列或者多列是一個函數或者表達式，索引根據函數或者表達式計算索引列的值，并可以將基于函數的索引創建成為位圖索引。02使用索引的原則使用索引的原則（1）（2）（3）（4）（5）（6）當用戶考慮在表中使用索引時，應遵循下列一些基本原則。在表中插入數據后創建索引。在表中插入數據后，創建索引效率將更高。如果在裝載數據之前創建索引，那么插入每行時Oracle系統都必須更改索引。索引正確的表和列。如果經常檢索的內容少于表中數據的15%行，就需要創建索引。為了改善多個表的相互關系，常常使用索引列進行關系連接。合理安排索引列。在CREATEINDEX語句中，列的排序會影響查詢的性能，通常將最常用的列放在前面。限制表中索引的數量。盡管表可以有任意數量的索引，但是索引越多，在修改表中的數據時對索引進行相應更改的工作量越大，效率也就越低。指定索引數據塊空間。創建索引時，索引數據塊是用表中現存值填充的，達到PCTREE時為止。根據索引大小設置存儲參數。創建索引之前應先估計索引的大小，以便更好地規劃和管理磁盤空間。03創建索引1．以界面方式創建索引2．以命令方式創建索引創建索引1．以界面方式創建索引以XSB表的“姓名”列創建索引為例，使用SQLDeveloper創建索引的操作過程如下。（1）啟動SQLDeveloper，右擊要創建索引的XSB表，選擇“索引”的“創建索引”選項，如圖。創建索引（2）在彈出的“創建索引”窗口中創建索引，如圖。創建索引（3）所有選項設置完后，單擊“確定”按鈕完成索引的創建，然后單擊XSB表，在“索引”選項頁中可以看到新創建的索引XSB_NAME_INDEX，如圖。創建索引2．以命令方式創建索引使用SQL命令可以靈活方便地創建索引。在使用SQL命令創建索引時，必須滿足下列條件之一。索引的表或簇必須在自己的模式中；必須在要索引的表上具有INDEX權限；必須具有CREATEANYINDEX權限。語法格式為：CREATE[UNIQUE∣BITMAP]INDEX /*索引類型*/ [用戶方案名.]索引名 ON表名(列名|列名表達式[ASC∣DESC][,…n])[LOGGING|NOLOGGING] /*指定是否創建相應的日志記錄*/[COMPUTESTATISTICS] /*生成統計信息*/[COMPAESS|NOCOMPRESS] /*對復合索引進行壓縮*/[TABLESPACE表空間名] /*索引所屬表空間*/[SORT|NOSORT] /*指定是否對表進行排序*/[REVERSE]創建索引相關參數說明如下。（1）UNIQUE：指定索引所基于的列（或多列）值必須唯一。默認索引是非唯一索引。（2）BITMAP：指定創建位圖索引而不是B*索引。（3）用戶方案名：表示包含索引的方案。（4）ON子句：在指定表的列中創建索引，ASC和DESC分別表示升序索引和降序索引。（5）列名表達式：用指定表的列、常數、SQL函數和自定義函數創建的表達式，用于創建基于函數的索引。（6）LOLOGGING|NOLOGGING：LOGGING選項規定在創建索引時，應創建相應的日志記錄，NOGGING選項則表示創建索引時不產生重做日志信息，默認為LOGGING。（7）COMPUTESTATISTICS：表示在創建索引時直接生成索引的統計信息，這樣可以避免以后對索引進行分析操作。（8）COMPAESS|NOCOMPRESS：對于復合索引，如果指定了COMPRESS選項，則可以在創建索引時對重復的索引值進行壓縮，以節省索引的存儲空間，但對索引進行壓縮后將會影響索引的使用效率，其默認為NOCOMPRESS。（9）SORT|NOSORT：默認情況下，Oracle系統在創建索引時會對表中的記錄進行排序，如果表中的記錄已經按照順序排序，則可指定NOSORT選項，這樣可以省略創建索引時對表進行的排序操作，加快索引的創建速度。（10）REVERSE：指定以反序索引塊的字節，不包含行標識符。創建索引【例5.1】

為KCB表的課程名列創建索引。CREATEINDEXkcb_name_idx ONkcb(課程名);執行結果如圖。創建索引【例5.2】

根據XSB表的姓名列和出生時間列創建復合索引。CREATEINDEXxsb_ind ONxsb(姓名,出生時間);執行結果如圖。04維護索引1．以界面方式維護索引2．以命令方式維護索引維護索引1．以界面方式維護索引右擊XSB表選擇“編輯”選項，在“編輯表”窗口左邊選擇“索引”選項，并在中間“索引”框中選中要維護的索引，在右邊“索引屬性”組中修改索引的信息，單擊“確定”按鈕后完成修改，如圖。維護索引2．以命令方式維護索引使用ALTERINDEX命令維護索引必須在操作者自己的模式中，或者操作者擁有ALTERANYINDEX系統權限。ALTERINDEX語句的語法格式為：ALTERINDEX[用戶方案名.]索引名[LOGGING|NOLOGGING][TABLESPACE表空間名][SORT|NOSORT][REVERSE][RENAMETO新索引名]【例5.3】

重命名索引kcb_name_idx。ALTERINDEXkcb_name_idx RENAMETOkcb_idx;執行結果如圖。05刪除索引1．以界面方式刪除索引2．以命令方式刪除索引刪除索引1．以界面方式刪除索引在SQLDeveloper中，右擊索引所在的表，選擇“索引”的“刪除”選項，在彈出的“刪除”窗口中選擇要刪除的索引，如圖所示，單擊“應用”按鈕即可。刪除索引2．以命令方式刪除索引語法格式為：DROPINDEX[用戶方案名.]索引名【例5.4】

刪除XSCJ數據庫中XSB表的復合索引XSB_ind。DROPINDEXxsb_ind;第5章索引與數據完整性數據完整性數據完整性例如，假設在KCB表的“開課學期”列上定義了完整性約束，要求該列的值只能是1～8。當INSERT語句或者UPDATE語句向該列插入大于8的值，則Oracle系統將回滾語句，并返回錯誤信息。使用完整性約束有以下優勢：（1）在數據庫應用的代碼中增強了商業規則；（2）使用存儲過程，可完整控制對數據的訪問；（3）增強了觸發存儲數據庫過程的商業規則。在定義完整性約束時，一般使用SQL語句。當定義和修改完整性約束時，不需要額外編程。SQL語句很容易編寫，可減少編程錯誤，Oracle系統能夠控制其功能。01數據完整性的分類

1．域完整性2．實體完整性3．參照完整性4．完整性約束的狀態數據完整性的分類

1．域完整性域完整性又稱列完整性，指一個數據集對某一個列是否有效和確定是否允許空值。域完整性通常是使用有效性檢查來實現的，還可以通過限制數據類型、格式或者可能的取值范圍來實現。例如，對于數據庫XSCJ的KCB表，課程的學分應在0～10分，為了對學分這一數據項輸入的數據范圍進行限制，可以在定義KCB表的同時定義學分列的約束條件以達到此目的。2．實體完整性實體完整性又稱行完整性，要求表中的每一行都有一個唯一的標識符，這個標識符就是主關鍵字，如居民身份證號是唯一的，這樣才能唯一地確定某一個人。通過索引、UNIQUE約束、PRIMARYKEY約束可實現數據的實體完整性。數據完整性的分類

3．參照完整性主鍵是指在表中能唯一標識表的每個數據行的一個或多個表列。外鍵是指如果一個表中的一個或若干個字段的組合是另一個表的主鍵，則稱該字段或字段組合為該表的外鍵。例如，對于XSCJ數據庫中XSB表的每一個學號，在CJB表中都有相關的課程成績記錄，將XSB作為主表，“學號”字段定義為主鍵，CJB作為從表，表中的“學號”字段定義為外鍵，從而建立主表和從表之間的聯系實現參照完整性。XSB和CJB表的對應關系如表。數據完整性的分類

一旦定義了兩個表之間的參照完整性，則有如下要求。（1）從表不能引用不存在的鍵值。例如，對于CJB表中行記錄出現的學號必須是XSB表中已存在的學號。如果主表中的鍵值更改了，那么在整個數據庫中，對從表中該鍵值的所有引用都要進行一致的更改。如果主表中沒有關聯的記錄，則不能將記錄添加到從表中。如果要刪除主表中的某條記錄，應先刪除從表中與該記錄匹配的相關記錄。（2）（3）（4）數據完整性的分類

完整性約束是通過限制列數據、行數據和表之間數據來保證數據完整性的有效方法，約束是保證數據完整性的標準方法，每一種數據完整性類型都可以由不同的約束類型來保障，約束能確保有效的數據輸入到列中并維護表與表之間的關系，如表。約束類型描述NOTNULL指定表中某個列不允許空值，必須為該列提供值UNIQUE使某個列或某些列的組合唯一，防止出現冗余值PRIMARYKEY使某個列或某些列的組合唯一，也是表的主關鍵字FOREIGNKEY使某個列或某些列為外關鍵字，其值與本表或者另表的主關鍵字匹配，實現引用完整性CHECK指定表中的每一行數據必須滿足的條件數據完整性的分類

4．完整性約束的狀態在Oracle系統中，完整性約束有四種狀態。（1）（2）（3）（4）禁止的非校驗狀態。表示該約束是不起作用的，即使該約束定義依然存儲在數據字典中。禁止的校驗狀態。表示對約束列的任何修改都是禁止的。這時該約束上的索引都被刪除，約束也被禁止，但仍然可以向表中有效地添加數據，即使這些數據與約束有沖突也沒關系。允許的非校驗狀態或強制狀態。該狀態可以向表中添加數據，但是與約束有沖突的數據不能添加。允許的校驗狀態。表示約束處于正常的狀態。這時表中所有數據（無論是已有的還是新添加的）都必須滿足約束條件。02域完整性的實現

1．以界面方式操作CHECK約束2．以命令方式操作CHECK約束域完整性的實現

1．以界面方式操作CHECK約束在XSCJ數據庫的CJB表中，學生每門功課的成績為0～100分，如果對用戶的輸入數據施加這個限制，則需要創建CHECK約束，其操作過程如下。右擊CJB表，選擇“約束條件”的“添加檢查”選項，如圖，彈出“添加檢查”窗口，在“約束條件名稱”欄中輸入約束名CH_CJ，并在“檢查條件”欄中輸入CHECK約束的條件“成績>=0AND成績<=100”，完成后單擊“應用”按鈕完成CHECK約束的創建。域完整性的實現

如果要修改或刪除已經創建的CHECK約束，可右擊CJB表選擇“編輯”選項，進入“檢查約束條件”選項頁面中，如圖。域完整性的實現

2．以命令方式操作CHECK約束（1）在創建表時創建約束語法格式為：CREATETABLE表名( 列名數據類型[DEFAULT默認值][NOTNULL|NULL] [CONSTRAINTCHECK約束名]CHECK(CHECK約束表達式) /*定義為列的約束*/ [,…n] [CONSTRAINTCHECK約束名]CHECK(CHECK約束表達式) /*定義為表的約束*/)域完整性的實現

【例5.5】

定義kcb2表，同時定義其“學分”列的約束條件。CREATETABLEkcb2(

課程號 char(3) NOTNULL,

課程名 char(16) NOTNULL,

開課學期 number(1) NULL,

學時 number(2) NULL,

學分 number(1) CHECK(學分>=0AND學分<=10)NOTNULL /*定義為列的約束*/);執行結果如圖。域完整性的實現

【例5.6】

在XSCJ數據庫中創建books表，其中包含所有的約束定義。CREATETABLEbooks( book_id number(10), book_name varchar2(50) NOTNULL, book_desc varchar2(50) DEFAULT'Newbook', max_lvl number(3,2) NOTNULL, trade_price number(4,1) NOTNULL, CONSTRAINTch_costCHECK(max_lvl<=250) /*定義為表的約束*/);執行結果如圖。域完整性的實現

（2）在修改表時創建約束語法格式為：ALTERTABLE表名 ADD(CONSTRAINTCHECK約束名CHECK(CHECK約束表達式))說明：ADDCONSTRAINT表示在已定義的表中增加一個約束定義。【例5.7】通過修改XSCJ數據庫的books表，增加trade_price（批發價）字段的CHECK約束。ALTERTABLEbooks ADD(CONSTRAINTch_priceCHECK(trade_price<=250));執行結果如圖。域完整性的實現

（3）刪除約束語法格式為：ALTERTABLE表名 DROPCONSTRAINTCHECK約束名說明：該語句在指定的表中，刪除名為指定名稱的CHECK約束。【例5.8】

刪除XSCJ數據庫的books表中“批發價”字段的CHECK約束。ALTERTABLEbooks DROPCONSTRAINTch_price;03實體完整性的實現1．以界面方式操作主鍵及唯一約束2．命令方式操作主鍵及唯一約束實體完整性的實現1．以界面方式操作主鍵及唯一約束使用SQLDeveloper創建和刪除PRIMARYKEY約束的方法：右擊要創建約束的表，選擇“編輯”選項，并在“編輯表”窗口中選擇“主鍵”選項，在右邊頁面中選擇要添加或刪除的主鍵列，如圖。實體完整性的實現創建和刪除UNIQUE約束的方法：在“編輯表”窗口中選擇“唯一約束條件”選項，單擊窗口右側的“添加”按鈕，在“名稱”欄中填寫UNIQUE約束的名稱，在“可用列”欄中選擇要添加UNIQUE約束的列后，單擊按鈕，將其添加到“所選列”欄中，單擊“確定”按鈕完成添加，如圖。實體完整性的實現2．命令方式操作主鍵及唯一約束（1）創建表的同時創建約束語法格式為：CREATETABLE表名 /*指定表名*/ (列名數據類型[NULL|NOTNULL] /*定義字段*/ {[CONSTRAINT約束名] /*定義約束名*/ PRIMARYKEY|UNIQUE} /*定義約束類型*/ [,…n] [,[CONSTRAINT約束名]{PRIMARYKEY|UNIQUE}(列名,[,…n])]])在上面語法中，通過關鍵字PRIMARYKEY、UNIQUE指定所創建的約束類型。實體完整性的實現【例5.9】

對XSCJ數據庫的XSB2表中“學號”字段創建PRIMARYKEY約束，并對“身份證號”字段定義UNIQUE約束。CREATETABLExsb2(

學號 char(6) NOTNULLCONSTRAINTPK_XHPRIMARYKEY,

姓名 char(8) NOTNULL,

身份證號 char(20) NOTNULLCONSTRAINTUN_IDUNIQUE,

性別 char(2) DEFAULT'1'NOTNULL,

出生時間 date NOTNULL,

專業 char(12) NULL,

總學分 number(2) NULL,

備注 varchar2(200) NULL);執行結果如圖。實體完整性的實現（2）通過修改表來創建約束語法格式為：ALTERTABLE表名 ADD([CONSTRAINT約束名]{PRIMARYKEY|UNIQUE}(列名[,…n])說明：ADDCONSTRAINT表示對指定表增加一個約束，約束類型為PRIMARYKEY或UNIQE。【例5.10】

先在XSCJ數據庫中創建XSB3表，然后通過修改表，對“學號”字段創建PRIMARYKEY約束，對“身份證號”字段創建UNIQUE約束。CREATETABLExsb3(

學號 char(6) NOTNULL,

姓名 char(8) NOTNULL,

身份證號char(20) NOTNULL,

性別 char(2) DEFAULT'1'NOTNULL,

出生時間date NOTNULL,

專業 char(12) NULL,

總學分 number(2)NULL,

備注 varchar2(200)NULL);ALTERTABLExsb3 ADD(PRIMARYKEY(學號));ALTERTABLExsb3 ADD(CONSTRAINTUN_XSUNIQUE(身份證號));執行結果如圖。實體完整性的實現（3）刪除約束刪除PRIMARYKEY或UNIQUE約束主要通過ALTERTABLE語句的DROP子句進行。語法格式為：ALTERTABLE表名 DROPCONSTRAINT約束名[,…n];【例5.11】

刪除XSB3的UN_XS約束。ALTERTABLExsb3 DROPCONSTRAINTUN_XS;04參照完整性的實現1．以界面方式操作表間的參照關系2．以命令方式操作表間的參照關系參照完整性的實現1．以界面方式操作表間的參照關系例如，建立XSB表和CJB表之間的參照完整性，其操作步驟如下。選擇CJB表，右擊選擇“編輯”選項，并在“編輯表”窗口中選擇“外鍵”選項，如圖。參照完整性的實現2．以命令方式操作表間的參照關系創建主鍵（PRMARYKEY約束）的方法已介紹了，在此將介紹通過SQL命令創建外鍵的方法。（1）在創建表的同時定義外鍵約束語法格式為：CREATETABLE從表名( 列定義[CONSTRAINT約束名]REFERENCES主表名[(列名[,...n])] [,…n] [[CONSTRAINT約束名][FOREIGNKEY(列名[,...n])[參照表達式]]);其中：參照表達式::= REFERENCES主表名[(列名[,...n])] [ONDELETE{CASCADE|SETNULL}]參照完整性的實現定義外鍵時還可以指定如下兩種參照動作：ONDELETECASCADE表示從主表刪除數據時自動刪除從表中匹配的行；ONDELETESETNULL表示從主表刪除數據時，設置從表