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

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