3 万字，关系型数据库性能体系，设计和效率提升

Lucifer三思而后行

发布于 2021-12-30 19:23:31

2K00

代码可运行

文章被收录于专栏：公众号：Lucifer三思而后行公众号：Lucifer三思而后行

运行总次数：0

代码可运行

前言

本文档详细定义了关系型数据库的：

数据库模型设计规范
表的设计规范
分区表的设计规范
索引的设计规范
其他数据库对象的设计规范
SQL的访问规范
编码和注释规范

并详细介绍了 SQL 调优 的关注点和常用方法，作为数据库规划、设计、开发及维护人员的技术参考资料。

用以指导关系型数据库的设计和开发，突出性能是设计出来的，证明质量也是可以设计出来的。

只要理解透这篇，关系型数据库性能提升 30% 不是问题！

一、预期的读者和阅读建议

本文档预期的读者：

项目经理
开发经理
DBA
数据结构管理师
系统设计师
开发师
测试师

等相关岗位的人员。

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

二、数据库模型设计规范

1、数据库建模原则性规范

对于涉及数据库的项目，需要构建数据库逻辑模型图，逻辑模型图是项目组成员之间在数据库层面沟通交互的依据，必须规范画图（表，主键，外键，关系）。

对于表的个数在 20 个以上的模型，需要 DBA 参与设计，并作最终审核。

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

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

2、实体型之间关系认定规范

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

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

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

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

1:1 关系选取任何一个表的主键到另一个表中，作为外键来体现。
1:N 关系将1表的主键在N表中，以外键形式来体现。
N:M 关系采用“关系表”来体现，该关系表的主键是由相关实体表的主键组成的复合主键；各实体表主键不但组成了该关系表的主键，同时也被看作外键在该关系表中存在。
对于三个以上表之间的“多元关系”常需要和反范式化冗余字段结合起来设计，以保证查询速度。

3、范式化 1NF 的规范

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

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

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

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

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

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

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

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

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

4、范式化 2NF 的规范

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

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

实体表中一般不会出现违反 2NF 的情况，因为都是“一个”主键列，而关系表是两个以上列的“复合”主键，故而关系表容易出现违反 2NF 的情况。主要是该关系表非主键外的属性，本该属于相关的某个实体表的，却放到了该关系表中。这使得该属性不能通过该关系表的复合主键唯一确定， DML 操作会发生错误。

如果违反了 2NF，那么应该把这个属性从关系表中拆分，也许会单独形成一个表，绝大部分情况下是将该属性归并到某个相关的实体表中。

违反 2NF 的例子：

学生考试情况中，有两个实体表：学生表和学科表，学生与学科之间的考试关系就是 N:M 的关系，就要创建一张关系表存储该多对多的考试关系，表的主键为学生编号和学科编号，属性为考试分数；那么“任课老师”该放在那里呢？

如果放到考试关系表中，那么安排任课老师，必须先进行考试，这显然不符合实际，也就是任课老师不该依赖于学生编号和学科编号，只是依赖于学科编号，也就是说任课教师信息应该放在学科表中。

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。

应该将其从学科表中拆出来放入教师表中，不然的话，会发生操作异常，比如，假设一个教师已经存在但是还没有为其分配科目，那么他的电话就无法存入库中。

6、反范式化冗余字段使用规范

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

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

常用在两个地方：

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

反范式化冗余字段实例：

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

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

如果在程序开发前设计的冗余字段，可以在正常的业务逻辑程序中一并处理；
如果是程序完成之后增加的冗余字段，可以使用触发器维护；
对于OLAP中大量存在冗余字段，可能需要使用单独的处理任务进行维护。

7、数据库对象命名基本规范

7.1、遵循行业规范

当有相关国家/行业强制性数据结构标准规范存在时，用于存储某业务数据的业务表在表名命名上原则上应该遵从标准规定，其表中相关字段的中文名称（即数据项名称）若标准规范上有规定的应遵循规定。

此外，若标准规范上对数据项的类型、长度有规定的，原则上也应当遵循或保证能直接兼容保存和访问。

7.2、简单命名原则

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

建议每个单词分段长度不要超过6位。

7.3、字符范围原则

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

如XXX_XXX_XXX，但不限于三段式。

7.4、字母全部大写或小写原则

所有数据库对象命名字母全部大写或小写。

Oracle对大小写不敏感，但是有些数据库对大小写敏感，统一大小写有助于在多个数据库间移植。

7.5、勿用保留词原则

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

7.6、同义性原则

对于同一含义尽量使用相同的单词命名，不管使用英文单词还是英文缩写，以免引起误解。

如TELEPNHOE的A表中表示固定电话号码，在B表中就不应该用于表示移动电话号码。尽量避免同一单词表示多种含义的情况。

7.7、富有含义原则

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

7.8、扩展性原则

各系统或者项目在遵循本规范的基础上可以根据需要制定更明确的规范细则，以满足项目管理需要。

如对模块进行统一命名，然后用于表名的前缀。建议每个系统在启动开发时建立数据字典，管理命名中使用的英文单词、英文单词缩写等，对用于命名的单词进行统一管理。

三、表的设计规范

1、命名规范

1.1、表的命名规范

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

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

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

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

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

1.2、字段的命名规范

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

字段用来存储 sequence 序列，命名以id结尾。例：bar_code_id。
字段用来存储号码，命名以 no 结尾。例：policy_no。
字段用来存储日期，命名以 date 结尾。例：create_date。
字段用来存储数量，命名以 num 结尾。例：insured_num。
字段用来存储金额，命名以 amt 结尾。例：prem_amt。
字段用来存储名称，命名以 name 结尾。例：client_name。
字段用来存储描述信息，命名以 desc 结尾。例：bank_desc。
字段用来存储基础表的 code 信息，命名以 code 结尾。例：region_code。
字段用来存储标志信息，命名以 flag 结尾。例：underwrit_flag。
字段用来存储英文名称和英文描述，命名以 en 结尾。例：address_en。

2、表的设计规范

2.1、指定表空间规范

每个表在创建时候，必须指定所在的表空间，不要采用默认表空间，以防止表建立在 system 空间上，导致性能问题。

对于事务比较繁忙的数据表，必须存放在在该表专用空间中。

2.2、表的主键规范

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

有无原则

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

构成原则

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

创建原则

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

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

2.3、表的外键规范

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

适量建立外键

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

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

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

以缺省的方式建立外键

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

2.4、字段类型及宽度的规范

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

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

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

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

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

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

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

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

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

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

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

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

数据库存储

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

操作系统存储

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

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

2.8、关于字段能否为 NULL 值

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

2.9、关于冗余列的规范

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

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

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

2.10、使用注释的规范

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

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

对表添加注释：

SQL>comment on  table  <table_name> is 'xx';

对字段添加注释：

SQL>comment on column<table_name>.<col_name> is 'xx';

2.11、一个表所含数据量的规范

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

在系统上线前： 通过对业务分析，判断一个表的数据量；

在系统上线后： 可以通过 exp 的日志，Top 性能 SQL，count(1) 来发现数据量大的表。

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

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

2.12、增量同步表的设计规范

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

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

3、字段类型规范

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

INTEGER 改为 NUMBER(n)

FLOAT 改为 NUMBER(p,s)

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

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

VARCHAR2(n CHAR) 改为 VARCHAR2(n)

CHAR(n CHAR) 改为 CHAR(n)

3.3、国家字符集相关

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

NCHAR 改为 CHAR

NVARCHAR2 改为 VARCHAR2

NCLOB 改为 CLOB

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

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

CLOB 和 NCLOB 改为 VARCHAR2

3.5、不能使用高精度：TIMESTAMP

不能使用高精度： TIMESTAMP

TIMESTAMP 改为 DATE

3.6、关于 CHAR 字段

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

四、分区表的设计规范

1、表空间及分区表的概念

1.1、表空间

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

1.2、分区表

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。

表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个“表空间”(物理文件上)，这样查询数据时，不至于每次都扫描整张表而只是从当前的分区查到所要的数据大提高了数据查询的速度。

2、表分区的具体作用

Oracle 的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。

通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。

但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

什么时候使用分区表：

表的大小超过 2GB，数据量超过 500 万；
表中包含历史数据，新的数据被增加都新的分区中。

3、表分区的优缺点

表分区有以下优点：

改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

缺点：

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

4、分区表设计规范

4.1、不使用全局索引

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

4.2、RANGE 分区的规范

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

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

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

大多数SQL操作的分区关键字值的范围；
数据维护的需要，比如以月为单位删除历史数据；
数据访问的性能，以操作范围确定的分区数据量还是过大，比如大于500万，则还需要进行细分；
一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来看，每个分区10万数据量的情况比每个分区20万数据量的情况要快很多，所以需要灵活掌握。

当各个分区中的数据能均等划分时性能最好，如果相差太大，则考虑采用其它分区，或者将大数据量的分区再进行 HASH 子分区；
各分区采用各自的表空间存储，使用 user_tab_partitions 字典来查看确定每个分区的表空间位置；
分区表的索引采用本地索引，因为常会根据分区关键字（比如时间）进行分区维护（比如删除1年前的数据，也就是删除1年前的分区），分区维护时全局索引会失效，而本地索引不会失效，这能保证访问表时索引正常可用。

4.3、LIST 分区的规范

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

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

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

一般使用一个分区关键字的值来划定一个分区；
可以把分区关键字的值相对应数据比较少的几个分区合并作一个分区；- 如果一个分区关键字值所对应的数据量过大，比如大于 500 万，则应该对表采用 RANGE 分区，对该值的分区再采用 HASH 子分区；也就是说，一个可以采用 LIST 分区的表，肯定可以转化成 RANGE 分区（可带子分区），反之不然；
一个分区的数据量要小于 500 万，这是一个硬性的尺度，但从技术上来看，每个分区 10 万数据量分区方法比每个分区 20 万数据量的分区方法要快很多，所以需要灵活掌握。

各分区采用各自的表空间存储，使用user_tab_partitions字典来确定每个分区的表空间；
分区表的索引采用本地索引。

4.4、HASH 分区的规范

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

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

对确定分区细度的考虑：

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

各分区采用各自的表空间存储，使用 user_tab_partitions 字典来确定每个分区的表空间；
对于 HASH 分区表，大多数情况下依然要求采用本地索引，但是如果分区过细，也可以采用全局索引，因为根据 HASH 分区表的特征（各分区无业务区分，都有数据），该表很少会发生分区维护的工作。

4.5、RANGE-LIST 分区的规范

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

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

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

分区划分的方法：

就按照大多数范围访问的范围值来划定RANGE分区的范围，依据单个 LIST 子分区关键字的值来划分子分区;
如果 LIST 子分区中数据量较小而且又常被一起访问的子分区可以合并成一个子分区；
如果 LIST 子分区中一个子分区关键字值对应的子分区数据量还是很大，超过 500，影响性能，那么可以通过细分 RANGE 分区来达到减少 LIST 子分区数据量的目的，这点和 LIST 分区在该情况下的处理方法（转化成 RANGE-HASH）不同。

各子分区应该尽量分散到不同的表空间中存储，使用 user_tab_subpartitions 字典来确定每个子分区的表空间；
RANGE-LIST 大多数情况采用本地索引，因为常根据 RANGE 分区关键字的来进行分区维护。

4.6、RANGE-HASH分区的规范

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

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

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

各子分区应该尽量分散到不同的表空间中存储，使用 user_tab_subpartitions 字典来确定每个子分区的表空间；
RANGE-HASH 大多数情况采用本地索引，因为常根据 RANGE 分区关键字的来进行分区维护。

五、索引的设计规范

1、索引分类

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

1.1、单列索引与复合索引

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

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

1.2、唯一索引与非唯一索引

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

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

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

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

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

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

2、命名规范

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

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

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

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

3、索引设计规范

索引是从数据库中获取数据的最高效方式之一，95% 的数据库性能问题都可以采用索引技术得到解决。

但大量的DML操作会增加系统对索引的维护成本，对性能会有一定影响，对于插入相当频繁的表要慎重建索引，索引也会占相当的存储空间，所以要根据硬件环境和应用需求在空间和时间上达到最好的平衡点。

主要原则：

适当利用索引提高查询速度：当数据量比较大，了解应用程序的会有哪些查询，依据这些查询需求建相应的索引；最好亲自试验一下，模拟一下生产环境的数据量，在此数据量下，比较一下建索引前后的查询速度；索引对性能会有一定影响，对于 DML 频繁列的索引要定期维护（重建）。但是，索引的结构对于索引的更新（比如在插入数据的时候）是有一定优化的，所以不要在没有试验以前过分夸大它对性能的影响，最终还是以试验为准。
不要建实际用不上的索引，与上条相关，如果建的索引并不提高任何一应用中的查询速度，则要把它删除；有些数据库有相关工具可以发现实际未被使用的索引，可以利用一下。
索引列的选择：如果检索条件有可能包含多列，创建联合主键或者联合索引，把最常用于检索条件的列放在最前端，其他的列排在后面；不要索引使用频繁的小型表，假如这些小表有频繁的 DML 就更不要建立索引，维护索引的代价远远高于扫描表的代价；
主键索引在建立的时候一定要明确的指定名称，不能让系统默认建立主键索引（可能有些数据库无法指定主键名，则例外）；
当有联合主键或者联合索引时，注意不要建重复的索引。 举例说明： 表 EMPLOYEES，它的主键是建立在列 DEPARTID 和 EMPLOYEEID 上的联合主键，并且创建主键的语句中 DEPARTID 在前，EMPLOYEEID 在后。在这样一个表里，通常就没有必要再为 DEPARTID 建一个索引了，联合索引的情况也一样。更复杂的情况，比如表 EMPLOYEES，有一个索引建立在列 CORPID，DEPARTID, EMPLOYEEID 三列上，在创建语句中也依据上述顺序，就没有必要再为 CORPID 建立索引；也没有必要再建立以 CORPID 在前，DEPARTID 在后的联合索引；如果EMPLOYEEID 需要索引，那么为 EMPLOYEEID 建立一个索引是不与上面的索引重复的；DEPARTID 列也类似。
控制一个表的索引数量，尽量使得一个表的索引数量小于五个。

3.1、指定表空间规范

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

3.2、主键索引的规范

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

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

在准备建主键的列上建立唯一索引（UNIQUE INDEX）:

CREATE UNIQUE INDEX Index_Name ON Table_Name(Column_Name) TABLESPACE  TBS_INDEX;

再加上主键约束：

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

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

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

3.3、唯一约束索引的规范

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

删除或禁用唯一性约束通常同时使相关联的唯一索引失效，因而降低了数据库性能。

要避免这样问题，可以采取下面的步骤：

在唯一性约束的列上创建非唯一性索引（普通索引）；
添加唯一性约束；

3.4、外键列索引的规范

对于关联两个表字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。

为了提高性能，无论表的大小，外键都要建立索引，一是为了子父表关联查询的性能考虑，二是为了避免父子表修改而发生死锁。

对于有要求级联删除属性的外键，必须指定 on delete cascade。

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

3.5、复合索引的规范

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

复合索引的第一列，可以通过不使用该种复合的 SQL 来确定。假设一些 SQL 的 WHERE 中复合使用列为 ABC，而其他一些 SQL 的 WHERE 中常使用的是 C 列，那么该复合索引可以按照 CAB 的顺序建立，这样上述两种 SQL 都能使用该索引；
对于不能把握好的复合索引，请在选择性大的列上分别建立单列索引；
切忌不能将表相关的所有 SQL 中 WHERE 涉及到的列复合起来建立复合索引；

3.6、函数索引的规范

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

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

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

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

3.7、位图索引的规范

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

3.8、反向索引的规范

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

3.9、分区索引的规范

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

3.10、索引重建的规范

重建索引使用 ALTER INDEX REBUILD 方式，禁止采用 DROP INDEX & CREATE INDEX 方式。

分区表等大数据量表的索引必须采用 ALTER INDEX REBUILD 方式重建。

方法：

ALTER INDEX IDX_NAME REBUILD [TABLESPACE TBSP_NAME]。

六、其他数据库对象设计规范

1、命名规范

视图： VW_相关表名，或者根据需要另取名字；
存储过程： SP_存储过程名，用英文表达存储过程意义；
函数： FUN_函数名称，用英文表达函数作用；
触发器： TR_触发器名称，用英文表达触发器作用；
包及包体： PKG_包或包体名称，用英文表达包及包体的作用；
序列： SEQ_序列名称，用英文表达序列的意义；
游标： CUR_游标名称；
自定义记录类型： REC_自定义记录类型名称，用英文表达自定义记录类型含义；
自定义记录类型变量： V_REC_自定义记录类型变量名称，用英文表达自定义记录类型变量含义；
自定义嵌套类型： TBL_自定义嵌套类型名称，用英文表达自定义嵌套类型含义；
自定义嵌套类型变量： V_TBL_自定义嵌套类型变量名称，用英文表达自定义嵌套类型变量含义；
输入参数： I_输入参数名称，用英文表达输入参数类型或含义；
输出参数： O_输出参数名称，用英文表达输出参数类型或含义。

2、视图设计规范

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

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

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

2.2、按照必要性原则建立视图

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

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

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

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

可以减少数据库与客户端的交互，提高性能；
有的数据库还对他们进行了某种程度的编译，在执行的时候，不用再对其中的SQL等语句进行解析，从而提高速度；
如果有多个应用，使用了不同的开发语言，当有某些关键的或者复杂逻辑希望共享，则可以考虑使用存储过程或者函数。因为存储过程等在数据库一级是共享的；
增强了应用对数据库的依赖，如果打算将来移植数据库的话，使用得越多，则移植的困难越大；数据库中的业务逻辑越多（存储过程等），应用以及存储过程等的维护难度也会增大；
通常存储过程等没有面向对象的特性，不容易设计出易于扩展的结构。当存储过程比较复杂时，或者它们相互间的调用关系比较复杂时，可能难于维护。

3.1、关于触发器的设计

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

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

???? 注意： 在系统中尽量不要使用触发器。

七、SQL访问规范

1、尽量不要写复杂的 SQL

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

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;

3、INSERT 时需写全列名

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

以下不符合规范：

insert into inv_parameters
values(:field1,:field2,:field3);

应如下书写：

insert into inv_parameters(field1,field2,field3)
values (:field1,:field2,:field3);

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 [选择出被操作数据的条件];

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

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

对于UPDATE和DELETE每次涉及数据量在1万条左右，并且每次执行完就提交；
对于INSERT INTO SELECT如果采用提示(/*+ append parallel */)可以处理百万级别的数据量。

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、选择最有效率的表名顺序

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秒;

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’;

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’);

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 和表关联哪种效率高，其实是根据两个表之间的数据量差别大小是有关的，如果差别不大实际上速度基本差不多。

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 的方式。

12、尽量用 union all 替换 union

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

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 都会影响性能，编程时尽量避免没有必要的分组和排序，或者通过其他的有效的编程办法去替换，比如上面的处理办法。

14、尽量避免用 Order by

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

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 子句中。

16、减少多表关联

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

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

17、避免重复访问

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;

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);

17.3、竖向显示变横向显示

竖向显示变横向显示。

如下不符合规范：

SELECT A.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')
GROUP BY TABTYPE;

18、完成事务及时 commit

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

19、数据库连接及时关闭

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

20、索引的使用

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

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;

20.2、避免改变索引列的类型

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

低效，索引失效：

Select * from policy where aac001=10001000;

高效，索引有效：

Select * from policy where aac001='10001000';

20.3、避免在索引列上使用 NOT

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

低效，索引失效：

select *
From claim
Where not apolicy9='10';

高效，索引有效：

select *
From claim
Where apolicy9 in ('20','30');

20.4、用 >= 替代 >

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

低效：

SELECT *
FROM policy
WHERE apolicy9 > '10';

高效：

SELECT *
FROM policy
WHERE apolicy9 >='10';

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

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’);

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

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%';

20.7、总是使用索引的第一个列

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

低效，索引失效：

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

高效，索引有效：

Select * from claim where aac001='10001000';

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

八、编码及注释规范

1、编码规范

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;

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;

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

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

示例：

select field1,field2,…
from t_tablename
where field1>1
and filed2<sysdate
group by field1,filed2
order by field1,filed2;

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);

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;

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;

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;

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);

1.9、其他编码规范

程序块采用缩进风格书写，保证代码准确高效、清晰易读、易于维护，风格一致，缩进格数统一为 2 个；
必须使用空格，不允许使用 TAB 键。
不允许把多个语句写在一行中，即一行只写一条语句。

示例：

v_count := 1;
v_creation_date := sysdate;

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

示例：

select duty_id,
duty_name
from sm_duty
where duty_id = :duty_id;

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

示例：

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;

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

示例：

以下不符合规范：

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

应写成：

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

说明：

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

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

begin、end 独立成行。

示例：

以下不符合规范：

begin null; exception when others then null; end;

应写成：

begin
	null;
exception
when others then
	null;
end;

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;

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

示例：

if ('abc'||'def') = 'abcdef' then
	null;
end if;

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

示例：

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

如 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;

对于非常复杂的 sql (特别是多层嵌套，带子句或相关的查询)，应该先考虑是否设计不当引起的，对于复杂的一些 sql 可以考虑使用程序实现，原则上遵循一句话只做一件事情。
尽可能地使用相关表字段的类型定义，形如 %type、%rowtype。
存储过程中变量的声明应集中在 as 和 begin 关键字之间，不允许在代码中随意定义变量。定义变量时，完成相同功能模块的变量应放在一起，与不同模块的变形量应空行隔开，增加代码的可读性。
order by 后面字段不唯一时分页会出现问题，分页时如果 order by 后面的字段不唯一，一定要让 order by 唯一，最佳方案是增加一 pk，如实在没办法则可以追加 rowid，order by 后尽量避免使用 rowid。分页可以考虑优化器设置为 first_rows。
当存储过程有多个分支返回时，若有事务，需确保各个分支都结束了事务。
in、out 参数应按类别分开书写，不要交叉，对于 out 参数，特别是 nesttable、record，尽量都带上 nocopy，提高程序的运行效率。

2、注释规范

2.1、注释语法

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

单行注释：注释前有两个连字符（–）。
多行注释：符号/和/之间的内容为注释内容。

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

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

/*****************************************************************
*name : --程序名
*function : --程序功能
*input : --输入参数
*output : --输出参数
*author : --作者
*CreateDate : --创建时间
*UpdateDate : --程序更改信息（包括作者、时间、更改内容等）
******************************************************************/

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

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

示例：

--注释放在上方或右方
select policy_no,--保单号
from  policy
where policy_no =‘000000000000000007’;

2.4、其他注释规范

创建每一数据库对象时都要加上COMMENT ON注释，以说明该对象的功能和用途；建表时，对数据列也要加上COMMENT ON注释，以说明该列取值的含义。
一般情况下，源程序有效注释量须在30%以上。
注释内容要清晰、明了、含义准确，防止注释二义性。
禁止在注释中使用缩写，特别是非常用的缩写。
对存储过程的任何修改，都需要在注释最后添加修改人、修改日期及修改原因等信息。
对程序分支必须书写注释。
在代码的功能、意图层次上进行注释，提供有用、额外的信息。
注释与所描述的内容进行同样的缩排。
注释上面的代码应空行隔开。

九、PLSQL优化

1、性能问题分析

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

整个系统运行速度都慢 1、在业务不忙的时候，所有模块都慢； 2、只有在业务繁忙的时候，所有模块都慢； 3、时快时慢。
个别业务模块运行速度慢 1、在业务不忙的时候，该模块就慢； 2、只有在业务繁忙的时候，该模块才慢； 3、时快时慢。

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

硬件：客户端、服务器 CPU、内存和存储设备配置不符合应用系统要求；
网络：网速低、丢包；
操作系统参数设置：参数设置不合理；
受到其他软件干扰：例如：防火墙、病毒；
Weblogic 设置：参数设置不合理；
Oracle 设置：内存、表空间、redolog、系统参数设置不合理等；
PLSQL 程序：plsql 不优化、未使用索引、锁表。

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

一般来说，如果在不办理业务的情况下，整个系统性能就慢的话，应该和 PLSQL程序优化是没有关系的。可能的因素为硬件、网络、操作系统、其他软件干扰、ORACLE 设置。
只有在业务繁忙的时候，整体系统性能都慢，有可能的因素有硬件、操作系统设置、 WEBLOGIC 设置、ORACLE 设置、PLSQL 程序。如果在 sqlplus下做查询都慢，那么就和 weblogic 没有关系。
一般来说，如果在不办理业务的情况下，个别业务模块速度就慢的话，那么基本上就是 PLSQL 程序不优化或未使用索引造成的。
只有在业务繁忙的时候，个别模块慢，有可能的因素有硬件、操作系统设置、WEBLOGIC 设置、ORACLE 设置、PLSQL 程序、锁表。

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

2、PLSQL 优化的核心思想

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

PLSQL 程序占用 CPU 的情况 1、系统解析SQL语句执行，会消耗CPU的使用； 2、运算（计算）会消耗CPU的使用。 3、PLSQL程序占用内存的情况 4、读写数据都需要访问内存； 5、内存不足时，也会使用磁盘。
PLSQL 程序增大 IO 的情况 1、读写数据都需要访问磁盘IO； 2、读取的数据越多，IO就越大。

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

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

避免过多复杂的SQL脚本，减少系统的解析过程；
避免过多的无用的计算，例如：死循环；
避免浪费内存空间没有必要的SQL脚本，导致内存不足；
内存中计算和访问速度很快；
尽可能的减少磁盘的访问的数据量，该原则是PLSQL优化中重要思想；
尽可能的减少磁盘的访问的次数，该原则是PLSQL优化中重要思想。

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

对于某段不优化的程序或语句频繁或者全表扫描一个表时，它访问磁盘的时间和占用的吞吐量是很高的，这就导致系统 IO 长时间处于忙的状态，导致整个系统性能下降。
对于某段不优化的程序或语句频繁或者全表扫描一个表时，其他的业务程序也访问同一个表时，速度将大大下降。
如果是更新表操作时间长，还可能会导致锁等待，导致会话堵塞，weblogic 端也出现压力问题，导致这个系统性能下降。

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

3、PLSQL优化示例

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倍。

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 秒，那么需要几天能执行完呢？

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';

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 );

3.5、COMMIT 使用

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

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

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

回滚段上用于恢复数据的信息；
被程序语句获得的锁；
redo log buffer 中的空间；
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 万必然浪费时间；如果整体提交，资源不能释放，性能必须下降。在实际编程时，应注意提交的次数和提交的数据量的平衡关系。

3.6、批量数据插入

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

Insert into …select 方式

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

例如：

Insert into policy_bak select * from policy;

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

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

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

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;

批绑定的方式

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

3.7、索引使用优化

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

多个平等的索引

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

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

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

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

不明确的索引等级

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

自动选择索引

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

3.8、使用提示(Hints)

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

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;

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 优化器缺省的执行路径不满意，需要手工修改。

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

3.9、表上存在过旧的分析

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

解决办法：

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

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

3.10、表上存在并行

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

3.11、关于索引建立

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

因此我们一定要合理的建立好有效的索引，编程也要符合索引的规则，而不能是索引符合编程的规则。

案例：

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

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

建立索引时可以开并行参数(如果系统支持并行)且不写日志，可以加快建立索引时间

create index idx_xxx on xxx(a,b) parallel 2 nologging;

3.12、Expain Plan 分析索引使用

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

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

COST：根据优化程序的基于开销的方法所估计出的操作开销值对于使用基于规则方法的语句该列为空该列值没有特定的测量单位它只是一个用于比较执行规划开销大小的权重值；
Cardinality：根据基于开销的方法对操作所访问行数的估计值；
Bytes：根据基于开销的方法对操作所访问字节的估计；
通过设置，我们还能看到更多的信息，例如：CPU 使用、时间等等。

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

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

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

目的

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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021/11/14 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库管理

数据库

oracle

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！