關(guān)系型數(shù)據(jù)庫性能體系

3萬字|關(guān)系型數(shù)據(jù)庫性能體系!

1前百

1.1目的

本文檔詳細(xì)定義了關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)庫模型設(shè)計規(guī)范、表的設(shè)計規(guī)范、分區(qū)

表的設(shè)計規(guī)范、索引的設(shè)計規(guī)范、其他數(shù)據(jù)庫對象的設(shè)計規(guī)范、SQL的訪問規(guī)范、

編碼和注釋規(guī)范，并介紹了SQL調(diào)優(yōu)的關(guān)注點(diǎn)和常用方法，作為數(shù)據(jù)庫規(guī)劃、設(shè)

計、開發(fā)及維護(hù)人員的技術(shù)參考資料，用以指導(dǎo)關(guān)系型數(shù)據(jù)庫的設(shè)計和開發(fā)，性能是

設(shè)計出來的，質(zhì)量也是可以設(shè)計出來的，理解這篇，關(guān)系型數(shù)據(jù)庫性能提升30%不

是問題。

我希望通過該規(guī)范的分享，能夠?qū)崿F(xiàn)以系統(tǒng)、體系的工程化思維模式去規(guī)范關(guān)

系型數(shù)據(jù)庫設(shè)計和開發(fā)，使數(shù)據(jù)庫結(jié)構(gòu)和編碼風(fēng)格標(biāo)準(zhǔn)化，提高模型的前瞻性、高

效性，以盡早提前避免由于數(shù)據(jù)庫設(shè)計不當(dāng)而產(chǎn)生的麻煩，同時好的規(guī)范，在執(zhí)行

的時候可以培養(yǎng)出好的習(xí)慣，好的習(xí)慣是軟件質(zhì)量的很好的保證。

目錄

1前言1

1.1目的1

1.2預(yù)期的讀者和閱讀建議6

2數(shù)據(jù)庫模型設(shè)計規(guī)范6

2.1數(shù)據(jù)庫建模原則性規(guī)范6

2.2實(shí)體型之間關(guān)系認(rèn)定規(guī)范7

2.3范式化1NF的規(guī)范7

2.4范式化2NF的規(guī)范8

2.5范式化3NF的規(guī)范9

2.5反范式化冗余字段使用規(guī)范9

2.6數(shù)據(jù)庫對象命名基本規(guī)范10

2.6.1遵循行業(yè)規(guī)范10

2.6.2簡單命名原則10

2.6.3字符范圍原則11

2.6.4字母全部大寫或小寫原則11

2.6.5勿用保留詞原則11

2.6.5同義性原則11

2.6.6富有含義原則11

2.6.7擴(kuò)展性原則11

3表的設(shè)計規(guī)范12

3.1命名規(guī)范12

3.1.1表的命名規(guī)范12

3.1.2字段的命名規(guī)范12

32表的設(shè)計規(guī)范13

3.2.1指定表空間規(guī)范13

3.2.2表的主鍵規(guī)范13

3.2.3表的外鍵規(guī)范13

3.2.4字段類型及寬度的規(guī)范14

3.2.5一個表所含字段總長度的規(guī)范14

3.2.6一個表所含字段訪問頻繁度的規(guī)范15

3.2.7大對象字段(BLOB,CLOB)使用規(guī)范15

3.2.8關(guān)于字段能否為NULL值15

3.2.9關(guān)于冗余列的規(guī)范16

3.2.10使用注釋的規(guī)范16

3.2.11一個表所含數(shù)據(jù)量的規(guī)范16

3.2.12增量同步表的設(shè)計規(guī)范17

3.3字段類型規(guī)范17

3.3.1不使用會發(fā)生隱式轉(zhuǎn)換：INTEGER,FLOAT17

3.3.2不使用過時老類型：RAW,LONG,LONGRAW17

3.33國家字符集相關(guān)17

3.3.4不能使用大對象：BLOB,CLOB,NCLOB18

3.35不能使用高精度：TIMESTAMP18

3.3.6關(guān)于CHAR字段18

4分區(qū)表的設(shè)計規(guī)范18

41表空間及分區(qū)表的概念18

4.1.1表空間18

4.1.2分區(qū)表18

4.2表分區(qū)的具體作用19

4.3表分區(qū)的優(yōu)缺點(diǎn)19

4.4分區(qū)表設(shè)計規(guī)范19

4.2.1不使用全局索引19

4.2.2RANGE分區(qū)的規(guī)范20

4.2.3LIST分區(qū)的規(guī)范20

4.2.4HASH分區(qū)的規(guī)范21

4.2.5RANGE-LIST分區(qū)的規(guī)范22

42.6RANGE-HASH分區(qū)的規(guī)范22

5索引的設(shè)計規(guī)范23

5.1索引分類23

5.1.1單列索引與復(fù)合索引23

5.1.2唯一索引與非唯一索引23

5.1.3B樹索引、位圖索引與函數(shù)索引23

52命名規(guī)范24

5.3索引設(shè)計規(guī)范24

5.31指定表空間規(guī)范25

5.3.2主鍵索引的規(guī)范25

5.3.3唯一約束索引的規(guī)范26

5.3.4外鍵列索引的規(guī)范26

5.3.5復(fù)合索引的規(guī)范26

5.3.6函數(shù)索引的規(guī)范27

5.3.7位圖索引的規(guī)范27

5.3.8反向索引的規(guī)范27

5.3.9分區(qū)索引的規(guī)范27

5.3.10索引重建的規(guī)范27

6其他數(shù)據(jù)庫對象設(shè)計規(guī)范28

6.1命名規(guī)范28

6.2視圖設(shè)計規(guī)范28

6.2.1盡量使用簡單的視圖，避免使用復(fù)雜的視圖28

6.2.2按照必要性原則建立視圖29

6.3存儲過程、函數(shù)、觸發(fā)器的設(shè)計規(guī)范29

6.3.1關(guān)于觸發(fā)器的設(shè)計29

7SQL訪問規(guī)范30

7.1盡量不要寫復(fù)雜的SQL30

7.2避免使用SELECT*30

7.3INSERT時需寫全列名30

7.4進(jìn)行DML操作時使用CTAS進(jìn)行數(shù)據(jù)備份31

7.5大數(shù)據(jù)量DML操作分多次執(zhí)行31

7.6使用綁定變量，降低高硬解析31

7.7選擇最有效率的表名順序32

7.8關(guān)注WHERE子句中的連接順序32

7.9用EXISTS替代IN33

7.10用表連接替換EXISTS34

7.11用EXISTS替換DISTINCT35

7.12盡量用unionall替換union35

7.13使用DECODE函數(shù)來減少處理時間35

7.14盡量避免用orderby36

7.15用Where子句替換HAVING子句36

7.16減少多表關(guān)聯(lián)37

7.17避免重復(fù)訪問37

7.17.1使用groupby37

7.17.2用表更新表38

7.17.3豎向顯示變橫向顯示38

7.18完成事務(wù)及時commil39

7.19數(shù)據(jù)庫連接及時關(guān)閉39

7.20索引的使用39

7.20.1避免在索引列上使用函數(shù)或運(yùn)算39

7.20.2避免改變索引列的類型40

7.20.3避免在索引列上使用N0T40

7.204用＞=替代＞41

7.20.5避免在索引列上使用ISNULL和ISNOTNULL41

7.20.6帶通配符（%）的like語句41

7.20.7總是使用索引的第一個列42

8編碼及注釋規(guī)范42

8.1編碼規(guī)范42

8.L1避免隱式的數(shù)據(jù)類型轉(zhuǎn)換42

8.1.2不要將空的變量值直接與比較運(yùn)算符（符號）比較43

8.1.3跨行語句，第一關(guān)鍵字應(yīng)當(dāng)左對齊43

8.1.4Insert…values和update語句書寫規(guī)范43

8.1.5Insert…select語句書寫規(guī)范44

8.1.6避免使用嵌套的IF語句44

8.1.7減少控制語句的檢查次數(shù)45

8.1.8語句涉及多個表時，使用別名來限定字段名46

8.1.9其他編碼規(guī)范47

8.2注釋規(guī)范50

8.2.1注釋語法50

8.2.2每個塊和過程開發(fā)放置注釋50

8.2.3代碼注釋應(yīng)放在其上方或右方50

8.2.4其他注釋規(guī)范51

9PLSQL優(yōu)化51

9.1性能問題分析51

9.2PLSQL優(yōu)化的核心思想52

9.3PLSQL優(yōu)化示例53

9.3.1減少對表的查詢53

9.3.2避免循環(huán)（游標(biāo)）里面嵌查詢55

9.3.3groupby優(yōu)化56

9.3.4刪除重復(fù)記錄57

9.3.5COMMIT使用57

93.6批量數(shù)據(jù)插入58

9.3.7索引使用優(yōu)化59

9.38使用提示（Hints）60

9.39表上存在過舊的分析61

9.310表上存在并行61

9.311關(guān)于索引建立62

9.3.12ExpainPlan分析索引使用62

1.2預(yù)期的讀者和閱讀建議

本文檔預(yù)期的讀者為項(xiàng)目經(jīng)理、開發(fā)經(jīng)理、DBA、數(shù)據(jù)結(jié)構(gòu)管理師、系統(tǒng)設(shè)計

師、開發(fā)師、測試師等相關(guān)崗位的人員。

讀者可以通篇閱讀該文檔，以整體熟悉和掌握Oracle數(shù)據(jù)庫設(shè)計規(guī)范，也可以

重點(diǎn)關(guān)注跟自身相關(guān)的內(nèi)容章節(jié)，如數(shù)據(jù)庫模型設(shè)計、表的設(shè)計，或SQL訪問規(guī)

范、編碼和注釋規(guī)范等。

2數(shù)據(jù)庫模型設(shè)計規(guī)范

2.1數(shù)據(jù)庫建模原則性規(guī)范

對于涉及數(shù)據(jù)庫的項(xiàng)目，需要構(gòu)建數(shù)據(jù)庫邏輯模型圖，邏輯模型圖是項(xiàng)目組成

員之間在數(shù)據(jù)庫層面溝通交互的依據(jù)，必須規(guī)范畫圖(表，主鍵，外鍵，關(guān)系)。

對于表的個數(shù)在20個以上的模型，需要DBA參與設(shè)計，并作最終審核。

對于0LTP系統(tǒng)，采用范式化思想進(jìn)行模型設(shè)計，對于OLAP系統(tǒng)，采用面向問

題及多級顆粒度的思想進(jìn)行模型設(shè)計。

需采用主流的模型設(shè)計軟件工具PowerDesigner,ERWin。

2.2實(shí)體型之間關(guān)系認(rèn)定規(guī)范

所有實(shí)體間的業(yè)務(wù)邏輯關(guān)系，除了語義上保留其原有的業(yè)務(wù)關(guān)系外，本質(zhì)上都

要轉(zhuǎn)化成關(guān)系數(shù)據(jù)庫的三種關(guān)系(1:1)(1:N)(N:M),對于3個及以上實(shí)體型

之間的“多元關(guān)系”，需要DBA參與設(shè)計。

比如，實(shí)體型A和實(shí)體型B之間的關(guān)系，可以通過問兩個問題來確定他們之間

的關(guān)系：一個A可以對應(yīng)幾個B?一個B可以對應(yīng)幾個A?

(1)一個A對應(yīng)一個B,相反一個B對應(yīng)一個A,那么A對B就是1:1關(guān)系；

(2)一個A對應(yīng)多個B,相反一個B對應(yīng)一個A,那么A對B就是1:N關(guān)

系；

(3)一個A對應(yīng)多個B,相反一個B對應(yīng)對個A,那么A對B就是N:M關(guān)

系；

具體實(shí)施的時候，掌握如下原則：

■?1:1關(guān)系選取任何一個表的主鍵到另一個表中，作為外鍵來體現(xiàn)。

■?1：N關(guān)系將1表的主鍵在N表中，以外鍵形式來體現(xiàn)。

■?N:M關(guān)系采用“關(guān)系表”來體現(xiàn)，該關(guān)系表的主鍵是由相關(guān)實(shí)體表的主鍵

組成的復(fù)合主鍵；各實(shí)體表主鍵不但組成了該關(guān)系表的主鍵，同時也被看作

外鍵在該關(guān)系表中存在。

■?對于三個以上表之間的“多元關(guān)系”常需要和反范式化冗余字段結(jié)合起

來設(shè)計，以保證查詢速度。

2.3范式化1NF的規(guī)范

OLTP系統(tǒng)的模型，需要符合第三范式，對于表在20個以上的模型，需要DBA

參與設(shè)計。

范式化要求(1NF)：列是訪問的最小單位，具有原子性，不可再被分割。

在具體實(shí)施的時候，需要依據(jù)情況對相應(yīng)屬性進(jìn)行拆分或者合并：

■?同一個屬性值的不同細(xì)度把握

比如,常見的“姓名”這個屬性,設(shè)計一:“姓名”是一個列,設(shè)計二:

“姓”是一個列,“名”是一個列,兩個列的值組合起來才表達(dá)一個“姓名"語

乂。兩種設(shè)計方法,在不同的系統(tǒng)中都有應(yīng)用,這主要是依據(jù)需求的細(xì)度來確定,

靈活把握。

■?把多個屬性值錯誤的作為一個屬性值存儲

比如:常見的0A系統(tǒng)要存儲員工的各種屬性,包括技能信息,技能范圍:

Oracle,JAVA,.NET,C#,Perl,UNIX等等,一種常見的錯誤設(shè)計是:設(shè)計一張員工

表，其中有一個技熊屬性字段，然后某員工所掌握的多種技能用逗號(,)間隔,

然后將這個字符串存儲到這個員工表的技能屬性字段中。

這里的錯誤在于將多個屬性值作為一個屬性值存儲在一個字段中,不能滿足直

接遍歷員工對某個技能掌握情況,而且如果再要求說明員工對個技能的掌握程度

(精通,熟悉,一般等等),則再增加字段,里面的對應(yīng)關(guān)系將很容易錯亂,這是

嚴(yán)重違反1NF的情況。

正確的設(shè)計應(yīng)該是:兩個實(shí)體表:一張是員工表,一張是技能字典表,一個員

工可以掌握多個技能,也就是(1:N)關(guān)系，相反一個技能可以被多個員工掌握,也

是(l:N)關(guān)系,雙向都是(I:N)關(guān)系,那么綜合起來員工和技能之間就是“多對

多關(guān)系(N:M)”，依據(jù)前述規(guī)范,應(yīng)該設(shè)計一張“關(guān)系表"來存儲''多對多關(guān)

系”，主鍵為復(fù)合主鍵(員工主鍵+技能主鍵)，該關(guān)系有一個屬性“技能掌握程

2.4范式化2NF的規(guī)范

OLTP系統(tǒng)的模型，需要符合第三范式。對于表在20個以上的模型，需要DBA

參與設(shè)計。

范式化要求(2NF)：滿足1NF,不存在非主鍵屬性對主鍵屬性的部分依賴。

實(shí)體表中一般不會出現(xiàn)違反2NF的情況，因?yàn)槎际恰耙粋€”主鍵列，而關(guān)系表

是兩個以上列的“復(fù)合”主鍵，故而關(guān)系表容易出現(xiàn)違反2NF的情況。主要是該關(guān)

系表非主鍵外的屬性，本該屬于相關(guān)的某個實(shí)體表的，卻放到了該關(guān)系表中。這使

得該屬性不能通過該關(guān)系表的復(fù)合主鍵唯一確定，DML操作會發(fā)生錯誤。如果違反

了2NF,那么應(yīng)該把這個屬性從關(guān)系表中拆分，也許會單獨(dú)形成一個表，絕大部分

情況下是將該屬性歸并到某個相關(guān)的實(shí)體表中。

違反2NF的例子:

學(xué)生考試情況中,有兩個實(shí)體表:學(xué)生表和學(xué)科表,學(xué)生與學(xué)科之間的考試關(guān)

系就是的關(guān)系,就要創(chuàng)建一張關(guān)系表存儲該多對多的考試關(guān)系,表的主鍵為學(xué)

生編號和學(xué)科編號,屬性為考試分?jǐn)?shù);那么“任課老師”該放在那里呢？如果放到

考試關(guān)系表中,那么安排任課老師,必須先進(jìn)行考試,這顯然不符合實(shí)際,也就是

任課老師不該依賴于學(xué)生編號和學(xué)科編號,只是依賴于學(xué)科編號,也就是說任課教

師信息應(yīng)該放在學(xué)科表中.

2.5范式化3NF的規(guī)范

OLTP系統(tǒng)的模型，需要符合第三范式。對于表在20個以上的模型，需要DBA

參與設(shè)計。

范式化要求(3NF)：滿足2NF,不存在非主鍵屬性對主鍵屬性的傳遞依賴；

違反3NF的情況，絕大多數(shù)是在含有外鍵的表中。比如A表中的外鍵字段Bkey

是B的主鍵，那么依賴于Bkey的屬性應(yīng)當(dāng)屬于B表的屬性，而不是A表，如果放

入A表，則這些對A表的主鍵Akey的依賴，首先是依賴于A(BKey),而后通過

A(BKey)對A(AKey)的依賴，傳遞依賴于A(Akey)；三種關(guān)系N:M)

都含有外鍵，都很可能發(fā)生違反3NF的情況。違反3NF的后果，會導(dǎo)致那些問題屬

性插入異常，或者被誤刪。

違反3NF的例子:

教師和學(xué)科之間,存在著上課關(guān)系,假設(shè)一個教師上一門課而且一門課只有一

個教師上,那么該關(guān)系為1:1關(guān)系,將教師表的主鍵教師編號在學(xué)科表中以外鍵形

式存在就表達(dá)了該1:1關(guān)系,那么教師的“聯(lián)系電話”屬性該放哪里呢？如果看到

“教師編號”出現(xiàn)在了學(xué)科表中,就將聯(lián)系電話放入學(xué)科表中,那么聯(lián)系電話首先

是對表中的教師編號依賴,再依據(jù)教師編號對學(xué)科的依賴,達(dá)到了學(xué)科編號的依

賴,那么聯(lián)系電話對學(xué)科編號的依賴就是傳遞依賴,違反了3NF。應(yīng)該將其從學(xué)科

表中拆出來放入教師表中,不然的話,會發(fā)生操作異常,比如,假設(shè)一個教師已經(jīng)

存在但是還沒有為其分配科目,那么他的電話就無法存入庫中。

2.5反范式化冗余字段使用規(guī)范

OLTP系統(tǒng)中在完成范式化工作之后，對某些表，可以適當(dāng)反范式化增加冗余字

段以提高數(shù)據(jù)訪問性能；在OLAP中采用的是面向問題的設(shè)計思想，應(yīng)該大量使用

反范式化冗余信息。

當(dāng)SQL關(guān)連查詢涉及到4張表時可考慮采用冗余字段。

常用在兩個地方：(1)關(guān)系表中的冗余：在關(guān)系表中增加相關(guān)實(shí)體表的相關(guān)屬

性，以達(dá)到關(guān)連查詢時減少表的關(guān)聯(lián)數(shù)量的目的(2)層次關(guān)系中的冗余：在多層次

的子父表關(guān)系中，將父表的屬性存儲在“子表”或者“孫子表”或者“重孫表”

中。

反范式化冗余字段實(shí)例:

（1）關(guān)系表中的冗余:比如在考試關(guān)系中,原本在學(xué)科表中的學(xué)分信息,可以

冗余添加到考試關(guān)系表中,這樣,每個學(xué)生得了多少學(xué)分,就可以直接從考試表得

到,而無需關(guān)聯(lián)學(xué)科表來得到。

（2）多層關(guān)系中的冗余:假設(shè)為之范疇從大到小有國家表，省份表,城市表,

城區(qū)表,社區(qū)表,它們之間的層次關(guān)系是通過上一級的主鍵在下一級中以外鍵形式

存在來體現(xiàn)的,但是,如果需要問:某個設(shè)計屬于哪個國家？這樣就要關(guān)連查詢所

有的5張表,性能會很差。這時可以將國家編號以外鍵形式放入到社區(qū)表中做冗

余,這樣直接關(guān)聯(lián)國家表和社區(qū)表即可得到答案。一般的,每間隔一級增加一個冗

余外鍵,比如將國家編號放入城市表中,將城市編號放入社區(qū)表中。

如何保證冗余字段數(shù)據(jù)的正確性（一致性）是反范式化的關(guān)鍵，需要對冗余字

段詳細(xì)添加注釋，說明冗余了什么，以及該字段的維護(hù)方法，常用維護(hù)方法如下：

■?如果在程序開發(fā)前設(shè)計的冗余字段，可以在正常的業(yè)務(wù)邏輯程序中一并

處理；

■?如果是程序完成之后增加的冗余字段，可以使用觸發(fā)器維護(hù)；

■?對于OLAP中大量存在冗余字段，可能需要使用單獨(dú)的處理任務(wù)進(jìn)行維

護(hù)。

2.6數(shù)據(jù)庫對象命名基本規(guī)范

2.6.1遵循行業(yè)規(guī)范

當(dāng)有相關(guān)國家/行業(yè)強(qiáng)制性數(shù)據(jù)結(jié)構(gòu)標(biāo)準(zhǔn)規(guī)范存在時，用于存儲某業(yè)務(wù)數(shù)據(jù)的業(yè)

務(wù)表在表名命名上原則上應(yīng)該遵從標(biāo)準(zhǔn)規(guī)定，其表中相關(guān)字段的中文名稱（即數(shù)據(jù)

項(xiàng)名稱）若標(biāo)準(zhǔn)規(guī)范上有規(guī)定的應(yīng)遵循規(guī)定。此外，若標(biāo)準(zhǔn)規(guī)范上對數(shù)據(jù)項(xiàng)的類

型、長度有規(guī)定的，原則上也應(yīng)當(dāng)遵循或保證能直接兼容保存和訪問。

2.6.2簡單命名原則

命名盡可能簡單，避免太長的命名，盡量使用縮寫形式，但是縮寫也要能夠表

達(dá)命名的含義。凡是需要命名的對象其標(biāo)識符均不能超過30個字符，也即：Oracle

中的表名、字段名，函數(shù)名，過程名，觸發(fā)器名，序列名，視圖名的長度均不能超

過30個字符，以免超過數(shù)據(jù)庫命名長度限制（Oracle有30的限制）。建議每個單

詞分段長度不要超過6位。

2.6.3字符范圍原則

數(shù)據(jù)庫各種名稱必須以字母開頭，但嚴(yán)禁使用SYS開頭；名稱只能含有字母，

數(shù)字和下劃線三類字符，用于間隔名稱中的各語義字段，以便閱讀同時

方便某些工具對數(shù)據(jù)庫對象的映射。如xxx_xxx_xxx,但不限于三段式。

2.6.4字母全部大寫或小寫原則

所有數(shù)據(jù)庫對象命名字母全部大寫或小寫。Oracle對大小寫不敏感，但是有些

數(shù)據(jù)庫對大小寫敏感，統(tǒng)一大小寫有助于在多個數(shù)據(jù)庫間移植。

2.6.5勿用保留詞原則

數(shù)據(jù)庫對象命名不能直接使用數(shù)據(jù)庫保留關(guān)鍵字，但分段中可以使用。如USER

不能用于表名、列名等，但是USER_NAME可以用于列名，USER」NFO也可以用

于表名。

2.6.5同義性原則

對于同一含義盡量使用相同的單詞命名，不管使用英文單詞還是英文縮寫，以

免引起誤解。如TELEPNHOE的A表中表示固定電話號碼，在B表中就不應(yīng)該用于表

示移動電話號碼。盡量避免同一單詞表示多種含義的情況。

2.6.6富有含義原則

命名盡量采用富有意義的英文詞匯，不準(zhǔn)采用漢語拼音。

2.6.7擴(kuò)展性原則

各系統(tǒng)或者項(xiàng)目在遵循本規(guī)范的基礎(chǔ)上可以根據(jù)需要制定更明確的規(guī)范細(xì)則，

以滿足項(xiàng)目管理需要。如對模塊進(jìn)行統(tǒng)一命名，然后用于表名的前綴。建議每個系

統(tǒng)在啟動開發(fā)時建立數(shù)據(jù)字典，管理命名中使用的英文單詞、英文單詞縮寫等，對

用于命名的單詞進(jìn)行統(tǒng)一管理。

3表的設(shè)計規(guī)范

3.1命名規(guī)范

3.1.1表的命名規(guī)范

命名規(guī)則：3位類別碼一模塊名一表名一附加碼，采用大寫字符。

類別碼：一般表TBL、臨時表TMP、中間表CVT、刪除表DEL、歷史表

HIS、配置表CFG,接口表INT,一般表的3位類別碼可以省略，其他類型表的類

別碼必填。

模塊名：模塊名代表子系統(tǒng)（或者子模塊）的名稱，如：保單相關(guān)表PLC；訂

單相關(guān)SLS；基礎(chǔ)數(shù)據(jù)：TYP。

表名：表名應(yīng)該簡潔明了，盡量使用完整的單詞，如果導(dǎo)致拼上表名后，長度

超過30個字符，則從最后一個單詞開始，依次向前采用該單詞的縮寫。（如果沒

有約定的縮寫，則采用該單詞前4個字母來表示）。另外，表名中的名詞單詞都應(yīng)

使用單數(shù)形式，以免混淆，如：使用FACTORY而非FACTORIES。

附加碼：為可選項(xiàng)，各系統(tǒng)根據(jù)實(shí)際情況自行編碼，如：可以用以標(biāo)記臨時表

的生成及數(shù)據(jù)存放日期YYMMDD。

3.1.2字段的命名規(guī)范

命名規(guī)則：英文單詞之間用下劃線連結(jié)，且每個單詞皆為單數(shù).例：

user_name,采用小寫字符。

■?字段用來存儲sequence序列,命名以字結(jié)尾。例：bar_code_ido

■?字段用來存儲號碼,命名以no結(jié)尾。例：policy_noo

■?字段用來存儲日期,命名以date結(jié)尾。例：create_dateo

■?字段用來存儲數(shù)量，命名以num結(jié)尾。例：insured_num。

■,字段用來存儲金額，命名以amt結(jié)尾。例：prem_amto

■,字段用來存儲名稱，命名以name結(jié)尾。例：client_nameo

■,字段用來存儲描述信息，命名以desc結(jié)尾。例：bank_desco

■?字段用來存儲基礎(chǔ)表的code信息，命名以code結(jié)尾。例：region_code＜）

■,字段用來存儲標(biāo)志信息，命名以flag結(jié)尾。例：underwrit_flago

■,字段用來存儲英文名稱和英文描述，命名以en結(jié)尾。例：address.eno

3.2表的設(shè)計規(guī)范

3.2.1指定表空間規(guī)范

每個表在創(chuàng)建時候，必須指定所在的表空間，不要采用默認(rèn)表空間，以防止表

建立在system空間上，導(dǎo)致性能問題。對于事務(wù)比較繁忙的數(shù)據(jù)表，必須存放在

在該表專用空間中。

3.2.2表的主鍵規(guī)范

表的主鍵設(shè)計，應(yīng)該遵循如下三點(diǎn)原則：

■?有無原則

除臨時表和外部表，以及流水表，日志表外，其他表都要建立主鍵。主鍵是每

行數(shù)據(jù)的唯一標(biāo)識，保證主鍵不可隨意更新修改，在不知道是否需要主鍵的時候，

請加上主鍵，它會為你的程序以及將來查找數(shù)據(jù)中的錯誤等等，提供一定的幫助。

■?構(gòu)成原則

主鍵不能使用含有實(shí)際語義的列，應(yīng)該增加一個xx_id字段做主鍵，類型為

number,取值來自序列sequence；

■?創(chuàng)建原則

對于500萬以上的表，采用先建唯一索引再添加主鍵約束的方式來創(chuàng)建主鍵。

對于實(shí)體表，主鍵就是一列，就是沒有任何語義的自增的NUMBER列；對于關(guān)系

表，主鍵就是相關(guān)實(shí)體表主鍵形成的復(fù)合主鍵，是多列。

3.2.3表的外鍵規(guī)范

一個表的某列與另一表有關(guān)聯(lián)關(guān)系的時候，如果加得上的話，請加上外鍵約

束。外鍵是很重要的，所以要特別強(qiáng)調(diào)。

■?適量建立外鍵

為了保證外鍵的一致性，數(shù)據(jù)庫會增加一些開銷，如果有確鑿的并且是對性能

影響到無法滿足用戶需求的證據(jù)，可以考慮不建外鍵。否則，還是應(yīng)該建外鍵。

■?不要以數(shù)據(jù)操作不方便為理由而不建外鍵

是的，加上外鍵以后，一些數(shù)據(jù)操作變得有些麻煩，但是這正是對數(shù)據(jù)一致性

的保護(hù)。正是因?yàn)檫@種保護(hù)很有效，所以最好不要拒絕它。

■?以缺省的方式建立外鍵

以缺省的方式建立外鍵（即用deleterestrict方式），以達(dá)到保護(hù)數(shù)據(jù)一致性的

目的；外鍵在保護(hù)數(shù)據(jù)一致方面非常有效。如果不建外鍵，數(shù)據(jù)庫中容易出現(xiàn)垃圾

數(shù)據(jù)，并且無人知曉。當(dāng)數(shù)據(jù)量很大的時候，查找這些垃圾數(shù)據(jù)也是相當(dāng)困難的。

而應(yīng)用程序在設(shè)計時，往往沒有考慮或者也無法照顧到垃圾數(shù)據(jù)。因此垃圾數(shù)據(jù)很

可能造成應(yīng)用程序工作不正常，并且表現(xiàn)出來的現(xiàn)象會很奇怪，讓人摸不著頭腦。

3.2.4字段類型及寬度的規(guī)范

字段的寬度要在一定時間內(nèi)足夠用，但也不要過寬，占用過多的存儲空間，對于

長度不確定的列，采用可變長度的數(shù)據(jù)類型如varchar類型；

字段的類型及寬度在設(shè)計以及后面進(jìn)行開發(fā)時，往往要與應(yīng)用的設(shè)計、開發(fā)人

員商討，以得到雙方認(rèn)可的類型及寬度；

3.2.5一個表所含字段總長度的規(guī)范

一個表中的所有字段，應(yīng)當(dāng)能存儲在一個數(shù)據(jù)塊中（BLOCK）,也即：表的單行

字段總長度＜db_bk）ck（減去pctfree）。對不含有大對象數(shù)據(jù)類型字段的表，字段數(shù)大

于5（）個的，請DBA團(tuán)隊(duì)參與設(shè)計。

查詢字典表USERTABCOLUMNS中的字段DATA_LENGTH得到表中所有字段的

總長度，再依據(jù)db_block和表的pctfree參數(shù)可以判斷是否一個數(shù)據(jù)行可以存儲在一

個數(shù)據(jù)塊(BLOCK)中。

對表添如果所有字段的總長度超出了一個數(shù)據(jù)塊，那么需要將該表拆分成兩個

(甚至多個)表，拆分的依據(jù)是字段的頻繁使用程度，也就是頻繁使用的字段在一

個表中，很少被使用的字段放在另一個表中，他們之間使用相同的主鍵值，用主外

鍵關(guān)聯(lián)。這點(diǎn)就是“一個表所含字段訪問頻繁度的規(guī)范”。

3.2.6一個表所含字段訪問頻繁度的規(guī)范

一個表中的各字段的訪問頻繁度應(yīng)該基本一致，如果一個表的字段數(shù)超過50

個，請DBA參與審核。

如果一個表的字段數(shù)過多超過50個，并且依據(jù)業(yè)務(wù)邏輯確定該表中一些字段頻

繁被訪問，另一些字段則很少被訪問，則該表需耍做拆分處理，這樣可以避免讀取

頻繁信息時多讀取很少被訪問的信息，可以提高10性能，減少內(nèi)存耗費(fèi)，這在

OLAP系統(tǒng)中比較常見。

將訪問頻繁度相差太遠(yuǎn)的字段拆分到兩個表中，一個表存頻繁訪問的字段，另

一個表存很少被訪問的字段。

3.2.7大對象字段(BLOB,CLOB)使用規(guī)范

存儲圖片，視頻，音頻，文件，500字節(jié)以上文本等占用太多空間的字段(大對

象字段)，不能和其他字段存儲在一個表中。含有大對象(BLOB,CLOB)字段的

表設(shè)計和存儲請DBA參與設(shè)計。一般有兩種方法：

■?數(shù)據(jù)庫存儲

可以重新建一個表專門存儲該大對象字段，該表基本為兩個字段，一個為大對

象編號ID為主鍵，一個為大對象內(nèi)容本身，并將該主鍵在原表中作外鍵關(guān)聯(lián)，該大

對象表存儲在單獨(dú)的表空間中。

■?操作系統(tǒng)存儲

將這些文件存儲在操作系統(tǒng)空間中，大對象字段存儲該文件的全路徑名。

如果該大對象字段常被修改，那么采用方法一；如果該大對象信息為靜態(tài)，加

載后基本不變，那么可以采用方法二，它有一個致命缺點(diǎn)就是信息存儲在數(shù)據(jù)庫外

部，不安全，容易丟失。

3.2.8關(guān)于字段能否為NULL值

對于字段能否為null,應(yīng)該在sql建表腳本中明確指定，不應(yīng)該使用缺省。由于

null值在參加任何計算時，結(jié)果均為null,所以在程序中必須用nvl()函數(shù)把可能為

null值的字段或變量轉(zhuǎn)換非null的默認(rèn)值。

3.2.9關(guān)于冗余列的規(guī)范

除非必要，否則盡量不加冗余列。

所謂冗余列，是指能通過其他列計算出來的列，或者是與某列表達(dá)同一含義的

列，或者是從其他表復(fù)制過來的列等等。冗余列需要應(yīng)用程序來維護(hù)一致性，相關(guān)

列的值改變的時候，冗余列也需要隨之修改，而這一規(guī)則未必所有人都知道，就有

可能因此發(fā)生不一致的情況。

如果是應(yīng)用的特殊需要，或者是為了優(yōu)化某些邏輯很復(fù)雜的查詢等操作，可以

加冗余列。

3.2.10使用注釋的規(guī)范

每個表，每個字段都要有注釋，說明其含義，對于冗余字段還要特別說明其維

護(hù)方法，外鍵字段說明參照與那個表。原則上誰設(shè)計誰注釋。

查詢字典表user_tab_comments和user_col_comments可知道表和字段的注釋

信息。

對表添加注釋:

SQL>commentontable<tablename>is'xx';

對字段添加注釋:

SQL>commentoncolumn<tablename>.<colname>is'xx

3.2.11一個表所含數(shù)據(jù)量的規(guī)范

一個非分區(qū)表中的數(shù)據(jù)量不要超過500萬。當(dāng)一個非分區(qū)表中的數(shù)據(jù)量超過500

萬時，需設(shè)計成分區(qū)表；如果該表數(shù)據(jù)量超過5000萬，請DBA參與設(shè)計。

在系統(tǒng)上線前，通過對業(yè)務(wù)分析，判斷一個表的數(shù)據(jù)量；在系統(tǒng)上線后，可以

通過exp的日志，Top性能SQL,count(l)來發(fā)現(xiàn)數(shù)據(jù)量大的表。

將這些表進(jìn)行分區(qū),具體方法請參看分區(qū)表的設(shè)計規(guī)范。

I

記錄數(shù)超過兩億條的表一定要考慮信息生命周期，必須考慮歷史數(shù)據(jù)的剝離，

并在應(yīng)用設(shè)計中完成對歷史數(shù)據(jù)的相應(yīng)處理功能(歷史數(shù)據(jù)的剝離規(guī)則須經(jīng)業(yè)務(wù)使

用部門的確認(rèn))。

3.2.12增量同步表的設(shè)計規(guī)范

字典信息表和需要使用增量同步的表必須增加如下屬性。

屬性名類型取值說明

StatusChar(1)Y/N：Y為激活N標(biāo)識該行是否使用。用于軟刪除，

為作廢，默認(rèn)為Y軟刪除需將主鍵和唯一約束列添加

隨機(jī)數(shù)后綴。

CreatetimeDate默認(rèn)為sysdate創(chuàng)建時間

Update_timeDate默認(rèn)為sysdate最后修改時間

3.3字段類型規(guī)范

3.3.1不使用會發(fā)生隱式轉(zhuǎn)換：INTEGER,FLOAT

INTEGER改為NUMBER(n)

FLOAT改為NUMBER(p,s)

3.3.2不使用過時老類型：RAW,LONG,LONGRAW

1非標(biāo)準(zhǔn):VARCHAR2(nCHAR)、CHAR(nCHAR)

VARCHAR2(nCHAR)改為VARCHAR2(n)

CHAR(nCHAR)改為CHAR(n)

3.3.3國家字符集相關(guān)

1國家字符集相關(guān)：NCHAR,NVARCHAR2,NCLOB

NCHAR改為CHAR

NVARCHAR2改為VARCHAR2

NCLOB改為CLOB

3.3.4不能使用大對象：BLOB,CLOB,NCLOB

1不能使用大對象：BLOB,CLOB,NCLOB

CLOB和NCLOB改為VARCHAR2

3.3.5不能使用高精度：TIMESTAMP

1不能使用高精度：TIMESTAMP

TIMESTAMP改為DATE

3.3.6關(guān)于CHAR字段

CHAR字段類型長度小于10(),長度大于1()()的字符型信息應(yīng)該使用VARCHAR2

字段類型來存儲。

4分區(qū)表的設(shè)計規(guī)范

4.1表空間及分區(qū)表的概念

4.1.1表空間

是一個或多個數(shù)據(jù)文件的集合，所有的數(shù)據(jù)對象都存放在指定的表空間中，但

主要存放的是表，所以稱作表空間。

4.1.2分區(qū)表

當(dāng)表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會變慢，應(yīng)用程序的性能就會下

降，這時就應(yīng)該考慮對表進(jìn)行分區(qū)。表進(jìn)行分區(qū)后，邏輯上表仍然是一張完整的

表，只是將表中的數(shù)據(jù)在物理上存放到多個“表空間”（物理文件上），這樣查詢數(shù)據(jù)

時，不至于每次都掃描整張表而只是從當(dāng)前的分區(qū)查到所要的數(shù)據(jù)大提高了數(shù)據(jù)查

詢的速度。

4.2表分區(qū)的具體作用

Oracle的表分區(qū)功能通過改善可管理性、性能和可用性，從而為各式應(yīng)用程序

帶來了極大的好處。通常，分區(qū)可以使某些查詢以及維護(hù)操作的性能大大提高。此

外，分區(qū)還可以極大簡化常見的管理任務(wù)，分區(qū)是構(gòu)建千兆字節(jié)數(shù)據(jù)系統(tǒng)或超高可用

性系統(tǒng)的關(guān)鍵工具。分區(qū)功能能夠?qū)⒈?、索引或索引組織表進(jìn)一步細(xì)分為段，這些

數(shù)據(jù)庫對象的段叫做分區(qū)。每個分區(qū)有自己的名稱，還可以選擇自己的存儲特性。

從數(shù)據(jù)庫管理員的角度來看，一個分區(qū)后的對象具有多個段，這些段既可進(jìn)行集體

管理，也可單獨(dú)管理，這就使數(shù)據(jù)庫管理員在管理分區(qū)后的對象時有相當(dāng)大的靈活

性。但是，從應(yīng)用程序的角度來看，分區(qū)后的表與非分區(qū)表完全相同，使用SQL

DML命令訪問分區(qū)后的表時，無需任何修改。

什么時候使用分區(qū)表：

1、表的大小超過2GB,數(shù)據(jù)量超過500萬；

2、表中包含歷史數(shù)據(jù)，新的數(shù)據(jù)被增加都新的分區(qū)中。

4.3表分區(qū)的優(yōu)缺點(diǎn)

表分區(qū)有以下優(yōu)點(diǎn)：

■?改善查詢性能：對分區(qū)對象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢

索速度。

■?增強(qiáng)可用性：如果表的某個分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可

用；

??維護(hù)方便：如果表的某個分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)

即可；

■?均衡I/O：可以把不同的分區(qū)映射到磁盤以平衡I/O,改善整個系統(tǒng)性

能。

缺點(diǎn)：

■?己經(jīng)存在的表沒有方法可以直接轉(zhuǎn)化為分區(qū)表。不過，Oracle提供了

在線重定義表的功能。

4.4分區(qū)表設(shè)計規(guī)范

4.2.1不使用全局索引

在分區(qū)表中不建議使用全局索引，因?yàn)閠runc分區(qū)時會導(dǎo)致全局索引失效，造

成難以維護(hù)。

4.2.2RANGE分區(qū)的規(guī)范

大數(shù)據(jù)量的表需進(jìn)行分區(qū)化，當(dāng)表的數(shù)據(jù)量超過500萬，需設(shè)計成分區(qū)表，當(dāng)

表的數(shù)據(jù)量超過500（）萬，請DBA參與設(shè)計。

SQL常依據(jù)某列的范圍訪問表，則對表使用RNAGE分區(qū)。常見情況是SQL根

據(jù)時間范圍進(jìn)行查新，則使用時間字段作為分區(qū)關(guān)鍵字進(jìn)行RANGE分區(qū)；

將對表的多種訪問結(jié)合考慮來確定分區(qū)的細(xì)度：

■?大多數(shù)SQL操作的分區(qū)關(guān)鍵字值的范圍；

■?數(shù)據(jù)維護(hù)的需要，比如以月為單位刪除歷史數(shù)據(jù)；

■?數(shù)據(jù)訪問的性能，以操作范圍確定的分區(qū)數(shù)據(jù)量還是過大，比如大于500

萬，則還需要進(jìn)行細(xì)分；

■?一個分區(qū)的數(shù)據(jù)量要小于500萬，這是一個硬性的尺度，但從技術(shù)上來

看，每個分區(qū)10萬數(shù)據(jù)量的情況比每個分區(qū)20萬數(shù)據(jù)量的情況要快很

多，所以需要靈活掌握。

/.當(dāng)各個分區(qū)中的數(shù)據(jù)能均等劃分時性能最好,如果相差太大,則考慮采用

其它分區(qū),或者將大數(shù)據(jù)量的分區(qū)再進(jìn)行HASH子分區(qū);

2.各分區(qū)采用各自的表空間存儲,使用usertabpartitions字典來查看-

確定每個分區(qū)的表空間位置;

3.分區(qū)表的索引采用本地索引,因?yàn)槌鶕?jù)分區(qū)關(guān)鍵字（比如時間）進(jìn)行

分區(qū)維護(hù)（比如刪除1年前的數(shù)據(jù),也就是刪除/年前的分區(qū)），分區(qū)維護(hù)

時全局索引會失效,而本地索引不會失效,這能保證訪問表時索引正?？?/p>

4.2.3LIST分區(qū)的規(guī)范

大數(shù)據(jù)量的表需進(jìn)行分區(qū)化，當(dāng)表的數(shù)據(jù)量超過500萬，需設(shè)計成分區(qū)表，當(dāng)

表的數(shù)據(jù)量超過5000萬，請DBA參與設(shè)計。

SQL常居于某列的散列值訪問表，則對表使用LIST分區(qū)，LIST分區(qū)不支持多列

分區(qū)關(guān)鍵字；常見情況針對某個地區(qū)或者某個業(yè)務(wù)進(jìn)行數(shù)據(jù)訪問，那么就使用地區(qū)

編號或者業(yè)務(wù)編號作為分區(qū)關(guān)鍵字。

將對表的多種訪問結(jié)合考慮來確定分區(qū)的細(xì)度：

■?一般使用一個分區(qū)關(guān)鍵字的值來劃定一個分區(qū)；

■?可以把分區(qū)關(guān)鍵字的值相對應(yīng)數(shù)據(jù)比較少的幾個分區(qū)合并作一個分區(qū)；

■?如果一個分區(qū)關(guān)鍵字值所對應(yīng)的數(shù)據(jù)量過大，比如大于500萬，則應(yīng)該

對表采用RANGE分區(qū)，對該值的分區(qū)再采用HASH子分區(qū)；也就是說，

一個可以采用LIST分區(qū)的表，肯定可以轉(zhuǎn)化成RANGE分區(qū)（可帶子分

區(qū)），反之不然；

■?一個分區(qū)的數(shù)據(jù)量要小于500萬，這是一個硬性的尺度，但從技術(shù)上來

看，每個分區(qū)10萬數(shù)據(jù)量分區(qū)方法比每個分區(qū)20萬數(shù)據(jù)量的分區(qū)方法要

快很多，所以需要靈活掌握。

1.各分區(qū)采用各自的表空間存儲,usertabpartitions字典來確定

每個分區(qū)的表空間;

2.分區(qū)表的索引采用本地索引。

4.2.4HASH分區(qū)的規(guī)范

大數(shù)據(jù)量的表需進(jìn)行分區(qū)化，當(dāng)表的數(shù)據(jù)量超過500萬，需設(shè)計成分區(qū)表，當(dāng)

表的數(shù)據(jù)量超過5000萬，請DBA參與設(shè)計。

SQL訪問表不按照某列的范圍進(jìn)行，也不按某列離散值進(jìn)行，而且對該表的數(shù)據(jù)

不會依據(jù)某列的值范圍或者離散值進(jìn)行定期維護(hù)，那么使用HASH分區(qū)；HASH分

區(qū)是不知道應(yīng)該選擇何種分區(qū)時的選擇；HASH分區(qū)的各分區(qū)都可能存有各種情況

的數(shù)據(jù)，故而不能用于依據(jù)分區(qū)清理數(shù)據(jù)的情況。

對確定分區(qū)細(xì)度的考慮：

■,依據(jù)分區(qū)的數(shù)據(jù)量規(guī)劃和表的最大數(shù)據(jù)量來確定分區(qū)數(shù)；

■?一個分區(qū)的數(shù)據(jù)量要小于500萬，這是一個硬性的尺度，但從技術(shù)上來

看，每個分區(qū)10萬數(shù)據(jù)量分區(qū)方法比每個分區(qū)20萬數(shù)據(jù)量的分區(qū)方法要

快很多，所以需要靈活掌握。

1.各分區(qū)采用各自的表空間存儲,使用usertabpartitions字典來確定

每個分區(qū)的表空間;

2.對于HASH分區(qū)表,大多數(shù)情況下依然要求采用本地索引,但是如果分區(qū)

過細(xì)，也可以采用全局索引,因?yàn)楦鶕?jù)HASH分區(qū)表的特征（各分區(qū)無業(yè)務(wù)

區(qū)分,都有數(shù)據(jù)），該表很少會發(fā)生分區(qū)維護(hù)的工作。

4.2.5RANGE-LIST分區(qū)的規(guī)范

大數(shù)據(jù)量的表需進(jìn)行分區(qū)化，當(dāng)表的數(shù)據(jù)量超過500萬，需設(shè)計成分區(qū)表，當(dāng)

表的數(shù)據(jù)量超過5000萬，請DBA參與設(shè)計。

SQL訪問表時，既依據(jù)某列值的范圍，又依據(jù)其他列的離散值或者范圍，這種情

況下采用RANGE-LIST復(fù)合分區(qū)，常用于語表中的數(shù)據(jù)需要依據(jù)一個時間字段做周

期性刪除等維護(hù)，并且正常業(yè)務(wù)SQL訪問既依據(jù)時間字段，又依據(jù)其他字段的散列

值進(jìn)行訪問的情況。

比如：電信增值業(yè)務(wù)計費(fèi)表，既有時間又有業(yè)務(wù)屬性列，統(tǒng)計的時候，會選擇

時間范圍和業(yè)務(wù)屬性，所以可以以時間列為分區(qū)關(guān)鍵字建立RANGE分區(qū)，以業(yè)務(wù)屬

性列為關(guān)鍵字建立LIST子分區(qū)；分區(qū)劃分的方法：

■?就按照大多數(shù)范圍訪問的范圍值來劃定RANGE分區(qū)的范圍，依據(jù)單個

LIST子分區(qū)關(guān)鍵字的值來劃分子分區(qū)；

■?如果LIST子分區(qū)中數(shù)據(jù)量較小而且又常被一起訪問的子分區(qū)可以合并成

一個子分區(qū)；

■?如果LIST子分區(qū)中一個子分區(qū)關(guān)鍵字值對應(yīng)的子分區(qū)數(shù)據(jù)量還是很大，

超過500,影響性能，那么可以通過細(xì)分RANGE分區(qū)來達(dá)到減少LIST子

分區(qū)數(shù)據(jù)量的目的，這點(diǎn)和LIST分區(qū)在該情況下的處理方法（轉(zhuǎn)化成

RANGE-HASH）不同。

/.各子分區(qū)應(yīng)該盡量分散到不同的表空間中存儲,使用

usertabsubpartitions字典來確定每個子分區(qū)的表空間;

2.RANGE-1.1ST大多數(shù)情況采用本地索引,因?yàn)槌８鶕?jù)RANGE分區(qū)關(guān)鍵字的

來進(jìn)行分區(qū)維護(hù)。

4.2.6RANGE-HASH分區(qū)的規(guī)范

大數(shù)據(jù)量的表需進(jìn)行分區(qū)化，當(dāng)表的數(shù)據(jù)量超過500萬，需設(shè)計成分區(qū)表，當(dāng)

表的數(shù)據(jù)量超過5000萬，請DBA參與設(shè)計。

SQL訪問表時，主要依據(jù)某個列的范圍進(jìn)行訪問，即訪問特征符合RANGE分區(qū)

的要求，或者數(shù)據(jù)維護(hù)特征符合RANGE分區(qū)的要求，但是以SQL或者維護(hù)的數(shù)據(jù)

范圍來劃定分區(qū)，分區(qū)數(shù)據(jù)量又很大，對性能有影響，需再進(jìn)行子分區(qū)，由于分區(qū)

中的數(shù)據(jù)都會被訪問到，所以子分區(qū)采用HASH方法，整個表就是RANGE-HASH

分區(qū)；

劃定分區(qū)的方法：先按照大多數(shù)范圍訪問的范圍值來劃定RANGE分區(qū)的范圍，

再依據(jù)性能情況來確定HASH子分區(qū)的數(shù)據(jù)量。

/.各子分區(qū)應(yīng)該盡量分散到不同的表空間中存儲,使用

usertabsubpartitions字典來確定每個子分區(qū)的表空間；

2.RANGE-HASH大多數(shù)情況采用本地索弓I，因?yàn)槌８鶕?jù)RANGE分區(qū)關(guān)鍵字的

來進(jìn)行分區(qū)維護(hù)。

5索引的設(shè)計規(guī)范

5.1索引分類

Oracle中可以創(chuàng)建多種類型的索引，以適應(yīng)各種表的特點(diǎn)和各種查詢條件的特

點(diǎn)?？梢园戳械亩嗌?、索引列是否唯一、索引數(shù)據(jù)的組織形式對索引進(jìn)行分類。

5.1.1單列索引與復(fù)合索引

一個索引可以由一個或多個列組成，用來創(chuàng)建索引的列被稱為“索引列”。

單列索引是基于單列所創(chuàng)建的索引，復(fù)合索引是基于兩列或者多列所創(chuàng)建的索

引。

5.1.2唯一索引與非唯一索引

唯一索引是索引列值不能重復(fù)的索引，非唯一索引是索引列可以重復(fù)的索引。

無論是唯一索引還是非唯一索引，索引列都允許取NULL值。默認(rèn)情況下，

Oracle創(chuàng)建的索引是不唯一索引。

5.1.3B樹索引、位圖索引與函數(shù)索引

B樹索引是按B樹算法組織并存放索引數(shù)據(jù)的，所以B樹索引主要依賴其組織

并存放索引數(shù)據(jù)的算法來實(shí)現(xiàn)快速檢索功能。

Oracle中不僅能夠直接對表中的列創(chuàng)建索引，還可以對包含列的函數(shù)或表達(dá)式

創(chuàng)建索引，這種索引稱為“函數(shù)索引”。

位圖索引在多列查詢時，可以對兩個列上的位圖進(jìn)行AND和OR操作，達(dá)到更好

的查詢效果。

5.2命名規(guī)范

命名規(guī)則：類別碼一表名一附加碼，采用大寫字符。

類別碼：一般索引IDX、位圖索引BIDX、唯一索引UK、主鍵PK、外鍵

FK,類別碼根據(jù)索引的性質(zhì)填寫。

表名：表名應(yīng)該簡潔明了，盡量使用完整的單詞，如果導(dǎo)致拼上表名后，長度

超過30個字符，則從最后一個單詞開始，依次向前采用該單詞的縮寫。（如果沒

有約定的縮寫，則采用該單詞前4個字母來表示）。另外，表名中的名詞單詞都應(yīng)

使用單數(shù)形式，以免混淆，如：使用FACTORY而非FACTORIES。

附加碼：可以是序號，也可以是字段名，根據(jù)實(shí)際的使用情況進(jìn)行填寫。

5.3索引設(shè)計規(guī)范

索引是從數(shù)據(jù)庫中獲取數(shù)據(jù)的最高效方式之一。95%的數(shù)據(jù)庫性能問題都可以采

用索引技術(shù)得到解決。但大量的DML操作會增加系統(tǒng)對索引的維護(hù)成本，對性能會

有一定影響，對于插入相當(dāng)頻繁的表要慎重建索引，索引也會占相當(dāng)?shù)拇鎯臻g，

所以要根據(jù)硬件環(huán)境和應(yīng)用需求在空間和時間上達(dá)到最好的平衡點(diǎn)。主要原則：

■?適當(dāng)利用索引提高查詢速度：當(dāng)數(shù)據(jù)量比較大，了解應(yīng)用程序的會有哪

些查詢，依據(jù)這些查詢需求建相應(yīng)的索引；最好親自試驗(yàn)一下，模擬一下

生產(chǎn)環(huán)境的數(shù)據(jù)量，在此數(shù)據(jù)量下，比較一下建索引前后的查詢速度；索

引對性能會有一定影響，對于DML頻繁列的索引要定期維護(hù)（重建）。但

是，索引的結(jié)構(gòu)對于索引的更新（比如在插入數(shù)據(jù)的時候）是有一定優(yōu)化

的，所以不要在沒有試驗(yàn)以前過分夸大它對性能的影響，最終還是以試驗(yàn)

為準(zhǔn)。

■?不要建實(shí)際用不上的索引，與上條相關(guān)，如果建的索引并不提高任何一

應(yīng)用中的查詢速度，則要把它刪除；有些數(shù)據(jù)庫有相關(guān)工具可以發(fā)現(xiàn)實(shí)際

未被使用的索引，可以利用一下。

??索引列的選擇：如果檢索條件有可能包含多列，創(chuàng)建聯(lián)合主鍵或者聯(lián)合

索引，把最常用于檢索條件的列放在最前端，其他的列排在后面；不要索

引使用頻繁的小型表，假如這些小表有頻繁的DML就更不要建立索引，維

護(hù)索引的代價遠(yuǎn)遠(yuǎn)高于掃描表的代價；

■?主鍵索引在建立的時候一定要明確的指定名稱，不能讓系統(tǒng)默認(rèn)建立主

鍵索引（可能有些數(shù)據(jù)庫無法指定主鍵名，則例外）；

■?當(dāng)有聯(lián)合主鍵或者聯(lián)合索引時，注意不要建重復(fù)的索引。

舉例說明:

表EMPLOYEES,它的主鍵是建立在列DEPARTID和EMPLOYEE1D上的聯(lián)合

主鍵,并且創(chuàng)建主鍵的語句中DEPART1D在前,EMPLOYEES在后。在這樣一個表

里,通常就沒有必要再為DEPART1D建一個索引了,聯(lián)合索引的情況也一樣。

更復(fù)雜的情況,比如表EMPLOYEES,有一個索引建立在列CORPID,

DEPARTID,EMPLOYEES三列上,在創(chuàng)建語句中也依據(jù)上述順序,就沒有必要再

為C0RP1D建立索引;也沒有必要再建立以C0RP1D在前,DEPARTID在后的聯(lián)合

索引:如果EMPL0YEE1D需要索引,那么為EMPLOYEEID建立一個索引是不與上

而的索引重復(fù)的;DEPARTID歹U也類f以

■?控制一個表的索引數(shù)量，盡量使得一個表的索引數(shù)量小于五個。

5.3.1指定表空間規(guī)范

每個索引在創(chuàng)建時，必須指定表空間，不要采用默認(rèn)表空間，以防止索引建立

在system空間和非索引專用空間，以減少IO沖突，提高性能。

5.3.2主鍵索引的規(guī)范

對數(shù)據(jù)量表應(yīng)該先在主鍵列建唯一索引，再建主鍵約束。分區(qū)表的主鍵必須采

用該方法設(shè)計。原則上所有的數(shù)據(jù)表都要有主鍵。

主鍵上隱含索引，drop或disable主鍵時，索引會丟失，為保證性能不變，為了

對主鍵約束和相應(yīng)索引有更多的控制，對大表(分區(qū)表)的索引采用如下方式建立：

1.在準(zhǔn)備建主鍵的列上建立唯一索引(UNIQUEINDEX):CREATEUNIQUE

INDEXIndex_NameONTable_Name(Column_Name)TABLESPACETBS_INDEX;

2.再加上主鍵約束：

ALTERTABLETable_NameADD(PRIMARYKEY(Column_Name)USING

INDEXTABLESPACETBSJNDEX);

Oracle會在指定的列上加上主鍵約束，并且使用該索引。

分區(qū)表的主鍵默認(rèn)索引是全局索引,所以主鍵索引的分區(qū)方法:先建立分區(qū)化

的唯一索引,再建主鍵約束。

5.3.3唯一約束索引的規(guī)范

針對大數(shù)據(jù)量表應(yīng)該先在唯一約束列上建立普通索引，再添加唯一性約束。分

區(qū)表的唯一約束必須采用該方法。

刪除或禁用唯一性約束通常同時使相關(guān)聯(lián)的唯一索引失效，因而降低了數(shù)據(jù)庫

性能。要避免這樣問題，可以采取下面的步驟：(a)在唯一性約束的列上創(chuàng)建非唯

一性索引(普通索引)；(b)添加唯一性約束。

5.3.4外鍵列索引的規(guī)范

對于關(guān)聯(lián)兩個表字段，一般應(yīng)該分別建立主鍵、外鍵。實(shí)際是否建立外鍵，根

據(jù)對數(shù)據(jù)完整性的要求決定。為了提高性能，無論表的大小，外鍵都要建立索引，

一是為了子父表關(guān)聯(lián)查詢的性能考慮，二是為了避免父子表修改而發(fā)生死鎖。對于

有要求級聯(lián)刪除屬性的外鍵，必須指定ondeletecascade,

普通表的外鍵列建立普通索引即可，如果表是分區(qū)表，則依據(jù)表的情況建立本

地索引或者全局索引。

5.3.5復(fù)合索引的規(guī)范

復(fù)合索引只有在該種復(fù)合常被和該表相關(guān)的大多數(shù)SQL使用時才建立。復(fù)合索

引的列數(shù)不能超過5個，否則該索引很少會被使用。

■?復(fù)合索引的第一列，可以通過不使用該種復(fù)合的SQL來確定。假設(shè)一些

SQL的WHERE中復(fù)合使用列為ABC,而其他一些SQL的WHERE中常使

用的是C列，那么該復(fù)合索引可以按照CAB的順序建立，這樣上述兩種

SQL都能使用該索引；

■?對于不能把握好的復(fù)合索引，請?jiān)谶x擇性大的列上分別建立單列索引：

■?切忌不能將表相關(guān)的所有SQL中WHERE涉及到的列復(fù)合起來建立復(fù)合

索引。

5.3.6函數(shù)索引的規(guī)范

由于使用形式需和創(chuàng)建形式一致，盡量避免使用函數(shù)索引。如果想要使用函數(shù)

索引，請盡量進(jìn)行轉(zhuǎn)化。

由于函數(shù)索引在使用時，使用形式必須和創(chuàng)建形式一致，故應(yīng)該盡量避免使用

函數(shù)索引，盡量采用如下方法轉(zhuǎn)化SQL以避免函數(shù)索引的使用：

原本在WHERE中列上添加函數(shù)的，取函數(shù)的反意義函數(shù)添加到“=”另一側(cè)的常

數(shù)項(xiàng)上，這樣只需要在列上建立普通索引即可，比如常見的日期轉(zhuǎn)化函數(shù)：

TO_CHAR（CREATE_TIME）=（2010-07-07'采用TO_DATE（）轉(zhuǎn)化為

CREATE_TIME=TO_DATE（12010-07-07'yyyy-mm-dd'）。

5.3.7位圖索引的規(guī)范

靜態(tài)表中的低基數(shù)列可以使用位圖索引。在事務(wù)型數(shù)據(jù)庫（OLTP）中禁止使用

位圖（bitmap）索引，在報表型數(shù)據(jù)庫（OLAP）中的靜態(tài)表，可以適當(dāng)使用。

5.3.8反向索引的規(guī)范

列值順序增加的列，其上的WHERE運(yùn)算是＜＞或者=而不是范圍（betweenand或

者＜and＞）檢索時，可以采用反向函數(shù)。一般創(chuàng)建反向索引的列為NUMBER類

型，值由SEQUENCE生成。

5.3.9分區(qū)索引的規(guī)范

對分區(qū)表的索引，需要做分區(qū)維護(hù)的，必須使用局部索引。一般情況下，HASH

分區(qū)表可以采用全局索引，其他分區(qū)，包括RANGE-HASH也應(yīng)該采用本地索引，

主要是由于HASH分區(qū)表不常進(jìn)行分區(qū)維護(hù)。

5.3.10索引重建的規(guī)范

重建索引使用ALTERINDEXREBUILD方式，禁止采用DROPINDEX&

CREATEINDEX方式。分區(qū)表等大數(shù)據(jù)量表的索引必須采用ALTERINDEX

REBUILD方式重建。

方法：

ALTERINDEXIDX_NAMEREBUILD[TABLESPACETBSP_NAME]。

6其他數(shù)據(jù)庫對象設(shè)計規(guī)范

6.1命名規(guī)范

??視圖：v歸相關(guān)表名，或者根據(jù)需要另取名字；

■?存儲過程：SP存儲過程名，用英文表達(dá)存儲過程意義；

■?函數(shù)：FUN一函數(shù)名稱，用英文表達(dá)