数据库开发规范

1、项目一部数据库开发规范版本号日期修改者说 明2012-03-06注：对该文件内容增加、删除或修改均需填写此变更记录，详细记载变更信息，以保证 其可追溯性。1. 命名规范1.1. 对象新建的表，存储过程，包等要遵循以下规则对象名称前缀备注表见卜表，根据功能分视图v_主键pk_索引idx_序列seq_存储过程pr_包pk_函数f_同义词s_数据库连 接 (dblink)dl_1.2. 表表名不得超过30个字母，全部采用大写字母，表的命名可以如下：模块缩写名 _表的名称，如 RES_NODE表资源模块的节点；值班：DUTY_CALENDAR1.3. 字段名称字段名不得超过30个字母，必须以英文单词构

2、成，每个单词之间以下划线隔 开，全部采用大写字母。对复杂的大型应用系统而言，必须建立表名和字段名的数 据字典，并附于开发规范附录中，在命名时必须严格遵守数据字典。2. 数据库对象管理2.1. 一 般规定数据库所有对象，包括表、视图、主键、索引、序列、存储过程、包等必须在数据库建模工具中进行管理并保持与数据库完全同步。2.2. 大 小写在数据库模型、数据库脚本中，所有对象，包括表、视图、主键、索引、序列、存储过程、包等名称必须大写。3. 语句书写规范3.1. 尽 量不使用某种数据库的特有功能为了保持可移植性，尽量不使用某种数据库的特有功能，如SQL Server 专用的Unique ID, Or

3、acle 专用的 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' ，在执行查询时会利用索引以提高响应速度。使用*通配符必须事先征得项目开发负责人同意。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、，)如果我们用连接来代替，且表关联放在条件语句的最后部。即：SELECT A FROM CMS_US，ERCMS_DEPARTMENT WHERECMS_DEPARTMENT .DEPARTM ENT 永 AND CMS_DEPARTMENT .USER_NAME =将提高一定的效率。查询嵌套层次越多，效率越低。应当

5、尽量避免子查询。如果子查询不可避免， 那么要在子查询中过滤掉尽可能多的行。3.3.3 排序利用索引自动以适当的次序输出时，可以避免对表中数据排序，当以下的情况 发生时，排序就不能省略：索引中不包括一个或几个待排序的列；group by或order by子句中列的次序与索引的次序不一样；排序的列来自不同的表。正确地增建索引、合理地合并数据库表，可以避免不必要的排序。如果排序不 可避免，那么应当试图简化它，如缩小排序列的范围等。3.3.4 UNION如果不过滤多表中的重复数据，请使用UNION ALL如果过滤多表中的重复数据，请使用UNION3.3.5 长语句避免使用很长、很复杂的查询语句，如果有

6、特殊需求必须书写较长的SQL®句，应该把语句分解成若干部分，每一部分形成一个存储过程或函数。3.3.6 大表尽可能使用分区大表分区：超过1G的表尽可能使用分区，分区的原则和尽可能和维护该表的机 制结合起来。比如：保留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 = 中将记录少的表放在等号的前部。表关联条件放 在语句的最后部。2）不使用容易与系统关键字重复的单词来命名，如 ID, DATE等，但可以使用如NODEID BUYDAT等可以来命名；3） SQL®句用大写字母（字段中的内容除外）；4）编写ddl和dml时，每个语句后面必须加上分号；5) 对于定期增加性的性能数据表，必须建立有效的索引； 6)查询语句的Where语句必须落在索引上。4 高效的 sql 语句4.1 Sql 优化方法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、行数多的放在前面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'使用ORi§何时，行数多的放在后面X SELECT *FROM

10、 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 :emp_

11、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_name,

12、 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 FROM t

13、ransactionWHERE 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, amount F

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

15、ROM 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, 'X&

16、#39;, 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 EWHERE =

17、;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 regionHAVIN

18、G 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_cat

19、egories)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 .FROM

20、 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 dept_c

21、at = '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 deptWHERE

22、 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 = 'MELBOUR

23、NE'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)索引中空值的使用IS

24、 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 参数表的生成$>s

25、qlplus - 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 >sqlplu

26、s /SQL> set autotrace onSQL> select * from wcattori;执行计划0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF 'WCATTORI'统言十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 clien

27、t1 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 实