關(guān)系型數(shù)據(jù)庫性能體系設(shè)計(jì)和效率提升收藏這篇就夠了

3萬字|關(guān)系型數(shù)據(jù)庫性能體系，設(shè)計(jì)和效率提升收藏這篇就夠了！1前言1.1目的本文檔詳細(xì)定義了關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)庫模型設(shè)計(jì)規(guī)范、表的設(shè)計(jì)規(guī)范、分區(qū)表的設(shè)計(jì)規(guī)范、索引的設(shè)計(jì)規(guī)范、其他數(shù)據(jù)庫對(duì)象的設(shè)計(jì)規(guī)范、SQL的訪問規(guī)范、編碼和注釋規(guī)范，并介紹了SQL調(diào)優(yōu)的關(guān)注點(diǎn)和常用方法，作為數(shù)據(jù)庫規(guī)劃、設(shè)計(jì)、開發(fā)及維護(hù)人員的技術(shù)參考資料，用以指導(dǎo)關(guān)系型數(shù)據(jù)庫的設(shè)計(jì)和開發(fā),性能是設(shè)計(jì)出來的，質(zhì)量也是可以設(shè)計(jì)出來的，理解這篇，關(guān)系型數(shù)據(jù)庫性能提升30%不是問題。我希望通過該規(guī)范的分享，能夠?qū)崿F(xiàn)以系統(tǒng)、體系的工程化思維模式去規(guī)范關(guān)系型數(shù)據(jù)庫設(shè)計(jì)和開發(fā)，使數(shù)據(jù)庫結(jié)構(gòu)和編碼風(fēng)格標(biāo)準(zhǔn)化，提高模型的前瞻性、高效性，以盡早提前避免由于數(shù)據(jù)庫設(shè)計(jì)不當(dāng)而產(chǎn)生的麻煩，同時(shí)好的規(guī)范，在執(zhí)行的時(shí)候可以培養(yǎng)出好的習(xí)慣，好的習(xí)慣是軟件質(zhì)量的很好的保證。目

錄1前言11.1目的11.2預(yù)期的讀者和閱讀建議62數(shù)據(jù)庫模型設(shè)計(jì)規(guī)范62.1數(shù)據(jù)庫建模原則性規(guī)范62.2實(shí)體型之間關(guān)系認(rèn)定規(guī)范72.3范式化1NF的規(guī)范72.4范式化2NF的規(guī)范82.5范式化3NF的規(guī)范92.5反范式化冗余字段使用規(guī)范92.6數(shù)據(jù)庫對(duì)象命名基本規(guī)范102.6.1遵循行業(yè)規(guī)范102.6.2簡(jiǎn)單命名原則102.6.3字符范圍原則112.6.4字母全部大寫或小寫原則112.6.5勿用保留詞原則112.6.5同義性原則112.6.6富有含義原則112.6.7擴(kuò)展性原則113表的設(shè)計(jì)規(guī)范123.1命名規(guī)范123.1.1表的命名規(guī)范123.1.2字段的命名規(guī)范123.2表的設(shè)計(jì)規(guī)范133.2.1指定表空間規(guī)范133.2.2表的主鍵規(guī)范133.2.3表的外鍵規(guī)范133.2.4字段類型及寬度的規(guī)范143.2.5一個(gè)表所含字段總長(zhǎng)度的規(guī)范143.2.6一個(gè)表所含字段訪問頻繁度的規(guī)范153.2.7大對(duì)象字段（BLOB，CLOB）使用規(guī)范153.2.8關(guān)于字段能否為NULL值153.2.9關(guān)于冗余列的規(guī)范163.2.10使用注釋的規(guī)范163.2.11一個(gè)表所含數(shù)據(jù)量的規(guī)范163.2.12增量同步表的設(shè)計(jì)規(guī)范173.3字段類型規(guī)范173.3.1不使用會(huì)發(fā)生隱式轉(zhuǎn)換：INTEGER，F(xiàn)LOAT173.3.2不使用過時(shí)老類型：RAW，LONG，LONGRAW173.3.3國家字符集相關(guān)173.3.4

不能使用大對(duì)象：BLOB，CLOB，NCLOB183.3.5不能使用高精度：TIMESTAMP183.3.6關(guān)于CHAR字段184分區(qū)表的設(shè)計(jì)規(guī)范184.1表空間及分區(qū)表的概念184.1.1表空間184.1.2分區(qū)表184.2表分區(qū)的具體作用194.3表分區(qū)的優(yōu)缺點(diǎn)194.4分區(qū)表設(shè)計(jì)規(guī)范194.2.1不使用全局索引194.2.2RANGE分區(qū)的規(guī)范204.2.3LIST分區(qū)的規(guī)范204.2.4HASH分區(qū)的規(guī)范214.2.5RANGE-LIST分區(qū)的規(guī)范224.2.6RANGE-HASH分區(qū)的規(guī)范225索引的設(shè)計(jì)規(guī)范235.1索引分類235.1.1單列索引與復(fù)合索引235.1.2唯一索引與非唯一索引235.1.3B樹索引、位圖索引與函數(shù)索引235.2命名規(guī)范245.3索引設(shè)計(jì)規(guī)范245.3.1指定表空間規(guī)范255.3.2主鍵索引的規(guī)范255.3.3唯一約束索引的規(guī)范265.3.4外鍵列索引的規(guī)范265.3.5復(fù)合索引的規(guī)范265.3.6函數(shù)索引的規(guī)范275.3.7位圖索引的規(guī)范275.3.8反向索引的規(guī)范275.3.9分區(qū)索引的規(guī)范275.3.10索引重建的規(guī)范276其他數(shù)據(jù)庫對(duì)象設(shè)計(jì)規(guī)范286.1命名規(guī)范286.2視圖設(shè)計(jì)規(guī)范286.2.1盡量使用簡(jiǎn)單的視圖，避免使用復(fù)雜的視圖286.2.2按照必要性原則建立視圖296.3存儲(chǔ)過程、函數(shù)、觸發(fā)器的設(shè)計(jì)規(guī)范296.3.1關(guān)于觸發(fā)器的設(shè)計(jì)297SQL訪問規(guī)范307.1盡量不要寫復(fù)雜的SQL307.2避免使用SELECT*307.3INSERT時(shí)需寫全列名307.4進(jìn)行DML操作時(shí)使用CTAS進(jìn)行數(shù)據(jù)備份317.5大數(shù)據(jù)量DML操作分多次執(zhí)行317.6使用綁定變量，降低高硬解析317.7選擇最有效率的表名順序327.8關(guān)注WHERE子句中的連接順序327.9用EXISTS替代IN337.10用表連接替換EXISTS347.11用EXISTS替換DISTINCT357.12盡量用unionall替換union357.13使用DECODE函數(shù)來減少處理時(shí)間357.14盡量避免用orderby367.15用Where子句替換HAVING子句367.16減少多表關(guān)聯(lián)377.17避免重復(fù)訪問377.17.1使用groupby377.17.2用表更新表387.17.3豎向顯示變橫向顯示387.18完成事務(wù)及時(shí)commit397.19數(shù)據(jù)庫連接及時(shí)關(guān)閉397.20索引的使用397.20.1避免在索引列上使用函數(shù)或運(yùn)算397.20.2避免改變索引列的類型407.20.3避免在索引列上使用NOT407.20.4用>=替代>417.20.5避免在索引列上使用ISNULL和ISNOTNULL417.20.6帶通配符（%）的like語句417.20.7總是使用索引的第一個(gè)列428編碼及注釋規(guī)范428.1編碼規(guī)范428.1.1避免隱式的數(shù)據(jù)類型轉(zhuǎn)換428.1.2不要將空的變量值直接與比較運(yùn)算符(符號(hào))比較438.1.3跨行語句，第一關(guān)鍵字應(yīng)當(dāng)左對(duì)齊438.1.4Insert…values和update語句書寫規(guī)范438.1.5Insert…select語句書寫規(guī)范448.1.6避免使用嵌套的IF語句448.1.7減少控制語句的檢查次數(shù)458.1.8語句涉及多個(gè)表時(shí)，使用別名來限定字段名468.1.9其他編碼規(guī)范478.2注釋規(guī)范508.2.1注釋語法508.2.2每個(gè)塊和過程開發(fā)放置注釋508.2.3代碼注釋應(yīng)放在其上方或右方508.2.4其他注釋規(guī)范519PLSQL優(yōu)化519.1性能問題分析519.2PLSQL優(yōu)化的核心思想529.3PLSQL優(yōu)化示例539.3.1減少對(duì)表的查詢539.3.2避免循環(huán)（游標(biāo)）里面嵌查詢559.3.3groupby優(yōu)化569.3.4刪除重復(fù)記錄579.3.5COMMIT使用579.3.6批量數(shù)據(jù)插入589.3.7索引使用優(yōu)化599.3.8使用提示(Hints)609.3.9表上存在過舊的分析619.3.10表上存在并行619.3.11關(guān)于索引建立629.3.12ExpainPlan分析索引使用621.2預(yù)期的讀者和閱讀建議本文檔預(yù)期的讀者為項(xiàng)目經(jīng)理、開發(fā)經(jīng)理、DBA、數(shù)據(jù)結(jié)構(gòu)管理師、系統(tǒng)設(shè)計(jì)師、開發(fā)師、測(cè)試師等相關(guān)崗位的人員。讀者可以通篇閱讀該文檔，以整體熟悉和掌握Oracle數(shù)據(jù)庫設(shè)計(jì)規(guī)范，也可以重點(diǎn)關(guān)注跟自身相關(guān)的內(nèi)容章節(jié)，如數(shù)據(jù)庫模型設(shè)計(jì)、表的設(shè)計(jì)，或SQL訪問規(guī)范、編碼和注釋規(guī)范等。2

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

數(shù)據(jù)庫建模原則性規(guī)范對(duì)于涉及數(shù)據(jù)庫的項(xiàng)目，需要構(gòu)建數(shù)據(jù)庫邏輯模型圖，邏輯模型圖是項(xiàng)目組成員之間在數(shù)據(jù)庫層面溝通交互的依據(jù)，必須規(guī)范畫圖（表，主鍵，外鍵，關(guān)系）。對(duì)于表的個(gè)數(shù)在20個(gè)以上的模型，需要DBA參與設(shè)計(jì)，并作最終審核。對(duì)于OLTP系統(tǒng)，采用范式化思想進(jìn)行模型設(shè)計(jì)，對(duì)于OLAP系統(tǒng)，采用面向問題及多級(jí)顆粒度的思想進(jìn)行模型設(shè)計(jì)。需采用主流的模型設(shè)計(jì)軟件工具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），對(duì)于3個(gè)及以上實(shí)體型之間的“多元關(guān)系”，需要DBA參與設(shè)計(jì)。比如，實(shí)體型A和實(shí)體型B之間的關(guān)系，可以通過問兩個(gè)問題來確定他們之間的關(guān)系：一個(gè)A可以對(duì)應(yīng)幾個(gè)B？一個(gè)B可以對(duì)應(yīng)幾個(gè)A?（1）一個(gè)A對(duì)應(yīng)一個(gè)B，相反一個(gè)B對(duì)應(yīng)一個(gè)A，那么A對(duì)B就是1:1關(guān)系；（2）一個(gè)A對(duì)應(yīng)多個(gè)B，相反一個(gè)B對(duì)應(yīng)一個(gè)A，那么A對(duì)B就是1:N關(guān)系；（3）一個(gè)A對(duì)應(yīng)多個(gè)B，相反一個(gè)B對(duì)應(yīng)對(duì)個(gè)A，那么A對(duì)B就是N:M關(guān)系；具體實(shí)施的時(shí)候，掌握如下原則：n

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

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

N:M關(guān)系采用“關(guān)系表”來體現(xiàn)，該關(guān)系表的主鍵是由相關(guān)實(shí)體表的主鍵組成的復(fù)合主鍵；各實(shí)體表主鍵不但組成了該關(guān)系表的主鍵，同時(shí)也被看作外鍵在該關(guān)系表中存在。n

對(duì)于三個(gè)以上表之間的“多元關(guān)系”常需要和反范式化冗余字段結(jié)合起來設(shè)計(jì)，以保證查詢速度。2.3范式化1NF的規(guī)范OLTP系統(tǒng)的模型，需要符合第三范式，對(duì)于表在20個(gè)以上的模型，需要DBA參與設(shè)計(jì)。范式化要求（1NF）：列是訪問的最小單位，具有原子性，不可再被分割。在具體實(shí)施的時(shí)候，需要依據(jù)情況對(duì)相應(yīng)屬性進(jìn)行拆分或者合并：n

同一個(gè)屬性值的不同細(xì)度把握比如，常見的“姓名”這個(gè)屬性，設(shè)計(jì)一：“姓名”是一個(gè)列，設(shè)計(jì)二：“姓”是一個(gè)列，“名”是一個(gè)列，兩個(gè)列的值組合起來才表達(dá)一個(gè)“姓名”語義。兩種設(shè)計(jì)方法，在不同的系統(tǒng)中都有應(yīng)用，這主要是依據(jù)需求的細(xì)度來確定，靈活把握。n

把多個(gè)屬性值錯(cuò)誤的作為一個(gè)屬性值存儲(chǔ)比如：常見的OA系統(tǒng)要存儲(chǔ)員工的各種屬性，包括技能信息，技能范圍：Oracle，JAVA，.NET,C#,Perl,UNIX等等，一種常見的錯(cuò)誤設(shè)計(jì)是：設(shè)計(jì)一張員工表，其中有一個(gè)技能屬性字段，然后某員工所掌握的多種技能用逗號(hào)（，）間隔，然后將這個(gè)字符串存儲(chǔ)到這個(gè)員工表的技能屬性字段中。這里的錯(cuò)誤在于將多個(gè)屬性值作為一個(gè)屬性值存儲(chǔ)在一個(gè)字段中，不能滿足直接遍歷員工對(duì)某個(gè)技能掌握情況，而且如果再要求說明員工對(duì)個(gè)技能的掌握程度（精通，熟悉，一般等等），則再增加字段，里面的對(duì)應(yīng)關(guān)系將很容易錯(cuò)亂，這是嚴(yán)重違反1NF的情況。正確的設(shè)計(jì)應(yīng)該是：兩個(gè)實(shí)體表：一張是員工表，一張是技能字典表，一個(gè)員工可以掌握多個(gè)技能，也就是（1:N）關(guān)系，相反一個(gè)技能可以被多個(gè)員工掌握，也是（1:N）關(guān)系，雙向都是（1:N）關(guān)系，那么綜合起來員工和技能之間就是“多對(duì)多關(guān)系（N:M）”，依據(jù)前述規(guī)范，應(yīng)該設(shè)計(jì)一張“關(guān)系表”來存儲(chǔ)“多對(duì)多關(guān)系”，主鍵為復(fù)合主鍵（員工主鍵+技能主鍵），該關(guān)系有一個(gè)屬性“技能掌握程度”。2.4范式化2NF的規(guī)范OLTP系統(tǒng)的模型，需要符合第三范式。對(duì)于表在20個(gè)以上的模型，需要DBA參與設(shè)計(jì)。范式化要求（2NF）：滿足1NF，不存在非主鍵屬性對(duì)主鍵屬性的部分依賴。實(shí)體表中一般不會(huì)出現(xiàn)違反2NF的情況，因?yàn)槎际恰耙粋€(gè)”主鍵列，而關(guān)系表是兩個(gè)以上列的“復(fù)合”主鍵，故而關(guān)系表容易出現(xiàn)違反2NF的情況。主要是該關(guān)系表非主鍵外的屬性，本該屬于相關(guān)的某個(gè)實(shí)體表的，卻放到了該關(guān)系表中。這使得該屬性不能通過該關(guān)系表的復(fù)合主鍵唯一確定，DML操作會(huì)發(fā)生錯(cuò)誤。如果違反了2NF，那么應(yīng)該把這個(gè)屬性從關(guān)系表中拆分，也許會(huì)單獨(dú)形成一個(gè)表，絕大部分情況下是將該屬性歸并到某個(gè)相關(guān)的實(shí)體表中。違反2NF的例子：學(xué)生考試情況中，有兩個(gè)實(shí)體表：學(xué)生表和學(xué)科表，學(xué)生與學(xué)科之間的考試關(guān)系就是N:M的關(guān)系，就要?jiǎng)?chuàng)建一張關(guān)系表存儲(chǔ)該多對(duì)多的考試關(guān)系，表的主鍵為學(xué)生編號(hào)和學(xué)科編號(hào)，屬性為考試分?jǐn)?shù)；那么“任課老師”該放在那里呢？如果放到考試關(guān)系表中，那么安排任課老師，必須先進(jìn)行考試，這顯然不符合實(shí)際，也就是任課老師不該依賴于學(xué)生編號(hào)和學(xué)科編號(hào)，只是依賴于學(xué)科編號(hào)，也就是說任課教師信息應(yīng)該放在學(xué)科表中。2.5范式化3NF的規(guī)范OLTP系統(tǒng)的模型，需要符合第三范式。對(duì)于表在20個(gè)以上的模型，需要DBA參與設(shè)計(jì)。范式化要求（3NF）：滿足2NF，不存在非主鍵屬性對(duì)主鍵屬性的傳遞依賴；違反3NF的情況，絕大多數(shù)是在含有外鍵的表中。比如A表中的外鍵字段Bkey是B的主鍵，那么依賴于Bkey的屬性應(yīng)當(dāng)屬于B表的屬性，而不是A表，如果放入A表，則這些對(duì)A表的主鍵Akey的依賴，首先是依賴于A（BKey），而后通過A(BKey)對(duì)A（AKey）的依賴，傳遞依賴于A(Akey)；三種關(guān)系（1:1,1:N，N:M）都含有外鍵，都很可能發(fā)生違反3NF的情況。違反3NF的后果，會(huì)導(dǎo)致那些問題屬性插入異常，或者被誤刪。違反3NF的例子：教師和學(xué)科之間，存在著上課關(guān)系，假設(shè)一個(gè)教師上一門課而且一門課只有一個(gè)教師上，那么該關(guān)系為1:1關(guān)系，將教師表的主鍵教師編號(hào)在學(xué)科表中以外鍵形式存在就表達(dá)了該1:1關(guān)系，那么教師的“聯(lián)系電話”屬性該放哪里呢？如果看到“教師編號(hào)”出現(xiàn)在了學(xué)科表中，就將聯(lián)系電話放入學(xué)科表中，那么聯(lián)系電話首先是對(duì)表中的教師編號(hào)依賴，再依據(jù)教師編號(hào)對(duì)學(xué)科的依賴，達(dá)到了學(xué)科編號(hào)的依賴，那么聯(lián)系電話對(duì)學(xué)科編號(hào)的依賴就是傳遞依賴，違反了3NF。應(yīng)該將其從學(xué)科表中拆出來放入教師表中，不然的話，會(huì)發(fā)生操作異常，比如，假設(shè)一個(gè)教師已經(jīng)存在但是還沒有為其分配科目，那么他的電話就無法存入庫中。

2.5反范式化冗余字段使用規(guī)范OLTP系統(tǒng)中在完成范式化工作之后，對(duì)某些表，可以適當(dāng)反范式化增加冗余字段以提高數(shù)據(jù)訪問性能；在OLAP中采用的是面向問題的設(shè)計(jì)思想，應(yīng)該大量使用反范式化冗余信息。當(dāng)SQL關(guān)連查詢涉及到4張表時(shí)可考慮采用冗余字段。常用在兩個(gè)地方：（1）關(guān)系表中的冗余：在關(guān)系表中增加相關(guān)實(shí)體表的相關(guān)屬性，以達(dá)到關(guān)連查詢時(shí)減少表的關(guān)聯(lián)數(shù)量的目的（2）層次關(guān)系中的冗余：在多層次的子父表關(guān)系中，將父表的屬性存儲(chǔ)在“子表”或者“孫子表”或者“重孫表”中。反范式化冗余字段實(shí)例：（1）關(guān)系表中的冗余：比如在考試關(guān)系中，原本在學(xué)科表中的學(xué)分信息，可以冗余添加到考試關(guān)系表中，這樣，每個(gè)學(xué)生得了多少學(xué)分，就可以直接從考試表得到，而無需關(guān)聯(lián)學(xué)科表來得到。（2）多層關(guān)系中的冗余：假設(shè)為之范疇從大到小有國家表，省份表，城市表，城區(qū)表，社區(qū)表，它們之間的層次關(guān)系是通過上一級(jí)的主鍵在下一級(jí)中以外鍵形式存在來體現(xiàn)的，但是，如果需要問：某個(gè)設(shè)計(jì)屬于哪個(gè)國家？這樣就要關(guān)連查詢所有的5張表，性能會(huì)很差。這時(shí)可以將國家編號(hào)以外鍵形式放入到社區(qū)表中做冗余，這樣直接關(guān)聯(lián)國家表和社區(qū)表即可得到答案。一般的，每間隔一級(jí)增加一個(gè)冗余外鍵，比如將國家編號(hào)放入城市表中，將城市編號(hào)放入社區(qū)表中。如何保證冗余字段數(shù)據(jù)的正確性（一致性）是反范式化的關(guān)鍵，需要對(duì)冗余字段詳細(xì)添加注釋，說明冗余了什么，以及該字段的維護(hù)方法，常用維護(hù)方法如下：n

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

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

對(duì)于OLAP中大量存在冗余字段，可能需要使用單獨(dú)的處理任務(wù)進(jìn)行維護(hù)。2.6數(shù)據(jù)庫對(duì)象命名基本規(guī)范2.6.1遵循行業(yè)規(guī)范當(dāng)有相關(guān)國家/行業(yè)強(qiáng)制性數(shù)據(jù)結(jié)構(gòu)標(biāo)準(zhǔn)規(guī)范存在時(shí)，用于存儲(chǔ)某業(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ī)范上對(duì)數(shù)據(jù)項(xiàng)的類型、長(zhǎng)度有規(guī)定的，原則上也應(yīng)當(dāng)遵循或保證能直接兼容保存和訪問。2.6.2簡(jiǎn)單命名原則命名盡可能簡(jiǎn)單，避免太長(zhǎng)的命名，盡量使用縮寫形式，但是縮寫也要能夠表達(dá)命名的含義。凡是需要命名的對(duì)象其標(biāo)識(shí)符均不能超過30個(gè)字符，也即：Oracle中的表名、字段名，函數(shù)名，過程名，觸發(fā)器名，序列名，視圖名的長(zhǎng)度均不能超過30個(gè)字符，以免超過數(shù)據(jù)庫命名長(zhǎng)度限制（Oracle有30的限制）。建議每個(gè)單詞分段長(zhǎng)度不要超過6位。2.6.3字符范圍原則數(shù)據(jù)庫各種名稱必須以字母開頭，但嚴(yán)禁使用SYS開頭；名稱只能含有字母，數(shù)字和下劃線“_”三類字符，“_”用于間隔名稱中的各語義字段，以便閱讀同時(shí)方便某些工具對(duì)數(shù)據(jù)庫對(duì)象的映射。如XXX_XXX_XXX，但不限于三段式。2.6.4字母全部大寫或小寫原則所有數(shù)據(jù)庫對(duì)象命名字母全部大寫或小寫。Oracle對(duì)大小寫不敏感，但是有些數(shù)據(jù)庫對(duì)大小寫敏感，統(tǒng)一大小寫有助于在多個(gè)數(shù)據(jù)庫間移植。2.6.5勿用保留詞原則數(shù)據(jù)庫對(duì)象命名不能直接使用數(shù)據(jù)庫保留關(guān)鍵字，但分段中可以使用。如USER不能用于表名、列名等，但是USER_NAME可以用于列名，USER_INFO也可以用于表名。2.6.5同義性原則對(duì)于同一含義盡量使用相同的單詞命名，不管使用英文單詞還是英文縮寫，以免引起誤解。如TELEPNHOE的A表中表示固定電話號(hào)碼，在B表中就不應(yīng)該用于表示移動(dòng)電話號(hào)碼。盡量避免同一單詞表示多種含義的情況。2.6.6富有含義原則命名盡量采用富有意義的英文詞匯，不準(zhǔn)采用漢語拼音。2.6.7擴(kuò)展性原則各系統(tǒng)或者項(xiàng)目在遵循本規(guī)范的基礎(chǔ)上可以根據(jù)需要制定更明確的規(guī)范細(xì)則，以滿足項(xiàng)目管理需要。如對(duì)模塊進(jìn)行統(tǒng)一命名，然后用于表名的前綴。建議每個(gè)系統(tǒng)在啟動(dòng)開發(fā)時(shí)建立數(shù)據(jù)字典，管理命名中使用的英文單詞、英文單詞縮寫等，對(duì)用于命名的單詞進(jìn)行統(tǒng)一管理。

3

表的設(shè)計(jì)規(guī)范3.1命名規(guī)范3.1.1表的命名規(guī)范命名規(guī)則：3位類別碼_模塊名_表名_附加碼，采用大寫字符。類別碼：一般表TBL、臨時(shí)表TMP、中間表CVT、刪除表DEL、歷史表HIS、配置表CFG，接口表INT，一般表的3位類別碼可以省略，其他類型表的類別碼必填。模塊名：模塊名代表子系統(tǒng)（或者子模塊）的名稱，如：保單相關(guān)表PLC；訂單相關(guān)SLS；基礎(chǔ)數(shù)據(jù)：TYP。表名：表名應(yīng)該簡(jiǎn)潔明了，盡量使用完整的單詞，如果導(dǎo)致拼上表名后，長(zhǎng)度超過30個(gè)字符，則從最后一個(gè)單詞開始，依次向前采用該單詞的縮寫。（如果沒有約定的縮寫，則采用該單詞前4個(gè)字母來表示）。另外，表名中的名詞單詞都應(yīng)使用單數(shù)形式，以免混淆，如：使用FACTORY而非FACTORIES。附加碼：為可選項(xiàng)，各系統(tǒng)根據(jù)實(shí)際情況自行編碼，如：可以用以標(biāo)記臨時(shí)表的生成及數(shù)據(jù)存放日期YYMMDD。3.1.2字段的命名規(guī)范命名規(guī)則：英文單詞之間用下劃線連結(jié)，且每個(gè)單詞皆為單數(shù).例：user_name，采用小寫字符。n

字段用來存儲(chǔ)sequence序列，命名以id結(jié)尾。例：bar_code_id。n

字段用來存儲(chǔ)號(hào)碼，命名以no結(jié)尾。例：policy_no。n

字段用來存儲(chǔ)日期，命名以date結(jié)尾。例：create_date。n

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

字段用來存儲(chǔ)金額，命名以amt結(jié)尾。例：prem_amt。n

字段用來存儲(chǔ)名稱，命名以name結(jié)尾。例：client_name。n

字段用來存儲(chǔ)描述信息，命名以desc結(jié)尾。例：bank_desc。n

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

字段用來存儲(chǔ)標(biāo)志信息，命名以flag結(jié)尾。例：underwrit_flag。n

字段用來存儲(chǔ)英文名稱和英文描述，命名以en結(jié)尾。例：address_en。3.2表的設(shè)計(jì)規(guī)范3.2.1指定表空間規(guī)范每個(gè)表在創(chuàng)建時(shí)候，必須指定所在的表空間，不要采用默認(rèn)表空間，以防止表建立在system空間上，導(dǎo)致性能問題。對(duì)于事務(wù)比較繁忙的數(shù)據(jù)表，必須存放在在該表專用空間中。3.2.2表的主鍵規(guī)范表的主鍵設(shè)計(jì)，應(yīng)該遵循如下三點(diǎn)原則：n

有無原則除臨時(shí)表和外部表，以及流水表，日志表外，其他表都要建立主鍵。主鍵是每行數(shù)據(jù)的唯一標(biāo)識(shí)，保證主鍵不可隨意更新修改，在不知道是否需要主鍵的時(shí)候，請(qǐng)加上主鍵，它會(huì)為你的程序以及將來查找數(shù)據(jù)中的錯(cuò)誤等等，提供一定的幫助。n

構(gòu)成原則主鍵不能使用含有實(shí)際語義的列，應(yīng)該增加一個(gè)xx_id字段做主鍵，類型為number，取值來自序列sequence；n

創(chuàng)建原則對(duì)于500萬以上的表，采用先建唯一索引再添加主鍵約束的方式來創(chuàng)建主鍵。對(duì)于實(shí)體表，主鍵就是一列，就是沒有任何語義的自增的NUMBER列；對(duì)于關(guān)系表，主鍵就是相關(guān)實(shí)體表主鍵形成的復(fù)合主鍵，是多列。3.2.3表的外鍵規(guī)范一個(gè)表的某列與另一表有關(guān)聯(lián)關(guān)系的時(shí)候，如果加得上的話，請(qǐng)加上外鍵約束。外鍵是很重要的，所以要特別強(qiáng)調(diào)。n

適量建立外鍵為了保證外鍵的一致性，數(shù)據(jù)庫會(huì)增加一些開銷，如果有確鑿的并且是對(duì)性能影響到無法滿足用戶需求的證據(jù)，可以考慮不建外鍵。否則，還是應(yīng)該建外鍵。n

不要以數(shù)據(jù)操作不方便為理由而不建外鍵是的，加上外鍵以后，一些數(shù)據(jù)操作變得有些麻煩，但是這正是對(duì)數(shù)據(jù)一致性的保護(hù)。正是因?yàn)檫@種保護(hù)很有效，所以最好不要拒絕它。n

以缺省的方式建立外鍵以缺省的方式建立外鍵（即用deleterestrict方式），以達(dá)到保護(hù)數(shù)據(jù)一致性的目的；外鍵在保護(hù)數(shù)據(jù)一致方面非常有效。如果不建外鍵，數(shù)據(jù)庫中容易出現(xiàn)垃圾數(shù)據(jù)，并且無人知曉。當(dāng)數(shù)據(jù)量很大的時(shí)候，查找這些垃圾數(shù)據(jù)也是相當(dāng)困難的。而應(yīng)用程序在設(shè)計(jì)時(shí)，往往沒有考慮或者也無法照顧到垃圾數(shù)據(jù)。因此垃圾數(shù)據(jù)很可能造成應(yīng)用程序工作不正常，并且表現(xiàn)出來的現(xiàn)象會(huì)很奇怪，讓人摸不著頭腦。3.2.4字段類型及寬度的規(guī)范字段的寬度要在一定時(shí)間內(nèi)足夠用，但也不要過寬，占用過多的存儲(chǔ)空間,對(duì)于長(zhǎng)度不確定的列，采用可變長(zhǎng)度的數(shù)據(jù)類型如varchar類型；字段的類型及寬度在設(shè)計(jì)以及后面進(jìn)行開發(fā)時(shí)，往往要與應(yīng)用的設(shè)計(jì)、開發(fā)人員商討，以得到雙方認(rèn)可的類型及寬度；3.2.5一個(gè)表所含字段總長(zhǎng)度的規(guī)范一個(gè)表中的所有字段，應(yīng)當(dāng)能存儲(chǔ)在一個(gè)數(shù)據(jù)塊中（BLOCK），也即：表的單行字段總長(zhǎng)度<db_block(減去pctfree)。對(duì)不含有大對(duì)象數(shù)據(jù)類型字段的表，字段數(shù)大于50個(gè)的，請(qǐng)DBA團(tuán)隊(duì)參與設(shè)計(jì)。查詢字典表USER_TAB_COLUMNS中的字段DATA_LENGTH得到表中所有字段的總長(zhǎng)度，再依據(jù)db_block和表的pctfree參數(shù)可以判斷是否一個(gè)數(shù)據(jù)行可以存儲(chǔ)在一個(gè)數(shù)據(jù)塊(BLOCK)中。對(duì)表添如果所有字段的總長(zhǎng)度超出了一個(gè)數(shù)據(jù)塊，那么需要將該表拆分成兩個(gè)（甚至多個(gè)）表，拆分的依據(jù)是字段的頻繁使用程度，也就是頻繁使用的字段在一個(gè)表中，很少被使用的字段放在另一個(gè)表中，他們之間使用相同的主鍵值，用主外鍵關(guān)聯(lián)。這點(diǎn)就是“一個(gè)表所含字段訪問頻繁度的規(guī)范”。3.2.6一個(gè)表所含字段訪問頻繁度的規(guī)范一個(gè)表中的各字段的訪問頻繁度應(yīng)該基本一致，如果一個(gè)表的字段數(shù)超過50個(gè)，請(qǐng)DBA參與審核。如果一個(gè)表的字段數(shù)過多超過50個(gè)，并且依據(jù)業(yè)務(wù)邏輯確定該表中一些字段頻繁被訪問，另一些字段則很少被訪問，則該表需要做拆分處理，這樣可以避免讀取頻繁信息時(shí)多讀取很少被訪問的信息，可以提高IO性能，減少內(nèi)存耗費(fèi)，這在OLAP系統(tǒng)中比較常見。將訪問頻繁度相差太遠(yuǎn)的字段拆分到兩個(gè)表中，一個(gè)表存頻繁訪問的字段，另一個(gè)表存很少被訪問的字段。3.2.7大對(duì)象字段（BLOB，CLOB）使用規(guī)范存儲(chǔ)圖片，視頻，音頻，文件，500字節(jié)以上文本等占用太多空間的字段（大對(duì)象字段），不能和其他字段存儲(chǔ)在一個(gè)表中。含有大對(duì)象（BLOB，CLOB）字段的表設(shè)計(jì)和存儲(chǔ)請(qǐng)DBA參與設(shè)計(jì)。一般有兩種方法：n

數(shù)據(jù)庫存儲(chǔ)可以重新建一個(gè)表專門存儲(chǔ)該大對(duì)象字段，該表基本為兩個(gè)字段，一個(gè)為大對(duì)象編號(hào)ID為主鍵，一個(gè)為大對(duì)象內(nèi)容本身，并將該主鍵在原表中作外鍵關(guān)聯(lián)，該大對(duì)象表存儲(chǔ)在單獨(dú)的表空間中。n

操作系統(tǒng)存儲(chǔ)將這些文件存儲(chǔ)在操作系統(tǒng)空間中，大對(duì)象字段存儲(chǔ)該文件的全路徑名。如果該大對(duì)象字段常被修改，那么采用方法一；如果該大對(duì)象信息為靜態(tài)，加載后基本不變，那么可以采用方法二，它有一個(gè)致命缺點(diǎn)就是信息存儲(chǔ)在數(shù)據(jù)庫外部，不安全，容易丟失。3.2.8關(guān)于字段能否為NULL值對(duì)于字段能否為null，應(yīng)該在sql建表腳本中明確指定，不應(yīng)該使用缺省。由于null值在參加任何計(jì)算時(shí)，結(jié)果均為null，所以在程序中必須用nvl()函數(shù)把可能為null值的字段或變量轉(zhuǎn)換非null的默認(rèn)值。3.2.9關(guān)于冗余列的規(guī)范除非必要，否則盡量不加冗余列。所謂冗余列，是指能通過其他列計(jì)算出來的列，或者是與某列表達(dá)同一含義的列，或者是從其他表復(fù)制過來的列等等。冗余列需要應(yīng)用程序來維護(hù)一致性，相關(guān)列的值改變的時(shí)候，冗余列也需要隨之修改，而這一規(guī)則未必所有人都知道，就有可能因此發(fā)生不一致的情況。如果是應(yīng)用的特殊需要，或者是為了優(yōu)化某些邏輯很復(fù)雜的查詢等操作，可以加冗余列。3.2.10使用注釋的規(guī)范每個(gè)表，每個(gè)字段都要有注釋，說明其含義，對(duì)于冗余字段還要特別說明其維護(hù)方法，外鍵字段說明參照與那個(gè)表。原則上誰設(shè)計(jì)誰注釋。查詢字典表user_tab_comments和user_col_comments可知道表和字段的注釋信息。對(duì)表添加注釋：SQL>commenton

table

<table_name>is'xx';對(duì)字段添加注釋：SQL>commentoncolumn<table_name>.<col_name>is'xx';3.2.11一個(gè)表所含數(shù)據(jù)量的規(guī)范一個(gè)非分區(qū)表中的數(shù)據(jù)量不要超過500萬。當(dāng)一個(gè)非分區(qū)表中的數(shù)據(jù)量超過500萬時(shí)，需設(shè)計(jì)成分區(qū)表；如果該表數(shù)據(jù)量超過5000萬，請(qǐng)DBA參與設(shè)計(jì)。在系統(tǒng)上線前，通過對(duì)業(yè)務(wù)分析，判斷一個(gè)表的數(shù)據(jù)量；在系統(tǒng)上線后，可以通過exp的日志，Top性能SQL，count(1)來發(fā)現(xiàn)數(shù)據(jù)量大的表。將這些表進(jìn)行分區(qū)，具體方法請(qǐng)參看分區(qū)表的設(shè)計(jì)規(guī)范。

記錄數(shù)超過兩億條的表一定要考慮信息生命周期，必須考慮歷史數(shù)據(jù)的剝離，并在應(yīng)用設(shè)計(jì)中完成對(duì)歷史數(shù)據(jù)的相應(yīng)處理功能（歷史數(shù)據(jù)的剝離規(guī)則須經(jīng)業(yè)務(wù)使用部門的確認(rèn)）。3.2.12增量同步表的設(shè)計(jì)規(guī)范字典信息表和需要使用增量同步的表必須增加如下屬性。屬性名類型取值說明StatusChar(1)Y/N：Y為激活N為作廢，默認(rèn)為Y標(biāo)識(shí)該行是否使用。用于軟刪除，軟刪除需將主鍵和唯一約束列添加隨機(jī)數(shù)后綴。Create_timeDate默認(rèn)為sysdate創(chuàng)建時(shí)間Update_timeDate默認(rèn)為sysdate最后修改時(shí)間3.3字段類型規(guī)范3.3.1不使用會(huì)發(fā)生隱式轉(zhuǎn)換：INTEGER，F(xiàn)LOATINTEGER改為NUMBER(n)FLOAT改為NUMBER(p,s)3.3.2不使用過時(shí)老類型：RAW，LONG，LONGRAWl非標(biāo)準(zhǔn)：VARCHAR2(nCHAR)、CHAR(nCHAR)VARCHAR2(nCHAR)改為VARCHAR2(n)CHAR(nCHAR)改為CHAR(n)3.3.3國家字符集相關(guān)l國家字符集相關(guān)：NCHAR，NVARCHAR2，NCLOBNCHAR改為CHARNVARCHAR2改為VARCHAR2NCLOB改為CLOB3.3.4

不能使用大對(duì)象：BLOB，CLOB，NCLOBl不能使用大對(duì)象：BLOB，CLOB，NCLOBCLOB和NCLOB改為VARCHAR23.3.5不能使用高精度：TIMESTAMPl不能使用高精度：TIMESTAMPTIMESTAMP改為DATE3.3.6關(guān)于CHAR字段CHAR字段類型長(zhǎng)度小于100，長(zhǎng)度大于100的字符型信息應(yīng)該使用VARCHAR2字段類型來存儲(chǔ)。4

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

表空間及分區(qū)表的概念4.1.1表空間是一個(gè)或多個(gè)數(shù)據(jù)文件的集合，所有的數(shù)據(jù)對(duì)象都存放在指定的表空間中，但主要存放的是表，所以稱作表空間。4.1.2分區(qū)表當(dāng)表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會(huì)變慢，應(yīng)用程序的性能就會(huì)下降，這時(shí)就應(yīng)該考慮對(duì)表進(jìn)行分區(qū)。表進(jìn)行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個(gè)“表空間”(物理文件上)，這樣查詢數(shù)據(jù)時(shí)，不至于每次都掃描整張表而只是從當(dāng)前的分區(qū)查到所要的數(shù)據(jù)大提高了數(shù)據(jù)查詢的速度。4.2

表分區(qū)的具體作用Oracle的表分區(qū)功能通過改善可管理性、性能和可用性，從而為各式應(yīng)用程序帶來了極大的好處。通常，分區(qū)可以使某些查詢以及維護(hù)操作的性能大大提高。此外,分區(qū)還可以極大簡(jiǎn)化常見的管理任務(wù)，分區(qū)是構(gòu)建千兆字節(jié)數(shù)據(jù)系統(tǒng)或超高可用性系統(tǒng)的關(guān)鍵工具。分區(qū)功能能夠?qū)⒈怼⑺饕蛩饕M織表進(jìn)一步細(xì)分為段，這些數(shù)據(jù)庫對(duì)象的段叫做分區(qū)。每個(gè)分區(qū)有自己的名稱，還可以選擇自己的存儲(chǔ)特性。從數(shù)據(jù)庫管理員的角度來看，一個(gè)分區(qū)后的對(duì)象具有多個(gè)段，這些段既可進(jìn)行集體管理，也可單獨(dú)管理，這就使數(shù)據(jù)庫管理員在管理分區(qū)后的對(duì)象時(shí)有相當(dāng)大的靈活性。但是，從應(yīng)用程序的角度來看，分區(qū)后的表與非分區(qū)表完全相同，使用SQLDML命令訪問分區(qū)后的表時(shí)，無需任何修改。

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

1、表的大小超過2GB，數(shù)據(jù)量超過500萬；2、表中包含歷史數(shù)據(jù)，新的數(shù)據(jù)被增加都新的分區(qū)中。4.3表分區(qū)的優(yōu)缺點(diǎn)表分區(qū)有以下優(yōu)點(diǎn)：n

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

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

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

均衡I/O：可以把不同的分區(qū)映射到磁盤以平衡I/O，改善整個(gè)系統(tǒng)性能。缺點(diǎn)：n

已經(jīng)存在的表沒有方法可以直接轉(zhuǎn)化為分區(qū)表。不過，Oracle提供了在線重定義表的功能。4.4分區(qū)表設(shè)計(jì)規(guī)范4.2.1不使用全局索引在分區(qū)表中不建議使用全局索引，因?yàn)閠runc分區(qū)時(shí)會(huì)導(dǎo)致全局索引失效，造成難以維護(hù)。4.2.2RANGE分區(qū)的規(guī)范大數(shù)據(jù)量的表需進(jìn)行分區(qū)化，當(dāng)表的數(shù)據(jù)量超過500萬，需設(shè)計(jì)成分區(qū)表，當(dāng)表的數(shù)據(jù)量超過5000萬，請(qǐng)DBA參與設(shè)計(jì)。SQL常依據(jù)某列的范圍訪問表，則對(duì)表使用RNAGE分區(qū)。常見情況是SQL根據(jù)時(shí)間范圍進(jìn)行查新，則使用時(shí)間字段作為分區(qū)關(guān)鍵字進(jìn)行RANGE分區(qū)；將對(duì)表的多種訪問結(jié)合考慮來確定分區(qū)的細(xì)度：n

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

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

數(shù)據(jù)訪問的性能，以操作范圍確定的分區(qū)數(shù)據(jù)量還是過大，比如大于500萬，則還需要進(jìn)行細(xì)分；n

一個(gè)分區(qū)的數(shù)據(jù)量要小于500萬，這是一個(gè)硬性的尺度，但從技術(shù)上來看，每個(gè)分區(qū)10萬數(shù)據(jù)量的情況比每個(gè)分區(qū)20萬數(shù)據(jù)量的情況要快很多，所以需要靈活掌握。1.

當(dāng)各個(gè)分區(qū)中的數(shù)據(jù)能均等劃分時(shí)性能最好，如果相差太大，則考慮采用其它分區(qū)，或者將大數(shù)據(jù)量的分區(qū)再進(jìn)行HASH子分區(qū)；2.

各分區(qū)采用各自的表空間存儲(chǔ)，使用user_tab_partitions字典來查看確定每個(gè)分區(qū)的表空間位置；3.

分區(qū)表的索引采用本地索引，因?yàn)槌?huì)根據(jù)分區(qū)關(guān)鍵字（比如時(shí)間）進(jìn)行分區(qū)維護(hù)（比如刪除1年前的數(shù)據(jù)，也就是刪除1年前的分區(qū)），分區(qū)維護(hù)時(shí)全局索引會(huì)失效，而本地索引不會(huì)失效，這能保證訪問表時(shí)索引正?？捎?。4.2.3LIST分區(qū)的規(guī)范大數(shù)據(jù)量的表需進(jìn)行分區(qū)化，當(dāng)表的數(shù)據(jù)量超過500萬，需設(shè)計(jì)成分區(qū)表，當(dāng)表的數(shù)據(jù)量超過5000萬，請(qǐng)DBA參與設(shè)計(jì)。SQL常居于某列的散列值訪問表，則對(duì)表使用LIST分區(qū)，LIST分區(qū)不支持多列分區(qū)關(guān)鍵字；常見情況針對(duì)某個(gè)地區(qū)或者某個(gè)業(yè)務(wù)進(jìn)行數(shù)據(jù)訪問，那么就使用地區(qū)編號(hào)或者業(yè)務(wù)編號(hào)作為分區(qū)關(guān)鍵字。將對(duì)表的多種訪問結(jié)合考慮來確定分區(qū)的細(xì)度：n

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

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

如果一個(gè)分區(qū)關(guān)鍵字值所對(duì)應(yīng)的數(shù)據(jù)量過大，比如大于500萬，則應(yīng)該對(duì)表采用RANGE分區(qū)，對(duì)該值的分區(qū)再采用HASH子分區(qū)；也就是說，一個(gè)可以采用LIST分區(qū)的表，肯定可以轉(zhuǎn)化成RANGE分區(qū)（可帶子分區(qū)），反之不然；n

一個(gè)分區(qū)的數(shù)據(jù)量要小于500萬，這是一個(gè)硬性的尺度，但從技術(shù)上來看，每個(gè)分區(qū)10萬數(shù)據(jù)量分區(qū)方法比每個(gè)分區(qū)20萬數(shù)據(jù)量的分區(qū)方法要快很多，所以需要靈活掌握。1.

各分區(qū)采用各自的表空間存儲(chǔ)，使用user_tab_partitions字典來確定每個(gè)分區(qū)的表空間；2.

分區(qū)表的索引采用本地索引。4.2.4HASH分區(qū)的規(guī)范大數(shù)據(jù)量的表需進(jìn)行分區(qū)化，當(dāng)表的數(shù)據(jù)量超過500萬，需設(shè)計(jì)成分區(qū)表，當(dāng)表的數(shù)據(jù)量超過5000萬，請(qǐng)DBA參與設(shè)計(jì)。SQL訪問表不按照某列的范圍進(jìn)行，也不按某列離散值進(jìn)行，而且對(duì)該表的數(shù)據(jù)不會(huì)依據(jù)某列的值范圍或者離散值進(jìn)行定期維護(hù)，那么使用HASH分區(qū)；HASH分區(qū)是不知道應(yīng)該選擇何種分區(qū)時(shí)的選擇；HASH分區(qū)的各分區(qū)都可能存有各種情況的數(shù)據(jù)，故而不能用于依據(jù)分區(qū)清理數(shù)據(jù)的情況。對(duì)確定分區(qū)細(xì)度的考慮：n

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

一個(gè)分區(qū)的數(shù)據(jù)量要小于500萬，這是一個(gè)硬性的尺度，但從技術(shù)上來看，每個(gè)分區(qū)10萬數(shù)據(jù)量分區(qū)方法比每個(gè)分區(qū)20萬數(shù)據(jù)量的分區(qū)方法要快很多，所以需要靈活掌握。1.

各分區(qū)采用各自的表空間存儲(chǔ)，使用user_tab_partitions字典來確定每個(gè)分區(qū)的表空間；2.

對(duì)于HASH分區(qū)表，大多數(shù)情況下依然要求采用本地索引，但是如果分區(qū)過細(xì)，也可以采用全局索引，因?yàn)楦鶕?jù)HASH分區(qū)表的特征（各分區(qū)無業(yè)務(wù)區(qū)分，都有數(shù)據(jù)），該表很少會(huì)發(fā)生分區(qū)維護(hù)的工作。4.2.5RANGE-LIST分區(qū)的規(guī)范大數(shù)據(jù)量的表需進(jìn)行分區(qū)化，當(dāng)表的數(shù)據(jù)量超過500萬，需設(shè)計(jì)成分區(qū)表，當(dāng)表的數(shù)據(jù)量超過5000萬，請(qǐng)DBA參與設(shè)計(jì)。SQL訪問表時(shí)，既依據(jù)某列值的范圍，又依據(jù)其他列的離散值或者范圍，這種情況下采用RANGE-LIST復(fù)合分區(qū)，常用于語表中的數(shù)據(jù)需要依據(jù)一個(gè)時(shí)間字段做周期性刪除等維護(hù)，并且正常業(yè)務(wù)SQL訪問既依據(jù)時(shí)間字段，又依據(jù)其他字段的散列值進(jìn)行訪問的情況。比如：電信增值業(yè)務(wù)計(jì)費(fèi)表，既有時(shí)間又有業(yè)務(wù)屬性列，統(tǒng)計(jì)的時(shí)候，會(huì)選擇時(shí)間范圍和業(yè)務(wù)屬性，所以可以以時(shí)間列為分區(qū)關(guān)鍵字建立RANGE分區(qū)，以業(yè)務(wù)屬性列為關(guān)鍵字建立LIST子分區(qū)；分區(qū)劃分的方法：n

就按照大多數(shù)范圍訪問的范圍值來劃定RANGE分區(qū)的范圍，依據(jù)單個(gè)LIST子分區(qū)關(guān)鍵字的值來劃分子分區(qū);n

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

如果LIST子分區(qū)中一個(gè)子分區(qū)關(guān)鍵字值對(duì)應(yīng)的子分區(qū)數(shù)據(jù)量還是很大，超過500，影響性能，那么可以通過細(xì)分RANGE分區(qū)來達(dá)到減少LIST子分區(qū)數(shù)據(jù)量的目的，這點(diǎn)和LIST分區(qū)在該情況下的處理方法（轉(zhuǎn)化成RANGE-HASH）不同。1.

各子分區(qū)應(yīng)該盡量分散到不同的表空間中存儲(chǔ)，使用user_tab_subpartitions字典來確定每個(gè)子分區(qū)的表空間；2.

RANGE-LIST大多數(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è)計(jì)成分區(qū)表，當(dāng)表的數(shù)據(jù)量超過5000萬，請(qǐng)DBA參與設(shè)計(jì)。SQL訪問表時(shí)，主要依據(jù)某個(gè)列的范圍進(jìn)行訪問，即訪問特征符合RANGE分區(qū)的要求，或者數(shù)據(jù)維護(hù)特征符合RANGE分區(qū)的要求，但是以SQL或者維護(hù)的數(shù)據(jù)范圍來劃定分區(qū)，分區(qū)數(shù)據(jù)量又很大，對(duì)性能有影響，需再進(jìn)行子分區(qū)，由于分區(qū)中的數(shù)據(jù)都會(huì)被訪問到，所以子分區(qū)采用HASH方法，整個(gè)表就是RANGE-HASH分區(qū)；劃定分區(qū)的方法：先按照大多數(shù)范圍訪問的范圍值來劃定RANGE分區(qū)的范圍，再依據(jù)性能情況來確定HASH子分區(qū)的數(shù)據(jù)量。1.

各子分區(qū)應(yīng)該盡量分散到不同的表空間中存儲(chǔ)，使用user_tab_subpartitions字典來確定每個(gè)子分區(qū)的表空間；2.

RANGE-HASH大多數(shù)情況采用本地索引，因?yàn)槌８鶕?jù)RANGE分區(qū)關(guān)鍵字的來進(jìn)行分區(qū)維護(hù)。5

索引的設(shè)計(jì)規(guī)范5.1

索引分類Oracle中可以創(chuàng)建多種類型的索引，以適應(yīng)各種表的特點(diǎn)和各種查詢條件的特點(diǎn)。可以按列的多少、索引列是否唯一、索引數(shù)據(jù)的組織形式對(duì)索引進(jìn)行分類。5.1.1單列索引與復(fù)合索引一個(gè)索引可以由一個(gè)或多個(gè)列組成，用來創(chuàng)建索引的列被稱為“索引列”。

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

唯一索引與非唯一索引唯一索引是索引列值不能重復(fù)的索引，非唯一索引是索引列可以重復(fù)的索引。無論是唯一索引還是非唯一索引，索引列都允許取NULL值。默認(rèn)情況下，Oracle創(chuàng)建的索引是不唯一索引。5.1.3

B樹索引、位圖索引與函數(shù)索引B樹索引是按B樹算法組織并存放索引數(shù)據(jù)的，所以B樹索引主要依賴其組織并存放索引數(shù)據(jù)的算法來實(shí)現(xiàn)快速檢索功能。Oracle中不僅能夠直接對(duì)表中的列創(chuàng)建索引，還可以對(duì)包含列的函數(shù)或表達(dá)式創(chuàng)建索引，這種索引稱為“函數(shù)索引”。位圖索引在多列查詢時(shí)，可以對(duì)兩個(gè)列上的位圖進(jìn)行AND和OR操作，達(dá)到更好的查詢效果。5.2

命名規(guī)范命名規(guī)則：類別碼_表名_附加碼，采用大寫字符。類別碼：一般索引IDX、位圖索引BIDX、唯一索引UK、主鍵PK、外鍵FK，類別碼根據(jù)索引的性質(zhì)填寫。表名：表名應(yīng)該簡(jiǎn)潔明了，盡量使用完整的單詞，如果導(dǎo)致拼上表名后，長(zhǎng)度超過30個(gè)字符，則從最后一個(gè)單詞開始，依次向前采用該單詞的縮寫。（如果沒有約定的縮寫，則采用該單詞前4個(gè)字母來表示）。另外，表名中的名詞單詞都應(yīng)使用單數(shù)形式，以免混淆，如：使用FACTORY而非FACTORIES。附加碼：可以是序號(hào)，也可以是字段名，根據(jù)實(shí)際的使用情況進(jìn)行填寫。5.3

索引設(shè)計(jì)規(guī)范索引是從數(shù)據(jù)庫中獲取數(shù)據(jù)的最高效方式之一。95%的數(shù)據(jù)庫性能問題都可以采用索引技術(shù)得到解決。但大量的DML操作會(huì)增加系統(tǒng)對(duì)索引的維護(hù)成本，對(duì)性能會(huì)有一定影響，對(duì)于插入相當(dāng)頻繁的表要慎重建索引，索引也會(huì)占相當(dāng)?shù)拇鎯?chǔ)空間，所以要根據(jù)硬件環(huán)境和應(yīng)用需求在空間和時(shí)間上達(dá)到最好的平衡點(diǎn)。主要原則：n

適當(dāng)利用索引提高查詢速度：當(dāng)數(shù)據(jù)量比較大，了解應(yīng)用程序的會(huì)有哪些查詢，依據(jù)這些查詢需求建相應(yīng)的索引；最好親自試驗(yàn)一下，模擬一下生產(chǎn)環(huán)境的數(shù)據(jù)量，在此數(shù)據(jù)量下，比較一下建索引前后的查詢速度；索引對(duì)性能會(huì)有一定影響，對(duì)于DML頻繁列的索引要定期維護(hù)（重建）。但是，索引的結(jié)構(gòu)對(duì)于索引的更新（比如在插入數(shù)據(jù)的時(shí)候）是有一定優(yōu)化的，所以不要在沒有試驗(yàn)以前過分夸大它對(duì)性能的影響，最終還是以試驗(yàn)為準(zhǔn)。n

不要建實(shí)際用不上的索引，與上條相關(guān)，如果建的索引并不提高任何一應(yīng)用中的查詢速度，則要把它刪除；有些數(shù)據(jù)庫有相關(guān)工具可以發(fā)現(xiàn)實(shí)際未被使用的索引，可以利用一下。n

索引列的選擇：如果檢索條件有可能包含多列，創(chuàng)建聯(lián)合主鍵或者聯(lián)合索引，把最常用于檢索條件的列放在最前端，其他的列排在后面；不要索引使用頻繁的小型表，假如這些小表有頻繁的DML就更不要建立索引，維護(hù)索引的代價(jià)遠(yuǎn)遠(yuǎn)高于掃描表的代價(jià)；n

主鍵索引在建立的時(shí)候一定要明確的指定名稱，不能讓系統(tǒng)默認(rèn)建立主鍵索引（可能有些數(shù)據(jù)庫無法指定主鍵名，則例外）；n

當(dāng)有聯(lián)合主鍵或者聯(lián)合索引時(shí)，注意不要建重復(fù)的索引。舉例說明：表EMPLOYEES，它的主鍵是建立在列DEPARTID和EMPLOYEEID上的聯(lián)合主鍵，并且創(chuàng)建主鍵的語句中DEPARTID在前，EMPLOYEEID在后。在這樣一個(gè)表里，通常就沒有必要再為DEPARTID建一個(gè)索引了，聯(lián)合索引的情況也一樣。更復(fù)雜的情況，比如表EMPLOYEES，有一個(gè)索引建立在列CORPID,DEPARTID,EMPLOYEEID三列上，在創(chuàng)建語句中也依據(jù)上述順序，就沒有必要再為CORPID建立索引；也沒有必要再建立以CORPID在前，DEPARTID在后的聯(lián)合索引；如果EMPLOYEEID需要索引，那么為EMPLOYEEID建立一個(gè)索引是不與上面的索引重復(fù)的；DEPARTID列也類似n

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

指定表空間規(guī)范每個(gè)索引在創(chuàng)建時(shí)，必須指定表空間，不要采用默認(rèn)表空間，以防止索引建立在system空間和非索引專用空間，以減少IO沖突，提高性能。5.3.2主鍵索引的規(guī)范對(duì)數(shù)據(jù)量表應(yīng)該先在主鍵列建唯一索引，再建主鍵約束。分區(qū)表的主鍵必須采用該方法設(shè)計(jì)。原則上所有的數(shù)據(jù)表都要有主鍵。主鍵上隱含索引，drop或disable主鍵時(shí)，索引會(huì)丟失，為保證性能不變，為了對(duì)主鍵約束和相應(yīng)索引有更多的控制，對(duì)大表(分區(qū)表)的索引采用如下方式建立：1.在準(zhǔn)備建主鍵的列上建立唯一索引（UNIQUEINDEX）:CREATEUNIQUEINDEXIndex_NameONTable_Name(Column_Name)TABLESPACE

TBS_INDEX;2.再加上主鍵約束：ALTERTABLETable_NameADD(PRIMARYKEY(Column_Name)USINGINDEXTABLESPACETBS_INDEX);

Oracle會(huì)在指定的列上加上主鍵約束，并且使用該索引。分區(qū)表的主鍵默認(rèn)索引是全局索引，所以主鍵索引的分區(qū)方法：先建立分區(qū)化的唯一索引，再建主鍵約束。5.3.3唯一約束索引的規(guī)范針對(duì)大數(shù)據(jù)量表應(yīng)該先在唯一約束列上建立普通索引，再添加唯一性約束。分區(qū)表的唯一約束必須采用該方法。刪除或禁用唯一性約束通常同時(shí)使相關(guān)聯(lián)的唯一索引失效，因而降低了數(shù)據(jù)庫性能。要避免這樣問題，可以采取下面的步驟：（a）在唯一性約束的列上創(chuàng)建非唯一性索引（普通索引）；（b）添加唯一性約束。5.3.4外鍵列索引的規(guī)范對(duì)于關(guān)聯(lián)兩個(gè)表字段，一般應(yīng)該分別建立主鍵、外鍵。實(shí)際是否建立外鍵，根據(jù)對(duì)數(shù)據(jù)完整性的要求決定。為了提高性能，無論表的大小，外鍵都要建立索引，一是為了子父表關(guān)聯(lián)查詢的性能考慮，二是為了避免父子表修改而發(fā)生死鎖。對(duì)于有要求級(jí)聯(lián)刪除屬性的外鍵，必須指定ondeletecascade。普通表的外鍵列建立普通索引即可，如果表是分區(qū)表，則依據(jù)表的情況建立本地索引或者全局索引。5.3.5復(fù)合索引的規(guī)范復(fù)合索引只有在該種復(fù)合常被和該表相關(guān)的大多數(shù)SQL使用時(shí)才建立。復(fù)合索引的列數(shù)不能超過5個(gè)，否則該索引很少會(huì)被使用。n

復(fù)合索引的第一列，可以通過不使用該種復(fù)合的SQL來確定。假設(shè)一些SQL的WHERE中復(fù)合使用列為ABC，而其他一些SQL的WHERE中常使用的是C列，那么該復(fù)合索引可以按照CAB的順序建立，這樣上述兩種SQL都能使用該索引；n

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

切忌不能將表相關(guān)的所有SQL中WHERE涉及到的列復(fù)合起來建立復(fù)合索引。5.3.6函數(shù)索引的規(guī)范由于使用形式需和創(chuàng)建形式一致，盡量避免使用函數(shù)索引。如果想要使用函數(shù)索引，請(qǐng)盡量進(jìn)行轉(zhuǎn)化。由于函數(shù)索引在使用時(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(‘2010-07-07’,’yyyy-mm-dd’)。5.3.7位圖索引的規(guī)范靜態(tài)表中的低基數(shù)列可以使用位圖索引。在事務(wù)型數(shù)據(jù)庫（OLTP）中禁止使用位圖（bitmap）索引，在報(bào)表型數(shù)據(jù)庫（OLAP）中的靜態(tài)表，可以適當(dāng)使用。5.3.8反向索引的規(guī)范列值順序增加的列，其上的WHERE運(yùn)算是<>或者=而不是范圍（betweenand或者<and>）檢索時(shí)，可以采用反向函數(shù)。一般創(chuàng)建反向索引的列為NUMBER類型，值由SEQUENCE生成。5.3.9分區(qū)索引的規(guī)范對(duì)分區(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ù)量表的索引必須采用ALTERINDEXREBUILD方式重建。方法：ALTERINDEXIDX_NAMEREBUILD[TABLESPACETBSP_NAME]。6

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

命名規(guī)范n

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

存儲(chǔ)過程：SP_存儲(chǔ)過程名，用英文表達(dá)存儲(chǔ)過程意義；n

函數(shù)：FUN_函數(shù)名稱，用英文表達(dá)函數(shù)作用；n

觸發(fā)器：TR_觸發(fā)器名稱，用英文表達(dá)觸發(fā)器作用；n

包及包體：PKG_包或包體名稱，用英文表達(dá)包及包體的作用；n

序列：SEQ_序列名稱，用英文表達(dá)序列的意義；n

游標(biāo)：CUR_游標(biāo)名稱；n

自定義記錄類型：REC_自定義記錄類型名稱，用英文表達(dá)自定義記錄類型含義；n

自定義記錄類型變量：V_REC_自定義記錄類型變量名稱，用英文表達(dá)自定義記錄類型變量含義；n

自定義嵌套類型：TBL_自定義嵌套類型名稱，用英文表達(dá)自定義嵌套類型含義；n

自定義嵌套類型變量：V_TBL_自定義嵌套類型變量名稱，用英文表達(dá)自定義嵌套類型變量含義；n

輸入?yún)?shù)：I_輸入?yún)?shù)名稱，用英文表達(dá)輸入?yún)?shù)類型或含義；n

輸出參數(shù)：O_輸出參數(shù)名稱，用英文表達(dá)輸出參數(shù)類型或含義。6.2視圖設(shè)計(jì)規(guī)范6.2.1盡量使用簡(jiǎn)單的視圖，避免使用復(fù)雜的視圖簡(jiǎn)單視圖：數(shù)據(jù)來自單個(gè)表，且無分組(distinct/groupby)、無函數(shù)。復(fù)雜視圖：數(shù)據(jù)來自多個(gè)表，或有分組、有函數(shù)。6.2.2按照必要性原則建立視圖在不太清楚視圖用法的情況下，盡量不建。因?yàn)橐坏┙耍陀斜粸E用的危險(xiǎn)；如果需要建視圖，只要是打算長(zhǎng)期使用的，請(qǐng)寫入數(shù)據(jù)庫設(shè)計(jì)中，明確它的用途、目的。6.3存儲(chǔ)過程、函數(shù)、觸發(fā)器的設(shè)計(jì)規(guī)范請(qǐng)把程序包、存儲(chǔ)過程、函數(shù)、觸發(fā)器，與應(yīng)用程序一同加入CVS中，進(jìn)行版本控制。因?yàn)榇怂恼甙舜a，應(yīng)用程序?qū)λ麄兊囊蕾嚦潭缺葘?duì)表、視圖的依賴程度更高。適量但盡量少使用存儲(chǔ)過程、函數(shù)、觸發(fā)器。使用存儲(chǔ)過程、函數(shù)、觸發(fā)器的影響：n

可以減少數(shù)據(jù)庫與客戶端的交互，提高性能；n

有的數(shù)據(jù)庫還對(duì)他們進(jìn)行了某種程度的編譯，在執(zhí)行的時(shí)候，不用再對(duì)其中的SQL等語句進(jìn)行解析，從而提高速度；n

如果有多個(gè)應(yīng)用，使用了不同的開發(fā)語言，當(dāng)有某些關(guān)鍵的或者復(fù)雜邏輯希望共享，則可以考慮使用存儲(chǔ)過程或者函數(shù)。因?yàn)榇鎯?chǔ)過程等在數(shù)據(jù)庫一級(jí)是共享的；n

增強(qiáng)了應(yīng)用對(duì)數(shù)據(jù)庫的依賴，如果打算將來移植數(shù)據(jù)庫的話，使用得越多，則移植的困難越大；數(shù)據(jù)庫中的業(yè)務(wù)邏輯越多（存儲(chǔ)過程等），應(yīng)用以及存儲(chǔ)過程等的維護(hù)難度也會(huì)增大；n

通常存儲(chǔ)過程等沒有面向?qū)ο蟮奶匦?，不容易設(shè)計(jì)出易于擴(kuò)展的結(jié)構(gòu)。當(dāng)存儲(chǔ)過程比較復(fù)雜時(shí)，或者它們相互間的調(diào)用關(guān)系比較復(fù)雜時(shí)，可能難于維護(hù)。6.3.1關(guān)于觸發(fā)器的設(shè)計(jì)觸發(fā)器是一種特殊的存儲(chǔ)過程，通過數(shù)據(jù)表的DML操作而觸發(fā)執(zhí)行，其作用為確保數(shù)據(jù)的完整性和一致性不被破壞而創(chuàng)建，實(shí)現(xiàn)數(shù)據(jù)的完整性約束。說明：觸發(fā)器的before或after事務(wù)屬性的選擇時(shí)候，對(duì)表操作的事務(wù)屬性必須與應(yīng)用程序保持一致，以避免死鎖發(fā)生，在大型導(dǎo)入表中，盡量避免使用觸發(fā)器。在系統(tǒng)中盡量不要使用觸發(fā)器。7SQL訪問規(guī)范7.1盡量不要寫復(fù)雜的SQL過于復(fù)雜的SQL可以用存儲(chǔ)過程或函數(shù)來代替，效率更高；甚至如果能保證不造成瓶頸的話，把條SQL拆成多條也是可以的。這與一般的編碼規(guī)范很相似的，首先是要易懂。易懂也就意味著容易維護(hù)，對(duì)較為復(fù)雜的sql語句加上注釋，說明算法、功能注釋風(fēng)格：注釋單獨(dú)成行、放在語句前面。7.2避免使用SELECT*程序中不能出現(xiàn)SELECT*，即使是選擇全部選擇項(xiàng)，也需要全部指明，這主要出于如下原因：第一，使用*相對(duì)比較慢，因?yàn)镺racle在解析的過程中，會(huì)將“*”依次轉(zhuǎn)換成所有的列名，這個(gè)工作是通過遍歷數(shù)據(jù)字典完成，這意味著將耗費(fèi)更多的時(shí)間；第二，為避免以后相關(guān)表增加字段造成程序錯(cuò)誤，比如INSERTINTOSELECT和SELECTINTO語句會(huì)報(bào)錯(cuò)。以下不符合規(guī)范：select*fromsm_duty;應(yīng)如下書寫：selectduty_id,duty_name,creation_date,created_by

fromsm_duty;7.3INSERT時(shí)需寫全列名代碼中INSERT語句必須寫出全部列名，以保證表增加字段后語句執(zhí)行不受影響。以下不符合規(guī)范：insertintoinv_parametersvalues(:field1,:field2,:field3);應(yīng)如下書寫：insertintoinv_parameters(field1,field2,field3)values(:field1,:field2,:field3);7.4進(jìn)行DML操作時(shí)使用CTAS進(jìn)行數(shù)據(jù)備份在進(jìn)行DML操作（INSERT，UPDATE，DELETE）之前，必須對(duì)數(shù)據(jù)進(jìn)行備份，使用如下語句。方法一：表數(shù)據(jù)全部備份：CREATETABLETAB_NAME_BAKASSELECT*FROMTAB_NAME;方法二：部分備份：對(duì)大表僅備份將要修改的數(shù)據(jù)：CREATETABLETAB_NAME_BAKASSELECT*FROMTAB_NAMEWHERE[選擇出被操作數(shù)據(jù)的條件];7.5大數(shù)據(jù)量DML操作分多次執(zhí)行DML操作涉及到大數(shù)據(jù)量時(shí)，請(qǐng)分解為多次執(zhí)行：對(duì)于UPDATE和DELETE每次涉及數(shù)據(jù)量在1萬條左右，并且每次執(zhí)行完就提交；對(duì)于INSERTINTOSELECT如果采用提示(/*+appendparallel*/)可以處理百萬級(jí)別的數(shù)據(jù)量。7.6使用綁定變量，降低高硬解析使用“變量綁定”來處理一條SQL帶不同常量多次執(zhí)行的情況，動(dòng)態(tài)綁定可以大大優(yōu)化SQL的執(zhí)行效率，還可以優(yōu)化Oracle的內(nèi)存使用。在Java中，結(jié)合使用setXXX系列方法，可以為不同數(shù)據(jù)類型的綁定變量進(jìn)行賦值，從而大大優(yōu)化了SQL語句的性能。JAVA情況下的動(dòng)態(tài)綁定示例如下：Stringv_id='xxxxx';Stringv_sql='selectnamefromtb_awhereid=?';stmt=con.prepareStatement(v_sql);stmt.setString(1,v_id);//為綁定變量賦值stmt.executeQuery();7.7選擇最有效率的表名順序ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此，F(xiàn)ROM子句中寫在最后的表(基礎(chǔ)表drivingtable)將被最先處理。在FROM子句中包含多個(gè)表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。當(dāng)ORACLE處理多個(gè)表時(shí)，會(huì)運(yùn)用排序及合并的方式連接它們：首先，掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行排序，然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表)，最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并。示例：表policy有18,888條記錄；表claim有1條記錄選擇policy作為基礎(chǔ)表(不好的方法)selectcount(*)fromclaim,policy執(zhí)行時(shí)間26.09秒選擇claim作為基礎(chǔ)表(好的方法)selectcount(*)frompolicy,claim執(zhí)行時(shí)間0.96秒;7.8關(guān)注WHERE子句中的連接順序ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個(gè)原理，表之間的連接必須寫在其他WHERE條件之前。示例：(低效)SELECTpolicy.aab001,claim.aab051

FROMpolicy,claimWHEREclaim.aae140=’31’

ANDpolicy.aab001=claim.aab001;(高效)SELECTpolicy.aab001,claim.aab051

FROMpolicy,claimWHEREpolicy.aab001=claim.aab001

ANDclaim.aae140=’31’;7.9用EXISTS替代IN實(shí)際情況看，使用exists替換in效果不是很明顯，基本一樣。在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件，往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接。在這種情況下，使用EXISTS(或NOTEXISTS)通常將提高查詢的效率。示例：(低效)SELECT*

FROMpolicyWhereaac001in(selectaac001fromclaimwhereaab001=str_aab001andaae140=’31’);或SELECT*

FROMpolicyWhereaac001in(selectdistinctaac001fromclaimwhereaab001=str_aab001andaae140=’31’);(高效)SELECT*

FROMpolicyWhereexists(select1fromclaimwhereaac001=policy.aac001andaab001=str_aab001andaae140=’31’);in的常量列表是優(yōu)化的(例如：aae110in(‘20’,’30’))，不用exists替換；in列表相當(dāng)于or用NOTEXISTS替代NOTINOracle在10g之前版本notin都是最低效的語句，雖然在10g上notin做到了一些改進(jìn)，但仍然還是存在一些問題，因此我們一定要使用notexists來替代notin的寫法。在子查詢中，NOTIN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并。無論在哪種情況下，NOTIN都是最低效的(因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷)。為了避免使用NOTIN，我們可以把它改寫成NOTEXISTS。示例：(低效)SELECT*

FROMpolicyWHEREaab001NOTIN(SELECTaab001frompolicywhereaclaim0=’100’);(高效)SELECT*

FROMpolicyWHEREnotexists(SELECT1frompolicywhereaab001=policy.aab001andaclaim0=’100’);7.10用表連接替換EXISTS

在子查詢的表和主表查詢是多對(duì)一的情況，一般采用表連接的方式比EXISTS更有效率。示例：(低效)SELECTpolicy.*

FROMpolicy

Whereexists(select1fromclaim

whereaac001=policy.aac001

andaab001=policy.aab001

andaae140='31'

andaae041='200801');(高效)SELECTpolicy.*

FROMpolicy,claimWherepolicy.aac001=claim.aac001

andpolicy.aab001=claim.aab001

andclaim.aae140='31'

andclaim.aae041='200801';到底exists和表關(guān)聯(lián)哪種效率高，其實(shí)是根據(jù)兩個(gè)表之間的數(shù)據(jù)量差別大小是有關(guān)的，如果差別不大實(shí)際上速度基本差不多。7.11用EXISTS替換DISTINCT

當(dāng)提交一個(gè)包含一對(duì)多表信息(比如個(gè)人基本信息表和個(gè)人參保信息表)的查詢時(shí)，避免在SELECT子句中使用DISTINCT。一般可以考慮用EXISTS替換。示例：(低效)selectdistinctpolicy.aac001fromclaim,policywhereclaim.aac001=policy.aac001andclaim.aae140='31'andpolicy.aab001='100100';(高效)selectpolicy.aac001frompolicywhereexists(select1fromclaimwhereaac001=policy.aac001andaae140='31')andpolicy.aab001='100100';EXISTS使查詢更為迅速，因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結(jié)果。因此如果不是特別研究和追求速度的話（例如：數(shù)據(jù)轉(zhuǎn)換），查詢一個(gè)表的數(shù)據(jù)需要關(guān)聯(lián)其他表的這種情況查詢，建議采用EXISTS的方式。7.12盡量用unionall替換union

Union會(huì)去掉重復(fù)的記錄，會(huì)有排序的動(dòng)作，會(huì)浪費(fèi)時(shí)間。因此在沒有重復(fù)記錄的情況下或可以允許有重復(fù)記錄的話，要盡量采用unionall來關(guān)聯(lián)。7.13使用DECODE函數(shù)來減少處理時(shí)間

使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。示例：(低效)selectcount(1)frompolicywhereaab001=’100001’andaac008=’1’;selectcount(1)frompolicywhereaab001=’100001’andaac008=’2’;(低效)Selectcount(1),aac008

FrompolicyWhereaab001=’100001’

andaac008in(’1’,’2’)groupbyaac008;(高效)selectcount(decode(aac008,’1’,’1’,null))zz,count(decode(aac008,’2’,’1’,null))txfrompolicywhereaab001=’100001’;groupby和orderby都會(huì)影響性能，編程時(shí)盡量避免沒有必要的分組和排序，或者通過其他的有效的編程辦法去替換，比如上面的處理辦法。7.14盡量避免用orderbyOrderby需要查詢后排序，速度慢影響性能，如果查詢數(shù)據(jù)量大，排序的時(shí)間就很長(zhǎng)。但我們也不能避免不使用，這樣大家一定注意一點(diǎn)的是如果使用orderby那么排序的列表必須符合索引，這樣在速度上會(huì)得到很大的提升。7.15用Where子句替換HAVING子句避免使用HAVING子句，HAVING只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾。這個(gè)處理需要排序，總計(jì)等操作。如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷。示例：(低效)SELECTaac008,count(1)FROMpolicyGROUPBYaac008HAVINGaac008in(‘1’,’2’);

(高效)SELECTaac008,count(1)

FROMpolicyWhereaac008in(‘1’,’2’)GROUPBYaac008;HAVING中的條件一般用于對(duì)一些集合函數(shù)的比較,如COUNT()等等。除此而外，一般的條件應(yīng)該寫在WHERE子句中。7.16減少多表關(guān)聯(lián)表關(guān)聯(lián)的越多，查詢速度就越慢，盡量減少多個(gè)表的關(guān)聯(lián)，建議表關(guān)聯(lián)不要超過3個(gè)（子查詢也屬于表關(guān)聯(lián)）。數(shù)據(jù)轉(zhuǎn)換上會(huì)存在大數(shù)據(jù)量表的關(guān)聯(lián)，關(guān)聯(lián)多了會(huì)影響索引的效率，可以采用建立臨時(shí)表的辦法，有時(shí)更能提高速度。7.17避免重復(fù)訪問7.17.1使用groupby同源單組單查詢?nèi)缦虏环弦?guī)范：SELECTCLASS,sum(COL)FROM

TAB_TEST

WHERECLASS=’A’UNIONALLSELECTCLASS,sum(COL)FROM

TAB_TEST

WHERECLASS=’B’UNIONALLSELECTCLASS,sum(COL)FROM

TAB_TEST

WHERECLASS=’C’;應(yīng)如下書寫：SELECTCLASS,sum(COL)FROMTAB_TESTGROUPBYCLASS;7.17.2用表更新表一個(gè)表同時(shí)更新另一個(gè)表的多個(gè)字段如下不符合規(guī)范：使用TB_SOURCE表更新表TB_TARGET的多個(gè)字段UPDATETB_TARGET

A

SET

A.COL1=(selectB.COL1fromTB_SOURCEBwhereB.id=A.id),A.COL2=(selectB.COL2fromTB_SOURCEBwhereB.id=A.id),A.COL3=(selectB.COL3fromTB_SOURCEBwhereB.id=A.id),A.COL4=(selectB.COL4fromTB_SOURCEBwhereB.id=A.id)WHEREA.idIN(selectB.idfromTB_SOURCEB);應(yīng)如下書寫：UPDATETB_TARGET

A

SET(COL1,A.COL2,A.COL3,A.COL4)=(SELECTB.COL1,B.COL2,B.COL3,B.COL4FROMTB_SOURCEBWHEREB.id=A.id)WHEREEXISTS(select1fromTB_SOURCEBwhereB.id=A.id);7.17.3豎向顯示變橫向顯示豎向顯示變橫向顯示如下不符合規(guī)范：SELECTA.C1AC1,A.C2AC2,A.C3AC3,

B.C1BC1,B.C2BC2,B.C3BC3,

C.C1CC1,C.C2CC2,C.C3CC3FROM

(SELECT'123'X,'SYNONYM'C1,sum(2)C2,count(1)C3

FROM

TABWHERETABTYPE='SYNONYM')A,

(SELECT'123'X,'TABLE'

C1,sum(2)C2,count(1)C3

FROM

TABWHERETABTYPE='TABLE')B,

(SELECT'123'X,'VIEW'

C1,sum(2)C2,count(1)C3

FROM

TABWHERETABTYPE='VIEW')C應(yīng)如下書寫：SELECTMAX(DECODE(TABTYPE,'SYNONYM','SYNONYM',NULL))AC1,

MAX(DECODE(TABTYPE,'SYNONYM',sum(2),0))AC2,

MAX(DECODE(TABTYPE,'SYNONYM',count(1),0))AC3,

MAX(DECODE(TABTYPE,'TABLE','TABLE',NULL))BC1,

MAX(DECODE(TABTYPE,'TABLE',sum(2),0))BC2,

MAX(DECODE(TABTYPE,'TABLE',count(1),0))BC3,

MAX(DECODE(TABTYPE,'VIEW','VIEW',NULL))CC1,

MAX(DECODE(TABTYPE,'VIEW',sum(2),0))CC2,

MAX(DECODE(TABTYPE,'VIEW',count(1),0))CC3

FROM

TABWHERE

TABTYPEIN('TABLE','SYNONYM','VIEW')GROUPBYTABTYPE;7.18完成事務(wù)及時(shí)commit對(duì)于一個(gè)完成了的事務(wù)，請(qǐng)用commit顯示提交，這是避免鎖爭(zhēng)用的鎖等待的需要，特別是對(duì)DML操作頻繁的表。7.19數(shù)據(jù)庫連接及時(shí)關(guān)閉程序中必須顯示關(guān)閉數(shù)據(jù)庫連接，不僅正常執(zhí)行完后需顯示關(guān)閉，而且在異常處理塊（例如java的exception段）也要顯示關(guān)閉。7.20索引的使用在實(shí)際的應(yīng)用系統(tǒng)中索引問題導(dǎo)致性能問題可能能占到80%，在程序優(yōu)化上索引問題是需要我們特別關(guān)注的。7.20.1避免在索引列上使用函數(shù)或運(yùn)算這個(gè)問題是在我們實(shí)際編程中出現(xiàn)過的，請(qǐng)大家一定注意。在索引列上使用函數(shù)或運(yùn)算，查詢條件都不會(huì)使用索引。低效，索引失效：Select*fromka02whereaka060=’10001000’andto_char(aae030,’yyyymm’)=’200801’;高效，索引有效：Select