數(shù)據(jù)庫設(shè)計參考標(biāo)準(zhǔn)

1、數(shù)據(jù)庫設(shè)計參考標(biāo)準(zhǔn)文檔控制文檔屬性文檔編號作者版本日期密級EDS06黃翠微V12011 年 6 月 16文檔修訂歷史版本狀態(tài)日期作者修訂描述概述為明確公司項目中數(shù)據(jù)庫邏輯設(shè)計及物理設(shè)計的內(nèi)容和流程，特制定本規(guī)范，供數(shù)據(jù)庫設(shè)計、開發(fā)及維護(hù)人員參考。數(shù)據(jù)庫設(shè)計方法目前可分為四類：直觀設(shè)計法、規(guī)范設(shè)計法、計算機(jī)輔助設(shè)計法和自動化設(shè)計法。新奧爾良法是目前公認(rèn)的比較完整和權(quán)威的一種規(guī)范設(shè)計法。新奧爾良法將數(shù)據(jù)庫設(shè)計分成需求分析（分析用戶需求）、概念設(shè)計（信息分析和定義）、邏輯設(shè)計（設(shè)計實現(xiàn)）和物理設(shè)計（物理數(shù)據(jù)庫設(shè)計）。目前，常用的規(guī)范設(shè)計方法大多起源于新奧爾良法，并在設(shè)計的每一階段采用一些輔助方法來具

2、體實現(xiàn)。以下是兩種常用的規(guī)范設(shè)計方法：1 . 基于 E-R 模型的數(shù)據(jù)庫設(shè)計方法。該方法是由 P.P.S.chen 于 1976 年提出的數(shù)據(jù)庫設(shè)計方法，其基本思想是在需求分析的基礎(chǔ)上，用 E-R （實體 聯(lián)系）圖構(gòu)造一個反映現(xiàn)實世界實體之間聯(lián)系的企業(yè)模式，然后再將此企業(yè)模式轉(zhuǎn)換成基于某一特定的 DBMS 的概念模式。2 .基于3NF的數(shù)據(jù)庫設(shè)計方法。該方法是由S Atre提出的結(jié)構(gòu)化設(shè)計方法，其基本思想是在需求分析的基礎(chǔ)上，確定數(shù)據(jù)庫模式中的全部屬性和屬性間的依賴關(guān)系，將它們組織在一個單一的關(guān)系模式中，然后再分析模式中不符合 3NF 的約束條件， 將其進(jìn)行投影分解， 規(guī)范成若干個3NF 關(guān)系

3、模式的集合。 其具體設(shè)計步驟分為五個階段：（ 1 ） 設(shè)計企業(yè)模式， 利用規(guī)范化得到的 3NF 關(guān)系模式畫出企業(yè)模式；（ 2 ） 設(shè)計數(shù)據(jù)庫的概念模式，把企業(yè)模式轉(zhuǎn)換成DBMS 所能接受的概念模式，并根據(jù)概念模式導(dǎo)出各個應(yīng)用的外模式；（ 3 ）設(shè)計數(shù)據(jù)庫的物理模式（存儲模式）；（ 4 ）對物理模式進(jìn)行評價；（ 5 ）實現(xiàn)數(shù)據(jù)庫。備注：數(shù)據(jù)庫設(shè)計規(guī)范、數(shù)據(jù)編程規(guī)范、數(shù)據(jù)庫物理設(shè)計規(guī)范中以 Oracle 數(shù)據(jù)庫為例，其它結(jié)構(gòu)的數(shù)據(jù)庫類似。二、 數(shù)據(jù)庫設(shè)計流程以規(guī)范性設(shè)計為例，把數(shù)據(jù)庫設(shè)計流程分為以下幾個階段。（一 ）需求分析階段1. 需求收集和分析，得到數(shù)據(jù)字典描述的數(shù)據(jù)需求和數(shù)據(jù)流圖描述的處理需

4、求。2. 需求分析的重點：調(diào)查、收集與分析用戶在數(shù)據(jù)管理中的信息要求、處理要求、安全性與完整性要求。3. 需求分析的方法：調(diào)查組織機(jī)構(gòu)情況、各部門的業(yè)務(wù)活動情況、協(xié)助用戶明確對新系統(tǒng)的各種要求、確定新系統(tǒng)的邊界。4. 常用的調(diào)查方法有： 跟班作業(yè)、開調(diào)查會、請專人介紹、詢問、設(shè)計調(diào)查表請用戶填寫、查閱記錄。5. 分析和表達(dá)用戶需求的方法：主要包括自頂向下和自底向上兩類方法。采用逐層分解的方式分析系統(tǒng)， 并把每一層用數(shù)據(jù)流圖和數(shù)據(jù)字典描述。6. 數(shù)據(jù)流圖（Data Flow Diagram , DFD）表達(dá)了數(shù)據(jù)和處理過程的關(guān)系。系統(tǒng)中的數(shù)據(jù)則借助數(shù)據(jù)字典（Data Dictionary ,簡稱

5、DD）來描述。（二 ）概念結(jié)構(gòu)設(shè)計階段概念模型用于信息世界的建模。概念模型不依賴于某一個DBMS 支持的數(shù)據(jù)模型。通過對用戶需求進(jìn)行綜合、歸納與抽象，形成一個獨(dú)立于具體DBMS的概念模型，可以用 E-R 圖表示。概念模型可以轉(zhuǎn)換為計算機(jī)上某一DBMS 支持的特定數(shù)據(jù)模型，其特點為：1. 具有較強(qiáng)的語義表達(dá)能力，能夠方便、直接地表達(dá)應(yīng)用中的各種語義知識。2. 應(yīng)該簡單、清晰、易于用戶理解，是用戶與數(shù)據(jù)庫設(shè)計人員之間進(jìn)行交流的語言。（三）邏輯設(shè)計階段將概念結(jié)構(gòu)轉(zhuǎn)換為某個DBMS 所支持的數(shù)據(jù)模型（例如關(guān)系模型）， 并對其進(jìn)行優(yōu)化。將E-R 圖轉(zhuǎn)換為關(guān)系模型實際上就是要將實體、實體的屬性和實體之間的

6、聯(lián)系轉(zhuǎn)化為關(guān)系模式,這種轉(zhuǎn)換一般遵循如下原則：1. 一個實體型轉(zhuǎn)換為一個關(guān)系模式，實體的屬性就是關(guān)系的屬性；2. 一個多對多的聯(lián)系轉(zhuǎn)換為一個關(guān)系模式。數(shù)據(jù)模型的優(yōu)化，確定數(shù)據(jù)依賴，消除冗余的聯(lián)系，確定各關(guān)系模式分別屬于第幾范式。確定是否要對它們進(jìn)行合并或分解。一般來說將關(guān)系分解為 3NF 的標(biāo)準(zhǔn)。（四 ） 物理設(shè)計階段為邏輯數(shù)據(jù)模型選取一個最適合應(yīng)用環(huán)境的物理結(jié)構(gòu) （包括存儲結(jié)構(gòu)和存取方法 ）。根據(jù)DBMS 特點和處理的需要，進(jìn)行物理存儲安排，設(shè)計索引，形成數(shù)據(jù)庫內(nèi)模式。（五 ） 數(shù)據(jù)庫實施階段運(yùn)用DBMS提供的數(shù)據(jù)語言（例如SQL）及其宿主語言（例如C）,根據(jù)邏輯設(shè) 計和物理設(shè)計的結(jié)果建立數(shù)

7、據(jù)庫， 編制與調(diào)試應(yīng)用程序， 組織數(shù)據(jù)入庫， 并進(jìn)行 試運(yùn)行。（六）數(shù)據(jù)庫運(yùn)行維護(hù)階段在數(shù)據(jù)庫系統(tǒng)運(yùn)行過程中必須不斷地對其進(jìn)行評價、 調(diào)整與修改。 內(nèi)容包括：數(shù)據(jù)庫的轉(zhuǎn)儲和恢復(fù)、數(shù)據(jù)庫的安全性、完整性控制、數(shù)據(jù)庫性能的監(jiān)督、分析和改進(jìn)、數(shù)據(jù)庫的重組織和重構(gòu)造。為加快數(shù)據(jù)庫設(shè)計速度，目前有很多數(shù)據(jù)庫輔助工具（CASE 工具 ） ，如Rational 公司的 Rational Rose ， CA 公司的 Erwin 和 Bpwin ， Sybase 公司的 PowerDesigner 以及 Oracle 公司的 Oracle Designer 等。數(shù)據(jù)庫設(shè)計規(guī)范（一）數(shù)據(jù)庫規(guī)范化的總體要求1. 數(shù)

8、據(jù)表中避免可空列雖然表中允許空列， 但空字段為一種特殊的數(shù)據(jù)類型， 數(shù)據(jù)庫將對其進(jìn)行特 殊的處理，為此將增加數(shù)據(jù)庫處理記錄的復(fù)雜性，且當(dāng)表中存在較多空字段時，在同等條件下， 數(shù)據(jù)庫處理的性能將降低許多。 因此， 在數(shù)據(jù)庫表設(shè)計時應(yīng)盡量避免。 若確實需要， 可通過一些折中方式處理， 讓其對數(shù)據(jù)庫性能的影響降低至 最少。在此推薦兩個方式。A 設(shè)置默認(rèn)值。 默認(rèn)值設(shè)置原則為業(yè)務(wù)啟動后不可能達(dá)到的值， 或者為 原始狀態(tài)的對應(yīng)值。 如無法從數(shù)值上區(qū)分是否為原始狀態(tài)則不推薦設(shè) 置默認(rèn)值。 以數(shù)值型字段為例， 假設(shè)其用于存放采集終端所獲取的子 艙位訂座， 由于訂座數(shù)默認(rèn)至少為大于等于 0 的數(shù)值， 為此可設(shè)

9、置默 認(rèn)值為 -1 。另一個例子，字符型的航班狀態(tài)，默認(rèn)情況下航班為正常 執(zhí)行的正班，當(dāng)因為天氣、機(jī)械或其它調(diào)控等原因狀態(tài)才發(fā)生改變， 此時可設(shè)置默認(rèn)值為“正班” 。日期型數(shù)據(jù)可默認(rèn)為 1900-01-01 ，在 應(yīng)用層面顯示需要根據(jù)實際情況進(jìn)行轉(zhuǎn)換。B 第二種方式是建立副表。 這是當(dāng)一張表中允許為空的列較多時， 如接 近表全部列數(shù)的三分之一， 且這些列數(shù)據(jù)在大多情況下均為空， 此時 建議另外建立一張副表， 以保存這些列； 主表與副表之間根據(jù)關(guān)鍵字 關(guān)聯(lián)，這樣將數(shù)據(jù)分別存儲于兩個獨(dú)立的表中使得主表設(shè)計更為簡 單，既保證了數(shù)據(jù)庫性能，又能滿足存儲空值的應(yīng)用需要。如果字段默認(rèn)值設(shè)置導(dǎo)致部分業(yè)務(wù)需求

10、無法滿足或開發(fā)代價大量增加，此時不建議設(shè)置， 保留可空。 如數(shù)值型字段， 如在該字段上需要與其他字段進(jìn)行組合運(yùn)算、 或基于該字段進(jìn)行統(tǒng)計匯總時， 空值具有不可替代的重要作用。2. 數(shù)據(jù)表中不應(yīng)存在重復(fù)數(shù)據(jù)值或列如客戶數(shù)據(jù)與客戶經(jīng)理數(shù)據(jù)存儲問題，如將二者放在同一張表中，為解決多個客戶經(jīng)理問題， 表中必須設(shè)置首選客戶經(jīng)理、 備選客戶經(jīng)理相關(guān)信息。 在客戶經(jīng)理離職后，必須修改表中所有相關(guān)數(shù)據(jù)，造成很多不便且不利于追蹤。為此，在數(shù)據(jù)庫設(shè)計的時候要盡量避免現(xiàn)象發(fā)生，建議改變策略，將客戶信息存入一張表， 客戶經(jīng)理信息存入一張表， 而客戶與客戶經(jīng)理之間的聯(lián)系關(guān)系存入第三張表。當(dāng)聯(lián)系關(guān)系發(fā)生變化時，僅需設(shè)置過

11、期日期或狀態(tài)即可。3. 數(shù)據(jù)表主鍵設(shè)置為無意義數(shù)值序列號在進(jìn)行數(shù)據(jù)庫表設(shè)計的時候，采用一個無意義的、數(shù)值型的 PKID 對行記錄進(jìn)行唯一的標(biāo)識，而不是通過航班號、航段、姓名、工號等具有業(yè)務(wù)意義的字段區(qū)分記錄。由于人工管理下很難保證PKID 值的不重復(fù)，為此建議每個表對應(yīng)的PKID 列的值由數(shù)據(jù)庫自動管理。建議在原有業(yè)務(wù)上需要唯一區(qū)分的字段或字段組合上，建立唯一性索引。以存儲10 年的航班計劃表為例，業(yè)務(wù)上唯一區(qū)分一個航班的通常包含三個字段，航班號、航段、起飛日期，先前的做法是在這三個上建立復(fù)合主鍵。在此推薦用一個Number （ 8）的 PKID 列作為主鍵，原因是以每天 600 個航班、一年

12、365 天， 10 年總計 219 萬個航班，考慮到業(yè)務(wù)的擴(kuò)展速度為 10 年內(nèi)翻三番， 657 萬， 按理可設(shè)置7 位， 考慮到其它可能損耗的 ID 值，多設(shè)置一位， 故為 Number（8） ； 在航班數(shù)據(jù)的增刪改過程中， 通常需要判斷是否存在重復(fù)數(shù)據(jù)， 是則為修改操作， 否則為插入操作， 此時僅以 PKID 列無法區(qū)分，為此建議在航班號、 航段、 起飛日期上建立唯一性索引， 由數(shù)據(jù)庫后臺執(zhí)行一致性校驗。利用 Sequence 生成的 PKID 若需要用于其他數(shù)據(jù)庫使用，需要在應(yīng)用層面記錄，以防止數(shù)據(jù)庫出現(xiàn)問題時恢復(fù)后 sequence 不匹配問題。4. 數(shù)據(jù)庫對象前綴名統(tǒng)一一個應(yīng)用系統(tǒng)對

13、應(yīng)的數(shù)據(jù)庫表、視圖、過程等對象，通常均高達(dá)千計，為對其進(jìn)行規(guī)范管理與快速定位， 要求遵循數(shù)據(jù)對象命名規(guī)范。 具體命名規(guī)范詳見下文第（二）點。5. 數(shù)據(jù)表上避免設(shè)置外鍵外鍵的作用在于兩方面，一是保證主從表數(shù)據(jù)一致性，這是數(shù)據(jù)庫提供的、用于保證數(shù)據(jù)質(zhì)量的強(qiáng)制性手段， 二是定義主表數(shù)據(jù)刪除時從表數(shù)據(jù)的級聯(lián)刪除方式；二者是外鍵的優(yōu)勢所在，減少了開發(fā)人員不分工作量。但是，外鍵的存在同樣帶來困擾。一是從應(yīng)用系統(tǒng)的三層架構(gòu)上考慮，如何設(shè)置外鍵關(guān)系到業(yè)務(wù)邏輯，這是將業(yè)務(wù)邏輯層部分功能后移至數(shù)據(jù)庫層，導(dǎo)致各層職責(zé)不清的緊耦合現(xiàn)象，而且當(dāng)業(yè)務(wù)邏輯隨著需求發(fā)展發(fā)生變化將引起數(shù)據(jù)庫層面與應(yīng)用層面的雙重修改； 二是在開

14、發(fā)過程中， 開發(fā)人員不得不了解數(shù)據(jù)庫物理設(shè)計細(xì)節(jié)， 在項目協(xié)作分工上造成不便； 三是從后續(xù)主數(shù)據(jù)、 各業(yè)務(wù)方向公共數(shù)據(jù)建設(shè)上， 為數(shù)據(jù)庫管理員在執(zhí)行數(shù)據(jù)表從 “公有區(qū)” 移入移出操作增加難度。為此，建議不設(shè)置外鍵；如果設(shè)置了外鍵，則僅保留數(shù)據(jù)一致性校驗功能，不建議設(shè)置級聯(lián)刪除的選項。此時要求開發(fā)人員通過培養(yǎng)一種良好的編程習(xí)慣，從程序邏輯上實現(xiàn)主表、從表數(shù)據(jù)的增、刪、改、查的規(guī)范操作。具體可參考如下說明。A. 從表數(shù)據(jù)增加操作首先從主表上查詢是否存在所需數(shù)據(jù)，是則可進(jìn)行增加操作；否則需先增加主表數(shù)據(jù)后才可進(jìn)行從表數(shù)據(jù)增加操作。B. 從表數(shù)據(jù)刪除操作直接刪除從表數(shù)據(jù)。當(dāng)主表數(shù)據(jù)未被任何從表引用時，

15、根據(jù)實際業(yè)務(wù)需要判斷是否級聯(lián)刪除主表數(shù)據(jù)。C. 從表數(shù)據(jù)修改操作從表數(shù)據(jù)修改后的內(nèi)容必須是主表內(nèi)存在的數(shù)據(jù)，否則需先增加主表數(shù)據(jù)后才可進(jìn)行數(shù)據(jù)修改操作。D. 主表數(shù)據(jù)增加操作直接數(shù)據(jù)數(shù)據(jù)，與從表數(shù)據(jù)無關(guān)。E. 主表數(shù)據(jù)刪除操作刪除之前判斷是否存在引用的從表數(shù)據(jù)，是則根據(jù)業(yè)務(wù)規(guī)則決定是否繼續(xù)，并根據(jù)業(yè)務(wù)規(guī)則判斷從表引用數(shù)據(jù)的處理方式為對應(yīng)數(shù)據(jù)項置空或刪除從表數(shù)據(jù)記錄。如果未存在引用的從表數(shù)據(jù)，則可直接刪除。注意保持事務(wù)的原子性，即主、從表數(shù)據(jù)同時提交或同時回滾。F. 主表數(shù)據(jù)修改操作判斷是否存在引用的從表數(shù)據(jù)，是則首先修改從表數(shù)據(jù)，然后進(jìn)行主表數(shù)據(jù)修改。注意保持事務(wù)的原子性，即主、從表數(shù)據(jù)同時提

16、交或同時回滾。為了在應(yīng)用程序上能順利完成上述六項操作， 開發(fā)人員除了嚴(yán)格記錄主從表調(diào)用關(guān)系外， 還必須在程序上實現(xiàn)數(shù)據(jù)一致性檢查功能， 以及時發(fā)現(xiàn)可能的數(shù)據(jù)不一致現(xiàn)象。 如果應(yīng)用程序未能實現(xiàn)這種一致性檢查工作， 則需將檢查規(guī)則提交予 DBA ，由 DBA 在后臺進(jìn)行定期檢查。6. 避免在數(shù)據(jù)庫上編寫存儲過程、代碼包、觸發(fā)器通常存儲過程、代碼包、觸發(fā)器包含很多業(yè)務(wù)邏輯，違背了三層架構(gòu)設(shè)計的松耦合原則。（二 ）數(shù)據(jù)對象命名規(guī)范1.命名規(guī)范總要求所有數(shù)據(jù)對象命名必須遵循如下規(guī)范要求。（ 1 ） 通常采用“望文知義”的形式命名，部分對安全具有特殊要求的可以編號命名。(2) 命名格式是，以字母打頭，包含

17、具有特定含義的一個或多個英文單詞, 單詞之間以下劃線“ ”間隔，長度不超過30個字符；(3) 大小寫不敏感。(4) 禁止使用關(guān)鍵字命名。(5) 不同數(shù)據(jù)對象命名的前綴要求如下表所示。編號數(shù)據(jù)對象前綴要求1非臨時數(shù)據(jù)表“4位模塊名表名”?？?r臨時數(shù)據(jù)表“TMP_表名”3分區(qū)表“ PT ”4主鍵“PK_表名”5索引“IDX_”6函數(shù)索引“FIDX_”7視圖“VW_”8實體化視圖“MV_"9存儲過程“SP_"10觸發(fā)器“TRG_”11函數(shù)“SF_"12數(shù)據(jù)包“PKG_”13予列“SEQ ”14數(shù)據(jù)類型“TYP ”15后臺任務(wù)“JB ”2 .表命名規(guī)范對于同一應(yīng)用，在命

18、名上區(qū)分不同功能模塊的所使用的表。 假設(shè)一個系統(tǒng)包 含三個模塊，基礎(chǔ)維護(hù)、查詢、系統(tǒng)管理，則基礎(chǔ)維護(hù)表名前綴為“TB_BASE_" 查詢模塊表名前綴為“ TB_QURY_"，后面加上具體的表名。對于一些特定作用的表，增加特定后綴表示。如歷史航班表可命名為 TB_FLIGHT_HISTORY ,復(fù)雜計算中間過程所產(chǎn)生的臨時數(shù)據(jù)表可命名為 TB_MIDTERM_DATA_TEMP 。3 .字段命名規(guī)范字段命名規(guī)范符合命名規(guī)范總要求。字段類型選擇滿足(1) 固定長度的字符串類型采用CHAR,非固定長度的字串類型采用VARCHAR2。(2) 日期型字段采用日期類型。(3) 數(shù)字型字

19、段采用NUMBER類型，并標(biāo)明長度與小數(shù)位數(shù)。(4) 如無特殊需要，避免使用大字段(BLOB, CLOB, LONG等)。(5) 相同業(yè)務(wù)字段出現(xiàn)在不同表中，使用相同命名，且保證類型和長度一致。4 .索引命名規(guī)范A.復(fù)合索引至少包含前兩個字段的縮寫或全名。B.在表上創(chuàng)建王鍵時，必須顯式指定王鍵唯一性約束名稱。舉例：為表TB_FLIGHT創(chuàng)建主鍵，正確格式為“alter table TB_FLIGHT add constraint PK_FLIGHT primary key (FLIGHT_ID) ”，語句中指定了主鍵的 唯一性約束名為 PK_FLIGHT。而錯誤的格式：“alter table

20、 TB_FLIGHT add primary key (FLIGHT_ID) ”此中，主鍵唯一性約束將被系統(tǒng)隨機(jī)分配一個名字。5 .分區(qū)命名規(guī)范采用可容易判斷各分區(qū)內(nèi)所存數(shù)據(jù)內(nèi)容的名字，如對表 TB_FLIGHT按季度 進(jìn)行分區(qū)，則各分區(qū)命名格式為“ PT_ XXXXQN "，其中XXXX為年份，N為1 至4的季度編號。6 .觸發(fā)器命名規(guī)范命名規(guī)則：trg_表名 _<before/after>_<ins/upd/del> 。如表 TB_FLIGHT 行插 入后的觸發(fā)器名字為，TRG_TB_FLIGHT_AFTER_INS 。(三)數(shù)據(jù)庫設(shè)計說明書格式數(shù)據(jù)庫設(shè)計

21、說明書的格式至少包含如下部分。1 .概述：從整體上說明數(shù)據(jù)庫設(shè)計思想、采用的方法、作用范圍、參考的 文獻(xiàn)、文檔結(jié)構(gòu)組成等。2 .數(shù)據(jù)表清單編R表英文名表中文名稱表描述使用的功能模塊1表12表23 . ER關(guān)系描述描述表之間存在的主外鍵聯(lián)系等。4 .數(shù)據(jù)表定義1）表1字段編R字段名稱中文描述數(shù)據(jù)Null標(biāo)志是否主鍵外鍵是否索引備注12備注數(shù)據(jù)量增長說明、外鍵關(guān)系說明等2）表25 .各系統(tǒng)相關(guān)的其它信息。要求在“數(shù)據(jù)表清單”每一行的表名上定義“超鏈接”指向“數(shù)據(jù)表定義” 中的對應(yīng)內(nèi)容，以方便地在文檔中瀏覽跳轉(zhuǎn)等。四、數(shù)據(jù)庫編程規(guī)范（一）書寫規(guī)范1 .關(guān)鍵字大寫，其他代碼統(tǒng)一使用小寫。2 .確保變量

22、和參數(shù)在類型和長度上與表數(shù)據(jù)列類型和長度相匹配。建議采用如下格式：vs_flightno tb_flight.flight_no%TYPE這樣當(dāng)tB_FLIGHT 一表的FLIGHT_NO字段定義發(fā)生變化時程序無需調(diào) 整。3.參數(shù)和變量命名前綴遵循如下規(guī)范:項目前綴要求傳入?yún)?shù)“一 力 ip_傳出參數(shù)« _ _ 力 op_數(shù)值型變量«_ 力 vn_字符型變量« _力 vs_日期型變量vd_4 .程序塊中的begin、end獨(dú)立成行。5 .程序塊采用縮進(jìn)風(fēng)格書寫，保證代碼清晰易讀，縮進(jìn)格數(shù)統(tǒng)一。6 . 一行僅寫一條語句。7 .同一語句占用多行時，每行的第一個關(guān)鍵字左

23、對齊。8 .對于Insert values和update語句，一行寫一個字段，字段后面緊跟注釋（注釋語句左對齊） ， values 和 insert 左對齊，左括號和右括號與 insert 、 values 左對齊例：insert into table_name（user_id,-用戶ID ，主鍵user_name,-用戶名login_name-登錄名）values（v_user_id,v_user_name, v_login_name）9 . 相對獨(dú)立的程序塊之間增加一空行。10 . 超過 110 列的語句要分行書寫，長表達(dá)式應(yīng)在低優(yōu)先級操作符處換行，操作符或關(guān)鍵字放在新行之首。（二 ）注釋

24、規(guī)范1. 以統(tǒng)一格式在頭部對代碼進(jìn)行注釋，以說明代碼所要完成功能、傳入?yún)?shù)、傳出參數(shù)、作者、創(chuàng)建時間，并在其后從修改日期、修改者、修改內(nèi)容角度描述各次修改歷史。/*功能描述： 傳入?yún)?shù)： 傳出參數(shù)： 返回結(jié)果： 作者： 創(chuàng)建日期： 修改歷史： （ 1 ） YYYY-MM-DD ：作者1 ，修改內(nèi)容1 ；（ 2 ） YYYY-MM-DD ：作者2 ，修改內(nèi)容2 ；*/2. 在所有變量定義的右側(cè)或上方，進(jìn)行注釋以說明變量的用途與含義。3. 注釋內(nèi)容清晰、明了，并確保無二義性。4. 對每一程序分支書寫注釋。5. 在代碼的功能分支、子層次上注釋，以幫助維護(hù)人員理解代碼。例：case vs_type1w

25、hen 1 then -里程獎勵when 2 then -里程扣除when 3 then -里程兌換end case;6. 代碼注釋應(yīng)放在描述的代碼上方或右方相近位置，不可放在下面。7. 注釋與所描述的內(nèi)容進(jìn)行字?jǐn)?shù)的縮進(jìn)排列。8. 在函數(shù)中，對所返回的代碼進(jìn)行詳細(xì)描述。9. 在程序塊的結(jié)束行右方加注釋，以表示程序塊結(jié)束。（三 ）語法規(guī)范1. 存儲過程的In 、 out 參數(shù)應(yīng)按類別分開書寫，不要交叉。2. 存儲過程中變量的聲明應(yīng)集中在is 和 begin 關(guān)鍵字之間申明3. 盡可能使用相關(guān)表字段類型來定義相關(guān)的變量，如 %type ， %rowtype 。4. 刪除無用的變量與參數(shù)。5. 存儲

26、過程中存在多分支時，若有事務(wù)控制，需確保各分支均有事務(wù)結(jié)束處理，且發(fā)生異常時，必須Exception 中進(jìn)行異常捕獲。（四 ） SQL 性能規(guī)范1. 避免在批量操作時頻繁使用 commit頻繁的 COMMIT 會導(dǎo)致物理I/O 增大，同時會產(chǎn)生log file sync 等待，但長時間不提交將帶來更多的性能問題。 建議小于3 秒的事務(wù)可以一次提交， 大于 3秒的操作盡可能3 秒左右提交一次。實際應(yīng)用中使用 COMMIT 時必須保證事務(wù)的完整性。2. 使用動態(tài) sql 時，需綁定變量，而非實際值。為了不重復(fù)解析相同的 SQL 語句，在第一次解析之后， ORACLE 將 SQL語句緩存在內(nèi)存中，當(dāng)

27、你執(zhí)行一個SQL 語句時，如果它和之前的執(zhí)行過的語句完全相同， ORACLE 就能很快獲得內(nèi)存中相同語句的執(zhí)行計劃，然后就直接執(zhí)行該語句，而不需要進(jìn)行解析。如果沒有使用綁定變量，雖然語句結(jié)構(gòu)相同，但因傳入的變量不一樣導(dǎo)致語句不相同， ORACLE 還是需要對每條結(jié)構(gòu)相同的語句重新解析一遍，同時在內(nèi)存中緩存該語句，導(dǎo)致共享池中存放了 N 條結(jié)構(gòu)相同的語句，既達(dá)不到共享的目的， 又浪費(fèi)了大量的內(nèi)存空間， 如果語句執(zhí)行的太頻繁， 會導(dǎo)致數(shù)據(jù)庫無法申請共享內(nèi)存而出錯。3. 避免不必要的排序?qū)Σ樵兘Y(jié)果進(jìn)行排序會大大的降低系統(tǒng)的性能4. 用 WHERE 子句替換 HAVING 子句例如：SELECT NA

28、ME ， SUM(AGE)FROM EMPLOYEEGROUP BY NAME HAVING NAME ！ = ABC修改為以下語句效果更好SELECT NAME ， SUM(AGE)FROM EMPLOYEEWHERE NAME ！ = ABCGROUP BY NAME5. 用“ >=”替代“> ”如：在 ID 列上建有索引，則語句 SELECT * FROM EMPLOYEE WHERE ID >= 9 要比語句 SELECT * FROM EMPLOYEE WHERE ID > 8 高效。這是由于前者 DBMS 將直接跳到第一個ID 等于 9 的記錄而后者將首先定

29、位到8 的記錄并且向前掃描到第一個DEPT 大于 8 的記錄。6. 刪除表中所有記錄時用 TRUNCATE 替代 DELETE當(dāng)刪除表中的記錄時，在通常情況下，回滾段用來存放可以被恢復(fù)的信息，如果你沒有COMMIT 事務(wù)， ORACLE 可以將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)； 而當(dāng)運(yùn)用 TRUNCATE 時，回滾段不存放任何可被用于恢復(fù)的信息，當(dāng)命令運(yùn)行后，數(shù)據(jù)不能被恢復(fù), 因此很少的資源被調(diào)用，執(zhí)行時間也會很短 ,空間立即釋放，detele 操作后的空間可以被重新利用，但不會釋放。7. 用 UNION-ALL 代替 UNION說明： UNION-ALL 不過慮重復(fù)記錄， UNION 過濾重復(fù)記錄，

30、 所以需要先排序。如果不需要過濾重復(fù)的記錄， UNION-ALL 比 UNION 性能更好。8. 用 (NOT) EXISTS 替代 (NOT) IN 。在許多基于驅(qū)動表的查詢中，為了滿足一個條件，往往需要對另一個表進(jìn)行聯(lián)接。在這種情況下，使用 EXISTS( 或 NOT EXISTS) 通常將提高查詢的效率。例如：SELECT *FROM EMP WHERE EMPNO > 0AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')沒有以下語句高效SELECT *FROM EMP WHERE EMPNO >

31、 0AND EXISTS (SELECT 'X' FROM DEPTWHERE DEPT.DEPTNO = EMP .DEPTNOAND LOC = 'MELB')9. 使用 DECODE 函數(shù)來減少處理時間。使用 DECODE 函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。例：SELECT NAME|UNDER AGE FROM EMPLOYEE WHERE AGE<18 UNIONSELECT NAME| OVER AGE FROM EMPLOYEE WHERE AGE>=18 可以改成：SELECTDECODE(SIGN(AGE-18),-1

32、,NAME| UNDERAGE ，1 ， NAME| OVERAGE )FROM EMPLOYEE10. 使用表的別名 (Alias) 。當(dāng)在 SQL 語句中連接多個表時， 應(yīng)使用表的別名并把別名前綴于每個列上，這樣可以減少解析的時間，并減少那些由 Column 歧義引起的語法錯誤。11. 盡量減少對表的查詢次數(shù)。說明：在含有子查詢的 SQL 語句中，要特別注意減少對表的查詢。例：UPDATE EMP SETEMP_CAT =(SELECT MAX(CTEGORY) FROM EMP_CATEGORIES),SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATE

33、GORIES)WHERE EMP_DEP T=0020改寫成下面這樣會更高效:UPDATE EMP SET EMP_CAT, SAL_RANGE =(SELECT MAX(CATEGORY),MAX(SAL_RANGE)FROM EMP_CATEGORI ES) WHERE EMP_DEPT = 002012. 用表連接替換EXISTS 。例：SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT 'X'FROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = 'A');改寫成下面這樣會更高效

34、:SELECT ENAMEFROM DEPT D, EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = 'A'13. 避免使用 DISTINCT ，可以用 EXISTS 替換 DISTINCT 。當(dāng)提交一個包含一對多表信息 ( 比如部門表和雇員表) 的查詢時,避免在SELECT 子句中使用 DISTINCT. 一般可以考慮用 EXIST 替換例:SELECT DISTINCT DEPT_NO, DEPT_NAMEFROM DEPT D, EMP EWHERE D.DEPT_NO = E.DEPT_NO改寫成下面這樣會更高效:SELECT

35、 DEPT_NO, DEPT_NAMEFROM DEPT DWHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO =D.DEPT_NO);EXISTS 使查詢更為迅速，因為 RDBMS 核心模塊將在子查詢的條件一旦 滿足后，立刻返回結(jié)果。14. 避免使用耗費(fèi)資源的操作帶有 DISTINCT ， UNION ， MINUS ， INTERSECT ， ORDER BY ， GROUP BY 的 SQL 語句會啟動 SQL 引擎執(zhí)行耗費(fèi)資源的排序(SORT) 功能 ,如果可能，應(yīng)盡量避免使用或改寫。15. 避免對索引列使用數(shù)據(jù)庫函數(shù)

36、、計算表達(dá)式等等對查詢索引列使用數(shù)據(jù)庫函數(shù)、計算表達(dá)式會造成對索引失效例：select t.reserver1, count(*) coutfrom custcare.password_login_fail twhere t.operating_source = 1and to_char(t.login_time,'yyyymmdd') between 20080904 and 20080918group by t.reserver1having count(*) > 30custcare.password_login_fail 表的 login_time 列上建了索引，

37、上面語句to_char 函數(shù)會造成索引失效，把a(bǔ)nd to_char(t.login_time,'yyyymmdd') between 20080904 and 20080918修改成：and t.login_time between to_date('20080904','yyyymmdd') and to_date('20080918','yyyymmdd') 可以正確索引。查詢時盡可能將操作移至等式的右邊，甚至去掉函數(shù)。16. 在查詢條件中，避免不必要的類型轉(zhuǎn)換。字符類型列賦予數(shù)值型數(shù)據(jù)時會使用隱含類型轉(zhuǎn)換，

38、 而數(shù)值型列賦予字符型 數(shù)據(jù)時不會隱含類型轉(zhuǎn)換，而是將右邊值轉(zhuǎn)換為數(shù)值類型。例：select * from atp_para_cfg where cfg_type=35cfg_type 是 字 符 型 列 ， 上 面 語 句 會 造 成 隱 含 類 型 轉(zhuǎn) 換 to_number(cfg_type)=35 ，即 Oracle 內(nèi)部會做一次類型轉(zhuǎn)換。17. 盡量避免字段與“ NULL ”比較索引并不存儲 null 值，使用 NULL 作為條件將不會使用索引。例：select * from staff_member where address is null ； (或 not null )該語句將

39、不會使用索引18. 在索引列上使用 <>(!=)和 like 將不會使用索引。例：select * from staff_member where dept_no<>2001;select * from staff_member where first_name like %DON ;以上語句都不會用到索引注 :like 語 句 改 為 select * from staff_member where first_name like DON% ;當(dāng)前面沒有% 時，將使用索引，ORACLE 內(nèi)部轉(zhuǎn)換為范圍操作19. 用 Case 語句合并多重掃描例：select coun

40、t(*) from emp where sal<1000;select count(*) from emp where sal between 1000 and 5000;select count(*) from emp where sal>5000;這樣我們需要進(jìn)行三次全表查詢，但是如果我們使用 case 語句：selectcount (sale when sal <1000then 1 else null end)count_poor,count (sale when between 1000 and 5000then 1 else null end)count_blue

41、_collar,count (sale when sal >5000then 1 else null end)count_poorfrom emp;這樣查詢的結(jié)果一樣，但是執(zhí)行計劃只進(jìn)行了一次全表查詢。(五 ) JOB 使用規(guī)范1. Job 的執(zhí)行時間盡量避開業(yè)務(wù)高峰；2. 如果有多個占用資源較多的 job 任務(wù)，考慮將它們執(zhí)行的時間錯開；3. 禁止在 job 運(yùn)行期間對其調(diào)用的存儲過程進(jìn)行修改和編譯。4. 在 Oracle10g 之后，利用 dbms_schedule 建立 Job 。(六 ) 索引使用規(guī)范1. 索引創(chuàng)建原則( 1 )表的主鍵、外鍵必須有索引；( 2 )數(shù)據(jù)量超過300

42、 條的表應(yīng)該有索引；( 3 )經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引；( 4 )經(jīng)常出現(xiàn)在Where 子句中的字段，特別是大表的字段，應(yīng)該建立索（ 5 ）索引應(yīng)該建在選擇性高的字段上；（ 6 ）索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引；（ 7 ）復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替：? 正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段；? 復(fù)合索引的幾個字段是否經(jīng)常同時以 AND 方式出現(xiàn)在Where 子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；? 如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where

43、 子句中， 則分解為多個單字段索引；? 如果復(fù)合索引所包含的字段超過3 個，那么仔細(xì)考慮其必要性，考慮減少復(fù)合的字段；（ 8 ）頻繁進(jìn)行數(shù)據(jù)增刪改操作的表，不要建立太多的索引；（ 9 ）刪除無用的索引，避免對執(zhí)行計劃造成負(fù)面影響；2. 索引使用建議（ 1 ） 避免對條件字段進(jìn)行操作對條件字段的操作會導(dǎo)致該列的索引失效，這里所謂的操作包括數(shù)據(jù)庫函數(shù)、計算表達(dá)式等。如以下 3 個 sql ，其條件列上都建有索引，但不會被使用：select * from record where substrb（CardNo,1,4）='5378' -（13 秒）select * from reco

44、rd where amount/30< 1000- （ 11 秒）select * from record where to_char（ActionTime,'yyyymmdd'）='19991201' -（10 秒）可改為如下形式：select * from record where CardNo like '5378%' - （ < 1 秒）select * from record where amount < 1000*30- （ < 1 秒）select * from record where ActionTime

45、= to_date （'19991201' ,'yyyymmdd'） -（ < 1 秒）（ 2 ）避免隱式轉(zhuǎn)換對于條件值書寫不規(guī)范的， oracle 會自動對條件列進(jìn)行隱式轉(zhuǎn)換， 以完成比較，但同時也會造成索引失效。如將字 符型數(shù)據(jù)與數(shù)值型數(shù)據(jù) 比較 ， ORACLE 會 自 動將字符 型用 to_number（） 函數(shù)進(jìn)行轉(zhuǎn)換，從而導(dǎo)致全表掃描。例：表 tab1 中的列 col1 是字符型（ char） ，則以下語句存在類型轉(zhuǎn)換：select col1,col2 from tab1 where col1>10，應(yīng)該寫為：select col1,co

46、l2 from tab1 where col1>'10'。（3） 盡量避免 “<> ”盡量去掉 "<>" ，避免全表掃描，如果數(shù)據(jù)是枚舉值，且取值范圍固定，則修改為"OR"方式。例： UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;以上語句由于其中包含了 "<>" ， 執(zhí)行計劃中用了全表掃描（TABLE ACCESSFULL ） ，沒有用到 state 字段上的索引。實際應(yīng)用中，由于業(yè)務(wù)邏輯的限制，字段 state 為枚舉

47、值，只能等于 0 ， 1 或 2 ，而且，值等于=1 ， 2 的很少，因此可以去掉 "<>" ，利用索引來提高效率。修改為： UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。（4） 避免 Where 子句中的IS NULL 和 IS NOT NULLWhere 字句中的 IS NULL 和 IS NOT NULL 將不會使用索引而是進(jìn)行全表搜索，因此需要通過改變查詢方式，分情況討論等方法，去掉Where 子句中的IS NULL 和 IS NOT NULL 。（5） 5 ） 數(shù)據(jù)分布不均勻列

48、的索引使用當(dāng)列值選擇性不高，但查詢返回值恰好是返回較少的數(shù)值，則應(yīng)創(chuàng)建索引。例 ： 表 ServiceInfo 中數(shù)據(jù)量很大 ， 有一百 萬行 ， 其 中有 一個字段 DisposalCourseFlag ，取值范圍為枚舉值： 0 ， 1 ， 2 ， 3 ， 4 ， 5 ， 6 ， 7 。按照前 面說的索引建立的規(guī)則， “選擇性不高的字段不應(yīng)該建立索引，該字段只有8 種取值，索引值的重復(fù)率很高，索引選擇性明顯很低。然而，由于該字段上數(shù)據(jù)值的分布情況非常特殊，具體如下表：取值范圍 1567占總數(shù)據(jù)量的百分比 1%98%1%而且，常用的查詢中，查詢 DisposalCourseFlag<6

49、的情況既多又頻繁，毫無疑問， 如果能夠建立索引， 并且被應(yīng)用， 那么將大大提高這種情況的查詢效率。（6） 6 ） 需要時利用 HINT 強(qiáng)制指定索引對于 ORACLE 優(yōu)化器無法用上合理索引的情況下，一方面可以單獨(dú)對該字段或該表用 analyze 語句進(jìn)行分析，對該列搜集足夠的統(tǒng)計數(shù)據(jù)，使ORACLE在查詢選擇性較高的值時能用上索引；另一方面，可以利用 HINT 提示，在SELECT 關(guān)鍵字后面，加上“ /*+ INDEX( 表名稱，索引名稱)*/”的方式，強(qiáng)制ORACLE 優(yōu)化器用上該索引。例：select /*+ index(mms_cdr IDX_MMS_USERID)*/ *FROM

50、mms_cdrWHERE (month_number >= 6 and month_number <= 7)AND user_id = to_number('1419200000741621')AND send_time >= '20070101000000'AND send_time <= '20070104235959'ORDER BY send_time;當(dāng) user_id 和 send_time 列上均有索引的情況下使用 user_id 列上的索引更高效，因為 user_id 列上的索引選擇性更高，但是由于統(tǒng)計信息

51、不全，優(yōu)化器可能選擇使用全表掃描或者走send_time 列上的索引，導(dǎo)致執(zhí)行計劃不是最優(yōu)，此時可以使用強(qiáng)制索引來解決。（7） 7 ) 屏蔽無用索引如果了解到條件字段建有索引，但返回值較多，如果走索引的話效率更低，則可通過特殊方法屏蔽掉索引的使用。如字段為數(shù)值型的就在表達(dá)式的字段名后，添加“ + 0 ” ，為字符型的就并上空串： “ |""”例： select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36'（8） 8 ) like 子句盡量前端匹配當(dāng) like 字句前段

52、為通配符，會導(dǎo)致索引失效。如： select * from city where name like %S% ; -該語句執(zhí)行全表掃描修改為：select * from city where name like; S% -會正確使用索引（9） 9 ) 使用基于函數(shù)的索引對條件列的操作可以使索引失效，但如果根據(jù)操作創(chuàng)建相應(yīng)索引，則可以被使用。如： select * from emp where substr(ename,1,2)= SM;在 ename 列上的索引將不會被使用，如果創(chuàng)建基于函數(shù)的索引，即：則在查詢的時create index emp_ename_substr on eemp (

53、substr(ename,1,2) ); 候可正確使用到索引。使用函數(shù)索引有如下限制：? 無法索引 LOB 字段? 無法索引嵌套表字段? 表達(dá)式不允許有分組函數(shù)，如 SUM ， AVG 等? 無法使用基于規(guī)則的優(yōu)化器? 無法儲存空值? 對于一個輸入，要求函數(shù)返回同一值，即確定性? 當(dāng)函數(shù)改變時，要重建函數(shù)索引（ 10 ） 使用分區(qū)索引在用分析命令對分區(qū)索引進(jìn)行分析時， 每一個分區(qū)的數(shù)據(jù)值的范圍信息會放入 Oracle 的數(shù)據(jù)字典中。 Oracle 可以利用這個信息來提取出那些只與SQL 查詢相關(guān)的數(shù)據(jù)分區(qū)。例如，假設(shè)你已經(jīng)定義了一個分區(qū)索引，并且某個SQL 語句需要在一個索引分區(qū)中進(jìn)行一次索引

54、掃描。 Oracle 會僅僅訪問這個索引分區(qū)，而且會在這個分區(qū)上調(diào)用一個此索引范圍的快速全掃描。 因為不需要訪問整個索引， 所以提高了查詢的速度。但是當(dāng)查詢跨多個分區(qū)，涉及到比較大小時，考慮使用全局索引。（11） 使用位圖索引當(dāng)列值的選擇性非常低時， 比如性別字段只有男與女兩個值， 可以考慮使用位圖索引。 位圖索引占用空間非常小， 效率較高。 位圖索引適合于不會變化的表，如果 dml 操作頻繁，則極易產(chǎn)生大面積鎖。（ 12 ） 使用復(fù)合索引當(dāng)幾個字段經(jīng)常同時以 and 方式出現(xiàn)在 where 子句中，可以建立復(fù)合索引；將最常用的列放在索引最前面；復(fù)合索引字段不宜過多，盡量在3 個以內(nèi)。（ 13

55、 ） 少數(shù)情況下用全表掃描替代索引掃描在大多數(shù)情況下， 全表掃描可能會導(dǎo)致更多的物理磁盤輸入輸出， 但是全表掃描有時又可能會因為高度并行化的存在而執(zhí)行的更快。 如果查詢的表完全沒有順序， 那么一個要返回記錄數(shù)小于 10 的查詢可能會讀取表中大部分的數(shù)據(jù)塊，這樣使用索引會使查詢效率提高很多。 但是如果表非常有順序， 那么如果查詢的記錄數(shù)大于 40 時，可能使用全表掃描更快。因此，有一個索引范圍掃描的總體原則是：? 對于原始排序的表： 僅讀取少于表記錄數(shù)40 的查詢應(yīng)該使用索引范圍掃描。 反之， 讀取記錄數(shù)目多于表記錄數(shù)的 40 的查詢應(yīng)該使用全表掃描。? 對于未排序的表：僅讀取少于表記錄數(shù)7 的查詢應(yīng)該使用索引范圍掃描。反之，讀取記錄數(shù)目多于表記錄數(shù)的 7的查詢應(yīng)該使用全表掃描。（七 ）分區(qū)表使用規(guī)范當(dāng)表的數(shù)據(jù)量達(dá)到 1G 或者 1000 萬行時，考慮將表分區(qū)；分區(qū)鍵的選擇參考業(yè)務(wù)查詢要求，通常是時間字段，以 range 方式來劃分。分區(qū)間隔也要看業(yè)務(wù)需要， 比如單次查詢總是以天為