1 前言

1.1目的

本文档详细定义了关系型数据库的数据库模型设计规范、表的设计规范、分区表的设计规范、索引的设计规范、其他数据库对象的设计规范、SQL的访问规范、编码和注释规范，并介绍了SQL调优的关注点和常用方法，作为数据库规划、设计、开发及维护人员的技术参考资料，用以指导关系型数据库的设计和开发,性能是设计出来的，质量也是可以设计出来的，理解这篇，关系型数据库性能提升30%不是问题。

我希望通过该规范的分享，能够实现以系统、体系的工程化思维模式去规范关系型数据库设计和开发，使数据库结构和编码风格标准化，提高模型的前瞻性、高效性，以尽早提前避免由于数据库设计不当而产生的麻烦，同时好的规范，在执行的时候可以培养出好的习惯，好的习惯是软件质量的很好的保证。

目    录

1 前言1

1.1目的1

1.2预期的读者和阅读建议6

2数据库模型设计规范6

2.1 数据库建模原则性规范6

2.2 实体型之间关系认定规范7

2.3 范式化1NF的规范7

2.4 范式化2NF的规范8

2.5 范式化3NF的规范9

2.5 反范式化冗余字段使用规范9

2.6 数据库对象命名基本规范10

2.6.1遵循行业规范10

2.6.2简单命名原则10

2.6.3 字符范围原则11

2.6.4字母全部大写或小写原则11

2.6.5勿用保留词原则11

2.6.5同义性原则11

2.6.6富有含义原则11

2.6.7 扩展性原则11

3表的设计规范12

3.1 命名规范12

3.1.1 表的命名规范12

3.1.2 字段的命名规范12

3.2 表的设计规范13

3.2.1 指定表空间规范13

3.2.2 表的主键规范13

3.2.3 表的外键规范13

3.2.4 字段类型及宽度的规范14

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

3.2.6 一个表所含字段访问频繁度的规范15

3.2.7 大对象字段（BLOB，CLOB）使用规范15

3.2.8 关于字段能否为NULL值15

3.2.9 关于冗余列的规范16

3.2.10 使用注释的规范16

3.2.11 一个表所含数据量的规范16

3.2.12 增量同步表的设计规范17

3.3 字段类型规范17

3.3.1 不使用会发生隐式转换：INTEGER，FLOAT17

3.3.2 不使用过时老类型：RAW，LONG，LONG RAW17

3.3.3 国家字符集相关17

3.3.4 不能使用大对象：BLOB，CLOB，NCLOB18

3.3.5 不能使用高精度：TIMESTAMP18

3.3.6 关于CHAR字段18

4分区表的设计规范18

4.1 表空间及分区表的概念18

4.1.1 表空间18

4.1.2 分区表18

4.2 表分区的具体作用19

4.3 表分区的优缺点19

4.4 分区表设计规范19

4.2.1 不使用全局索引19

4.2.2 RANGE分区的规范20

4.2.3 LIST分区的规范20

4.2.4 HASH分区的规范21

4.2.5 RANGE-LIST分区的规范22

4.2.6 RANGE-HASH分区的规范22

5索引的设计规范23

5.1 索引分类23

5.1.1单列索引与复合索引23

5.1.2 唯一索引与非唯一索引23

5.1.3 B树索引、位图索引与函数索引23

5.2 命名规范24

5.3 索引设计规范24

5.3.1 指定表空间规范25

5.3.2 主键索引的规范25

5.3.3 唯一约束索引的规范26

5.3.4 外键列索引的规范26

5.3.5 复合索引的规范26

5.3.6 函数索引的规范27

5.3.7 位图索引的规范27

5.3.8 反向索引的规范27

5.3.9 分区索引的规范27

5.3.10 索引重建的规范27

6其他数据库对象设计规范28

6.1 命名规范28

6.2 视图设计规范28

6.2.1 尽量使用简单的视图，避免使用复杂的视图28

6.2.2 按照必要性原则建立视图29

6.3 存储过程、函数、触发器的设计规范29

6.3.1 关于触发器的设计29

7 SQL访问规范30

7.1 尽量不要写复杂的SQL30

7.2 避免使用SELECT *30

7.3 INSERT时需写全列名30

7.4 进行DML操作时使用CTAS进行数据备份31

7.5 大数据量DML操作分多次执行31

7.6 使用绑定变量，降低高硬解析31

7.7 选择最有效率的表名顺序32

7.8 关注WHERE子句中的连接顺序32

7.9 用EXISTS替代IN33

7.10 用表连接替换EXISTS34

7.11用EXISTS替换DISTINCT35

7.12 尽量用union all替换union35

7.13 使用DECODE函数来减少处理时间35

7.14 尽量避免用order by36

7.15 用Where子句替换HAVING子句36

7.16 减少多表关联37

7.17 避免重复访问37

7.17.1 使用group by37

7.17.2 用表更新表38

7.17.3竖向显示变横向显示38

7.18 完成事务及时commit39

7.19数据库连接及时关闭39

7.20 索引的使用39

7.20.1避免在索引列上使用函数或运算39

7.20.2避免改变索引列的类型40

7.20.3避免在索引列上使用NOT40

7.20.4用>=替代>41

7.20.5 避免在索引列上使用IS NULL和IS NOT NULL41

7.20.6 带通配符（%）的like语句41

7.20.7 总是使用索引的第一个列42

8 编码及注释规范42

8.1 编码规范42

8.1.1 避免隐式的数据类型转换42

8.1.2 不要将空的变量值直接与比较运算符(符号)比较43

8.1.3 跨行语句，第一关键字应当左对齐43

8.1.4 Insert … values 和update 语句书写规范43

8.1.5 Insert … select 语句书写规范44

8.1.6 避免使用嵌套的IF语句44

8.1.7 减少控制语句的检查次数45

8.1.8 语句涉及多个表时，使用别名来限定字段名46

8.1.9 其他编码规范47

8.2 注释规范50

8.2.1 注释语法50

8.2.2 每个块和过程开发放置注释50

8.2.3 代码注释应放在其上方或右方50

8.2.4 其他注释规范51

9 PLSQL优化51

9.1性能问题分析51

9.2 PLSQL优化的核心思想52

9.3 PLSQL优化示例53

9.3.1 减少对表的查询53

9.3.2 避免循环（游标）里面嵌查询55

9.3.3 group by优化56

9.3.4 删除重复记录57

9.3.5 COMMIT使用57

9.3.6 批量数据插入58

9.3.7 索引使用优化59

9.3.8 使用提示(Hints)60

9.3.9 表上存在过旧的分析61

9.3.10 表上存在并行61

9.3.11 关于索引建立62

9.3.12 Expain Plan分析索引使用62

1.2预期的读者和阅读建议

本文档预期的读者为项目经理、开发经理、DBA、数据结构管理师、系统设计师、开发师、测试师等相关岗位的人员。

读者可以通篇阅读该文档，以整体熟悉和掌握Oracle数据库设计规范，也可以重点关注跟自身相关的内容章节，如数据库模型设计、表的设计，或SQL访问规范、编码和注释规范等。

2 数据库模型设计规范

2.1 数据库建模原则性规范

对于涉及数据库的项目，需要构建数据库逻辑模型图，逻辑模型图是项目组成员之间在数据库层面沟通交互的依据，必须规范画图（表，主键，外键，关系）。对于表的个数在20个以上的模型，需要DBA参与设计，并作最终审核。

对于OLTP系统，采用范式化思想进行模型设计，对于OLAP系统，采用面向问题及多级颗粒度的思想进行模型设计。

需采用主流的模型设计软件工具PowerDesigner，ERWin。

2.2 实体型之间关系认定规范

所有实体间的业务逻辑关系，除了语义上保留其原有的业务关系外，本质上都要转化成关系数据库的三种关系（1:1）（1:N）（N:M），对于3个及以上实体型之间的“多元关系”，需要DBA参与设计。

比如，实体型A和实体型B之间的关系，可以通过问两个问题来确定他们之间的关系：一个A可以对应几个B？一个B可以对应几个A?

（1）一个A对应一个B，相反一个B对应一个A，那么A对B就是1:1关系；

（2）一个A对应多个B，相反一个B对应一个A，那么A对B就是1:N关系；

（3）一个A对应多个B，相反一个B对应对个A，那么A对B就是N:M关系；

具体实施的时候，掌握如下原则：

n 1:1关系选取任何一个表的主键到另一个表中，作为外键来体现。

n 1：N关系将1表的主键在N表中，以外键形式来体现。

n N:M关系采用“关系表”来体现，该关系表的主键是由相关实体表的主键组成的复合主键；各实体表主键不但组成了该关系表的主键，同时也被看作外键在该关系表中存在。

n 对于三个以上表之间的“多元关系”常需要和反范式化冗余字段结合起来设计，以保证查询速度。

2.3 范式化1NF的规范

OLTP系统的模型，需要符合第三范式，对于表在20个以上的模型，需要DBA参与设计。

范式化要求（1NF）：列是访问的最小单位，具有原子性，不可再被分割。

在具体实施的时候，需要依据情况对相应属性进行拆分或者合并：

n 同一个属性值的不同细度把握

比如，常见的“姓名”这个属性，设计一：“姓名”是一个列，设计二：“姓”是一个列，“名”是一个列，两个列的值组合起来才表达一个“姓名”语义。两种设计方法，在不同的系统中都有应用，这主要是依据需求的细度来确定，灵活把握。

n 把多个属性值错误的作为一个属性值存储

比如：常见的OA系统要存储员工的各种属性，包括技能信息，技能范围：Oracle，JAVA，.NET,C#,Perl,UNIX等等，一种常见的错误设计是：设计一张员工表，其中有一个技能属性字段，然后某员工所掌握的多种技能用逗号（，）间隔，然后将这个字符串存储到这个员工表的技能属性字段中。

这里的错误在于将多个属性值作为一个属性值存储在一个字段中，不能满足直接遍历员工对某个技能掌握情况，而且如果再要求说明员工对个技能的掌握程度（精通，熟悉，一般等等），则再增加字段，里面的对应关系将很容易错乱，这是严重违反1NF的情况。

正确的设计应该是：两个实体表：一张是员工表，一张是技能字典表，一个员工可以掌握多个技能，也就是（1:N）关系，相反一个技能可以被多个员工掌握，也是（1:N）关系，双向都是（1:N）关系，那么综合起来员工和技能之间就是“多对多关系（N:M）”，依据前述规范，应该设计一张“关系表”来存储“多对多关系”，主键为复合主键（员工主键+技能主键），该关系有一个属性“技能掌握程度”。

2.4 范式化2NF的规范

OLTP系统的模型，需要符合第三范式。对于表在20个以上的模型，需要DBA参与设计。

范式化要求（2NF）：满足1NF，不存在非主键属性对主键属性的部分依赖。

实体表中一般不会出现违反2NF的情况，因为都是“一个”主键列，而关系表是两个以上列的“复合”主键，故而关系表容易出现违反2NF的情况。主要是该关系表非主键外的属性，本该属于相关的某个实体表的，却放到了该关系表中。这使得该属性不能通过该关系表的复合主键唯一确定，DML操作会发生错误。如果违反了2NF，那么应该把这个属性从关系表中拆分，也许会单独形成一个表，绝大部分情况下是将该属性归并到某个相关的实体表中。

违反2NF的例子：

学生考试情况中，有两个实体表：学生表和学科表，学生与学科之间的考试关系就是N:M的关系，就要创建一张关系表存储该多对多的考试关系，表的主键为学生编号和学科编号，属性为考试分数；那么“任课老师”该放在那里呢？如果放到考试关系表中，那么安排任课老师，必须先进行考试，这显然不符合实际，也就是任课老师不该依赖于学生编号和学科编号，只是依赖于学科编号，也就是说任课教师信息应该放在学科表中。

2.5 范式化3NF的规范

OLTP系统的模型，需要符合第三范式。对于表在20个以上的模型，需要DBA参与设计。

范式化要求（3NF）：满足2NF，不存在非主键属性对主键属性的传递依赖；

违反3NF的情况，绝大多数是在含有外键的表中。比如A表中的外键字段Bkey是B的主键，那么依赖于Bkey的属性应当属于B表的属性，而不是A表，如果放入A表，则这些对A表的主键Akey的依赖，首先是依赖于A（BKey），而后通过A(BKey)对A（AKey）的依赖，传递依赖于A(Akey)；三种关系（1:1,1:N，N:M）都含有外键，都很可能发生违反3NF的情况。违反3NF的后果，会导致那些问题属性插入异常，或者被误删。

违反3NF的例子：

教师和学科之间，存在着上课关系，假设一个教师上一门课而且一门课只有一个教师上，那么该关系为1:1关系，将教师表的主键教师编号在学科表中以外键形式存在就表达了该1:1关系，那么教师的“联系电话”属性该放哪里呢？如果看到“教师编号”出现在了学科表中，就将联系电话放入学科表中，那么联系电话首先是对表中的教师编号依赖，再依据教师编号对学科的依赖，达到了学科编号的依赖，那么联系电话对学科编号的依赖就是传递依赖，违反了3NF。应该将其从学科表中拆出来放入教师表中，不然的话，会发生操作异常，比如，假设一个教师已经存在但是还没有为其分配科目，那么他的电话就无法存入库中。

 2.5 反范式化冗余字段使用规范

OLTP系统中在完成范式化工作之后，对某些表，可以适当反范式化增加冗余字段以提高数据访问性能；在OLAP中采用的是面向问题的设计思想，应该大量使用反范式化冗余信息。

当SQL关连查询涉及到4张表时可考虑采用冗余字段。

常用在两个地方：（1）关系表中的冗余：在关系表中增加相关实体表的相关属性，以达到关连查询时减少表的关联数量的目的（2）层次关系中的冗余：在多层次的子父表关系中，将父表的属性存储在“子表”或者“孙子表”或者“重孙表”中。

反范式化冗余字段实例：

（1）关系表中的冗余：比如在考试关系中，原本在学科表中的学分信息，可以冗余添加到考试关系表中，这样，每个学生得了多少学分，就可以直接从考试表得到，而无需关联学科表来得到。

（2）多层关系中的冗余：假设为之范畴从大到小有国家表，省份表，城市表，城区表，社区表，它们之间的层次关系是通过上一级的主键在下一级中以外键形式存在来体现的，但是，如果需要问：某个设计属于哪个国家？这样就要关连查询所有的5张表，性能会很差。这时可以将国家编号以外键形式放入到社区表中做冗余，这样直接关联国家表和社区表即可得到答案。一般的，每间隔一级增加一个冗余外键，比如将国家编号放入城市表中，将城市编号放入社区表中。

如何保证冗余字段数据的正确性（一致性）是反范式化的关键，需要对冗余字段详细添加注释，说明冗余了什么，以及该字段的维护方法，常用维护方法如下：

n 如果在程序开发前设计的冗余字段，可以在正常的业务逻辑程序中一并处理；

n 如果是程序完成之后增加的冗余字段，可以使用触发器维护；

n 对于OLAP中大量存在冗余字段，可能需要使用单独的处理任务进行维护。

2.6 数据库对象命名基本规范

2.6.1遵循行业规范

当有相关国家/行业强制性数据结构标准规范存在时，用于存储某业务数据的业务表在表名命名上原则上应该遵从标准规定，其表中相关字段的中文名称（即数据项名称）若标准规范上有规定的应遵循规定。此外，若标准规范上对数据项的类型、长度有规定的，原则上也应当遵循或保证能直接兼容保存和访问。

2.6.2简单命名原则

命名尽可能简单，避免太长的命名，尽量使用缩写形式，但是缩写也要能够表达命名的含义。凡是需要命名的对象其标识符均不能超过30个字符，也即：Oracle中的表名、字段名，函数名，过程名，触发器名，序列名，视图名的长度均不能超过30个字符，以免超过数据库命名长度限制（Oracle有30的限制）。建议每个单词分段长度不要超过6位。

2.6.3 字符范围原则

数据库各种名称必须以字母开头，但严禁使用SYS开头；名称只能含有字母，数字和下划线“_”三类字符，“_”用于间隔名称中的各语义字段，以便阅读同时方便某些工具对数据库对象的映射。如XXX_XXX_XXX，但不限于三段式。

2.6.4字母全部大写或小写原则

所有数据库对象命名字母全部大写或小写。Oracle对大小写不敏感，但是有些数据库对大小写敏感，统一大小写有助于在多个数据库间移植。

2.6.5勿用保留词原则

数据库对象命名不能直接使用数据库保留关键字，但分段中可以使用。如USER不能用于表名、列名等，但是USER_NAME可以用于列名，USER_INFO也可以用于表名。

2.6.5同义性原则

对于同一含义尽量使用相同的单词命名，不管使用英文单词还是英文缩写，以免引起误解。如TELEPNHOE的A表中表示固定电话号码，在B表中就不应该用于表示移动电话号码。尽量避免同一单词表示多种含义的情况。

2.6.6富有含义原则

命名尽量采用富有意义的英文词汇，不准采用汉语拼音。

2.6.7 扩展性原则

各系统或者项目在遵循本规范的基础上可以根据需要制定更明确的规范细则，以满足项目管理需要。如对模块进行统一命名，然后用于表名的前缀。建议每个系统在启动开发时建立数据字典，管理命名中使用的英文单词、英文单词缩写等，对用于命名的单词进行统一管理。

 

3 表的设计规范

3.1 命名规范

3.1.1 表的命名规范

命名规则：3位类别码_模块名_表名_附加码，采用大写字符。

类别码：一般表 TBL、临时表 TMP、中间表 CVT、删除表 DEL、历史表 HIS、配置表 CFG，接口表 INT，一般表的3位类别码可以省略，其他类型表的类别码必填。

模块名：模块名代表子系统（或者子模块）的名称，如：保单相关表 PLC；订单相关SLS；基础数据：TYP。

表名：表名应该简洁明了，尽量使用完整的单词，如果导致拼上表名后，长度超过30个字符，则从最后一个单词开始，依次向前采用该单词的缩写。（如果没有约定的缩写，则采用该单词前4个字母来表示）。另外，表名中的名词单词都应使用单数形式，以免混淆，如：使用FACTORY而非FACTORIES。

附加码：为可选项，各系统根据实际情况自行编码，如：可以用以标记临时表的生成及数据存放日期YYMMDD。

3.1.2 字段的命名规范

命名规则：英文单词之间用下划线连结，且每个单词皆为单数.例：user_name，采用小写字符。

n 字段用来存储sequence序列，命名以id结尾。例：bar_code_id。

n 字段用来存储号码，命名以no结尾。例：policy_no。

n 字段用来存储日期，命名以date结尾。例：create_date。

n 字段用来存储数量，命名以num结尾。例：insured_num。

n 字段用来存储金额，命名以amt结尾。例：prem_amt。

n 字段用来存储名称，命名以name结尾。例：client_name。

n 字段用来存储描述信息，命名以desc结尾。例：bank_desc。

n 字段用来存储基础表的code信息，命名以code结尾。例：region_code。

n 字段用来存储标志信息，命名以flag结尾。例：underwrit_flag。

n 字段用来存储英文名称和英文描述，命名以en结尾。例：address_en。

3.2 表的设计规范

3.2.1 指定表空间规范

每个表在创建时候，必须指定所在的表空间，不要采用默认表空间，以防止表建立在system 空间上，导致性能问题。对于事务比较繁忙的数据表，必须存放在在该表专用空间中。

3.2.2 表的主键规范

表的主键设计，应该遵循如下三点原则：

n 有无原则

除临时表和外部表，以及流水表，日志表外，其他表都要建立主键。主键是每行数据的唯一标识，保证主键不可随意更新修改，在不知道是否需要主键的时候，请加上主键，它会为你的程序以及将来查找数据中的错误等等，提供一定的帮助。

n 构成原则

主键不能使用含有实际语义的列，应该增加一个xx_id字段做主键，类型为number，取值来自序列sequence；

n 创建原则

对于500万以上的表，采用先建唯一索引再添加主键约束的方式来创建主键。

对于实体表，主键就是一列，就是没有任何语义的自增的NUMBER列；对于关系表，主键就是相关实体表主键形成的复合主键，是多列。

3.2.3 表的外键规范

一个表的某列与另一表有关联关系的时候，如果加得上的话，请加上外键约束。外键是很重要的，所以要特别强调。

n 适量建立外键

为了保证外键的一致性，数据库会增加一些开销，如果有确凿的并且是对性能影响到无法满足用户需求的证据，可以考虑不建外键。否则，还是应该建外键。

n 不要以数据操作不方便为理由而不建外键

是的，加上外键以后，一些数据操作变得有些麻烦，但是这正是对数据一致性的保护。正是因为这种保护很有效，所以最好不要拒绝它。

n 以缺省的方式建立外键

以缺省的方式建立外键（即用delete restrict方式），以达到保护数据一致性的目的；外键在保护数据一致方面非常有效。如果不建外键，数据库中容易出现垃圾数据，并且无人知晓。当数据量很大的时候，查找这些垃圾数据也是相当困难的。而应用程序在设计时，往往没有考虑或者也无法照顾到垃圾数据。因此垃圾数据很可能造成应用程序工作不正常，并且表现出来的现象会很奇怪，让人摸不着头脑。

3.2.4 字段类型及宽度的规范

字段的宽度要在一定时间内足够用，但也不要过宽，占用过多的存储空间,对于长度不确定的列，采用可变长度的数据类型如varchar类型；

字段的类型及宽度在设计以及后面进行开发时，往往要与应用的设计、开发人员商讨，以得到双方认可的类型及宽度；

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

一个表中的所有字段，应当能存储在一个数据块中（BLOCK），也即：表的单行字段总长度减去pctfree)。对不含有大对象数据类型字段的表，字段数大于50个的，请DBA团队参与设计。

查询字典表USER_TAB_COLUMNS中的字段DATA_LENGTH得到表中所有字段的总长度，再依据db_block和表的pctfree参数可以判断是否一个数据行可以存储在一个数据块(BLOCK)中。

对表添如果所有字段的总长度超出了一个数据块，那么需要将该表拆分成两个（甚至多个）表，拆分的依据是字段的频繁使用程度，也就是频繁使用的字段在一个表中，很少被使用的字段放在另一个表中，他们之间使用相同的主键值，用主外键关联。这点就是“一个表所含字段访问频繁度的规范”。

3.2.6 一个表所含字段访问频繁度的规范

一个表中的各字段的访问频繁度应该基本一致，如果一个表的字段数超过50个， 请DBA参与审核。

如果一个表的字段数过多超过50个，并且依据业务逻辑确定该表中一些字段频繁被访问，另一些字段则很少被访问，则该表需要做拆分处理，这样可以避免读取频繁信息时多读取很少被访问的信息，可以提高IO性能，减少内存耗费，这在OLAP系统中比较常见。

将访问频繁度相差太远的字段拆分到两个表中，一个表存频繁访问的字段，另一个表存很少被访问的字段。

3.2.7 大对象字段（BLOB，CLOB）使用规范

存储图片，视频，音频，文件，500字节以上文本等占用太多空间的字段（大对象字段），不能和其他字段存储在一个表中。含有大对象（BLOB，CLOB）字段的表设计和存储请DBA参与设计。一般有两种方法：

n 数据库存储

可以重新建一个表专门存储该大对象字段，该表基本为两个字段，一个为大对象编号ID为主键，一个为大对象内容本身，并将该主键在原表中作外键关联，该大对象表存储在单独的表空间中。

n 操作系统存储

将这些文件存储在操作系统空间中，大对象字段存储该文件的全路径名。

如果该大对象字段常被修改，那么采用方法一；如果该大对象信息为静态，加载后基本不变，那么可以采用方法二，它有一个致命缺点就是信息存储在数据库外部，不安全，容易丢失。

3.2.8 关于字段能否为NULL值

对于字段能否为null，应该在sql 建表脚本中明确指定，不应该使用缺省。由于null 值在参加任何计算时，结果均为null，所以在程序中必须用nvl()函数把可能为null 值的字段或变量转换非null 的默认值。

3.2.9 关于冗余列的规范

除非必要，否则尽量不加冗余列。

所谓冗余列，是指能通过其他列计算出来的列，或者是与某列表达同一含义的列，或者是从其他表复制过来的列等等。冗余列需要应用程序来维护一致性，相关列的值改变的时候，冗余列也需要随之修改，而这一规则未必所有人都知道，就有可能因此发生不一致的情况。

如果是应用的特殊需要，或者是为了优化某些逻辑很复杂的查询等操作，可以加冗余列。

3.2.10 使用注释的规范

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

查询字典表user_tab_comments和user_col_comments可知道表和字段的注释信息。

对表添加注释：

SQL>comment on  table   is 'xx';

对字段添加注释：

SQL>comment on column. is 'xx';

3.2.11 一个表所含数据量的规范

一个非分区表中的数据量不要超过500万。当一个非分区表中的数据量超过500万时，需设计成分区表；如果该表数据量超过5000万，请DBA参与设计。

在系统上线前，通过对业务分析，判断一个表的数据量；在系统上线后，可以通过exp的日志，Top性能SQL，count(1)来发现数据量大的表。

将这些表进行分区，具体方法请参看分区表的设计规范。

 

记录数超过两亿条的表一定要考虑信息生命周期，必须考虑历史数据的剥离，并在应用设计中完成对历史数据的相应处理功能（历史数据的剥离规则须经业务使用部门的确认）。

3.2.12 增量同步表的设计规范

字典信息表和需要使用增量同步的表必须增加如下属性。

属性名	类型	取值	说明
Status	Char(1)	Y/N：Y为激活N为作废，默认为Y	标识该行是否使用。用于软删除，软删除需将主键和唯一约束列添加随机数后缀。
Create_time	Date	默认为sysdate	创建时间
Update_time	Date	默认为sysdate	最后修改时间

3.3 字段类型规范

3.3.1 不使用会发生隐式转换：INTEGER，FLOAT

INTEGER改为NUMBER(n)

FLOAT改为NUMBER(p,s)

3.3.2 不使用过时老类型：RAW，LONG，LONG RAW

l非标准：VARCHAR2(n CHAR)、CHAR(n CHAR)

VARCHAR2(n CHAR)改为VARCHAR2(n)

CHAR(n CHAR)改为CHAR(n)

3.3.3 国家字符集相关

l国家字符集相关：NCHAR，NVARCHAR2，NCLOB

NCHAR改为CHAR

NVARCHAR2改为VARCHAR2

NCLOB改为CLOB

3.3.4 不能使用大对象：BLOB，CLOB，NCLOB

l不能使用大对象：BLOB，CLOB，NCLOB

CLOB和NCLOB改为VARCHAR2

3.3.5 不能使用高精度：TIMESTAMP

l不能使用高精度：TIMESTAMP

TIMESTAMP改为DATE

3.3.6 关于CHAR字段

CHAR字段类型长度小于100，长度大于100的字符型信息应该使用VARCHAR2字段类型来存储。

4 分区表的设计规范

4.1 表空间及分区表的概念

4.1.1 表空间

是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。

4.1.2 分区表

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个“表空间”(物理文件上)，这样查询数据时，不至于每次都扫描整张表而只是从当前的分区查到所要的数据大提高了数据查询的速度。

4.2 表分区的具体作用

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

 什么时候使用分区表：

 1、表的大小超过2GB，数据量超过500万；

2、表中包含历史数据，新的数据被增加都新的分区中。

4.3 表分区的优缺点

表分区有以下优点：

n 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

n 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；

n 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；

n 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

缺点：

n 已经存在的表没有方法可以直接转化为分区表。不过， Oracle 提供了在线重定义表的功能。

4.4 分区表设计规范

4.2.1 不使用全局索引

在分区表中不建议使用全局索引，因为trunc 分区时会导致全局索引失效，造成难以维护。

4.2.2 RANGE分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当表的数据量超过5000万，请DBA参与设计。

SQL常依据某列的范围访问表，则对表使用RNAGE分区。常见情况是SQL根据时间范围进行查新，则使用时间字段作为分区关键字进行RANGE分区；

将对表的多种访问结合考虑来确定分区的细度：

n 大多数SQL操作的分区关键字值的范围；

n 数据维护的需要，比如以月为单位删除历史数据；

n 数据访问的性能，以操作范围确定的分区数据量还是过大，比如大于500万，则还需要进行细分；

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

1.  当各个分区中的数据能均等划分时性能最好，如果相差太大，则考虑采用其它分区，或者将大数据量的分区再进行HASH子分区；

2.  各分区采用各自的表空间存储，使用user_tab_partitions字典来查看确定每个分区的表空间位置；

3.  分区表的索引采用本地索引，因为常会根据分区关键字（比如时间）进行分区维护（比如删除1年前的数据，也就是删除1年前的分区），分区维护时全局索引会失效，而本地索引不会失效，这能保证访问表时索引正常可用。

4.2.3 LIST分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当表的数据量超过5000万，请DBA参与设计。

SQL常居于某列的散列值访问表，则对表使用LIST分区，LIST分区不支持多列分区关键字；常见情况针对某个地区或者某个业务进行数据访问，那么就使用地区编号或者业务编号作为分区关键字。

将对表的多种访问结合考虑来确定分区的细度：

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

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

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

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

1.  各分区采用各自的表空间存储，使用user_tab_partitions字典来确定每个分区的表空间；

2.  分区表的索引采用本地索引。

4.2.4 HASH分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当表的数据量超过5000万，请DBA参与设计。

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

对确定分区细度的考虑：

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

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

1.  各分区采用各自的表空间存储，使用user_tab_partitions字典来确定每个分区的表空间；

2.  对于HASH分区表，大多数情况下依然要求采用本地索引，但是如果分区过细，也可以采用全局索引，因为根据HASH分区表的特征（各分区无业务区分，都有数据），该表很少会发生分区维护的工作。

4.2.5 RANGE-LIST分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当表的数据量超过5000万，请DBA参与设计。

SQL访问表时，既依据某列值的范围，又依据其他列的离散值或者范围，这种情况下采用RANGE-LIST复合分区，常用于语表中的数据需要依据一个时间字段做周期性删除等维护，并且正常业务SQL访问既依据时间字段，又依据其他字段的散列值进行访问的情况。

比如：电信增值业务计费表，既有时间又有业务属性列，统计的时候，会选择时间范围和业务属性，所以可以以时间列为分区关键字建立RANGE分区，以业务属性列为关键字建立LIST子分区；分区划分的方法：

n 就按照大多数范围访问的范围值来划定RANGE分区的范围，依据单个LIST子分区关键字的值来划分子分区;

n 如果LIST子分区中数据量较小而且又常被一起访问的子分区可以合并成一个子分区；

n 如果LIST子分区中一个子分区关键字值对应的子分区数据量还是很大，超过500，影响性能，那么可以通过细分RANGE分区来达到减少LIST子分区数据量的目的，这点和LIST分区在该情况下的处理方法（转化成RANGE-HASH）不同。

1.  各子分区应该尽量分散到不同的表空间中存储，使用user_tab_subpartitions字典来确定每个子分区的表空间；

2.  RANGE-LIST大多数情况采用本地索引，因为常根据RANGE分区关键字的来进行分区维护。

4.2.6 RANGE-HASH分区的规范

大数据量的表需进行分区化，当表的数据量超过500万，需设计成分区表，当表的数据量超过5000万，请DBA参与设计。

SQL访问表时，主要依据某个列的范围进行访问，即访问特征符合RANGE分区的要求，或者数据维护特征符合RANGE分区的要求，但是以SQL或者维护的数据范围来划定分区，分区数据量又很大，对性能有影响，需再进行子分区，由于分区中的数据都会被访问到，所以子分区采用HASH方法，整个表就是RANGE-HASH分区；

划定分区的方法：先按照大多数范围访问的范围值来划定RANGE分区的范围，再依据性能情况来确定HASH子分区的数据量。

1.  各子分区应该尽量分散到不同的表空间中存储，使用user_tab_subpartitions字典来确定每个子分区的表空间；

2.  RANGE-HASH大多数情况采用本地索引，因为常根据RANGE分区关键字的来进行分区维护。

5 索引的设计规范

5.1 索引分类

Oracle中可以创建多种类型的索引，以适应各种表的特点和各种查询条件的特点。可以按列的多少、索引列是否唯一、索引数据的组织形式对索引进行分类。

5.1.1单列索引与复合索引

一个索引可以由一个或多个列组成，用来创建索引的列被称为“索引列”。

 单列索引是基于单列所创建的索引，复合索引是基于两列或者多列所创建的索引。

5.1.2 唯一索引与非唯一索引

唯一索引是索引列值不能重复的索引，非唯一索引是索引列可以重复的索引。

无论是唯一索引还是非唯一索引，索引列都允许取NULL值。默认情况下，Oracle创建的索引是不唯一索引。

5.1.3 B树索引、位图索引与函数索引

B树索引是按B树算法组织并存放索引数据的，所以B树索引主要依赖其组织并存放索引数据的算法来实现快速检索功能。

Oracle中不仅能够直接对表中的列创建索引，还可以对包含列的函数或表达式创建索引，这种索引称为“函数索引”。

位图索引在多列查询时，可以对两个列上的位图进行AND和OR操作，达到更好的查询效果。

5.2 命名规范

命名规则：类别码_表名_附加码，采用大写字符。

类别码：一般索引 IDX、位图索引BIDX 、唯一索引 UK、主键 PK、外键 FK，类别码根据索引的性质填写。

表名：表名应该简洁明了，尽量使用完整的单词，如果导致拼上表名后，长度超过30个字符，则从最后一个单词开始，依次向前采用该单词的缩写。（如果没有约定的缩写，则采用该单词前4个字母来表示）。另外，表名中的名词单词都应使用单数形式，以免混淆，如：使用FACTORY而非FACTORIES。

附加码：可以是序号，也可以是字段名，根据实际的使用情况进行填写。

5.3 索引设计规范

索引是从数据库中获取数据的最高效方式之一。95%的数据库性能问题都可以采用索引技术得到解决。但大量的DML操作会增加系统对索引的维护成本，对性能会有一定影响，对于插入相当频繁的表要慎重建索引，索引也会占相当的存储空间，所以要 根据硬件环境和应用需求在空间和时间上达到最好的平衡点。主要原则：

n 适当利用索引提高查询速度：当数据量比较大，了解应用程序的会有哪些查询，依据这些查询需求建相应的索引；最好亲自试验一下，模拟一下生产环境的数据量，在此数据量下，比较一下建索引前后的查询速度；索引对性能会有一定影响，对于DML频繁列的索引要定期维护（重建）。但是，索引的结构对于索引的更新（比如在插入数据的时候）是有一定优化的，所以不要在没有试验以前过分夸大它对性能的影响，最终还是以试验为准。

n 不要建实际用不上的索引，与上条相关，如果建的索引并不提高任何一应用中的查询速度，则要把它删除；有些数据库有相关工具可以发现实际未被使用的索引，可以利用一下。

n 索引列的选择：如果检索条件有可能包含多列，创建联合主键或者联合索引，把最常用于检索条件的列放在最前端，其他的列排在后面；不要索引使用频繁的小型表，假如这些小表有频繁的DML就更不要建立索引，维护索引的代价远远高于扫描表的代价；

n 主键索引在建立的时候一定要明确的指定名称，不能让系统默认建立主键索引（可能有些数据库无法指定主键名，则例外）；

n 当有联合主键或者联合索引时，注意不要建重复的索引。

举例说明：

表 EMPLOYEES ，它的主键是建立在列 DEPARTID 和 EMPLOYEEID 上的联合主键，并且创建主键的语句中 DEPARTID 在前， EMPLOYEEID 在后。在这样一个表里，通常就没有必要再为 DEPARTID 建一个索引了，联合索引的情况也一样。

更复杂的情况，比如表 EMPLOYEES ，有一个索引建立在列 CORPID, DEPARTID, EMPLOYEEID 三列上，在创建语句中也依据上述顺序，就没有必要再为 CORPID 建立索引；也没有必要再建立以 CORPID 在前， DEPARTID 在后的联合索引；如果 EMPLOYEEID 需要索引，那么为 EMPLOYEEID 建立一个索引是不与上面的索引重复的； DEPARTID 列也类似

n 控制一个表的索引数量，尽量使得一个表的索引数量小于五个。

5.3.1 指定表空间规范

每个索引在创建时，必须指定表空间，不要采用默认表空间，以防止索引建立在system 空间和非索引专用空间，以减少IO 冲突，提高性能。

5.3.2 主键索引的规范

对数据量表应该先在主键列建唯一索引，再建主键约束。分区表的主键必须采用该方法设计。原则上所有的数据表都要有主键。

主键上隐含索引，drop或disable主键时，索引会丢失，为保证性能不变，为了对主键约束和相应索引有更多的控制，对大表(分区表)的索引采用如下方式建立：

1. 在准备建主键的列上建立唯一索引（UNIQUE INDEX）: CREATE UNIQUE INDEX Index_Name ON Table_Name(Column_Name) TABLESPACE  TBS_INDEX;

2. 再加上主键约束：

ALTER TABLE Table_Name ADD (PRIMARY KEY(Column_Name) USING INDEX TABLESPACE TBS_INDEX );

 Oracle会在指定的列上加上主键约束，并且使用该索引。

分区表的主键默认索引是全局索引，所以主键索引的分区方法：先建立分区化的唯一索引，再建主键约束。

5.3.3 唯一约束索引的规范

针对大数据量表应该先在唯一约束列上建立普通索引，再添加唯一性约束。分区表的唯一约束必须采用该方法。

删除或禁用唯一性约束通常同时使相关联的唯一索引失效，因而降低了数据库性能。要避免这样问题，可以采取下面的步骤：（a）在唯一性约束的列上创建非唯一性索引（普通索引）；（b）添加唯一性约束。

5.3.4 外键列索引的规范

对于关联两个表字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。为了提高性能，无论表的大小，外键都要建立索引，一是为了子父表关联查询的性能考虑，二是为了避免父子表修改而发生死锁。对于有要求级联删除属性的外键，必须指定on delete cascade。

普通表的外键列建立普通索引即可，如果表是分区表，则依据表的情况建立本地索引或者全局索引。

5.3.5 复合索引的规范

复合索引只有在该种复合常被和该表相关的大多数SQL使用时才建立。复合索引的列数不能超过5个，否则该索引很少会被使用。

n 复合索引的第一列，可以通过不使用该种复合的SQL来确定。假设一些SQL的WHERE中复合使用列为ABC，而其他一些SQL的WHERE中常使用的是C列，那么该复合索引可以按照CAB的顺序建立，这样上述两种SQL都能使用该索引；

n 对于不能把握好的复合索引，请在选择性大的列上分别建立单列索引；

n 切忌不能将表相关的所有SQL中WHERE涉及到的列复合起来建立复合索引。

5.3.6 函数索引的规范

由于使用形式需和创建形式一致，尽量避免使用函数索引。如果想要使用函数索引，请尽量进行转化。

由于函数索引在使用时，使用形式必须和创建形式一致，故应该尽量避免使用函数索引，尽量采用如下方法转化SQL以避免函数索引的使用：

原本在WHERE中列上添加函数的，取函数的反意义函数添加到“=”另一侧的常数项上，这样只需要在列上建立普通索引即可，比如常见的日期转化函数：

TO_CHAR(CREATE_TIME)=’2010-07-07’采用TO_DATE()转化为CREATE_TIME=TO_DATE(‘2010-07-07’,’yyyy-mm-dd’)。

5.3.7 位图索引的规范

静态表中的低基数列可以使用位图索引。在事务型数据库（OLTP）中禁止使用位图（bitmap）索引，在报表型数据库（OLAP）中的静态表，可以适当使用。

5.3.8 反向索引的规范

列值顺序增加的列，其上的WHERE运算是或者=而不是范围（between and或者 ）检索时，可以采用反向函数。一般创建反向索引的列为NUMBER类型，值由SEQUENCE生成。

5.3.9 分区索引的规范

对分区表的索引，需要做分区维护的，必须使用局部索引。一般情况下，HASH分区表可以采用全局索引，其他分区，包括RANGE-HASH也应该采用本地索引，主要是由于HASH分区表不常进行分区维护。

5.3.10 索引重建的规范

重建索引使用ALTER INDEX REBUILD方式，禁止采用DROP INDEX & CREATE INDEX方式。分区表等大数据量表的索引必须采用ALTER INDEX REBUILD方式重建。

方法：

ALTER INDEX IDX_NAME REBUILD [TABLESPACE TBSP_NAME]。

6 其他数据库对象设计规范

6.1 命名规范

n 视图：VW_相关表名，或者根据需要另取名字；

n 存储过程：SP_存储过程名，用英文表达存储过程意义；

n 函数：FUN_函数名称，用英文表达函数作用；

n 触发器：TR_触发器名称，用英文表达触发器作用；

n 包及包体：PKG_包或包体名称，用英文表达包及包体的作用；

n 序列：SEQ_序列名称，用英文表达序列的意义；

n 游标：CUR_游标名称；

n 自定义记录类型：REC_自定义记录类型名称，用英文表达自定义记录类型含义；

n 自定义记录类型变量：V_REC_自定义记录类型变量名称，用英文表达自定义记录类型变量含义；

n 自定义嵌套类型：TBL_自定义嵌套类型名称，用英文表达自定义嵌套类型含义；

n 自定义嵌套类型变量：V_TBL_自定义嵌套类型变量名称，用英文表达自定义嵌套类型变量含义；

n 输入参数：I_输入参数名称，用英文表达输入参数类型或含义；

n 输出参数：O_输出参数名称，用英文表达输出参数类型或含义。

6.2 视图设计规范

6.2.1 尽量使用简单的视图，避免使用复杂的视图

简单视图：数据来自单个表，且无分组(distinct/group by)、无函数。

复杂视图：数据来自多个表，或有分组、有函数。

6.2.2 按照必要性原则建立视图

在不太清楚视图用法的情况下，尽量不建。因为一旦建了，就有被滥用的危险；如果需要建视图，只要是打算长期使用的，请写入数据库设计中，明确它的用途、目的。

6.3 存储过程、函数、触发器的设计规范

请把程序包、存储过程、函数、触发器，与应用程序一同加入CVS中，进行版本控制。因为此四者包含了代码，应用程序对他们的依赖程度比对表、视图的依赖程度更高。

适量但尽量少使用存储过程、函数、触发器。使用存储过程、函数、触发器的影响：

n 可以减少数据库与客户端的交互，提高性能；

n 有的数据库还对他们进行了某种程度的编译，在执行的时候，不用再对其中的SQL等语句进行解析，从而提高速度；

n 如果有多个应用，使用了不同的开发语言，当有某些关键的或者复杂逻辑希望共享，则可以考虑使用存储过程或者函数。因为存储过程等在数据库一级是共享的；

n 增强了应用对数据库的依赖，如果打算将来移植数据库的话，使用得越多，则移植的困难越大；数据库中的业务逻辑越多（存储过程等），应用以及存储过程等的维护难度也会增大；

n 通常存储过程等没有面向对象的特性，不容易设计出易于扩展的结构。当存储过程比较复杂时，或者它们相互间的调用关系比较复杂时，可能难于维护。

6.3.1 关于触发器的设计

触发器是一种特殊的存储过程，通过数据表的DML 操作而触发执行，其作用为确保数据的完整性和一致性不被破坏而创建，实现数据的完整性约束。

说明：触发器的before 或after 事务属性的选择时候，对表操作的事务属性必须与应用程序保持一致，以避免死锁发生，在大型导入表中，尽量避免使用触发器。

在系统中尽量不要使用触发器。

7 SQL访问规范

7.1 尽量不要写复杂的SQL

过于复杂的SQL可以用存储过程或函数来代替，效率更高；甚至如果能保证不造成瓶颈的话，把条SQL拆成多条也是可以的。这与一般的编码规范很相似的，首先是要易懂。易懂也就意味着容易维护，对较为复杂的sql语句加上注释，说明算法、功能注释风格：注释单独成行、放在语句前面。

7.2 避免使用SELECT *

程序中不能出现SELECT *，即使是选择全部选择项，也需要全部指明，这主要出于如下原因：

第一，使用*相对比较慢，因为Oracle在解析的过程中，会将“*”依次转换成所有的列名，这个工作是通过遍历数据字典完成，这意味着将耗费更多的时间；

第二，为避免以后相关表增加字段造成程序错误，比如INSERT INTO SELECT和SELECT INTO语句会报错。

以下不符合规范：

select * from sm_duty ;

应如下书写：

select duty_id,duty_name,creation_date,created_by   from sm_duty ;

7.3 INSERT时需写全列名

代码中INSERT语句必须写出全部列名，以保证表增加字段后语句执行不受影响。

以下不符合规范：

insert into inv_parameters

values(:field1,:field2,:field3);

应如下书写：

insert into inv_parameters(field1,field2,field3)

values (:field1,:field2,:field3) ;

7.4 进行DML操作时使用CTAS进行数据备份

在进行DML操作（INSERT，UPDATE，DELETE）之前，必须对数据进行备份，使用如下语句。

方法一：表数据全部备份：

CREATETABLE TAB_NAME_BAK AS SELECT * FROM TAB_NAME ;

方法二：部分备份：对大表仅备份将要修改的数据：

CREATE TABLE TAB_NAME_BAK

AS SELECT * FROM TAB_NAME WHERE [选择出被操作数据的条件];

7.5 大数据量DML操作分多次执行

DML操作涉及到大数据量时，请分解为多次执行：

对于UPDATE和DELETE每次涉及数据量在1万条左右，并且每次执行完就提交；

对于INSERT INTO SELECT如果采用提示(/*+ append parallel */)可以处理百万级别的数据量。

7.6 使用绑定变量，降低高硬解析

使用“变量绑定”来处理一条SQL带不同常量多次执行的情况，动态绑定可以大大优化SQL的执行效率，还可以优化Oracle的内存使用。

在Java中，结合使用setXXX系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL语句的性能。

JAVA情况下的动态绑定示例如下：

String v_id = 'xxxxx';

String v_sql = 'select name from tb_a where id = ? ';

stmt = con.prepareStatement( v_sql );

stmt.setString(1, v_id ); //为绑定变量赋值

stmt.executeQuery();

7.7 选择最有效率的表名顺序

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此，FROM子句中写在最后的表(基础表 driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们：首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行排序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

示例：表 policy有 18,888 条记录；表 claim 有1 条记录

选择policy作为基础表 (不好的方法)

select count(*) from claim, policy 执行时间26.09秒

选择claim作为基础表 (好的方法)

select count(*) from policy, claim 执行时间0.96秒 ;

7.8 关注WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前。

示例：

(低效)

SELECT policy.aab001,claim.aab051

  FROM policy,claim

WHERE claim.aae140=’31’

   AND policy.aab001=claim.aab001;

(高效)

SELECT policy.aab001,claim.aab051

 FROM policy,claim

WHERE policy.aab001=claim.aab001

   AND claim.aae140=’31’;

7.9 用EXISTS替代IN

实际情况看，使用exists替换in效果不是很明显，基本一样。

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。

示例：

(低效)

SELECT *

 FROM policy

Where aac001 in (select aac001 from claim where aab001=str_aab001 and aae140=’31’);

或

SELECT *

 FROM policy

Where aac001 in (select distinct aac001 from claim where aab001=str_aab001 and aae140=’31’);

(高效)

SELECT *

 FROM policy

Where exists (select 1 from claim where aac001=policy.aac001 and aab001=str_aab001 and aae140=’31’);

in的常量列表是优化的(例如：aae110 in (‘20’,’30’))，不用exists替换；in列表相当于or

用NOT EXISTS替代NOT IN

Oracle在10g之前版本not in都是最低效的语句，虽然在10g上not in做到了一些改进，但仍然还是存在一些问题，因此我们一定要使用not exists来替代not in的写法。

在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成NOT EXISTS。

示例：

(低效)

SELECT *

  FROM policy

WHERE aab001 NOT IN (SELECT aab001 from policy where aclaim0=’100’);

(高效)

SELECT *

  FROM policy

WHERE not exists (SELECT 1 from policy where aab001=policy.aab001 and aclaim0=’100’);

7.10 用表连接替换EXISTS 

在子查询的表和主表查询是多对一的情况，一般采用表连接的方式比EXISTS更有效率。

示例：

(低效)

SELECT policy.*

  FROM policy

 Where exists (select 1 from claim

                      where aac001=policy.aac001

                        and aab001=policy.aab001

                        and aae140='31'

                        and aae041='200801');

(高效)

SELECT policy.*

 FROM policy, claim

Where policy.aac001 = claim.aac001

  and policy.aab001 = claim.aab001

  and claim.aae140='31'

  and claim.aae041='200801';

到底exists和表关联哪种效率高，其实是根据两个表之间的数据量差别大小是有关的，如果差别不大实际上速度基本差不多。

7.11用EXISTS替换DISTINCT 

当提交一个包含一对多表信息(比如个人基本信息表和个人参保信息表)的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXISTS替换。

示例：

(低效)

select distinct policy.aac001

from claim,policy

where claim.aac001 = policy.aac001

and claim.aae140='31'

and policy.aab001='100100';

(高效)

select policy.aac001

from policy

where exists (select 1 from claim where aac001 = policy.aac001

and aae140='31')

and policy.aab001='100100';

EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。因此如果不是特别研究和追求速度的话（例如：数据转换），查询一个表的数据需要关联其他表的这种情况查询，建议采用EXISTS的方式。

7.12 尽量用union all替换union 

Union会去掉重复的记录，会有排序的动作，会浪费时间。因此在没有重复记录的情况下或可以允许有重复记录的话，要尽量采用union all来关联。

7.13 使用DECODE函数来减少处理时间 

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

示例：

(低效)

select count(1) from policy where aab001=’100001’ and aac008=’1’;

select count(1) from policy where aab001=’100001’ and aac008=’2’;

(低效)

Select count(1),aac008

 From policy

Where aab001=’100001’

  and aac008 in (’1’,’2’)

group by aac008;

(高效)

select count(decode(aac008,’1’,’1’,null)) zz,

count(decode(aac008,’2’,’1’,null)) tx

from policy

where aab001=’100001’;

group by和order by 都会影响性能，编程时尽量避免没有必要的分组和排序，或者通过其他的有效的编程办法去替换，比如上面的处理办法。

7.14 尽量避免用order by

Order by需要查询后排序，速度慢影响性能，如果查询数据量大，排序的时间就很长。但我们也不能避免不使用，这样大家一定注意一点的是如果使用order by那么排序的列表必须符合索引，这样在速度上会得到很大的提升。

7.15 用Where子句替换HAVING子句

避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。

示例：

(低效)

SELECT aac008,count(1)

FROM policy

GROUP BY aac008

HAVING aac008 in (‘1’,’2’);  

(高效)

SELECT aac008,count(1)

 FROM policy

Where aac008 in (‘1’,’2’)

GROUP BY aac008 ;

HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等。除此而外，一般的条件应该写在WHERE子句中。

7.16 减少多表关联

表关联的越多，查询速度就越慢，尽量减少多个表的关联，建议表关联不要超过3个（子查询也属于表关联）。

数据转换上会存在大数据量表的关联，关联多了会影响索引的效率，可以采用建立临时表的办法，有时更能提高速度。

7.17 避免重复访问

7.17.1 使用group by

同源单组单查询

如下不符合规范：

SELECT CLASS,sum(COL) FROM  TAB_TEST  WHERE CLASS=’A’ UNION ALL

SELECT CLASS,sum(COL) FROM  TAB_TEST  WHERE CLASS=’B’ UNION ALL

SELECT CLASS,sum(COL) FROM  TAB_TEST  WHERE CLASS=’C’ ;

应如下书写：

SELECT CLASS,sum(COL) FROM TAB_TEST GROUP BY CLASS ;

7.17.2 用表更新表

一个表同时更新另一个表的多个字段

如下不符合规范：使用TB_SOURCE表更新表TB_TARGET的多个字段

UPDATE TB_TARGET  A  SET  

A.COL1 = (select B.COL1 from TB_SOURCE B where B.id = A.id) ,

A.COL2 = (select B.COL2 from TB_SOURCE B where B.id = A.id) ,

A.COL3 = (select B.COL3 from TB_SOURCE B where B.id = A.id) ,

A.COL4 = (select B.COL4 from TB_SOURCE B where B.id = A.id)

WHERE A.id IN ( select B.id from TB_SOURCE B) ;

应如下书写：

UPDATE TB_TARGET  A  

SET (COL1, A.COL2, A.COL3, A.COL4 )=(SELECT B.COL1, B.COL2, B.COL3, B.COL4

FROM TB_SOURCE B WHERE B.id = A.id)

WHERE EXISTS (select 1 from TB_SOURCE B where B.id = A.id) ;

7.17.3竖向显示变横向显示

竖向显示变横向显示

如下不符合规范：

SELECTA.C1 AC1,A.C2AC2,A.C3AC3,

      B.C1BC1,B.C2BC2,B.C3BC3,

      C.C1CC1,C.C2CC2,C.C3CC3

FROM

 (SELECT'123' X,'SYNONYM' C1, sum(2)C2,count(1)C3  

    FROM   TAB WHERE TABTYPE = 'SYNONYM')A,

 (SELECT'123' X,'TABLE'  C1, sum(2)C2,count(1)C3  

    FROM   TAB WHERE TABTYPE = 'TABLE')B,

 (SELECT'123' X,'VIEW'  C1, sum(2)C2,count(1)C3  

    FROM   TAB WHERE TABTYPE = 'VIEW')C

应如下书写：

SELECTMAX(DECODE(TABTYPE,'SYNONYM','SYNONYM',NULL)) AC1,

      MAX(DECODE(TABTYPE,'SYNONYM',sum(2),0))AC2,

      MAX(DECODE(TABTYPE,'SYNONYM',count(1),0))AC3,

      MAX(DECODE(TABTYPE,'TABLE','TABLE',NULL)) BC1,

      MAX(DECODE(TABTYPE,'TABLE',sum(2),0))BC2,

      MAX(DECODE(TABTYPE,'TABLE',count(1),0))BC3,

      MAX(DECODE(TABTYPE,'VIEW','VIEW',NULL)) CC1,

      MAX(DECODE(TABTYPE,'VIEW',sum(2),0))CC2,

      MAX(DECODE(TABTYPE,'VIEW',count(1),0))CC3       

FROM   TAB

WHERE   TABTYPE IN('TABLE','SYNONYM','VIEW')

GROUPBY TABTYPE;

7.18 完成事务及时commit

对于一个完成了的事务，请用commit显示提交，这是避免锁争用的锁等待的需要，特别是对DML操作频繁的表。

7.19数据库连接及时关闭

程序中必须显示关闭数据库连接，不仅正常执行完后需显示关闭，而且在异常处理块（例如java的exception段）也要显示关闭。

7.20 索引的使用

在实际的应用系统中索引问题导致性能问题可能能占到80%，在程序优化上索引问题是需要我们特别关注的。

7.20.1避免在索引列上使用函数或运算

这个问题是在我们实际编程中出现过的，请大家一定注意。在索引列上使用函数或运算，查询条件都不会使用索引。

低效，索引失效：

Select * from ka02 where aka060=’10001000’ and to_char(aae030,’yyyymm’)=’200801’;

高效，索引有效：

Select * from ka02 where aka060=’10001000’ and aae030=to_date(’200801’,’yyyymm’);

低效，索引失效：

Select * from ka02 where aka060=’10001000’ and aae031+1=sysdate;

高效，索引有效：

Select * from ac04 where aac001=’10001000’ and aae031=sysdate -1;

7.20.2避免改变索引列的类型

索引列的条件如果类型不匹配，则不能使用索引。

低效，索引失效：

Select * from policy where aac001=10001000;

高效，索引有效：

Select * from policy where aac001=’10001000’;

7.20.3避免在索引列上使用NOT

避免在索引列上使用NOT, NOT不会使查询条件使用索引。对于!=这样的判断也是不能使用索引的，索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中。

低效，索引失效：

select *

From claim

Where not apolicy9=’10’;

高效，索引有效：

select *

From claim

Where apolicy9 in (’20’,’30’);

7.20.4用>=替代>

虽然效果不是特别明显，但建议采用这种方式。

低效：

SELECT *

FROM policy

WHERE apolicy9 > ‘10’;

高效：

SELECT *

FROM policy

WHERE apolicy9 >=’ 1 0’

两者的区别在于, 前者DBMS首先定位到apolicy9=10的记录并且向前扫描到第一个apolicy9大于10的记录,而后者DBMS将直接跳到第一个apolicy9等于10的记录。

7.20.5 避免在索引列上使用IS NULL和IS NOT NULL

对于索引列使用is null或is not null不会使用上索引。因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

低效，索引失效：

select * from policy where apolicy9 is not null;

高效，索引有效：

select * from policy where apolicy9 in(‘10’,’20’,’30’) ;  

在实际开发中，对于这类的问题很难避免，如果不是特别影响速度或者要求速度的，可以忽略。

7.20.6 带通配符（%）的like语句

带通配符（%）的like语句，%在常量前面索引就不会使用。

低效，索引失效：

Select * from policy where aac002 like ‘%210104’;

Select * from policy where aac002 like ‘%210104%’;

高效，索引有效：

Select * from policy where aac002 like ‘210104%’;

7.20.7 总是使用索引的第一个列

如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时,优化器才会选择使用该索引。如Claim的复合索引：aac001、aae140、aae041

低效，索引失效：

Select * from claim where aae140=’31’ and aae041=’200801’;

高效，索引有效：

Select * from claim where aac001=’10001000’ ;

如果不使用索引第一列基本上不会使用索引，使用索引要按照索引的顺序使用，另外使用复合索引的列越多，查询的速度就越快   。

8 编码及注释规范

8.1 编码规范

8.1.1 避免隐式的数据类型转换

在书写代码时，必须确定表的结构和表中各个字段的数据类型，特别是书写查询条件时的字段就更要注意了。

以下代码不符合规范，status_type 是number 型数据.

select wdj.wip_entity_id

from wip.wip_discrete_jobs wdj

where wdj.status = ‘3’;

应如下书写：

select wdj.wip_entity_id

from wip.wip_discrete_jobs wdj

where wdj.status = 3;

8.1.2 不要将空的变量值直接与比较运算符(符号)比较

不要将空的变量值直接与比较运算符(符号)比较。如果变量可能为空，应使用is null 或is not null 或nvl 函数进行比较。

以下代码不符合规范

if v_user_name = null then

dbms_output.put_line(‘user name is null’);

end if;

应该如下书写:

if v_user_name is null then

dbms_output.put_line(‘user name is null’);

end if;

8.1.3 跨行语句，第一关键字应当左对齐

同一条语句占用多于一行时，每行的第一个关键字应当左对齐。

示例

select field1,field2,…

from t_tablename

where field1>1

and filed2

group by field1,filed2

order by field1,filed2;

8.1.4 Insert … values 和update 语句书写规范

对于Insert … values 和update 语句，一行写一个字段，这段后面紧跟注释（注释语句左对齐），values 和insert 左对齐，左括号和右括号与insert、values 左对齐。

示例

insert into sm_user

(

user_id, --用户ID，主键

user_name, --用户名

login_name --登录名

)

values

(

p_user_id,

p_user_name,

p_login_name

);

8.1.5 Insert … select 语句书写规范

insert…select 语句时，应使每行的字段顺序对应，以每行最多不超过4个字段，以方便代码阅读，括号的内容另起一行缩进2 格开始书写，关键字单词左对齐，左括号、右括号另起一行与左对齐。

示例

insert into sm_duty_bak

(

duty_id,duty_name,created_by,creation_date,

last_updated_by,last_update_date,disable_date

)

select duty_id,duty_name,created_by,creation_date,

last_updated_by,last_update_date,disable_date

from sm_duty

where duty_id=:duty_id;

8.1.6 避免使用嵌套的IF语句 

尽量避免使用嵌套的if 语句，在这种情况应使用多个if 语句来判断其可能。

以下示例不符合规范

if v_count =0 then

if v_flag = 0 then

null;

else

null;

end if;

else v_count =1 then

if v_flag = 0 then

null;

else

null;

end if;

end if;

应如下书写：

if (v_count = 0) and (v_flag = 0) then

null;

elsif (v_count = 0 ) and (v_flag = 1) then

null;

elsif (v_count = 1) and (v_flag = 0) then

null;

elsif (v_count = 1) and (v_flag = 1) then

null;

end if;

8.1.7 减少控制语句的检查次数 

减少控制语句的检查次数，如在 else(if..else)控制语句中，对最常用符合条件，尽量往前被检查到。

以下示例不符合规范（假设v_count = 1 条件大数情况会被满足）

if (v_count = 0) then

null;

elsif (v_count = 1) then

null;

end if;

应如下书写：

if (v_count = 1) then

null;

elsif (v_count = 0) then

null;

end if;

8.1.8 语句涉及多个表时，使用别名来限定字段名 

当一个PL/SQL 或SQL 语句中涉及到多个表时，始终使用别名来限定字段名，这使其它人阅读起来更方便，避免了含义模糊的引用，其中能够通过别名中清晰地判断出表名，别名统一。

别名命名时，尽量避逸使用无意义的代号a、b 、c… ， 而应该有意义。

如表mtl_system_items_b 对应别名为msi，po_headers_all 别名对应为pha。

以下编码不符合规范：

select wip_entity_name,a.wip_entity_id,a.date_released

from wip.wip_entities b,

wip.wip_discrete_jobs a

where b.wip_entity_id = a.wip_entity_id

and a.status_type = 3

and a.date_released > trunc(sysdate)

应如下书写：

select we.wip_entity_name, wdj.wip_entity_id, wdj.date_released

from wip.wip_entities we,

wip.wip_discrete_jobs wdj

where we.wip_entity_id = wdj.wip_entity_id

and wdj.status_type = 3

and wdj.date_released > trunc(sysdate) ;

8.1.9 其他编码规范 

n 程序块采用缩进风格书写，保证代码准确高效、清晰易读、易于维护，风格一致，缩进格数统一为2 个。

n 必须使用空格，不允许使用TAB 键。

n 不允许把多个语句写在一行中，即一行只写一条语句。

示例：

v_count := 1;

v_creation_date := sysdate;

n 避免将SQL 语句写到同一行，再短的语句也要在关键字和谓词处换行。

示例：

select duty_id,

duty_name

from sm_duty

where duty_id = :duty_id;

n 相对独立的程序块之间必须加空行。

示例：

v_duty_id := 1;

 

if trunc(nvl(disabled_date, sysdate + 1)) > trunc(sysdate) then

select duty_name

from sm_duty

where duty_id = :duty_id;

…

end if;

n 超过110 列的语句要分行书写，长表达式应在低先级操作符处换行，操任符或关键字放在新行之首。划分出新行应当适当地缩进，使排版整齐，语句可读。

示例：

以下不符合规范

(a*b*c*d) + (e*f) + …

应写成：

(a*b*c*d)

+ (e*f) + …

说明：

A. 加法的优先级低于乘法，因此应在加号处折行。

B. 两组乘法虽然在逻辑上会先于加法，但显示加上括号使用可读性更强。

n begin、end 独立成行。

示例：

示例：以下不符合规范

begin null; exception when others then null; end;

应写成：

begin

null;

exception

when others then

null;

end;

n if 后的条件要用括号括起来，括号内每行最多两个条件。

示例：

if (v_count = 1 or v_count = 2

or v_count = 5 or v_count = 6

)  then

select sysdate

into v_date

from dual;

end if;

n 不同类型的操作符混合使用时，建议使用括号进行隔离，以使代码清晰。

示例：

if (‘abc’||’def’) = ‘abcdef’ then

null;

end if;

n 确保变量和参数在类型和长度与表数据列类型和长度相匹配。

示例：

说明：如果与表数据列宽度不匹配，则当较宽或较大的数据传进来时会产生运行异常。

如fnd_users 表user_name 字符宽为50，当用户名大于10 时会报错。

declare

v_user_name varchar2(10);

begin

select fu.user_name

into v_user_name

from fnd_user fu

where fu.user_id = p_user_id;

end;

n 对于非常复杂的sql(特别是多层嵌套，带子句或相关的查询)，应该先考虑是否设计不当引起的，对于复杂的一些sql 可以考虑使用程序实现，原则上遵循一句话只做一件事情。

n 尽可能地使用相关表字段的类型定义，形如%type、%rowtype。

n 存储过程中变量的声明应集中在as 和begin 关键字之间，不允许在代码中随意定义变量。定义变量时，完成相同功能模块的变量应放在一起，与不同模块的变形量应空行隔开，增加代码的可读性。

n order by 后面字段不唯一时分页会出现问题，分页时如果order by 后面的字段不唯一，一定要让order by 唯一，最佳方案是增加一pk，如实在没办法则可以追加rowid，order by 后尽量避免使用rowid。分页可以考虑优化器设置为first_rows.

n 当存储过程有多个分支返回时，若有事务，需确保各个分支都结束了事务。

n in、out 参数应按类别分开书写，不要交叉，对于out 参数，特别是nesttable、record，尽量都带上nocopy，提高程序的运行效率。

8.2 注释规范

8.2.1 注释语法 

注释语法包含两种情况：单行注释、多行注释。

n 单行注释：注释前有两个连字符（--）。

n 多行注释：符号/*和*/之间的内容为注释内容。

8.2.2 每个块和过程开发放置注释 

在每一个块和过程（存储过程、函数、包、触发器、视图等）的开头放置注释

/*****************************************************************

*name : --程序名

*function : --程序功能

*input : --输入参数

*output : --输出参数

*author : --作者

*CreateDate : --创建时间

*UpdateDate : --程序更改信息（包括作者、时间、更改内容等）

******************************************************************/

8.2.3 代码注释应放在其上方或右方 

注释应与其描述的代码相似，对代码注释应放在其上方或右方(对单条语句的注释)相应位置，不可放在下面。

示例：

--注释放在上方或右方

select policy_no,--保单号

from  policy

where policy_no =‘000000000000000007’;

8.2.4 其他注释规范 

n 创建每一数据库对象时都要加上COMMENT ON注释，以说明该对象的功能和用途；建表时，对数据列也要加上COMMENT ON注释，以说明该列取值的含义。

n 一般情况下，源程序有效注释量须在30%以上。

n 注释内容要清晰、明了、含义准确，防止注释二义性。

n 禁止在注释中使用缩写，特别是非常用的缩写。

n 对存储过程的任何修改，都需要在注释最后添加修改人、修改日期及修改原因等信息。

n 对程序分支必须书写注释。

n 在代码的功能、意图层次上进行注释，提供有用、额外的信息。

n 注释与所描述的内容进行同样的缩排。

n 注释上面的代码应空行隔开。

9 PLSQL优化

9.1性能问题分析

出现性能问题，需要从整体进行分析，一般总体上会有几种现象：

n  整个系统运行速度都慢

n -- 在业务不忙的时候，所有模块都慢；

n -- 只有在业务繁忙的时候，所有模块都慢；

n -- 时快时慢。

n  个别业务模块运行速度慢

n -- 在业务不忙的时候，该模块就慢；

n -- 只有在业务繁忙的时候，该模块才慢；

n -- 时快时慢。

一般导致系统性能慢的因素：

n  硬件：客户端、服务器 CPU 、内存和存储设备配置不符合应用系统要求；

n  网络：网速低、丢包；

n  操作系统参数设置：参数设置不合理；

n  受到其他软件干扰：例如：防火墙、病毒；

n  Weblogic 设置：参数设置不合理；

n  Oracle 设置：内存、表空间、 redolog 、系统参数设置不合理等；

n  PLSQL 程序： plsql 不优化、未使用索引、锁表。

在不同现象下，可能导致性能问题的因素：

n  一般来说，如果在不办理业务的情况下，整个系统性能就慢的话，应该和PLSQL程序优化是没有关系的。可能的因素为硬件、网络、操作系统、其他软件干扰、ORACLE设置。

n  只有在业务繁忙的时候，整体系统性能都慢，有可能的因素有硬件、操作系统设置、WEBLOGIC设置、ORACLE设置、PLSQL程序。如果在sqlplus下做查询都慢，那么就和weblogic没有关系。

n  一般来说，如果在不办理业务的情况下，个别业务模块速度就慢的话，那么基本上就是PLSQL程序不优化或未使用索引造成的。

n  只有在业务繁忙的时候，个别模块慢，有可能的因素有硬件、操作系统设置、WEBLOGIC设置、ORACLE设置、PLSQL程序、锁表。

这里我们重点是说明PLSQL优化、索引优化的问题，其他例如：硬件、网络、操作系统和oracle设置等因素我们不进行说明。

9.2 PLSQL优化的核心思想

出现PLSQL优化实际上就是避免出现“导致性能问题的内在原因”，实际上编写程序，以及性能问题跟踪应该本着这个核心思想去考虑和解决问题。

n  PLSQL程序占用 CPU 的情况

n -- 系统解析 SQL 语句执行，会消耗 CPU 的使用；

n -- 运算（计算）会消耗 CPU 的使用。

n  PLSQL程序占用内存的情况

n -- 读写数据都需要访问内存；

n -- 内存不足时，也会使用磁盘。

n  PLSQL程序增大 IO 的情况

n -- 读写数据都需要访问磁盘 IO ；

n -- 读取的数据越多， IO 就越大。

大家都知道CPU现在都很高，计算速度非常快；访问内存的速度也很快；但磁盘的访问相对前两个相比速度就差的非常大了，因此PLSQL性能优化的重点也就是减少IO的瓶颈，换句话说就是尽量减少IO的访问。

性能的优先级CPU->内存->IO，影响性能的因素依次递增。根据上面的分析，PLSQL优化的核心思想为：

n  避免过多复杂的SQL脚本，减少系统的解析过程；

n  避免过多的无用的计算，例如：死循环；

n  避免浪费内存空间没有必要的SQL脚本，导致内存不足；

n  内存中计算和访问速度很快；

n  尽可能的减少磁盘的访问的数据量，该原则是PLSQL优化中重要思想；

n  尽可能的减少磁盘的访问的次数，该原则是PLSQL优化中重要思想。

PLSQL优化、索引不使用的问题产生的影响：

n  对于某段不优化的程序或语句频繁或者全表扫描一个表时，它访问磁盘的时间和占用的吞吐量是很高的，这就导致系统IO长时间处于忙的状态，导致整个系统性能下降。

n  对于某段不优化的程序或语句频繁或者全表扫描一个表时，其他的业务程序也访问同一个表时，速度将大大下降。

n  如果是更新表操作时间长，还可能会导致锁等待，导致会话堵塞，weblogic端也出现压力问题，导致这个系统性能下降。

我们一般根据这些现象、以及一些方法判断，来初步分析产生性能问题的大致原因的范围。不过对于这一点，还是比较困难的，因为产生问题的原因是多种的，并且还有一定的关联。

9.3 PLSQL优化示例

9.3.1 减少对表的查询

该问题是我们编程中出现过的问题，请大家一定注意，并且该类问题优化可以带来较大性能的提升。

示例：

低效

cursor cur_kc24_mz is

Select akc260

from kc24

where akb020 =str_akb020

and aka130=’11’;    

cursor cur_kc24_zy is

Select akc260

from kc24

where akb020 =str_akb020

and aka130=’21’;

for rec_mz in cur_kc24_mz loop

     …..

end loop;  

for rec_mz in cur_kc24_zy loop

   …..

end loop;

高效

cursor cur_kc24 is

Select akc260,aka130

from kc24

where akb020 =str_akb020

and aka130 in (’11’,’21’);

for rec_kc24 in cur_kc24 loop

   if rec_kc24.aka130=’11’ then

     …..

   end if;

   if rec_kc24.aka130=’21’ then

     …..

end if;

end loop;

高效的做法使用同样的条件（或者说是索引）只访问一次磁盘，低效的做法访问了2次磁盘，这样速度差别将近2倍。

9.3.2 避免循环（游标）里面嵌查询

游标里面不能嵌入查询(或者再嵌游标)，其实也不能有update delete等语句，只能有insert语句。但在实际的编程情况下是不可能完全避免的，但我们一定要尽量避免。该类问题也是我们程序中出现过的问题，该类问题也可以大大提升程序效率，请大家一定注意。

示例：

低效

Cursor cur_ac04 is

  Select aac001,akc010

   From ac04

  Where aab001= prm_aab001;

……

For rec_ac04 in cur_ac04 loop

   Select aac008

     Into str_aac008

     from policy

where aac001=rec_ac04.aac001;

   if str_aac008=’1’ then

     n_jfje := rec_ac04.akc010*0.08;

   end if;

   if str_aac008=’2’ then

        n_jfje := rec_ac04.akc010*0.1;

   end if;

End loop;

高效

Cursor cur_ac04 is

  Select policy.aac001,ac04.akc010,policy.aac008

   From ac04,policy

  Where ac04.aac001=policy.aac001

and aab001= prm_aab001;

……

For rec_ac04 in cur_ac04 loop

   if rec.aac008=’1’ then

     n_jfje := rec_ac04.akc010*0.08;

   end if;

   if rec.aac008=’2’ then

        n_jfje := rec_ac04.akc010*0.1;

   end if;

end loop;

优化的方法是尽量把游标循环中的查询语句放到游标查询中一起查询出来，这样相当于只访问了1次磁盘读到内存；如果放到游标中的话，假如游标有100万数据量，那么程序需要100万次磁盘，可以想象浪费了多少IO的访问。

如果在程序编写上没有办法避免游标中有查询语句的话（一般情况是可以避免的），那么也要保证游标中的查询使用的索引（即查询速度非常快），例如：游标100万数据量，游标中的查询语句执行需要0.02秒，从这个速度上来说是很快的，但总体上看100万*0.02秒=2万秒=5小时33分钟，如果写一个不够优化的语句需要1秒，那么需要几天能执行完呢？

9.3.3 group by优化

Group by需要查询后排序，速度慢影响性能，如果查询数据量大，并且分组复杂，这样的查询语句在性能上是有问题的。尽量避免使用分组或者采用上面的一节的办法去代替。

采用group by的也一定要进行优化。

示例：

低效

select ac04.aac001,policy.aac002,policy.aac003,sum(aac040),policy.aab001

 from ac04,policy

 where ac04.aac001=policy.aac001 and policy.aab001='1000000370'

 group by ac04.aac001,policy.aac002,policy.aac003,policy.aab001;

高效

select ac04.aac001,policy.aac002,policy.aac003,gzze,policy.aab001

 from (select aac001,sum(aac040) gzze from ac04 group by aac001) ac04,policy

 where ac04.aac001=policy.aac001

   and aab001='1000000370';

9.3.4 删除重复记录

一般数据转换的程序经常会使用到该方法。最高效的删除重复记录方法 ( 因为使用了ROWID)。

示例：

DELETE FROM policy a

WHERE a.rowid > (SELECT MIN(b.rowid)

FROM policy b

WHERE a.aac002=b.aac002

and a.aac003=b.aac003 );

9.3.5 COMMIT使用

数据转换的程序需要关注这一点。

1. Commit执行也是有时间的，不过时间特别短，但提交频率特别大，必然也会浪费时间。

2. commit可以释放资源，在大量数据更新时，必须及时提交。

n 回滚段上用于恢复数据的信息；

n 被程序语句获得的锁；

n redo log buffer 中的空间；

n ORACLE为管理上述3种资源中的内部花费。

示例：

Cur_ac20有5000万数据

n_count :=0;

For arec in cur_ac20 loop

   Insert into ac20 ……

   n_count := n_count + 1;

   If n_count = = 100000 then  --10万一提交

     commit;

     n_count := 0;

   End if;

End loop;

Commit;

如果1条一提交，需要提交5000万必然浪费时间；如果整体提交，资源不能释放，性能必须下降。在实际编程时，应注意提交的次数和提交的数据量的平衡关系。

9.3.6 批量数据插入

数据转换时或者大业务数据插入时，有以下几种办法进行数据插入（不包括imp、impdp和sqlloader）。

n Insert into …select 方式

将查询的结果一次插入到目标表中。

例如：

Insert into policy_bak select * from policy;

由于是一次查询一次插入，并且最后一次提交，他的速度要比下面描述的curosr的方式速度要快。但查询插入的数据量过大必然会占用更多的内存和undo表空间，只能在插入完成后提交，这样资源不能释放，会导致回滚表空间不足和快照过旧的问题，另外一旦失败需要全部回滚。因此建议小数据量（例如：300万以下）的导入采用该种方式。

n Insert /*+append */ into … select方式

该种方式同上种方式，不过由于有append的提示，这种语句不走回滚段直接插入数据文件，速度非常快。注意系统开发编程不能使用该种方式，数据转换可以灵活使用。

n Cursor方式

定义游标，然后逐行进行插入，然后定量提交。

示例：

Cusor cur_ac20 is

   Select * from ac20;

… .

n_count :=0;

For rec_ac20 in cur_ac20 loop

   Insert into ac20_bak

        (aac001,

…… .)

      Values

        (rec_ac20.aac001,

         ….);

   If n_count :==100000 then

     Commit;

     n_count :=0;

   End if;

End loop;

n 批绑定的方式

通过游标查询将数据逐行写到数组里（实际上就是内存），然后通过批绑定的语句forall … in… insert into…values…;将内存的数据一次写入到数据文件中。相比cursor的方式减少了对io的访问次数，提高了速度，但注意内存别溢出了。

9.3.7 索引使用优化

在实际的应用系统中索引问题导致性能问题可能能占到80%，在程序优化上索引问题是需要我们特别关注的。本节主要描述什么情况索引会不生效。

n 多个平等的索引

当SQL语句的执行路径可以使用分布在多个表上的多个索引时, ORACLE会同时使用多个索引并在运行时对它们的记录进行合并, 检索出仅对全部索引有效的记录。

在ORACLE选择执行路径时，唯一性索引的等级高于非唯一性索引。然而这个规则只有当WHERE子句中索引列和常量比较才有效。如果索引列和其他表的索引类相比较，这种子句在优化器中的等级是非常低的。

如果不同表中两个相同等级的索引将被引用，FROM子句中表的顺序将决定哪个会被率先使用。FROM子句中最后的表的索引将有最高的优先级。

如果同一表中有两个相同等级的索引被引用，oracle会分析最有效的索引去引用，其他的索引不会使用，如果这些相同等级的索引效果差不多，oracle可能会自动合并进行使用。

n 不明确的索引等级

当ORACLE无法判断索引的等级高低差别，优化器将只使用一个索引，它就是在WHERE子句中被列在最前面的。

n 自动选择索引

如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性。在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引。

9.3.8 使用提示(Hints)

对于表的访问，可以使用两种Hints：FULL 和 ROWID。

n FULL hint 告诉ORACLE使用全表扫描的方式访问指定表。

示例：

SELECT /*+ FULL(POLICY) */ *

FROM POLICY

WHERE AAC001 = ‘10001000’;

如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束时仍然停留在SGA中，你就可以使用CACHE hint 来告诉优化器把数据保留在SGA中。通常CACHE hint 和 FULL hint 一起使用。

示例：

SELECT /*+ FULL(POLICY) CACHE(POLICY)*/ *

FROM POLICY;

n ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID的操作访问表。

采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候, 使用这种方式，你需要知道ROIWD的值或者使用索引。

索引hint 告诉ORACLE使用基于索引的扫描方式，你不必说明具体的索引名称。

示例：

SELECT /*+index(IDX_POLICY_AAC002)*/ aac001

FROM POLICY

WHERE  aac002='8881111111111111111';

在不使用hint的情况下, 以上的查询应该也会使用索引。然而，如果该索引的重复值过多而你的优化器是CBO, 优化器就可能忽略索引。在这种情况下, 你可以用INDEX hint强制ORACLE使用该索引。

ORACLE hints 还包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等。

使用hint ，表示我们对ORACLE优化器缺省的执行路径不满意，需要手工修改。

这是一个很有技巧性的工作，除非特定的情况，例如：数据转换，其他情况最好不用。

9.3.9 表上存在过旧的分析

我们现在很多项目都存在性能问题，其中有很多种情况都是由于分析过旧，导致ORACLE判断索引级别和资源成本上出现问题，会导致ORACLE判断错误不使用索引。

解决办法：

第一种办法：删除分析，停止oracle10g的自动分析，但不使用分析，oracle访问数据的CPU消耗就过大。

第二种办法：重新分析，但过长时间后，索引是否会再次失效，没有验证过。

9.3.10 表上存在并行

表上存在并行，ORACLE判断索引级别和资源成本上出现问题，会导致ORACLE判断错误不使用索引。尽量不要在表级别定义并行。

9.3.11 关于索引建立

索引的使用是肯定会大大提高查询的速度，但索引其实也是一种数据，它也是存放的用户类型的表空间下的，索引建立的越多越大，占用的空间也越大，从用户的环境来说这也不是问题，但如果一个表有过多过大的查询，必然会影响insert、delete和update索引列的速度，因为这些操作改变了整个表的索引顺序，oracle需要进行调整，这样性能就下降了。因此我们一定要合理的建立好有效的索引，编程也要符合索引的规则，而不能是索引符合编程的规则。

案例：

某项目数据转换，采用游标循环insert的方式，总共2000万的数据，总共用了4个小时，原因就是目标表里面有很多索引。解决方法是先删除索引再执行转换脚本，结果不用1小时就完成了，建立全部的索引不到半个小时。

原因就是第一种方式每次insert都改变索引顺序，共执行改变2000万次，而第二种方式整体上执行索引顺序就一次。

建立索引时可以开并行参数(如果系统支持并行)且不写日志，可以加快建立索引时间(create index idx_xxx on xxx(a,b) parallel 2 nologging)

9.3.12 Expain Plan分析索引使用

在PL/SQL Developer等工具有一个Expain Plan分析的功能，这个功能可以帮助我们分析SQL语句是否使用了索引、使用哪些索引和使用索引的效果。

1. 选择explain plan的窗口

 

2.在上面栏中输入SQL语句，然后点击工具栏上的EXECUTE执行（或按F8），就会在下面显示Optmizergoal优化器的默认方式（也可手工选择），以及下面的解释计划，从解释计划上能看到哪个条件语句使用了索引，哪个没有使用；哪个表使用了索引，使用了哪个索引，哪些表是全表扫描的(TABLE ACCESS FULL)。

 

 3.分析内容说明：

n  COST ：根据优化程序的基于开销的方法所估计出的操作开销值对于使用基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较执行规划开销大小的权重值；

n  Cardinality ：根据基于开销的方法对操作所访问行数的估计值；

n  Bytes ：根据基于开销的方法对操作所访问字节的估计；

n  通 过设置，我们还能看到更多的信息，例如： CPU 使用、时间等等。

全表扫描的(TABLE ACCESS FULL)肯定是速度慢的，如果是大数据量的表，那么这个语句是绝对影响性能的。

另外使用了索引也不一定性能就高，因为索引使用也有效率的情况，下面列出索引常见的使用类型：

n  INDEX UNIQUE SCAN：唯一索引扫描，速度最快

n  INDEX RANGE SCAN：范围索引扫描，使用这个索引时，就需要看COST、Cardinality、Bytes的大小了，如果特别大，有时候还可能速度低于全表扫描的速度。

结语

---

上面是我对关系型数据库优化的一些理解和实战经验，如果对您有帮助，烦请星标收藏，关注、转发，如有任何问题，请留言或关注加我微信入群，我们一起探讨，一起持续构建数据治理体系。