Oralce数据库设计规范V3

一个优秀的数据库设计，应该是满足需求的前提下，实现性能最优的综合考虑，因此，数据库表的设计都应经过 2 个阶段：逻辑设计阶段（将需求转化为数据库表达的方式），物理设计阶段（对逻辑设计的结果物理调优设计）。

1.表规范

1.1 逻辑设计方法及规范

1.1.1 采用 ER 模型进行逻辑设计

实体关系模型（ER 模型，以下简称 ER 模型）是人们认识客观世界的一种方法、工具。ER 模型的设计过程，基本上是两大步：

l 先设计实体类型（此时不要涉及到“联系”）；

l 再设计联系类型（考虑实体间的联系）。

具体设计时，有时“实体”与“联系”两者之间的界线是模糊的。数据库设计者的任务就是要把现实世界中的数据以及数据间的联系抽象出来，用“实体”与“联系”来表示。E-R 模型还描述了数据库对其内容必须遵守的某些约束，一个重要概念是映射基数，它表示通过某个联系集与另外一个实体的关联的实体数目，包括：一对一，一对多，多对一，多对多。另外， ER 模型应该充分反映用户需求，ER 模型要得到用户的认可才能确定下来。

建议采用主流的模型设计软件工具 PowerDesigner，ERWin 等进行数据库设计。

1.1.2 首先遵循范式设计

在设计前，要考虑表的规范化级别，即要求所有的关系模式都达到第几范式。由于函数依赖是现实环境中最重要的、最大量的数据依赖，一般提出 3NF 或 BCNF 的要求就足够了。简单来说，第三范式以上的设计消除了字段的冗余，能有效避免出现数据变化时容易产生的不一致的情况，设计范式定义如下：

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再

分。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

实体表中一般不会出现违反 2NF 的情况，因为都是“一个”主键列，而关系表是两个以上列的“复合”主键，故而关系表容易出现违反 2NF 的情况。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则 C 传递函数依赖于 A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段 x → 非关键字段 y

违反 3NF 的情况，绝大多数是在含有外键的表中

鲍依斯-科得范式（BCNF）：在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合 BCNF 范式。

当处理表间的关联约束所付出的代价（常常是使用性上的代价）超过了保证不会出现修改、删除、更改异常所付出的代价，并且数据冗余也不是主要的问题时，表设计可以不符合四个范式。四个范式确保了不会出现异常，但也可能由此导致过于纯洁的设计，使得表结构难于使用，所以在设计时需要进行综合判断，但首先确保符合四个范式，然后再进行精化修正是刚刚进入数据库设计领域时可以采用的最好办法。

1.1.3 根据实际需要进行反规范化设计

数据库规范化设计目前占数据库设计主导地位，规范化设计达到了数据存储空间最佳利用和控制数据冗余，但是也带来了查询性能的降低，因此在适当的条件下，对数据库进行反规范化设计也是必要的。

反规范化设计通过增加数据冗余，数据分片等策略来改进原有规范设计，以达到改善数据库性能的目的。

反规范化设计的几个原则：

1）确信对系统的逻辑设计有全面的了解

使系统设计人员清楚，当改变数据库设计时，会对其它部分造成哪些影响。

2）尽量采取索引、数据存储等技术来提升数据库性能

分析系统的数据量，对于数据量不大，响应时间不高的尽量通过数据库本身技术来提升性能。

3）数据完整性维护

使用反规范带来最大的问题使数据完整性，在程序设计中充分考虑到数据库事务的处理，增删改查保证数据库操作一致性；完整性约束还可以通过批处理及触发器实现（不建议）。

反规范化设计的几个方法

1）增加冗余列

在表中增加经常需要访问而频繁访问其它表的列，导致多个表有相同的列，即冗余列。常用在两个地方：（1）关系表中的冗余：在关系表中增加相关实体表的相关属性，以达到关连查询时减少表的关联数量的目的（2）层次关系中的冗余：在多层次的子父表关系中，将父表的属性存储在“子表”或者“孙子表”或者“重孙表”中。

2）数据分片

主要有三种方式：水平分片，垂直分片，混合分片

水平分片：是指按一定规则，将数据库所有行划分成若干不相交的子集，即多个表。在数据库比较庞大的情况下，通常在以下几个情况下使用：一是数据访问频率差别极大时候，可以将经常访问的数据放在一个表中，不常访问的放在另外一个表，比如历史表；一个是数据表中数据本身有独立性，如状态表中状态字段。

垂直分片：是指把一个数据库属性集分成若干子集，在每个子集上做投影运算，每个投影称为垂直分片。比如可以将一个表中主键和经常访问的列放在一个表中，主键和不常访问的放在另一表中。优点是查询是可以直接查找经常访问的表，减少了 IO 操作，缺点是查询所有数据时需要多表连接。

混合分片：实际应用中，根据需要将水平分片及垂直分片结合使用的做法。

2.1基本规范

2.1.1长度规范

凡是需要命名的对象其标识符均不能超过30个字符，也即：Oracle中的表名、字段名，函数名，过程名，触发器名，序列名，视图名的长度均不能超过30个字符；

2.1.2构成规范

数据库各种名称必须以字母开头，但严禁使用SYS开头；名称只能含有字母，数字和下划线“_”三类字符，“_”用于间隔名称中的各语义字段；不要使用DUAL作表名；

2.1.3大小写规范

构成Oracle数据库中的各种名称（表明，字段名，过程名，视图名等等）的所有字符，必须使用大写，也就是不能在脚本中，对任何名称添加双引号“”来设定字符的大小写形式，只要不采用“”限制，Oracle自动会将各名称转化成大写。

2.1.4主键规范

主键设计应遵循以下规范：1、除临时表和外部表，以及流水表，日志表外，其他表都要建立主键；2、主键不能使用含有实际语义的列，应该增加一个xx_id字段做主键，类型为number，取值来自序列sequence；3、对于500万以上的表，请数据组参与设计实施，采用先建唯一索引再添加主键约束的方式来创建主键；

2.1.5要有注释

每个表，每个字段都要有注释，说明其含义，对于冗余字段还要特别说明其维护方法，外键字段说明参照与那个表

2.1.6一个表所含字段总长度的规范

一个表中的所有字段，应当能存储在一个数据块中（BLOCK），也即：表的单行字段总长度<db_block(减去pctfree)

2.2表设计规范

2.2.1一般需要有主键

主键最好设计成单一主键,尽量不用复合主键,尽量使用没有业务语义的字段作为主键，建议使用系统生成的键作为主键。例如:oracle的sequence来维护一个主键。主键一般建议使用数值型，这样提高检索效率。

2.2.2尽量规划在同一表空间

对于每个应用系统都能用到的表放到同一个表空间中，一般是系统配置表。如果应用系统还包括一些大数据量的图片等多媒体数据表，应该放入第三个库中，这样可改善数据库的性能。

2.2.3大表尽量要有明确的数据保留策略

在设计阶段

应与需求人员沟通，明确业务字段的生命周期，并与本单位的数据生命周期管理向匹配，制定表的数据迁移、保留策略。

在表的物理设计阶段

每个表中都应该添加的3个有用的字段：记录创建日期，记录创建/更新人，记录的版本标记；有助于准确说明记录中出现null数据或者丢失数据的原因。

2.2.4因大表坚决不允许有触发器

触发器的功能通常可以用其他方式实现。在调试程序时触发器可能成为干扰。假如你确实需要采用触发器，你最好集中对它文档化。

2.2.5包含版本机制

建议你在数据库中引入版本控制机制来确定使用中的数据库的版本。无论如何你都要实现这一要求。时间一长，用户的需求总是会改变的。最终可能会要求修改数据库结构。虽然你可以通过检查新字段或者索引来确定数据库结构的版本，但把版本信息直接存放到数据库中会更方便。

2.2.6中间表

在通过ER模型设计表时，中间表是针对多对多关系的。

一般中间表都是有一个本表的自增主键，还有另外两个表的主键。中间表是没有属性的因为它不是一个基本表。

2.3分区设计规范

分区表设计总体原则：

每个表必须包含以下属性参数: TABLESPACE，PCTUSED，

STORAGE。根据表的不同属性进行表类型设计：

分区表：

1.对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能。

2.如果表按某些字段进行增长，则采用按字段值范围进行范围

分区。

3.如果表按某个字段的几个关键值进行分布，则采用列表分区。

4.对于静态表，则采用hash分区或列表分区。

5.在范围分区中，如果数据按某关键字段均衡分布，则采用子分区的复合分区方法。

聚簇表：如果某几个静态表关系比较密切，则可以采用聚蔟表的

方法。

2.3.1大数据量的表需进行分区化

当表的数据量超过500万，需要设计成分区表。

2.3.2 RANGE分区规范

SQL常依据某列的范围访问表，则对表使用RNAGE分区，常见情况是SQL根据时间范围进行查新，则使用时间字段作为分区关键字进行RANGE分区；将对表的多种访问结合考虑来确定分区的细度：1.大多数SQL操作的分区关键字值的范围；2.数据维护的需要，比如以月为单位删除历史数据；3.数据访问的性能，以操作范围确定的分区数据量还是过大，比如大于500万，则还需要进行细分；4.一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来看，每个分区10万数据量的情况比每个分区20万数据量的情况要快很多，所以需要灵活掌握；

2.3.3 LIST分区的规范

SQL常居于某列的散列值访问表，则对表使用LIST分区，LIST分区不支持多列分区关键字；常见情况针对某个地区或者某个业务进行数据访问，那么就使用地区编号或者业务编号作为分区关键字；将对表的多种访问结合考虑来确定分区的细度：

1.一般使用一个分区关键字的值来划定一个分区；

2.可以把分区关键字的值相对应数据比较少的几个分区合并作一个分区；

3.如果一个分区关键字值所对应的数据量过大，比如大于500万，则应该对表采用RANGE分区，对该值的分区再采用HASH子分区；也就是说，一个可以采用LIST分区的表，肯定可以转化成RANGE分区（可带子分区），反之不然；

4.一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来看，每个分区10万数据量分区方法比每个分区20万数据量的分区方法要快很多，所以需要灵活掌握。

2.3.4 HASH分区的规范

SQL访问表不按照某列的范围进行，也不按某列离散值进行，而且对该表的数据不会依据某列的值范围或者离散值进行定期维护，那么使用HASH分区；HASH分区是不知道应该选择何种分区时的选择；HASH分区的各分区都可能存有各种情况的数据，故而不能用于依据分区清理数据的情况；对确定分区细度的考虑：

1.依据分区的数据量规划和表的最大数据量来确定分区数；

2.一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来看，每个分区10万数据量分区方法比每个分区20万数据量的分区方法要快很多，所以需要灵活掌握。

2.4列设计及命名规范

2.4.1采用有意义的字段名

尽量采用有意义的字段名，使描述尽可能清楚，如采用缩写，尽量使用通用的缩写语言，如addr代表address，避免出现只有自己理解的缩写。

2.4.2应增加更新日期字段

时效性数据应包括“最近更新日期/时间”字段。时间标记对查找数据问题的原因、按日期重新处理/重载数据和清除旧数据特别有用。

2.4.3不要有大小写混写的对象名

数据库里不应有大小写混用的对象名，比如CustomerData。这一问题从Access到Oracle数据库都存在。我不喜欢采用这种大小写混用的对象命名方法，结果还不得不手工修改名字。想想看，这种数据库/应用程序能混到采用更强大数据库的那一天吗？采用全部大写而且包含下划符的名字具有更好的可读性（CUSTOMER_DATA），绝对不要在对象名的字符之间留空格。

2.4.4注意不要有保留词

要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突，比如，最近我编写的一个ODBC连接程序里有个表，其中就用了DESC作为说明字段名。后果可想而知！DESC是DESCENDING缩写后的保留词。表里的一个SELECT *语句倒是能用，但我得到的却是一大堆。

2.4.5保持字段名和类型的一致性

在命名字段并为其指定数据类型的时候一定要保证一致性。假如字段在某个表中叫做“agreement_number”，你就别在另一个表里把名字改成“ref1”。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

2.4.6仔细选择数字类型

Oracle数字的3种基本Oracle Number类型，即，NUMBER、BINARY_INTENER与PLS_INTEGER。NUMBER的主要功能是用来

描述相关整数或是实数，但是PLS_INTEGER与BINARY_INTENER只能来描述整数。

NUMBER，是以十进制格式进行存储的，它便于存储，但是在计算上，系统会自动的将它转换成为二进制进行运算的。它的定义方式是NUMBER(P，S)，P是精度，最大38位，S是刻度范围，可在-84127间取值。例如：NUMBER(5，2)可以用来存储表示-999.99999.99间的数值。P、S可以在定义是省略，例如：NUMBER(5)、NUMBER等。

BINARY_INTENER用来描述不存储在数据库中，但是需要用来计算的带符号的整数值。它以2的补码二进制形式表述。循环计数器经常使用这种Oracle Number类型。

PLS_INTEGER和BINARY_INTENER唯一区别是在计算当中发生溢出时，BINARY_INTENER型的变量会被自动指派给一个NUMBER型而不会出错，PLS_INTEGER型的变量将会发生错误。

2.4.7给文本字段留足余量

ID类型的文本字段，比如客户ID或定单号等等都应该设置得比一般想象更大，因为时间不长你多半就会因为要添加额外的字符而难堪不已。比方说，假设你的客户ID为10位数长。那你应该把数据库表字段的长度设为12或者13个字符长。这算浪费空间吗？是有一点，但也没你想象的那么多：一个字段加长3个字符在有1百万条记录，再加上一点索引的情况下才不过让整个数据库多占据3MB的空间。但这额外占据的空间却无需将来重构整个数据库就可以实现数据库规模的增长了。

2.4.8列命名技巧

我们发现，假如你给每个表的列名都采用统一的前缀，那么在编

写SQL表达式的时候会得到大大的简化。这样做也确实有缺点，比如破坏了自动表连接工具的作用，后者把公共列名同某些数据库联系

起来，不过就连这些工具有时不也连接错误嘛。举个简单的例子，假设有两个表：Customer和order。Customer表的前缀是cu_，所以该表内的子段名如下：cu_name_id、cu_surname、cu_initials和cu_address等。order表的前缀是or_，所以子段名是：or_order_id、or_cust_name_id、or_quantity和or_description等。

3.索引规范

3.1用不上分区条件的局部索引不宜建

分区表建立分区索引后，如果在查询应用中无法用到这个分区索引列的条件，索引读将可能遍历所有的分区，如果有100个分区，相当于遍历了100个小索引，将会严重影响性能，此时需要慎重考虑，判断是否需要修改为全局索引。

3.2函数索引大多用于列运算，一般需要避免

从实际应用情况来分析，应用函数索引大多是因为设计阶段考虑步骤，比如trunc(时间列)的写法，往往可以轻易转换成去掉trunc的写法，所以需要取出验证，如下：

SELECTt.index_name,

t.index_type,

t.status,

t.blevel,

t.leaf_blocks

FROM user_indexes t

WHERE index_type in ('FUNCTION-BASED NORMAL');

3.3位图索引遇到更新将是噩梦，需谨慎设计

1.位图索引不适合用在表频繁更新的场合。

2.位图索引不适合在所在列重复度很低的场合。

因为位图索引的应用比较特殊，使用场合比较少，因此有必要取

出系统中的位图索引，进行核对检测，如下：

SELECT

t.index_name,

t.index_type,

t.status,

t.blevel,

t.leaf_blocks

FROM user_indexes t

WHERE index_type in ('BITMAP');

3.4外键未建索引将引发死锁及影响表连接性能

外键未建索引，将有可能导致两个严重的问题：1.更新相关的表产生死锁；2两表关联查询时性能低下，因此设计中需要谨慎考虑。以下为外键未建索引的表的查找语句，以方便我们分析和确认：

SELECT table_name,

constraint_name,

cname1 || nvl2(cname2, ',' || cname2, null) || nvl2(cname3, ',' || cname3, null) || nvl2(cname4, ',' || cname4, null) || nvl2(cname5, ',' || cname5, null) || nvl2(cname6, ',' || cname6, null) || nvl2(cname7, ',' || cname7, null) || nvl2(cname8, ',' || cname8, null) columns

FROM (SELECT b.table_name,

b.constraint_name,

max(decode(position, 1, column_name, null)) cname1, max(decode(position, 2, column_name, null)) cname2, max(decode(position, 3, column_name, null)) cname3, max(decode(position, 4, column_name, null)) cname4, max(decode(position, 5, column_name, null)) cname5, max(decode(position, 6, column_name, null)) cname6, max(decode(position, 7, column_name, null)) cname7, max(decode(position, 8, column_name, null)) cname8, count(*) col_cnt

FROM (SELECT substr(table_name, 1, 30) table_name, substr(constraint_name, 1, 30) constraint_name, substr(column_name, 1, 30) column_name, position

FROM user_cons_columns) a,

user_constraints b

WHERE a.constraint_name = b.constraint_name AND b.constraint_type = 'R'

GROUP by b.table_name, b.constraint_name) cons

WHERE col_cnt> ALL

(SELECT count(*)

FROM user_ind_columns i

WHERE i.table_name = cons.table_name

AND i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8)

AND i.column_position<= cons.col_cnt

GROUP by i.index_name)

3.5建立联合索引需谨慎

3.5.1要结合单列查询考虑、决定前缀

如：即可以建立col1,col2的联合索引，又可以建col2,col1的联合索引，此时如果存在col1列单独查询较多的情况下，一般倾向于

建col1,col2的联合索引。

3.5.2超过4个字段的联系索引需注意

SELECT table_name, index_name, count(*)

FROM user_ind_columns

GROUP by table_name, index_name

having count(*) >= 4

ORDER by count(*) desc

3.5.3范围查询影响组合索引

组合查询中，如果有等值条件和范围条件组合的情况，等值条件在前，性能更高。

如：WHERE col1=2 AND col2>=100 AND col2<=120 ,此时是col1,col2的组合索引性能高过col2,col1的组合索引，可以在系统执行SQL进行简单分析，如下：

SELECT sql_text,

sql_id,

service,

module,

t.first_load_time,

FROM v$sql t

WHERE (sql_text LIKE '%%>%%' or sql_text LIKE '%%<%%' or sql_text LIKE '%%<>%%') AND sql_text not LIKE '%%=>%%'

AND service not LIKE 'SYS$%%'

3.5.4需考虑回表因素

一般情况下，如果建索引可以避免回表（在索引中即可完成检测），也可考虑对多列建组合索引，不过组合索引列不宜超过4个。

3.6单表索引个数需控制

3.6.1索引个数超过5个以上的

超过5个以上的索引，在表的记录很大时，将会极大的影响该表的更新，因此在表中建索引时需要谨慎考虑，以下是查询索引个数超过5个表的脚本，如下：

SELECT table_name, count(*)

FROM user_indexes

GROUP by table_name

having count(*) >= 5

ORDER by count(*) desc

3.6.2建后2个月内从未使用过的索引

一般来说，在2个月内从未被用到的索引是多余的索引，可以考虑删除，具体跟踪和定位的方法如下：

SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;' FROM user_indexes;

然后观察：

set linesize 166

col INDEX_NAME for a10

col TABLE_NAME for a10

col START_MONITORING for a25

col END_MONITORING for a25

SELECT * FROM v$object_usage;

--停止对索引的监控，观察v$object_usage状态变化alter index IDX_OBJECT_ID nomonitoring usage;

3.7单表无任何无索引需重视

单表无任何索引的情况一般比较少见，可以查询出来，再结合SQL应用进行分析，观察该表的大小以及是否有时间字段及编码字段这样的适宜建索引的列，分析可以从以下脚本开始：

SELECT table_name

FROM user_tables

WHERE table_name not in (SELECT table_name FROM user_indexes);

3.8需注意索引的失效情况

3.8.1导致索引失效的一般因素

1.对表进行move操作，会导致索引失效，操作需考虑索引的重

建。

2.对分区表进行系列操作，如split、drop、truncate分区时，容易导致分区表的全局索引失效，需要考虑增加update global indexes的关键字进行操作，或者重建索引。

3.分区表SPLIT的时候，如果MAX区中已经有记录了，这个时

候SPLIT就会导致有记录的新增分区的局部索引失效

3.8.2不同类型索引失效的查询

普通表及分区表的全局索引失效：

SELECT index_name, table_name, tablespace_name, index_type FROM user_indexes

WHERE status = 'UNUSABLE';

分区表局部索引失效:

SELECT t1.index_name,

t1.partition_name,

t1.global_stats,

t2.table_name,

t2.table_type

FROM user_ind_partitions t1, user_indexes t2

WHERE t2.index_name = t1.index_name

AND t1.status = 'UNUSABLE'

4. 环境参数规范

4.1 数据库参数

4.1.1 SGA 及 PGA 参数

OLTP应用是主机内存的80%%分配数据库，其中SGA80%%,PGA20%% OLAP应用是二手手机号码交易主机内存的80%%分配数据库，其中SGA50%%,PGA50%%

如OLTP应用：主机内存30G，SGA即使30*0.8*0.8=20G左右

----不过这里还是要注意：并没有什么黄金参数，这些还只能是参考。

4.1.2 PROCESS/SESSION

sqlplus "/ as sysdba"

show parameter process

show parameter session

SELECT count(*) FROM v$process;

SELECT count(*) FROM v$session;

-----默认连接数是150，这对大多数应用都无法满足，大型应用一般不少于1000个。

4.1.3 OPEN_CURSOR 游标参数

sqlplus "/ as sysdba"

show parameter open_cursor

----默认 open_cursors 是 300，大型应用需设置 1000 以上，原则上不超过 PROCESS 设置。

4.1.4 日志参数

一般来说，ORALCE 默认的日志参数是 3 组，大小为 500M，在实际较大的生产应用中往往不够，需要至少考虑在 5 组以上，大小在 1G 以上。

4.1.5 是否归档

一般来说，生产系统大多需要开启归档，只有在特殊的场合下数据安全性无关紧要（如测试环境），才可以关闭归档。

archive log list

4.2 表空间规划

4.2.1 回滚表空间

l 自动管理

l 避免自动扩展

l 尽可能规划大

4.2.2 临时表空间

l 避免自动扩展

l 尽可能大

l 尽可能使用临时表空间组

4.2.3 业务表空间

l 控制个数，不超过 6 个为宜

l 尽量避免自动扩展，超阀值由监控来检查

l 根据自己的业务，固定表空间名

l 表空间需良好分类（参数配置表，业务数据表，历史记录表）

l 表空间需合理命名

4.3 RAC 系统

l 尽量采用 BALANCE 模式，保证两节点压力大致相当。

l 可适当考虑不同类型的业务部署在不同的节点上，避免 RAC 的 CACHE 争用。

l 尽量考虑不同的节点使用不同的临时表空间。

4.4 命名规范

需要特别说明的是，并非一定要这么命名，只是强调但是在同一开发团队甚至同一公司里，必须统一规范，在内部达成一致的认可。所以下述命名规范，仅供参考。

4.4.1 表以 t_为前缀

SELECT * FROM user_tables WHERE substr(table_name,1,2)<>'T_' ;

4.4.2 视图以 v_为前缀

SELECT view_name FROM user_views WHERE substr(view_name,1,2)<>'V_'

4.4.3 同义词以 s_为前缀

SELECT synonym_name, table_owner, table_name

FROM user_synonyms

WHERE substr(synonym_name, 1, 2) <> 'S_';

4.4.4 簇表以 c_为前缀

SELECT t.cluster_name,t.cluster_type

FROM user_clusters t

WHERE substr(cluster_name, 1, 2) <> 'C_';

4.4.5 序列以 seq_为前缀或后缀

SELECT sequence_name,cache_size

FROM user_sequences

WHERE sequence_name not LIKE '%%SEQ%%';

4.4.6 存储过程以 p_为前缀

SELECT object_name,procedure_name

FROM user_procedures

WHERE object_type = 'PROCEDURE'

AND substr(object_name, 1, 2) <> 'P_';

4.4.7 函数以 f_为前缀

SELECT object_name,procedure_name

FROM user_procedures

WHERE object_type = 'FUNCTION'

AND substr(object_name, 1, 2) <> 'F_';

4.4.8 包以 pkg_为前缀

SELECT object_name,procedure_name

FROM user_procedures

WHERE object_type = 'PACKAGE'

AND substr(object_name, 1, 4) <> 'PKG_';

4.4.9 类以 typ_为前缀

SELECT object_name,procedure_name

FROM user_procedures

WHERE object_type = 'TYPE'

AND substr(object_name, 1, 4) <> 'TYP_';

4.4.10 主键以 pk_为前缀

SELECT constraint_name, table_name

FROM user_constraints

WHERE constraint_type = 'P'

AND substr(constraint_name, 1, 3) <> 'PK_'

AND constraint_name not LIKE 'BIN$%%';

4.4.11 外键以 fk_为前缀

SELECT constraint_name,table_name

FROM user_constraints

WHERE constraint_type = 'R'

AND substr(constraint_name, 1, 3) <> 'FK_'

AND constraint_name not LIKE 'BIN$%%';

4.4.12 唯一索引以 ux_为前缀

SELECT constraint_name,table_name

FROM user_constraints

WHERE constraint_type = 'U'

AND substr(constraint_name, 1, 3) <> 'UX_'

AND table_name not LIKE 'BIN$%%';

4.4.13 普通索引以 idx_为前缀

SELECT index_name,table_name

FROM user_indexes

WHERE index_type='NORMAL'

AND uniqueness='NONUNIQUE'

AND substr(index_name, 1, 4) <> 'IDX_'

AND table_name not LIKE 'BIN$%%';

4.4.14 位图索引以 bx_为前缀

SELECT index_name,table_name

FROM user_indexes

WHERE index_type LIKE'%%BIT%%'

AND substr(index_name, 1, 3) <>'BX_'

AND table_name not LIKE'BIN$%%';

4.4.15 函数索引以 fx_为前缀

SELECT index_name,table_name

FROM user_indexes

WHERE index_type='FUNCTION-BASED NORMAL'

AND substr(index_name, 1, 3) <>'FX_'

AND table_name not LIKE'BIN$%%';

5.SQL编写规范

5.1书写规范

关键字大小写（一般建议用大写）；

表、字段是否使用别名（使用别名，需要有明确规定别名的命名方法）；

SQL脚本采用缩进风格，风格一致，缩进格式一致，使用空格。

例如：

SELECT col_1,col_2,col_3

FROM t_name

WHERE col_1>1

AND col_2<sysdate；

5.2 SQL中直接使用表名

一般不使用其他用户下的表，如需使用，可通过synonym，并在设计文档中进行记录。

5.3 SELECT *语句及INSERT语句中的字段名

避免使用SELECT *语句，应给出字段列表；同样，INSERT语句也必须给出字段列表。避免由于表结构的变更导致语句的不可执行。

例如：

SELECT col_1,col_2,col_3

FROM t_name;

INSERT INTO t_name

(

user_id,

login_name

)

VALUES

(

v_user_id,

v_login_name

);

5.4 INSERT…SELECT语句书写方法

应使每行的字段顺序对应，以每行固定个数字段，括号内的内容另起一行缩进2格开始缩写，关键字单词左对齐，左括号、右括号另起一行与左对齐。

例如：

INSERT INTO dba_objects_bak

(

owner,object_name,bubobject_name,object_id, data_object_id,object_type,created,last_ddl_time, timestamp

)

SELECT owner,object_name,bubobject_name,object_id, data_object_id,object_type,created,last_ddl_time, timestamp

FROM dba_objects;

5.5避免隐式数据类型转换及字段操作

书写时，必须确定表结构及表中各个字段的数据类型，特别是查询条件中的字段，避免由于类型的不同导致隐式数据类型转换的发生。同时，字段上计算表达式及数据库函数的使用也会屏蔽该列上索引的使用。

例如：

表t_name中的字段col_1为varchar2(10)，则：

SELECT c_1,c_2

FROM t_name

WHERE col_1 = ‘1’;

5.6 NULL的使用

空值不可以直接和比较运算符比较，如可能为空，应使用is null或is not null进行比较，或通过函数nvl进行转换后使用。

例如：

SELECT col_1,col_2,col_3

FROM t_name

WHERE col_1 IS NOT NULL;

5.7 LIKE子句

LIKE子句应尽量前端匹配，避免通配符在前端，导致索引屏蔽的发生，引发全表扫描。

5.8绑定变量（适用于OLTP系统）

SQL中常量的直接使用，会导致频繁的硬解析，进而严重影响数据库的性能。

5.9动态SQL

由于动态SQL是在运行时才进行确定的，相对来说会更多的损失一些系统性能来换取灵活性，所以，原则上不允许使用动态SQL。在不得不使用的情况下，必须使用绑定变量。

5.10 SQL子查询嵌套不宜超过3层

禁止使用多层的SQL嵌套，除了分页查询，一般SQL语句建议不超过3层嵌套，过于复杂的SQL可根据业务逻辑拆分为多条SQL来实现

5.11排序的使用

避免不必要的排序，对查询结果进行的排序会大大降低系统的性能。应将大多数的排序工作交给应用层去完成

5.12尽量避免HINT在代码中出现

Hint是Oracle提供的一种语法，能够通过它影响SQL的执行方式。但是执行计划的确定应随环境的变化而变化。通过Hint的影响，使执行计划固化下来，有时会导致错误的结果。一般不建议使用。

5.13用到并行度需谨慎

需要明确并行执行是一项比较重要的技术，但它不是在做优化。由于整个系统的资源总量是固定的，那么并行执行的SQL语句不可避免的会对其他语句的性能产生影响，所以并行技术的使用需要考虑很多因素，例如CPU核数、系统负载等等。此项技术常在OLAP系统中使用。

6. PLSQL编写规范

6.1注释不少于代码十分之一

注释是指程序中会被编译器忽略掉的部分，目的是描述代码的用途，合理的添加注释可以使得程序结构清晰，可以使得别人和自己更加容易的理解代码的作用。

注释是伴随着最原始的编程语言出现而出现的，当我们在读一些低层语言比如汇编所写的代码时，大量的注释可以极大地帮助人们理解代码的作用，从而提高代码的利用率。

绝大多数资深程序员都能够正确且充分的认识注释在程序开发过程中的重要性，有无注释以及注释质量的高低可以从某种程度上发映出程序员专业技能的素养。

而PL/SQL作为开发语句的一种，同样对注释有着极高的要求。PL/SQL的注释从注释风格上而言，可以分为单行注释和多行注

释，单行注释由一对连字符(--)开头,多行注释由斜线星号(/*)开头，星号斜线(*/)结尾，可以注释多行内容。

另外，高质量的程序注释一般包括序言性注释和功能性注释。序言性注释的主要内容包括模块的接口、数据的描述和模块的功能。模块的功能性注释的主要内容包括程序段的功能、语句的功能和数据的状态。

序言性注释一般会放在程序的开头，可以采用如下格式：

--*******************************************

--过程名：

--功能描述：

--输入参数说明：

--输出参数说明：

--调用的过程或函数：

--被哪些过程或函数调用：

--创建人员：

--创建日期：

--修改信息：

--版本说明：

--引用对象:

--*************************************

功能性注释分散在整个程序体中，重点解释程序的主要部分和所有关键的逻辑步骤，注释本身应当能够较好地解释代码的业务逻辑，如果是进行后期修改，则应该解释修改的原因和可能带来的影响，如果有明确的BUG，则需要注明修改所对应的BUG号，以便获取更加详尽的信息。

好的注释应当避免对SQL语句的简单翻译，比如对某insert语句，添加注释“向表A中插入一条记录”意义不大，比较合适的做法是注释该SQL的业务逻辑，比如“保存参保人员基本信息”，这样的注释可以更加方便相关人员对这段代码的理解。

对于较长的代码，通过在注释中添加有层级的序号，则可以体现出注释的层级，同样也可以处代码的层级，这样也可以使得代码更加容易理解。例如：

--0.初始化

<代码>

--1.获取参保人员基本信息

<代码>

--1.1获取参保人员收入信息

<代码>

--1.2获取参保人员纳税信息

<代码>

--1.3获取参保人员缴费记录

<代码>

--2.根据基本信息计算参保人员剩余缴费信息

<代码>

--2.1计算剩余缴费年限

<代码>

--2.2计算剩余缴费金额

<代码>

--3.保存参保人员基本信息及缴费系数

<代码>

下面是在实际书写注释是一些有用的提示：

1尽量使用单行注释而不是多行注释，虽然这两者对编译器而言是等价的。但是多行注释不能嵌套，使用不当容易出错，并且没有单行注释灵活，因此尽可能多的使用单行注释。

2．通常在PL/SQL块的declare，begin，exception和end部分设置分隔线和注释；

3．每个变量都需要加上变量的注释，说明变量的用途；

4．请在重要的程序段和难懂的程序段加上分隔线和注释；

5．请注明游标等的用途和用法

6．注释不少于代码量的十分之一

6.2代码必须提供最小化测试案例于注释中

PL/SQL的测试案例是指一个能够调用对应存储过程或者函数的一段脚本，通过这段脚本的执行以及结果的输出可以判断该对象是否工作正常，就好比我们在数据库启动后往往会执行一下SELECT sysdate FROM dual用来确定数据库是否已经成功打开一样。

但是这种测试脚本不宜过长，越简短越好。主要用于粗略的检查下该段程序是否能够成功运行，一般不需要涉及业务和逻辑相关的内容。我们称这样的测试案例为最小化测试案例。

为了后期测试，修改的方便，我们都会在注释中添加一个最小化测试案例，用来检查程序是否可以正常运行。这种测试案例虽然被保存在注释中，对程序执行不产生任何的影响，但是可以有效的帮助我们检查程序状态，快速定位有问题的程序。

这点在团队协同开发以及大型软件系统开发过程中，显得尤为重要。合理的使用最小化测试案例可以极大的提高团队开发效率。

6.3绑定变量

绑定变量(binding variable)是指在sql语句的条件中使用变量而不是常量，比如我们要查询名为Kelson的员工,查询语句可以这样写:

SELECT * FROM hr_staff WHERE staff_name=’Kelson’;也可以这样写:

SELECT * FROM hr_staff WHERE staff_name=:staff_name;

后一种写法就采用了绑定变量。

要理解绑定变量带来的优势，首先要解释下硬解析。所谓硬解析就是当一条SQL语句首次运行时，数据库引擎需要对其进行语法分析，语义识别，跟据统计信息生成最佳的执行计划的过程。

回到刚才的例子，如果我们想继续查询名为”CongHui”的员工信息，如果继续采用第一种写法，则数据库引擎会认为这个是一条全新的语句，因此数据库引擎会重新进行语法分析，语义识别，跟据统计信息生成最佳的执行计划，然后对其执行，即做一次新的硬解析。如果采用了绑定变量，则数据库引擎会任务这是一条已经分析过的语句，会直接读取内存中的执行计划，然后执行，这样就避免了一次硬解析。

下面看一个例子来了解绑定变量的使用带来的性能上的差别：

首先是不使用绑定变量的：

alter system flush shared_pool;

set timing on

begin

for i in 1 .. 1000

loop

execute immediate'SELECT object_name FROM dba_objects WHERE

object_id= ' || i;

end loop;

end;

PL/SQL过程已成功完成。

执行时间:已用时间: 00: 00: 07.42

下面是使用绑定变量的情况:

alter system flush shared_pool;

set timing on

begin

for i in 1 .. 1000

loop

execute immediate 'SELECT object_name FROM dba_objects WHERE

object_id=:1' using i;

end loop;

end;

PL/SQL过程已成功完成。

执行时间:已用时间: 00: 00: 00.14

同样的操作,区别就在与是否使用了绑定变量，执行时间就从7.42秒减少到0.14秒,这个还仅仅是针对单个Session的操作，如果有较多的并发用户，性能提升会更加明显，由此可见，合理的使用绑定变量可以极大地提升系统的性能。

除了有效地降低硬解析以外，绑定变量的使用可以使得Oracle应用程序具有更好的可伸缩性，解决library cache的过度耗用以提高性能。可以在library cache中共享游标，避免硬解析以及与之相关的额外开销，在大批量数据操作时将呈数量级来减少闩锁的使用，避免闩锁的竞争。因此在实际开发过程中合理的使用绑定变量是非常重要的。

6.3.1相似语句需考虑绑定变量

当然和任何事物一样，绑定变量也有着一定的适用场景，使用不当则会适得其反。

绑定变量比较适合于下列场景：SQL语句重复执行频度高，处理的数据量较少，结果集也相对较小，尤其是使用表上的索引来缩小中间结果集，在这种场景下，解析时间通常会接近或高于执行时间，因此比较适合使用绑定变量。

但是在某些环境尤其是数据仓库环境下，SQL语句执行时间远高于其解析时间，可能的情况有SQL语句执行次数较少,SQL重复频次小，返回的数据量大，全表扫描使用较多等。在这些环境下，使用绑定变量对于总响应时间影响不大。

且使用绑定变量时，查询优化器会忽略其具体值，因此其预估的准确性远不如使用字面量值真实，尤其是在表存在数据倾斜(表上的数据非均匀分布)的列上会提供错误的执行计划。从而使得非高效的执行计划被使用。

一旦这种低效的执行计划在大数据环境情况下被执行，会大量的占用系统资源，从而有可能极大地降低数据库的整体性能。

因此绑定变量的使用一定需要考虑其适应的场景，这样才能最大限度的发挥它的作用。

6.3.2动态SQL最容易遗忘绑定变量

首先我们应该了解什么是动态SQL，在Oracle数据库开发PL/SQL块中我们使用的SQL分为：静态SQL语句和动态SQL语句。所谓静态SQL指在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象。而动态SQL是指在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

在实际开发过程中，对静态SQL采用绑定变量比较简单，大家也往往会记得。但是经常会忽略对动态SQL采用绑定变量，下面是一个对动态SQL使用绑定变量的简单例子，希望大家可以举一反三，更加深入的理解下这个问题。

declare i int;

str_obj_name varchar2(100);

begin

SELECT min(object_id) into i FROM dba_objects; --下面这句是静态SQL绑定变量

SELECT object_name into str_obj_name FROM dba_objects WHERE object_id=i ; --下面这句就是动态SQL通过using使用了绑定变量

execute immediate 'SELECT object_name FROM dba_objects WHERE

object_id=:1' into str_obj_name using i;

end;

6.4尽量使用批量提交

Oracle作为企业级数据库在架构设计上有很多闪光点，锁的管理是其中一项，和其他很多数据库不同的是，锁在Oracle中不是以资源形式存在的，因此不需要尽早提交以释放锁资源。由于在提交以前，数据修改的动作实际已经完成，而提交只是处理一些诸如生产SCN号等后续工作，因此提交一万条数据和提交一条数据所消耗的时间几乎是相同的。

因此尽可能采用批量提交，从而得到更好的性能，请看下面的例

子，

首先建立一张和dba_objects表结构相同的表t：

Create table t as SELECT * FROM dba_objects WHERE 1=2; --采用逐行提交方式，commit在循环内

alter system flush shared_pool;

truncate table t;

set timing on

DECLARE

BEGIN

FOR cur IN (SELECT * FROM dba_objects) LOOP INSERT INTO t VALUES cur; COMMIT;

END LOOP;

END;

PL/SQL过程已成功完成。

执行时间:已用时间: 00: 00: 12.58

--采用批量提交方式，commit在循环外

alter system flush shared_pool;

truncate table t;

set timing on

DECLARE

BEGIN

FOR cur IN (SELECT * FROM dba_objects) LOOP INSERT INTO t VALUES cur; END LOOP;

COMMIT;

END;

PL/SQL过程已成功完成。

执行时间:已用时间: 00: 00: 04.88

通过上面的例子，大家可以发现，第一段脚本和第二段几乎完全一样，差别就在于一个commit在循环内，而另一个在循环外，但是消耗的时间从12秒多减少到了4秒多，这种性能提升还是非常明显的，因此在条件允许的情况下，尽可能的使用批量提交可以有效的提升程序性能。

6.5同一过程包中出现重复逻辑块需封装，统一调用

在实际的开发过程中，我们经常会在程序中写一些有着相同逻辑，处理相同任务的脚本，在这种情况下，可以将这些重复的代码单独提取出来，以子程序的形式单独存放在数据库中。

所谓的子程序就是能够接受参数并被其他程序所调用的命名PL/SQL块。

PL/SQL子程序有两种类型，过程和函数。一般地，过程用于执行一个操作，而函数用于计算一个结果值。与未命名或匿名PL/SQL块一样，子程序也有声明部分，执行部分和一个可选的异常处理部分。声明部分包含类型、游标、常量、变量、异常和嵌套子程序的声明。这些内容都是本地的，在程序退出时会自动销毁。执行部分包含赋值语句、流程控制语句和Oracle的数据操作语句。异常处理部分包含异常处理程序

利用好子程序，能够给开发提供很好的扩展性，它能够让我们根据自己的需求来编写特定的PL/SQL。

子程序还能提供模块化，就是说它可以把一个程序定义成多个模块，更易管理。这样，我们就可以用自顶而下的设计和逐步求精的方法来解决问题。此外，子程序在提高程序的重用性和可维护方面也是很有用的。只要编译成功，子程序就可以放心地用在很多应用程序中。如果它的定义内容发生了改变，受到影响的只有子程序本身而已，这就简化了维护过程。最后，子程序还有助于逻辑的抽象。使用子程序时，我们需要知道的是它们的功能，而不是它们实现功能的细节问题。

6.6生产环境尽量使用包来封装过程和函数

包就是一个把各种逻辑相关的类型、常量、变量、异常和子程序组合在一起的模式对象。包通常由两个部分组成：包说明和包体，但有时包体是不需要的。包说明是应用程序接口；它声明了可用的类型、变量、常量、异常、游标和子程序，包体部分完全定义游标和子程序，并对说明中的内容加以实现。

我们可以认为说明部分是一个可选接口，而包体是一个"黑盒"。

我们可以调试、增强或替换一个包体而不同改变接口。

在包说明部分声明的内容都是公有的，对应用程序是可见的。我们必须在所有的其他内容(除了用于为一个特殊的函数命名的编译指示；这样的编译指示必须跟在函数说明之后)声明之后才可以声明子程序。包体中的内容有私有的，它实现了说明部分定义的细节内容，并且对应用程序是不可见的。紧跟着包体声明部分的是一个可选的初始化部分，它用于初始化包中的变量等。AUTHID语句决定了是否是所有的打包子程序都按定义者权限(默认)或调用者权限执行，其中涉及到的模式对象是在定义者的模式中解析还是在调用者的模式中解析。一个调用说明能让我们在Oracle数据词典中发布一个Java方法或外部C函数。调用说明靠把程序的名称、参数类型和返回类型映射到它们的SQL副本中来发布程序。

在大多数生产环境中，我们会发现几乎所有的存储过程和函数都被封装在程序包里面，很少会见到单独存在的存储过程和函数。之所以会出现这样的情况，主要是因为采用包封装和单独的存储过程和函数相比有着明显的优势，下面我来给大家简单的介绍下这些优势：

包提供了几个优点：模块化、程序设计更加简单、信息隐藏、附加功能，良好的性能和重载。

1.模块化

包能让我们把逻辑相关的类型、常量、变量、异常和子程序等放到一个命名的PL/SQL模块中。每一个包都容易理解，包与包之间接口简单、清晰。这将有助于程序开发。

2.程序设计更加简单

设计应用程序时，我们首先要确定的是包说明中的接口信息。我们可以在没有包体的条件下编写并编译说明部分。然后引用该包的存储子程序也会被编译。在完成整个应用程序之前，我们是不需要完全实现包体部分的。

3.信息隐藏

有了包，我们就可以指定哪些类型、常量、变量、异常和子程序等是公有(可见和可访问)或私有(隐藏和不可访问)。例如，如果一个包里包含了四个子程序，其中三个是公有的一个是私有的。包就会隐藏私有子程序的实现，这样的话，如果实现内容发生改变，受到影响的只有包本身(不是我们的应用程序)。同样，对用户隐藏实现细节也能保证包的完整性。

4.附加功能

打包公有变量和游标在一个会话期会一直存在。所以，它们可以被当前环境下的所有子程序共享。并且它们允许我们跨事务来维护数据而不用把它保存在数据库中。

5.良好的性能

在我们首次调用打包子程序时，整个包就会被加载到内存中。所以，以后调用包中的相关子程序时，就不需要再次读取磁盘了。包能阻塞级联依赖，这样就能避免不必要的编译。

例如，如果我们改变打包函数的实现，Oracle不需要重新编译调用子程序，因为它们并不依赖于包体。

6.重载

在程序包中，我们可以重载一些存储过程和函数，也就是说，我们可以在单个程序包中创建多个拥有不同数量和类型变量但是名称相同的子程序，这点在实际编码的过程中是非常实用的。

鉴于以上这些优势的存在，我们在生产环境中应当极可能的使用程序包，而不是单个的存储过程和函数。

6.7动态SQL编写需记录真实SQL记录表中

动态SQL是PL/SQL开发过程中经常使用的方法之一。很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行SQL查询语句，对于这种情况需要使用动态SQL来完成。再比如，对于分页的情况，对于不同的表，必定存在不同的字段，因此使用静态SQL则只能针对某几个特定的表来形成分页。而使用动态的SQL，则可以对不同的表，不同的字段进行不同的分页。这些情况的处理通常都是用动态SQL来完成。在实际开发过程中，由于业务逻辑的复杂性，我们会大量的使用动态SQL，这样做在带来极大的便利的同时，往往也会产生一些不可预知的后果，比如操作的不可追踪性，容易产生SQL注入，为数据库安全带来隐患等。

为了有效的降低使用动态SQL带来的副作用，一个比较可行的做法是建立一种动态SQL执行日志表，将所有被执行的动态SQL以及执行者，执行时间等相关信息记录到该日志表中。

以便检查是否动态SQL在按照自己的最初设计在执行，以及在发现数据异常的时候可以进行反向溯查。

同样为了避免在所有执行动态SQL时都去做维护日志信息的操作，这样做一来增加工作量，同时也经常会被遗忘。可以参照上面子程序的相关内容，把执行动态SQL以及维护日志信息的操作写入一个子程序，这样以后在执行动态SQL的时候直接调用这个子程序即可。这样可以减少开发开发工作量且保证所有的动态SQL操作都被记录。

更多相关文章

随机推荐