數據庫開發(fā)規(guī)范

1、項目一部數據庫開發(fā)規(guī)范版本號日期修改者說 明2012-03-06注：對該文件內容增加、刪除或修改均需填寫此變更記錄，詳細記載變更信息，以保證 其可追溯性。1. 命名規(guī)范1.1. 對象新建的表，存儲過程，包等要遵循以下規(guī)則對象名稱前綴備注表見卜表，根據功能分視圖v_主鍵pk_索引idx_序列seq_存儲過程pr_包pk_函數f_同義詞s_數據庫連 接 (dblink)dl_1.2. 表表名不得超過30個字母，全部采用大寫字母，表的命名可以如下：模塊縮寫名 _表的名稱，如 RES_NODE表資源模塊的節(jié)點；值班：DUTY_CALENDAR1.3. 字段名稱字段名不得超過30個字母，必須以英文單詞構

2、成，每個單詞之間以下劃線隔 開，全部采用大寫字母。對復雜的大型應用系統(tǒng)而言，必須建立表名和字段名的數 據字典，并附于開發(fā)規(guī)范附錄中，在命名時必須嚴格遵守數據字典。2. 數據庫對象管理2.1. 一般規(guī)定數據庫所有對象，包括表、視圖、主鍵、索引、序列、存儲過程、包等必須在數據庫建模工具中進行管理并保持與數據庫完全同步。2.2. 大小寫在數據庫模型、數據庫腳本中，所有對象，包括表、視圖、主鍵、索引、序列、存儲過程、包等名稱必須大寫。3. 語句書寫規(guī)范3.1. 盡 量不使用某種數據庫的特有功能為了保持可移植性，盡量不使用某種數據庫的特有功能，如 SQL Server 專用的Unique ID, Ora

3、cle 專用的 Sequence 的功能；3.2. 查詢 sql 語句盡量使用綁定變量3.3. 盡 最大可能不使用通配符在SQL®句中，LIKE關鍵字支持通配符匹配，但這種匹配特別耗費時間。如：SELECT A FROM ABC WHERE A LIKE 'M%'。在A字段上建立了索引。把語句改為 SELECT A FROM ABC WHERE A >'M' AND A <'N' ，在執(zhí)行查詢時會利用索引以提高響應速度。使用 *通配符必須事先征得項目開發(fā)負責人同意。3.3.1 Distinct使用 distinct 會增加查

4、詢和I/O 的操作次數。應當避免使用 distinct 關鍵字。3.3.2 嵌套查詢SELECT A FROM CMS_USER WHERE USER_NAME IN ( SELECT USER_NAME FROM CMS_DEPARTMENT WHERE DEPARTMENT、，)如果我們用連接來代替，且表關聯(lián)放在條件語句的最后部。即：SELECT A FROM CMS_US，ERCMS_DEPARTMENT WHERECMS_DEPARTMENT .DEPARTM ENT 永 AND CMS_DEPARTMENT .USER_NAME =將提高一定的效率。查詢嵌套層次越多，效率越低。應當盡

5、量避免子查詢。如果子查詢不可避免， 那么要在子查詢中過濾掉盡可能多的行。3.3.3 排序利用索引自動以適當的次序輸出時，可以避免對表中數據排序，當以下的情況 發(fā)生時，排序就不能省略：索引中不包括一個或幾個待排序的列；group by或order by子句中列的次序與索引的次序不一樣；排序的列來自不同的表。正確地增建索引、合理地合并數據庫表，可以避免不必要的排序。如果排序不 可避免，那么應當試圖簡化它，如縮小排序列的范圍等。3.3.4 UNION如果不過濾多表中的重復數據，請使用UNION ALL如果過濾多表中的重復數據，請使用UNION3.3.5 長語句避免使用很長、很復雜的查詢語句，如果有特

6、殊需求必須書寫較長的SQL®句，應該把語句分解成若干部分，每一部分形成一個存儲過程或函數。3.3.6 大表盡可能使用分區(qū)大表分區(qū)：超過1G的表盡可能使用分區(qū)，分區(qū)的原則和盡可能和維護該表的機 制結合起來。比如：保留10天數據，每天刪除10天前的一天數據，在刪除數據的 時候，可以采用采用 alter table table_name truncate partitionpartition_name, 而后 alter table table_name drop partition partition_name;這里不直接使用drop的原因是減小數據庫的開銷。Truncate是在秒的級別

7、中完 成。3.3.7 其他注意事項1）在條件語句中，如 Where = 中將記錄少的表放在等號的前部。表關聯(lián)條件放 在語句的最后部。2）不使用容易與系統(tǒng)關鍵字重復的單詞來命名，如 ID, DATE等，但可以使用如NODEID BUYDAT等可以來命名；3） SQL®句用大寫字母（字段中的內容除外）；4）編寫ddl和dml時，每個語句后面必須加上分號；5) 對于定期增加性的性能數據表，必須建立有效的索引； 6)查詢語句的Where語句必須落在索引上。4 高效的 sql 語句4.1 Sql 優(yōu)化方法RBO(rule-based optimizer)CBO4.1.1 驅動表1 ) 2 張行

8、數不一致的表連接表TAB1行數：16,384行表TAB2行數：1行X SELECT COUNT(*) FROM TAB2, TAB1;O SELECT COUNT(*) FROM TAB1, TAB2;2 ) 3 張表連接X SELECT * FROM EMP E, LOC L, CAT CWHERE BETWEEN 1000 AND 2000 AND=AND= ;OSELECT * FROM LOC L, CAT C, EMP EWHERE BETWEEN 1000 AND 2000ANDAND4.1.2 Wherei§句順序的效率1)使用索引引起的where語句效率使用AND&#

9、167;包時行數多的放在前面X SELECT *FROM emp EWHERE emp_sal > 50000AND emp_type = 'MANAGER'AND 25< (SELECT COUNT(*) FROM EMP WHERE emp_mgr = ;OSELECT *FROM emp EWHERE 25< (SELECT COUNT(*) FROM EMP WHEREemp_mgr =AND emp_sal > 50000AND emp_type = 'MANAGER'使用OR®何時，行數多的放在后面X SELECT

10、*FROM emp EWHERE 25< (SELECT COUNT(*) FROM EMP WHERE emp_mgr =OR (emp_sal > 50000AND emp_type = 'MANAGER');OSELECT *FROM emp EWHERE (emp_sal > 50000AND emp_type = 'MANAGER')OR 25< (SELECT COUNT(*) FROM EMP WHERE emp_mgr= ;2) ROWID勺使用使用ROWID勺WHERE旬效率最高。SELECT ROWID, .INTO

11、:emp_rowid, .FROM empWHERE = 56722FOR UPDATE;UPDATE empSET = . ,WHERE ROWID = :emp_rowid;3) )減少訪問次數x SELECT emp_name, sal, grade FROM empWHERE emp_no = 0342;SELECT emp_name, sal, gradeFROM empWHERE emp_no = 0291;OSELECT,FROM emp A, emp B WHERE = 0342AND = 0291;4) Where語句的索引的使用(1) SUBSTRx SELECT acc_

12、name, trans_date, amount FROM transactionWHERE SUBSTR(account_name,1,7) = 'CAPITAL'O SELECT acc_name, trans_date, amount FROM transactionWHERE account_name LIKE 'CAPITAL%'！=x SELECT acc_name, trans_date, amount FROM transactionWHERE amount != 0;O SELECT acc_name, trans_date, amount F

13、ROM transactionWHERE amount > 0;(3) TRUNCx SELECT acc_name, trans_date, amount FROM transactionWHERE TRUNC(trans_date) = TRUNC(SYSDATE);O SELECT acc_name, trans_date, amount FROM transactionWHERE trans_dateBETWEEN TRUNC(SYSDATE)AND TRUNC(SYSDATE) + .99999;(4) |x SELECT acc_name, trans_date, amoun

14、t FROM transactionWHERE acc_name | acc_type = 'AMEXA'O SELECT acc_name, trans_date, amount FROM transactionWHERE acc_name = 'AMEX' AND acc_type = 'A'(5) 5) 運算x SELECT acc_name, trans_date, amount FROM transactionWHERE amount + 3000 < 5000;O SELECT acc_name, trans_date, amo

15、unt FROM transactionWHERE amount < 5000 + 3000;4.2 SQ階令的使用秘訣4.2.1 DECODE 使用x SELECT COUNT(*), SUM(salary)FROM empWHERE dept_no = 0020AND emp_name LIKE 'SMITH%'SELECT COUNT(*), SUM(salary)FROM empWHERE dept_no = 0030AND emp_name LIKE 'SMITH%'O SELECT COUIN TDECODE(dept_no, 0020, &#

16、39;X', NULL)D0020_count,COUNT (DECODE(dept_no, 0030, 'X', NULL)D0030_count,SUM (DECODE(dept, 0020, salary, NULL) D0020_sal,SUM (DECODE(dept, 0030, salary, NULL) D0030_salFROM empWHERE emp_name LIKE 'SMITH%'4.2.2 表的別名的使用x SELECT , name, tax_no, , comp_nameFROM company C, emp EWHER

17、E = ;O SELECT,FROM company C, emp EWHERE = ;4.2.3 去掉重復行DELETE FROM emp EWHERE > ( SELECT MINFROM emp XWHERE = );4.2.4 表的行計數SELECT COUNT(索弓 I 的歹U ) FROM TRANS;SELECT COUNT(*) FROM TRANS;SELECT COUNT(1) FROM TRANS;4.2.5 用 WHERE旬替換HAVIN緒句的使用x SELECT region, AVG(loc_size)FROM locationGROUP BY regionH

18、AVING region != 'SYDNEY'AND region != 'PERTH'O SELECT region, AVG(loc_size)FROM locationWHERE region != 'SYDNEY'AND region != 'PERTH'GROUP BY region4.2.6 使用表連接替代EXISTS!用x SELECT emp_nameFROM empWHERE (emp_cat, sal_range) =(SELECT MAX(category), MAX(sal_range) FROM emp

19、_categories)AND emp_dept = 0020;O SELECT emp_nameFROM empWHERE emp_cat = (SELECT MAX(category) FROM emp_categories)AND sal_range = (SELECT MAX(sal_range) FROM emp_categories)AND emp_dept = 0020;4.2.7 使用EXISTSfH弋表連接x SELECT .FROM dept D, emp E WHERE =AND = 'MANAGER'AND = 'A'O SELECT .

20、FROM emp EWHERE EXISTS ( SELECT 'X' FROM deptWHERE dept_no =AND dept_cat = 'A')AND = 'MANAGER'X SELECT .FROM dept D, emp EWHERE =AND = 'MANAGER'OR = 'A');O SELECT .FROM emp EWHERE = 'MANAGER'OR EXISTS ( SELECT 'X'FROM deptWHERE dept_no = AND de

21、pt_cat = 'A');4.2.8 使用EXISTS弋替DISTINC話句x SELECT DISTINCT dept_no, dept_nameFROM dept D, emp EWHERE = ;O SELECT dept_no, dept_nameFROM dept DWHERE EXISTS ( SELECT 'X'FROM emp EWHERE = ;4.2.9 使用 NOT EXISTS替 NOT IN®旬x SELECT .FROM empWHERE dept_no NOT IN ( SELECT dept_no FROM deptW

22、HERE dept_cat = 'A');O SELECT .FROM emp EWHERE NOT EXISTS ( SELECT 'X'FROM deptWHERE dept_no =AND dept_cat = 'A');4.2.10 使用 union all 代替 union 語句Union 進行排序Union All 不排序4.2.11 使用Union和IN代替OR§句Loc_no, region上有索弓 Ix SELECT .FROM locationWHERE loc_id = 10OR region = 'MEL

23、BOURNE'O SELECT .FROM locationWHERE loc_id = 10UNION ALLSELECT .FROM locationWHERE region = 'MELBOURNE'X SELECT .FROM locationWHERE loc_id = 10OR loc_id = 20OR loc_id = 30;AO SELECT .FROM locationWHERE loc_id IN (10, 20, 30);5高效索引5.1 索引的使用使用索引時，要考慮以下因素：1)索引列的計算2)索引列的增加3)索引列不要用NOT4)索引中空值的

24、使用IS NULL, IS NOT NULL5）索引列的數據類型的變換EMP_TYPEvarchar2類型，下列語句使用索引SELECT .FROM empWHERE emp_type = 123;SELECT .FROM empWHERE TO_NUMBER（emp_type） = 123;SELECT .FROM empWHERE emp_type = '123'5.2 增加索引增加索引要考慮多種因素，要考慮對更新、插入的影響等。1）頻繁查詢不超過大表1%15%的場合。2）列的不同值3）列4）列5）同表6 解析表的使用方法6.1 SQLTRACE6.1.1 參數表的生成$&

25、gt;sqlplus - s /SQL>$ORACLE_HOME/rdbms/admin/SQL> commitSQL> exit6.1.2 AUTOTRACffl法afe57gc2/users/home/ino 29 >sqlplus sys/*SQL> $ORACLE_HOME/sqlplus/admin/SQL> drop role plustrace;SQL> grant plustrace to ops$ino;SQL> exit6.1.3 AUTOTRACEg 的使用afe57gc2/oracle/product/ 37 >sq

26、lplus /SQL> set autotrace onSQL> select * from wcattori;執(zhí)行計劃0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF 'WCATTORI'統(tǒng)言十0 recursive calls12 db block gets4 consistent gets0 physical reads0 redo size4779 bytes sent via SQL*Net to client314 bytes received via SQL*Net from c

27、lient1 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)0 rows processedSQL>6.2 TKPROF6.2.1 初始化afe57gc2/oracle/product/ 40 >sqlplus /SQL> ALTER SESSION SET TIMED_STATISTICS = true;SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE = 500;6.2.2 SQL Trace 開始SQL> ALTER SESSION SET SQL_TRACE = true;6.2.3