数据库设计参考标准

1、数据库设计参考标准数据库设计参考标准 文档控制 文档属性文档编号作者版本日期密级EDS06黄翠微V12011年6月16 文档修订历史版本状态日期作者修订描述一、 概述为明确公司项目中数据库逻辑设计及物理设计的内容和流程，特制定本规范，供数据库设计、开发及维护人员参考。数据库设计方法目前可分为四类：直观设计法、规范设计法、计算机辅助设计法和自动化设计法。新奥尔良法是目前公认的比较完整和权威的一种规范设计法。新奥尔良法将数据库设计分成需求分析（分析用户需求）、概念设计（信息分析和定义）、逻辑设计（设计实现）和物理设计（物理数据库设计）。目前，常用的规范设计方法大多起源于新奥尔良法，并在设计的每一阶

2、段采用一些辅助方法来具体实现。以下是两种常用的规范设计方法：1. 基于E-R模型的数据库设计方法。该方法是由P.P.S.chen于1976年提出的数据库设计方法，其基本思想是在需求分析的基础上，用E-R（实体联系）图构造一个反映现实世界实体之间联系的企业模式，然后再将此企业模式转换成基于某一特定的DBMS的概念模式。2. 基于3NF的数据库设计方法。该方法是由S·Atre提出的结构化设计方法，其基本思想是在需求分析的基础上，确定数据库模式中的全部属性和属性间的依赖关系，将它们组织在一个单一的关系模式中，然后再分析模式中不符合3NF的约束条件，将其进行投影分解，规范成若干个3NF关系模

3、式的集合。 其具体设计步骤分为五个阶段： （1） 设计企业模式，利用规范化得到的3NF关系模式画出企业模式； （2） 设计数据库的概念模式，把企业模式转换成DBMS所能接受的概念模式，并根据概念模式导出各个应用的外模式； （3） 设计数据库的物理模式（存储模式）； （4） 对物理模式进行评价； （5） 实现数据库。备注：数据库设计规范、数据编程规范、数据库物理设计规范中以Oracle数据库为例，其它结构的数据库类似。二、 数据库设计流程以规范性设计为例，把数据库设计流程分为以下几个阶段。(一) 需求分析阶段1. 需求收集和分析，得到数据字典描述的数据需求和数据流图描述的处理需求。2. 需求分析

4、的重点：调查、收集与分析用户在数据管理中的信息要求、处理要求、安全性与完整性要求。3. 需求分析的方法：调查组织机构情况、各部门的业务活动情况、协助用户明确对新系统的各种要求、确定新系统的边界。4. 常用的调查方法有： 跟班作业、开调查会、请专人介绍、询问、设计调查表请用户填写、查阅记录。5. 分析和表达用户需求的方法：主要包括自顶向下和自底向上两类方法。采用逐层分解的方式分析系统，并把每一层用数据流图和数据字典描述。6. 数据流图（Data Flow Diagram，DFD）表达了数据和处理过程的关系。系统中的数据则借助数据字典(Data Dictionary，简称DD)来描述。(二) 概念

5、结构设计阶段概念模型用于信息世界的建模。概念模型不依赖于某一个DBMS支持的数据模型。通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型，可以用E-R图表示。概念模型可以转换为计算机上某一DBMS支持的特定数据模型，其特点为：1. 具有较强的语义表达能力，能够方便、直接地表达应用中的各种语义知识。2. 应该简单、清晰、易于用户理解，是用户与数据库设计人员之间进行交流的语言。(三) 逻辑设计阶段将概念结构转换为某个DBMS所支持的数据模型(例如关系模型)，并对其进行优化。将E-R图转换为关系模型实际上就是要将实体、实体的属性和实体之间的联系转化为关系模式,这种转换一般遵循如

6、下原则：1. 一个实体型转换为一个关系模式，实体的属性就是关系的属性；2. 一个多对多的联系转换为一个关系模式。数据模型的优化，确定数据依赖，消除冗余的联系，确定各关系模式分别属于第几范式。确定是否要对它们进行合并或分解。一般来说将关系分解为3NF的标准。(四) 物理设计阶段为逻辑数据模型选取一个最适合应用环境的物理结构(包括存储结构和存取方法)。根据DBMS特点和处理的需要，进行物理存储安排，设计索引，形成数据库内模式。(五) 数据库实施阶段运用DBMS提供的数据语言(例如SQL)及其宿主语言(例如C)，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。

7、(六) 数据库运行维护阶段在数据库系统运行过程中必须不断地对其进行评价、调整与修改。内容包括：数据库的转储和恢复、数据库的安全性、完整性控制、数据库性能的监督、分析和改进、数据库的重组织和重构造。为加快数据库设计速度，目前有很多数据库辅助工具(CASE工具)，如Rational公司的Rational Rose，CA公司的Erwin和Bpwin，Sybase公司的PowerDesigner以及Oracle公司的Oracle Designer等。三、 数据库设计规范(一) 数据库规范化的总体要求1. 数据表中避免可空列虽然表中允许空列，但空字段为一种特殊的数据类型，数据库将对其进行特殊的处理，为此

8、将增加数据库处理记录的复杂性，且当表中存在较多空字段时，在同等条件下，数据库处理的性能将降低许多。因此，在数据库表设计时应尽量避免。若确实需要，可通过一些折中方式处理，让其对数据库性能的影响降低至最少。在此推荐两个方式。A 设置默认值。默认值设置原则为业务启动后不可能达到的值，或者为原始状态的对应值。如无法从数值上区分是否为原始状态则不推荐设置默认值。以数值型字段为例，假设其用于存放采集终端所获取的子舱位订座，由于订座数默认至少为大于等于0的数值，为此可设置默认值为-1。另一个例子，字符型的航班状态，默认情况下航班为正常执行的正班，当因为天气、机械或其它调控等原因状态才发生改变，此时可设置默认

9、值为“正班”。日期型数据可默认为1900-01-01，在应用层面显示需要根据实际情况进行转换。B 第二种方式是建立副表。这是当一张表中允许为空的列较多时，如接近表全部列数的三分之一，且这些列数据在大多情况下均为空，此时建议另外建立一张副表，以保存这些列；主表与副表之间根据关键字关联，这样将数据分别存储于两个独立的表中使得主表设计更为简单，既保证了数据库性能，又能满足存储空值的应用需要。如果字段默认值设置导致部分业务需求无法满足或开发代价大量增加，此时不建议设置，保留可空。如数值型字段，如在该字段上需要与其他字段进行组合运算、或基于该字段进行统计汇总时，空值具有不可替代的重要作用。2. 数据表中

10、不应存在重复数据值或列如客户数据与客户经理数据存储问题，如将二者放在同一张表中，为解决多个客户经理问题，表中必须设置首选客户经理、备选客户经理相关信息。在客户经理离职后，必须修改表中所有相关数据，造成很多不便且不利于追踪。为此，在数据库设计的时候要尽量避免现象发生，建议改变策略，将客户信息存入一张表，客户经理信息存入一张表，而客户与客户经理之间的联系关系存入第三张表。当联系关系发生变化时，仅需设置过期日期或状态即可。3. 数据表主键设置为无意义数值序列号在进行数据库表设计的时候，采用一个无意义的、数值型的PKID对行记录进行唯一的标识，而不是通过航班号、航段、姓名、工号等具有业务意义的字段区分

11、记录。由于人工管理下很难保证PKID值的不重复，为此建议每个表对应的PKID列的值由数据库自动管理。建议在原有业务上需要唯一区分的字段或字段组合上，建立唯一性索引。以存储10年的航班计划表为例，业务上唯一区分一个航班的通常包含三个字段，航班号、航段、起飞日期，先前的做法是在这三个上建立复合主键。在此推荐用一个Number（8）的PKID列作为主键，原因是以每天600个航班、一年365天，10年总计219万个航班，考虑到业务的扩展速度为10年内翻三番，657万，按理可设置7位，考虑到其它可能损耗的ID值，多设置一位，故为Number(8)；在航班数据的增删改过程中，通常需要判断是否存在重复数据，

12、是则为修改操作，否则为插入操作，此时仅以PKID列无法区分，为此建议在航班号、航段、起飞日期上建立唯一性索引，由数据库后台执行一致性校验。利用Sequence生成的PKID若需要用于其他数据库使用，需要在应用层面记录，以防止数据库出现问题时恢复后sequence不匹配问题。4. 数据库对象前缀名统一一个应用系统对应的数据库表、视图、过程等对象，通常均高达千计，为对其进行规范管理与快速定位，要求遵循数据对象命名规范。具体命名规范详见下文第（二）点。5. 数据表上避免设置外键 外键的作用在于两方面，一是保证主从表数据一致性，这是数据库提供的、用于保证数据质量的强制性手段，二是定义主表数据删除时从表

13、数据的级联删除方式；二者是外键的优势所在，减少了开发人员不分工作量。但是，外键的存在同样带来困扰。一是从应用系统的三层架构上考虑，如何设置外键关系到业务逻辑，这是将业务逻辑层部分功能后移至数据库层，导致各层职责不清的紧耦合现象，而且当业务逻辑随着需求发展发生变化将引起数据库层面与应用层面的双重修改；二是在开发过程中，开发人员不得不了解数据库物理设计细节，在项目协作分工上造成不便；三是从后续主数据、各业务方向公共数据建设上，为数据库管理员在执行数据表从“公有区”移入移出操作增加难度。为此，建议不设置外键；如果设置了外键，则仅保留数据一致性校验功能，不建议设置级联删除的选项。此时要求开发人员通过培

14、养一种良好的编程习惯，从程序逻辑上实现主表、从表数据的增、删、改、查的规范操作。具体可参考如下说明。A. 从表数据增加操作首先从主表上查询是否存在所需数据，是则可进行增加操作；否则需先增加主表数据后才可进行从表数据增加操作。B. 从表数据删除操作直接删除从表数据。当主表数据未被任何从表引用时，根据实际业务需要判断是否级联删除主表数据。C. 从表数据修改操作从表数据修改后的内容必须是主表内存在的数据，否则需先增加主表数据后才可进行数据修改操作。D. 主表数据增加操作直接数据数据，与从表数据无关。E. 主表数据删除操作删除之前判断是否存在引用的从表数据，是则根据业务规则决定是否继续，并根据业务规则

15、判断从表引用数据的处理方式为对应数据项置空或删除从表数据记录。如果未存在引用的从表数据，则可直接删除。注意保持事务的原子性，即主、从表数据同时提交或同时回滚。F. 主表数据修改操作判断是否存在引用的从表数据，是则首先修改从表数据，然后进行主表数据修改。注意保持事务的原子性，即主、从表数据同时提交或同时回滚。为了在应用程序上能顺利完成上述六项操作，开发人员除了严格记录主从表调用关系外，还必须在程序上实现数据一致性检查功能，以及时发现可能的数据不一致现象。如果应用程序未能实现这种一致性检查工作，则需将检查规则提交予DBA，由DBA在后台进行定期检查。6. 避免在数据库上编写存储过程、代码包、触发器

16、通常存储过程、代码包、触发器包含很多业务逻辑，违背了三层架构设计的松耦合原则。(二) 数据对象命名规范1. 命名规范总要求所有数据对象命名必须遵循如下规范要求。（1） 通常采用“望文知义”的形式命名，部分对安全具有特殊要求的可以编号命名。（2） 命名格式是，以字母打头，包含具有特定含义的一个或多个英文单词，单词之间以下划线“_”间隔，长度不超过30个字符；（3） 大小写不敏感。（4） 禁止使用关键字命名。（5） 不同数据对象命名的前缀要求如下表所示。编号数据对象前缀要求1非临时数据表“4位模块名_表名”。 2临时数据表“TMP_表名”3分区表“PT_”4主键“PK_表名”5索引“IDX_”6函

17、数索引“FIDX_”7视图“VW_”8实体化视图“MV_”9存储过程“SP_”10触发器“TRG_”11函数“SF_”12数据包“PKG_”13序列“SEQ_”14数据类型“TYP_”15后台任务“JB_”2. 表命名规范对于同一应用，在命名上区分不同功能模块的所使用的表。假设一个系统包含三个模块，基础维护、查询、系统管理，则基础维护表名前缀为“TB_BASE_”，查询模块表名前缀为“TB_QURY_”，后面加上具体的表名。对于一些特定作用的表，增加特定后缀表示。如历史航班表可命名为TB_FLIGHT_HISTORY，复杂计算中间过程所产生的临时数据表可命名为TB_MIDTERM_DATA_T

18、EMP。3. 字段命名规范字段命名规范符合命名规范总要求。字段类型选择满足（1） 固定长度的字符串类型采用CHAR，非固定长度的字串类型采用VARCHAR2。（2） 日期型字段采用日期类型。（3） 数字型字段采用NUMBER类型，并标明长度与小数位数。（4） 如无特殊需要，避免使用大字段(BLOB，CLOB，LONG等)。（5） 相同业务字段出现在不同表中，使用相同命名，且保证类型和长度一致。4. 索引命名规范A. 复合索引至少包含前两个字段的缩写或全名。B. 在表上创建主键时，必须显式指定主键唯一性约束名称。举例：为表TB_FLIGHT创建主键，正确格式为“alter table TB_FL

19、IGHT add constraint PK_FLIGHT primary key (FLIGHT_ID) ”，语句中指定了主键的唯一性约束名为PK_FLIGHT。而错误的格式：“alter table TB_FLIGHT add primary key (FLIGHT_ID)”此中，主键唯一性约束将被系统随机分配一个名字。 5. 分区命名规范采用可容易判断各分区内所存数据内容的名字，如对表TB_FLIGHT按季度进行分区，则各分区命名格式为“PT_ XXXXQN”，其中XXXX为年份，N为1至4的季度编号。6. 触发器命名规范命名规则：trg_表名_<before/after>_

20、<ins/upd/del>。如表TB_FLIGHT行插入后的触发器名字为,TRG_TB_FLIGHT_AFTER_INS。(三) 数据库设计说明书格式数据库设计说明书的格式至少包含如下部分。1. 概述：从整体上说明数据库设计思想、采用的方法、作用范围、参考的文献、文档结构组成等。2. 数据表清单编号表英文名表中文名称表描述使用的功能模块1表12表23. ER关系描述描述表之间存在的主外键联系等。4. 数据表定义1) 表1字段编号字段名称中文描述数据类型Null标志是否主键外键是否索引备注12备注数据量增长说明、外键关系说明等2) 表25. 各系统相关的其它信息。要求在“数据表清单”

21、每一行的表名上定义“超链接”指向“数据表定义”中的对应内容，以方便地在文档中浏览跳转等。四、 数据库编程规范(一) 书写规范1. 关键字大写，其他代码统一使用小写。2. 确保变量和参数在类型和长度上与表数据列类型和长度相匹配。建议采用如下格式：vs_flightno tb_flight.flight_no%TYPE这样当TB_FLIGHT表的FLIGHT_NO字段定义发生变化时程序无需调整。3. 参数和变量命名前缀遵循如下规范：项目前缀要求传入参数“ip_”传出参数“op_”数值型变量“vn_”字符型变量“vs_”日期型变量“vd_”4. 程序块中的begin、end独立成行。5. 程序块采用

22、缩进风格书写，保证代码清晰易读，缩进格数统一。6. 一行仅写一条语句。7. 同一语句占用多行时，每行的第一个关键字左对齐。8. 对于 Insert values 和 update 语句，一行写一个字段，字段后面紧跟注释（注释语句左对齐），values 和 insert 左对齐，左括号和右括号与 insert、values 左对齐例：insert into table_name(user_id, -用户 ID，主键user_name,-用户名login_name -登录名)values( v_user_id,v_user_name,v_login_name）9. 相对独立的程序块之间增加一空行。

23、10. 超过110列的语句要分行书写，长表达式应在低优先级操作符处换行，操作符或关键字放在新行之首。(二) 注释规范1. 以统一格式在头部对代码进行注释，以说明代码所要完成功能、传入参数、传出参数、作者、创建时间，并在其后从修改日期、修改者、修改内容角度描述各次修改历史。/*功能描述：-传入参数：-传出参数：-返回结果：-作者：-创建日期：-修改历史：- （1）YYYY-MM-DD：作者1，修改内容1； （2）YYYY-MM-DD：作者2，修改内容2；*/2. 在所有变量定义的右侧或上方，进行注释以说明变量的用途与含义。3. 注释内容清晰、明了，并确保无二义性。4. 对每一程序分支书写注释。5

24、. 在代码的功能分支、子层次上注释，以帮助维护人员理解代码。例：case vs_type1 when 1 then -里程奖励 . . when 2 then -里程扣除 . . when 3 then -里程兑换 . .end case;6. 代码注释应放在描述的代码上方或右方相近位置，不可放在下面。7. 注释与所描述的内容进行字数的缩进排列。8. 在函数中，对所返回的代码进行详细描述。9. 在程序块的结束行右方加注释，以表示程序块结束。(三) 语法规范1. 存储过程的In、out参数应按类别分开书写，不要交叉。2. 存储过程中变量的声明应集中在is和begin关键字之间申明3. 尽可能使用

25、相关表字段类型来定义相关的变量，如%type，%rowtype。4. 删除无用的变量与参数。5. 存储过程中存在多分支时，若有事务控制，需确保各分支均有事务结束处理，且发生异常时，必须Exception中进行异常捕获。(四) SQL性能规范1. 避免在批量操作时频繁使用commit频繁的COMMIT会导致物理I/O增大，同时会产生log file sync等待，但长时间不提交将带来更多的性能问题。建议小于3秒的事务可以一次提交，大于3秒的操作尽可能3秒左右提交一次。实际应用中使用COMMIT时必须保证事务的完整性。2. 使用动态 sql时，需绑定变量，而非实际值。为了不重复解析相同的SQL语句

26、，在第一次解析之后，ORACLE将SQL语句缓存在内存中，当你执行一个SQL语句时，如果它和之前的执行过的语句完全相同，ORACLE就能很快获得内存中相同语句的执行计划，然后就直接执行该语句，而不需要进行解析。如果没有使用绑定变量，虽然语句结构相同，但因传入的变量不一样导致语句不相同，ORACLE还是需要对每条结构相同的语句重新解析一遍，同时在内存中缓存该语句，导致共享池中存放了N条结构相同的语句，既达不到共享的目的，又浪费了大量的内存空间，如果语句执行的太频繁，会导致数据库无法申请共享内存而出错。3. 避免不必要的排序对查询结果进行排序会大大的降低系统的性能4. 用WHERE子句替换HAVI

27、NG子句例如：SELECT NAME，SUM(AGE) FROM EMPLOYEE GROUP BY NAME HAVING NAME！=ABC 修改为以下语句效果更好 SELECT NAME，SUM(AGE) FROM EMPLOYEE WHERE NAME！=ABC GROUP BY NAME 5. 用“>=”替代“>”如：在ID列上建有索引，则语句SELECT * FROM EMPLOYEE WHERE ID >= 9 要比语句SELECT * FROM EMPLOYEE WHERE ID > 8高效。这是由于前者DBMS将直接跳到第一个ID等于9的记录而后者将首

28、先定位到8的记录并且向前扫描到第一个DEPT大于8的记录。6. 删除表中所有记录时用TRUNCATE替代DELETE当删除表中的记录时，在通常情况下，回滚段用来存放可以被恢复的信息，如果你没有COMMIT事务，ORACLE可以将数据恢复到删除之前的状态；而当运用TRUNCATE时，回滚段不存放任何可被用于恢复的信息，当命令运行后，数据不能被恢复,因此很少的资源被调用，执行时间也会很短,空间立即释放，detele操作后的空间可以被重新利用，但不会释放。7. 用UNION-ALL 代替UNION说明：UNION-ALL不过虑重复记录，UNION 过滤重复记录，所以需要先排序。如果不需要过滤重复的记

29、录，UNION-ALL 比UNION 性能更好。8. 用(NOT) EXISTS替代(NOT) IN。在许多基于驱动表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。例如：SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')没有以下语句高效SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT '

30、;X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')9. 使用DECODE函数来减少处理时间。使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例：SELECT NAME|UNDER AGEFROM EMPLOYEE WHERE AGE<18 UNIONSELECT NAME|OVER AGEFROM EMPLOYEE WHERE AGE>=18可以改成：SELECT DECODE(SIGN(AGE-18),-1,NAME|UNDERAGE，1，NAME|OVERAGE)F

31、ROM EMPLOYEE10. 使用表的别名(Alias)。当在SQL语句中连接多个表时，应使用表的别名并把别名前缀于每个列上，这样可以减少解析的时间，并减少那些由Column歧义引起的语法错误。11. 尽量减少对表的查询次数。说明：在含有子查询的SQL语句中，要特别注意减少对表的查询。例：UPDATE EMP SET EMP_CAT =(SELECT MAX(CTEGORY) FROM EMP_CATEGORIES),SAL_RANGE =(SELECT MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT=0020改写成下面这样会更高效:UPDA

32、TE EMP SET EMP_CAT, SAL_RANGE = (SELECT MAX(CATEGORY),MAX(SAL_RANGE)FROM EMP_CATEGORIES) WHERE EMP_DEPT = 002012. 用表连接替换EXISTS。例： SELECT ENAME FROM EMP E WHERE EXISTS (SELECT 'X' FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = 'A');改写成下面这样会更高效:SELECT ENAME FROM DEPT D, EMP E WHERE

33、 E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = 'A'13. 避免使用DISTINCT，可以用EXISTS替换DISTINCT。当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换例:SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO = E.DEPT_NO改写成下面这样会更高效:SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (

34、SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。14. 避免使用耗费资源的操作带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY，GROUP BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能,如果可能，应尽量避免使用或改写。15. 避免对索引列使用数据库函数、计算表达式等等对查询索引列使用数据库函数、计算表达式会造成对索引失效例：select t.reserver1, count(*

35、) coutfrom custcare.password_login_fail twhere t.operating_source = 1 and to_char(t.login_time,'yyyymmdd') between 20080904 and 20080918group by t.reserver1having count(*) > 30custcare.password_login_fail表的login_time列上建了索引，上面语句to_char函数会造成索引失效，把and to_char(t.login_time,'yyyymmdd')

36、 between 20080904 and 20080918修改成：and t.login_time between to_date('20080904','yyyymmdd') and to_date('20080918','yyyymmdd')可以正确索引。查询时尽可能将操作移至等式的右边，甚至去掉函数。16. 在查询条件中，避免不必要的类型转换。字符类型列赋予数值型数据时会使用隐含类型转换，而数值型列赋予字符型数据时不会隐含类型转换，而是将右边值转换为数值类型。例：select * from atp_para_cfg whe

37、re cfg_type=35cfg_type是字符型列，上面语句会造成隐含类型转换to_number(cfg_type)=35，即Oracle内部会做一次类型转换。17. 尽量避免字段与“NULL”比较索引并不存储null值，使用NULL作为条件将不会使用索引。例：select * from staff_memberwhere address is null；（或not null）该语句将不会使用索引18. 在索引列上使用<>(!=)和 like 将不会使用索引。例：select * from staff_member where dept_no<>2001;selec

38、t * from staff_member where first_name like %DON; 以上语句都不会用到索引注:like语句改为 select * from staff_member where first_name like DON%;当前面没有%时，将使用索引，ORACLE内部转换为范围操作19. 用Case语句合并多重扫描例： select count(*) from emp where sal<1000;select count(*) from emp where sal between 1000 and 5000;select count(*) from emp

39、where sal>5000;这样我们需要进行三次全表查询，但是如果我们使用case语句：select count (sale when sal <1000then 1 else null end) count_poor,count (sale when between 1000 and 5000then 1 else null end) count_blue_collar,count (sale when sal >5000then 1 else null end) count_poorfrom emp;这样查询的结果一样，但是执行计划只进行了一次全表查询。(五) JOB使

40、用规范1. Job的执行时间尽量避开业务高峰；2. 如果有多个占用资源较多的job任务，考虑将它们执行的时间错开；3. 禁止在job运行期间对其调用的存储过程进行修改和编译。4. 在Oracle10g之后，利用dbms_schedule建立Job。(六) 索引使用规范1. 索引创建原则（1） 表的主键、外键必须有索引；（2） 数据量超过300条的表应该有索引；（3） 经常与其他表进行连接的表，在连接字段上应该建立索引；（4） 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；（5） 索引应该建在选择性高的字段上；（6） 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建

41、索引；（7） 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：Ø 正确选择复合索引中的主列字段，一般是选择性较好的字段；Ø 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；Ø 如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；Ø 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；（8） 频繁进行数据增删改操作的表，不要建立太多的索引；（9） 删除无用的索引，避免对执行计划造成负面影响；2. 索引使

42、用建议（1） 避免对条件字段进行操作对条件字段的操作会导致该列的索引失效，这里所谓的操作包括数据库函数、计算表达式等。如以下3个sql，其条件列上都建有索引，但不会被使用：select * from record where substrb(CardNo,1,4)='5378' -(13秒) select * from record where amount/30< 1000 -（11秒） select * from record where to_char(ActionTime,'yyyymmdd')='19991201' -（10秒）

43、可改为如下形式：select * from record where CardNo like '5378%' -（< 1秒）select * from record where amount < 1000*30 -（< 1秒）select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd') -（< 1秒）（2） 避免隐式转换对于条件值书写不规范的，oracle会自动对条件列进行隐式转换，以完成比较，但同时也会造成索引失效。如将字符型数据与数值型数

44、据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致全表扫描。例：表tab1中的列col1是字符型（char)，则以下语句存在类型转换：select col1,col2 from tab1 where col1>10，应该写为： select col1,col2 from tab1 where col1>'10'。（3） 尽量避免 “<>”尽量去掉 "<>"，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为"OR"方式。例：UPDATE SERVICEINFO SET

45、 STATE=0 WHERE STATE<>0;以上语句由于其中包含了"<>"，执行计划中用了全表扫描（TABLE ACCESS FULL），没有用到state字段上的索引。实际应用中，由于业务逻辑的限制，字段state为枚举值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉"<>"，利用索引来提高效率。修改为：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。（4） 避免Where子句中的IS NULL和IS NOT NULLWhe

46、re字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where子句中的IS NULL和IS NOT NULL。（5） 数据分布不均匀列的索引使用当列值选择性不高，但查询返回值恰好是返回较少的数值，则应创建索引。例：表ServiceInfo中数据量很大，有一百万行，其中有一个字段DisposalCourseFlag，取值范围为枚举值：0，1，2，3，4，5，6，7。按照前面说的索引建立的规则，“选择性不高的字段不应该建立索引，该字段只有8种取值，索引值的重复率很高，索引选择性明显很低。然而，由于该字段上数据值的分布情况

47、非常特殊，具体如下表：取值范围 15 6 7占总数据量的百分比 1% 98% 1%而且，常用的查询中，查询DisposalCourseFlag<6 的情况既多又频繁，毫无疑问，如果能够建立索引，并且被应用，那么将大大提高这种情况的查询效率。（6） 需要时利用HINT强制指定索引对于ORACLE优化器无法用上合理索引的情况下，一方面可以单独对该字段或该表用analyze语句进行分析，对该列搜集足够的统计数据，使ORACLE在查询选择性较高的值时能用上索引；另一方面，可以利用HINT提示，在SELECT关键字后面，加上“/*+ INDEX(表名称，索引名称）*/”的方式，强制ORACLE优化

48、器用上该索引。例：select /*+ index(mms_cdr IDX_MMS_USERID)*/ * FROM mms_cdr WHERE (month_number >= 6 and month_number <= 7) AND user_id = to_number('1419200000741621') AND send_time >= '20070101000000' AND send_time <= '20070104235959' ORDER BY send_time;当user_id和send_time

49、列上均有索引的情况下使用user_id列上的索引更高效，因为user_id列上的索引选择性更高，但是由于统计信息不全，优化器可能选择使用全表扫描或者走send_time列上的索引，导致执行计划不是最优，此时可以使用强制索引来解决。（7） 屏蔽无用索引如果了解到条件字段建有索引，但返回值较多，如果走索引的话效率更低，则可通过特殊方法屏蔽掉索引的使用。如字段为数值型的就在表达式的字段名后，添加“+ 0”，为字符型的就并上空串：“|""”例：select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo =

50、 '36'（8） like子句尽量前端匹配当like字句前段为通配符，会导致索引失效。如：select * from city where name like %S%; -该语句执行全表扫描修改为：select * from city where name like S% ; -会正确使用索引（9） 使用基于函数的索引 对条件列的操作可以使索引失效，但如果根据操作创建相应索引，则可以被使用。如：select * from emp where substr(ename,1,2)=SM;在ename列上的索引将不会被使用，如果创建基于函数的索引，即：create index emp

51、_ename_substr on eemp ( substr(ename,1,2) );则在查询的时候可正确使用到索引。使用函数索引有如下限制：Ø 无法索引LOB字段Ø 无法索引嵌套表字段Ø 表达式不允许有分组函数，如SUM，AVG等Ø 无法使用基于规则的优化器Ø 无法储存空值Ø 对于一个输入，要求函数返回同一值，即确定性Ø 当函数改变时，要重建函数索引（10） 使用分区索引在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关

52、的数据分区。例如，假设你已经定义了一个分区索引，并且某个SQL语句需要在一个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索引分区，而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引，所以提高了查询的速度。但是当查询跨多个分区，涉及到比较大小时，考虑使用全局索引。（11） 使用位图索引当列值的选择性非常低时，比如性别字段只有男与女两个值，可以考虑使用位图索引。位图索引占用空间非常小，效率较高。位图索引适合于不会变化的表，如果dml操作频繁，则极易产生大面积锁。（12） 使用复合索引当几个字段经常同时以 and 方式出现在 where 子句中，可以建立复合索引；将最

53、常用的列放在索引最前面；复合索引字段不宜过多，尽量在3个以内。（13） 少数情况下用全表扫描替代索引扫描在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于10的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于40时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：Ø 对于原始排序的表：仅读取少于表记录数40的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的40的查询应该使用全表扫描。

54、Ø 对于未排序的表：仅读取少于表记录数7的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的7的查询应该使用全表扫描。(七) 分区表使用规范当表的数据量达到1G或者1000万行时，考虑将表分区；分区键的选择参考业务查询要求，通常是时间字段，以range方式来划分。分区间隔也要看业务需要，比如单次查询总是以天为单位，则按天分区，如果总是以小时为单位，则按小时分区，但并非分得越细越好；如果插入数据所需要的分区不存在，则插入失败，所以要制定分区维护策略。通常有两种方式，一种是提前划分，比如把一年的分区一次性划分出来，用完时再划一年，另一种是编写分区维护存储过程自动添加或删除分区。五、 数据库物理设计规范(一) 环境配置数据库环境包含生产、开发、测试三种，各种环境之间的配置原则为：1. 生产、开发环境分离；2. 生产、开发职责分离；3. 由于一个业务方向配置一套生产库集群，不同业务方向数据交互通过中间件平台实现，不直接使用DBLlink访问。4. 一个生产应用对应一个开发环境。5. 测试环境根据实际需要部署。各种环境的硬件需求（CPU频率，内存容量，磁盘容量等）及软件需求（操作系统版本及内核参数，交换分区大小，用户权限等）详见可参考业务实际需要及Oracle在各操作系统