數(shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范.doc

數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范XXX科技技術(shù)規(guī)范數(shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范12目 錄1、目的22、讀者23、名詞解釋24、存儲(chǔ)過(guò)程開(kāi)發(fā)規(guī)范24.1名稱(chēng)規(guī)范24.2注釋規(guī)范34.3命名規(guī)范34.3.1參數(shù)的命名34.3.2變量命名44.4模板規(guī)范44.5存儲(chǔ)過(guò)程功能模塊組成說(shuō)明44.5.1過(guò)程名定義44.5.2過(guò)程注釋54.5.3過(guò)程體54.5.4存儲(chǔ)過(guò)存功能模板（附件）74.6日志74.6.1功能74.6.2調(diào)用方法74.6.3日志查詢(xún)74.6.4日志結(jié)果74.6.5日志過(guò)程（附件）74.6.6日志表（附件）84.7書(shū)寫(xiě)規(guī)范和優(yōu)化方法84.7.1操作符優(yōu)化84.7.2SQL書(shū)寫(xiě)的影響104.7.3WHERE后面的條件順序影響114.7.4查詢(xún)表順序的影響114.7.5SQL語(yǔ)句索引的利用121、 目的為了提高項(xiàng)目開(kāi)發(fā)效率及所開(kāi)發(fā)軟件的可維護(hù)性，提高軟件的質(zhì)量，避免文件、目錄、公用參數(shù)名字沖突，統(tǒng)一項(xiàng)目組內(nèi)部開(kāi)發(fā)風(fēng)格，增加程序可讀性和可理解性。2、 讀者本文適用以下讀者： ETL設(shè)計(jì)、開(kāi)發(fā)人員。3、 名詞解釋4、 存儲(chǔ)過(guò)程開(kāi)發(fā)規(guī)范4.1 名稱(chēng)規(guī)范注意事項(xiàng)：一、存儲(chǔ)過(guò)程名稱(chēng)全部使用英文，參數(shù)注釋也必須使用英文。以 SP開(kāi)頭格式如下 : SP_TABLENAME 。二、函數(shù)名稱(chēng)全部使用英文，參數(shù)注釋也必須使用英文。以 FN開(kāi)頭格式如下 : FN_,如：FN_LOG_EXEC 。報(bào)表類(lèi)存儲(chǔ)過(guò)程命名規(guī)則：SP_RPT_部門(mén)_報(bào)表功能_月表M,日表D編號(hào) 中文說(shuō)明 存儲(chǔ)過(guò)程名- - -2DY0001 網(wǎng)銀開(kāi)銷(xiāo)戶(hù)統(tǒng)計(jì)表 SP_RPT_DY_WYKXHTJB_M4.2 注釋規(guī)范存儲(chǔ)過(guò)程注釋信息應(yīng)包含如下信息：- -版權(quán)信息：版權(quán)所有(c) 2014， -文件名稱(chēng)：SP_MODEL.prc -版本號(hào) ： -創(chuàng)建者 ：system -創(chuàng)建日期：2014-01-01 -內(nèi)容摘要： -功能描述：生成核心客戶(hù)信息表 O_HX_CUST_INFO -運(yùn)行頻率：日跑 -傳入?yún)?shù)： I_STATEDATE -業(yè)務(wù)日期 - O_FLAG INTEGER -返回過(guò)程運(yùn)行狀態(tài) 0-正常結(jié)束 1 異常 -更改歷史： -更改日期： -更改人 ：lys -更改說(shuō)明： -4.3 命名規(guī)范注意事項(xiàng)：n 存儲(chǔ)過(guò)程命名全部使用英文，參數(shù)注釋也必須使用英文。4.3.1 參數(shù)的命名所有的輸入、輸出均采用參數(shù)賦值，參數(shù)名稱(chēng)需為 英文 數(shù)字 下劃線 不能有漢字，參數(shù)名稱(chēng)最好是有意義的英文組成，方便閱讀理解。參數(shù)名如下：輸入：I_參數(shù)名 輸出：O_參數(shù)名4.3.2 變量命名所有的變量名稱(chēng)：變量類(lèi)型首寫(xiě)字母_變量名稱(chēng)，變量名稱(chēng)最好是有意義的英文組成，方便閱讀理解。 數(shù)字變量變量名稱(chēng)：需在前面加個(gè) N_ 如：N_SQLCODE變量定義： V_SQLCODE NUMBER:=0;變量付值：V_SQLCODE:=1; 注：左右邊類(lèi)型必須一致。 字符串變量變量名稱(chēng)：需在前面加個(gè) V_ 如：V_STATMONTH變量定義： V_STATMONTH VARCHAR2(8):=20110819;變量付值：V_ STATMONTH:=20110819; 注：左右邊類(lèi)型必須一致。4.4 模板規(guī)范4.5 存儲(chǔ)過(guò)程功能模塊組成說(shuō)明對(duì)基礎(chǔ)數(shù)據(jù)進(jìn)行匯總，生成報(bào)表。4.5.1 過(guò)程名定義 CREATE OR REPLACE PROCEDURE SP_LOG_EXEC(NATIONAL CHARACTER VARYING(100), CHARACTER VARYING(10), TIMESTAMP, INTEGER, INTEGER)RETURNS INTEGERLANGUAGE NZPLSQL ASBEGIN_PROCDECLAREI_PRC_NAME ALIAS for $1;O_FLAG integer;注：參數(shù)賦值時(shí)候需要與參數(shù)列表中順序一一對(duì)應(yīng)。參數(shù) O_FLAG，為過(guò)程出參，這里是返回過(guò)程運(yùn)行狀態(tài)，判斷該過(guò)程是否正確運(yùn)行完成，以便判斷后續(xù)作業(yè)是否繼續(xù)，這里規(guī)定每個(gè)過(guò)程都必需有這個(gè)出參，并且在過(guò)程運(yùn)行過(guò)程中對(duì)該參數(shù)進(jìn)行付值，如過(guò)程運(yùn)行出現(xiàn)異常退出時(shí)，需返回 值 -1，如過(guò)程正常結(jié)束，則付值 0。4.5.2 過(guò)程注釋過(guò)程注釋說(shuō)明該過(guò)程的功能、創(chuàng)建人、創(chuàng)建時(shí)間及參數(shù)用途等- -版權(quán)信息：版權(quán)所有(c) 2014， -文件名稱(chēng)：SP_MODEL.prc -版本號(hào) ： -創(chuàng)建者 ：system -創(chuàng)建日期：2014-01-01 -內(nèi)容摘要： -功能描述：生成核心客戶(hù)信息表 O_HX_CUST_INFO -運(yùn)行頻率：日跑 -傳入?yún)?shù)： I_STATEDATE -業(yè)務(wù)日期 - O_FLAG INTEGER -返回過(guò)程運(yùn)行狀態(tài) 0-正常結(jié)束 1 異常 -更改歷史： -更改日期： -更改人 ：lys -更改說(shuō)明： -4.5.3 過(guò)程體 為了規(guī)范存儲(chǔ)過(guò)程書(shū)寫(xiě)，方便理解閱讀，和統(tǒng)一處理方式，現(xiàn)對(duì)過(guò)程體劃分為以下幾個(gè)部分u 過(guò)程初始 該部分用于定義變量，并記錄日志，生成過(guò)程開(kāi)始運(yùn)行日志，以下內(nèi)容為每個(gè)過(guò)程初始部分。/*過(guò)程初始*/ -DECLARE V_PRCNAME VARCHAR(50) := SP_MOBAN;-定義過(guò)程名變量,該變量值與過(guò)程名一樣 D_TIME_START date ; V_SQLCODE INTEGER := 0; V_ROWCOUNT INTEGER := 0; V_SQLSTR VARCHAR2(500); -SET 初始化變量 D_TIME_START:=sysdate;-獲取過(guò)程起始時(shí)間 -寫(xiě)日志，記錄過(guò)程開(kāi)始運(yùn)行 SP_LOG_EXEC(V_PRCNAME,I_STATEDATE,V_TIME_START,0,0,0,過(guò)程開(kāi)始運(yùn)行！,O_FLAG) ; u 作業(yè)處理部份這部份為過(guò)程處理數(shù)據(jù)內(nèi)容部分。要求在select語(yǔ)句的每個(gè)字段要有別名。每段sql前面需要添加功能注釋修改的時(shí)候，修改部分應(yīng)標(biāo)注修改人、修改日期u 過(guò)程結(jié)束部分過(guò)程結(jié)束，記錄過(guò)程結(jié)束日志，并返回 O_FLAG -返回過(guò)程運(yùn)行狀態(tài) 值=0。4.5.4 存儲(chǔ)過(guò)存功能模板（附件）4.6 日志4.6.1 功能記錄存儲(chǔ)過(guò)程及其它程序運(yùn)行日志4.6.2 調(diào)用方法如： call SP_LOG_EXEC(V_PRCNAME, V_PAR_DAY, D_TIME_START, N_ROWCOUNT, 0, V_STEP_DES, );4.6.3 日志查詢(xún)select * from etl_log_exec where prc_name=SP_MODEL order by start_dt,log_dt desc;4.6.4 日志結(jié)果4.6.5 日志過(guò)程（附件）4.6.6 日志表（附件）4.7 書(shū)寫(xiě)規(guī)范和優(yōu)化方法4.7.1 操作符優(yōu)化 IN 操作符 用IN寫(xiě)出來(lái)的SQL的優(yōu)點(diǎn)是比較容易寫(xiě)及清晰易懂，這比較適合現(xiàn)代軟件開(kāi)發(fā)的風(fēng)格。 但是用IN的SQL性能總是比較低的，從執(zhí)行的步驟來(lái)分析用IN的SQL與不用IN的SQL有以下區(qū)別：試圖將其轉(zhuǎn)換成多個(gè)表的連接，如果轉(zhuǎn)換不成功則先執(zhí)行IN里面的子查詢(xún)，再查詢(xún)外層的表記錄，如果轉(zhuǎn)換成功則直接采用多個(gè)表的連接方式查詢(xún)。由此可見(jiàn)用IN的SQL至少多了一個(gè)轉(zhuǎn)換的過(guò)程。一般的SQL都可以轉(zhuǎn)換成功，但對(duì)于含有分組統(tǒng)計(jì)等方面的SQL就不能轉(zhuǎn)換了。 推薦方案：在業(yè)務(wù)密集的SQL當(dāng)中盡量不采用IN操作符。 NOT IN操作符 此操作是強(qiáng)列推薦不使用的，因?yàn)樗荒軕?yīng)用表的索引。 推薦方案：用NOT EXISTS 或（外連接+判斷為空）方案代替 操作符（不等于） 不等于操作符是永遠(yuǎn)不會(huì)用到索引的，因此對(duì)它的處理只會(huì)產(chǎn)生全表掃描。 推薦方案：用其它相同功能的操作運(yùn)算代替，如: a0 改為 a0 or a0 a 改為 a IS NULL 或IS NOT NULL操作（判斷字段是否為空）判斷字段是否為空一般是不會(huì)應(yīng)用索引的，因?yàn)锽樹(shù)索引是不索引空值的。推薦方案：用其它相同功能的操作運(yùn)算代替，如a is not null 改為 a0 或a等。不允許字段為空，而用一個(gè)缺省值代替空值，如業(yè)擴(kuò)申請(qǐng)中狀態(tài)字段不允許為空，缺省為申請(qǐng)。 建立位圖索引（有分區(qū)的表不能建，位圖索引比較難控制，如字段值太多索引會(huì)使性能下降，多人更新操作會(huì)增加數(shù)據(jù)塊鎖的現(xiàn)象） 操作符（大于或小于操作符） 大于或小于操作符一般情況下是不用調(diào)整的，因?yàn)樗兴饕蜁?huì)采用索引查找，但有的情況下可以對(duì)它進(jìn)行優(yōu)化，如一個(gè)表有100萬(wàn)記錄，一個(gè)數(shù)值型字段A，30萬(wàn)記錄的A=0，30萬(wàn)記錄的A=1，39萬(wàn)記錄的A=2，1萬(wàn)記錄的A=3。那么執(zhí)行A2與A=3的效果就有很大的區(qū)別了，因?yàn)锳2時(shí)會(huì)先找出為2的記錄索引再進(jìn)行比較，而A=3時(shí)則直接找到=3的記錄索引。 LIKE操作符 LIKE操作符可以應(yīng)用通配符查詢(xún)，里面的通配符組合可能達(dá)到幾乎是任意的查詢(xún)，但是如果用得不好則會(huì)產(chǎn)生性能上的問(wèn)題，如LIKE %5400% 這種查詢(xún)不會(huì)引用索引，這個(gè)條件會(huì)產(chǎn)生全表掃描。而LIKE X5400%則會(huì)引用范圍索引。一個(gè)實(shí)際例子：用A1_BH LIKE %5400% 這個(gè)條件會(huì)產(chǎn)生全表掃描，如果改成A1_BH X5400% OR A1_BH LIKE B5400% 則會(huì)利用A1_BH的索引進(jìn)行兩個(gè)范圍的查詢(xún)，性能肯定大大提高（前提是A1_BH上建的索引）。 UNION操作符 UNION在進(jìn)行表鏈接后會(huì)篩選掉重復(fù)的記錄，所以在表鏈接后會(huì)對(duì)所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算，刪除重復(fù)的記錄再返回結(jié)果。實(shí)際大部分應(yīng)用中是不會(huì)產(chǎn)生重復(fù)的記錄，最常見(jiàn)的是過(guò)程表與歷史表UNION。如：select * from A1unionselect * from A2 這個(gè)SQL在運(yùn)行時(shí)先取出兩個(gè)表的結(jié)果，再用排序空間進(jìn)行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會(huì)導(dǎo)致用磁盤(pán)進(jìn)行排序。 推薦方案：采用UNION ALL操作符替代UNION，因?yàn)閁NION ALL操作只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。 select * from A1union allselect * from A24.7.2 SQL書(shū)寫(xiě)的影響同一功能同一性能不同寫(xiě)法SQL的影響。如一個(gè)SQL在A程序員寫(xiě)的為Select * from a1B程序員寫(xiě)的為Select * from user1.a1（帶表所有者的前綴）C程序員寫(xiě)的為Select * from USER1.A1（大寫(xiě)表名）D程序員寫(xiě)的為Select * from USER1.A1（中間多了空格） 以上四個(gè)SQL在數(shù)據(jù)庫(kù)分析整理之后產(chǎn)生的結(jié)果及執(zhí)行的時(shí)間是一樣的，但是從數(shù)據(jù)庫(kù)共享內(nèi)存SGA的原理，可以得出數(shù)據(jù)庫(kù)對(duì)每個(gè)SQL都會(huì)對(duì)其進(jìn)行一次分析，并且占用共享內(nèi)存，如果將SQL的字符串及格式寫(xiě)得完全相同則數(shù)據(jù)庫(kù)只會(huì)分析一次，共享內(nèi)存也只會(huì)留下一次的分析結(jié)果，這不僅可以減少分析SQL的時(shí)間，而且可以減少共享內(nèi)存重復(fù)的信息，數(shù)據(jù)庫(kù)也可以準(zhǔn)確統(tǒng)計(jì)SQL的執(zhí)行頻率。 推薦方案：SQL關(guān)鍵字統(tǒng)一采用小寫(xiě)，表名，字段名統(tǒng)一采用大寫(xiě)，統(tǒng)一不加表的所有者，另外，所有的分隔間隔均只能有一個(gè)空格字符。4.7.3 WHERE后面的條件順序影響 WHERE子句后面的條件順序?qū)Υ髷?shù)據(jù)量表的查詢(xún)會(huì)產(chǎn)生直接的影響，如Select * from A1 where use_bz=1 and name = 1KV以下Select * from a1 where name = 1KV以下 and use_bz=1 以上兩個(gè)SQL中name（名稱(chēng)）及use_bz（使用標(biāo)志）兩個(gè)字段都沒(méi)進(jìn)行索引，所以執(zhí)行的時(shí)候都是全表掃描， SQL的name = 1KV以下條件在記錄集內(nèi)比率為99%，而use_bz=1的比率只為0.5%，在進(jìn)行第一條SQL的時(shí)候99%條記錄都進(jìn)行name及use_bz的比較，而在進(jìn)行第二條SQL的時(shí)候0.5%條記錄都進(jìn)行name及use_bz的比較，以此可以得出第二條SQL的CPU占用率明顯比第一條低。 SQL的where后面條件的原則:越接近結(jié)果的條件優(yōu)先級(jí)最高,順序?yàn)榘磧?yōu)先級(jí)從高到低,以減輕CPU占用率 4.7.4 查詢(xún)表順序的影響 在FROM后面的表中的列表順序會(huì)對(duì)SQL執(zhí)行性能影響，在沒(méi)有索引及數(shù)據(jù)庫(kù)沒(méi)有對(duì)表進(jìn)行統(tǒng)計(jì)分析的情況下數(shù)據(jù)庫(kù)會(huì)按表出現(xiàn)的順序進(jìn)行鏈接，由此因?yàn)楸淼捻樞虿粚?duì)會(huì)產(chǎn)生十分耗服務(wù)器資源的數(shù)據(jù)交叉。（注：如果對(duì)表進(jìn)行了統(tǒng)計(jì)分析，數(shù)據(jù)庫(kù)會(huì)自動(dòng)先進(jìn)小表的鏈接，再進(jìn)行大表的鏈接） 對(duì)表進(jìn)行統(tǒng)計(jì)分析的語(yǔ)法： GENERATE STATISTICS ON ； 4.7.5 SQL語(yǔ)句索引的利用對(duì)操作符的優(yōu)化（見(jiàn)上節(jié)）對(duì)條件字段的一些優(yōu)化采用函數(shù)處理的字段不能利用索引，如： substr(bh,1,4)=5400，優(yōu)化處理：bh like 5400% trunc(rq)=trunc(sysdate)，優(yōu)化處理：rq=trunc(s