xx

 

 

 

NGCRM数据库设计开发规范

 

 

 

 

Prepared by

拟制

文栈良 w39827

Date

日期

2006-08-10

Reviewed by

审核

 

Date

日期

 

Approved by

批准

 

Date

日期

 

 

 

 

 

 

 

 

华为技术有限公司

版权所有  侵权必究

 

修订记录Revision record

日期

Date

修订版本Revision version

描述Description

作者Author

2006-08-10

1.0.0

初稿完成

文栈良(w39827)

2006-08-18

1.0.1

增加宏定义和SQL语句的规范

文栈良(w39827)

2010-11-19

1.0.2

补充部分SQL语句的规范和书写规范、常用语法、设计约束和建议

贺文喜46159

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

目 录

1 前言

1.1 目的

1.2 范围

1.3 读者

2数据库设计规范

2.1数据库对象命名规范

2.2 书写规范

2.2.1 大小写风格

2.2.2 缩进风格

2.2.3 空格及空行

2.2.4 创建表

2.2.5 创建过程/函数/包

2.3 常用语法

2.3.1 变量声明

2.3.2 返回值

2.3.3 包的使用

2.3.4 代码规模

2.4设计约束和建议

2.4.1 严禁用系统表空间作为用户默认表空间；严禁在系统表空间上创建用户数据库对象；严禁在SYSTEM/SYS等系统用户下，创建用户数据库对象

2.4.2 禁止在没有事务的存储过程和代码中，随意使用COMMIT和ROLLBACK

2.4.3 禁止在触发器中使用COMMIT和ROLLBACK

2.4.4 创建组合索引时，要注意组合索引的顺序和字段的选择性，把经常出现在WHERE条件中同时选择性比较好的字段放在复合索引的第一个位置。

2.4.5 创建分区表的索引时，必须创建本地（LOCAL）索引

2.4.6 不要在经常被修改的字段上建索引。

2.4.7 选择常用来关联表的字段做索引字段

2.4.8 尽量避免在业务表的参数及状态字段上创建索引

2.4.9 无特殊情况，不允许定义Undefined、nvarchar2等类型的字段

2.4.10 对访问比较频繁的字典表，需要加载到共享内存中

2.4.11 如果要创建的表需要高级复制，建表时必须创建唯一索引

2.4.12 如果表中有REGION字段，此表必须按照REGION进行分区

2.4.13 未按照REGION分区的表中有REGION字段的（历史创建的表），如果联合索引中且包含了REGION字段，REGION字段需要放在最后一位

2.4.14 对于分区表中，如果索引是唯一索引，分区字段必须被包含进去，且放在联合唯一索引的最后；否则，不能包含分区字段

2.4.15 关于非表类的数据库对象的存放

2.4.16 同义词的使用

3 SQL脚本开发规范和建议

3.1 SQL语句涉及region字段的不绑定(数据库分库分用户的情况下).

3.2 SQL绑定

3.3 SQL语句设置

3.4 SQL优化建议

3.4.1 SELECT子句中避免使用‘*’

3.4.2 减少访问数据库的次数

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

3.4.4 删除重复记录

3.4.5 用Where子句替换HAVING子句

3.4.6 使用表的别名(Alias)

3.4.7 用EXISTS替代IN、用NOT EXISTS替代NOT IN

3.4.8 识别'低效执行'的SQL语句

3.4.9 用索引提高效率

3.4.10 用EXISTS替换DISTINCT

3.4.11 sql语句用大写的，因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

3.4.12 避免在索引列上使用NOT

3.4.13 避免在索引列上使用计算

3.4.14 用>=替代>

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

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

3.4.17 用UNION-ALL 替换UNION ( 如果有可能的话)

3.4.18 用WHERE替代ORDER BY

3.4.19 避免改变索引列的类型

3.4.20 需要当心的WHERE子句

3.4.21 如果检索数据量超过30%的表中记录数，使用索引将没有显著的效率提高

3.4.22 避免使用耗费资源的操作

3.4.23 存储过程中取sysdate

3.4.24 禁止对VARCHAR(2000)之类的大字段值进行ORDER BY、DISTINCT、GROUP BY、UNION之类的操作。

3.4.25 如果要对整个表或分区的数据删除，建议使用TRUNCATE替代DELETE。

3.4.26 选择最有效的WHERE子句中的连接顺序

3.4.27 两个表连接查询时，数据量小的表做为基础表（离FROM最远的表），三个及以上的表连接查询时，交叉表作为基础表

3.4.28 INSERT语句是否指定了列名

 

 

1 前言

1.1 目的

为了统一规范NGCRM数据库设计和开发，除了遵守Oracle设计开发规范，本规范结合目前NGCRM系统的特点做一些必要的补充，希望能够更好的提高编程质量，规范数据库模型结构，提高开发人员设计及开发的规范性，进而提高版本质量。

1.2 范围

本规范主要适用于NGCRM数据库设计及开发。

1.3 读者

参与NGCRM系统数据库设计及开发的设计人员、开发人员。

 

2 数据库设计规范

2.1 数据库对象命名规范

具体参考《NGCRM 数据库对象命名规定.doc》。

2.2 书写规范

2.2.1 大小写风格

规则：数据库脚本中涉及到的各种保留字、关键字、操作符、数据类型、标识符、对象、字段、变量、常量等的大小写风格必须遵循以下规范：

表1            大小写规范表

图1 文字类型

图2 大小写

图3 示例

ORACLE保留字（Reserved Word）

大写

IDENTIFIED、INITIAL、LONG、MAXEXTENTS、RENAME、ROWNUM、SYSDATE、VARCHAR2

ORACLE关键字（Keywords）

大写

ANALYZE、AUTOEXTEND、COMPILE、DBA、EXTENT、INSTANCE、MAXTRANS、SAVEPOINT、TABLESPACE

ANSI SQL保留字（Reserved Word）

大写

ADD、BETWEEN、CREATE、DISTINCT、DROP、FROM、SESSION、SELECT、TABLE、UPDATE、VARCHAR

ANSI SQL关键字（Keywords）

大写

BEGIN、COMMIT、CONSTRAINT、COUNT、CURSOR、DECLARE、EXECUTE、PRIMARY、SUM、TRANSACTION

PL/SQL保留字（Reserved Word）

大写

ARRAY、BOOLEAN、DATABASE、DO、EXIT、RAISE、SAVEPOINT、RUN、WHILE、XOR

操作符（Operators）

大写

ALL、AND、ANY、ESCAPE、EXISTS、OR、UNION

数据类型（Data Types）

大写

INT、NUMBER、DATE

系统数据库对象（System Tables、Views、Functions、Packages、Package Methods、Exceptions）

大写

USER$、DBA_JOBS、DECODE、SUBSTR、DBMS_OUTPUT、PUT_LINE、NO_DATA_FOUND

自定义数据库对象（User Objects）

大写

T_PUB_TABLE_NAME、F_PUB_GETID、P_KBS_GET_SERVICEINFO

字段名（Field）

大写

USER_NAME、STAFFNO

常量（Constants）

大写

SECONDS_OF_DAY

变量（Variable）

逻辑首字母大写

v_BeginDate、v_FaxNumber、c_StaffAuth

说明：

l         ORACLE的关键字和保留字请参见视图V$RESERVED_WORDS；

l         上表中需要大写的关键字/保留字，可以录入UltraEdit的wordfile.txt，在使用UltraEdit开发时输入这些关键字保留字后会自动变为大写。

l          

2.2.2 缩进风格

规则：脚本代码块采用缩进风格书写，保证代码清晰易读，风格一致。缩进格数统一为2个空格，但需要关键字右对齐或者参数/字段向上对齐的时候例外。

示例1：

CREATE SEQUENCE SQ_CSP_TABLENAME

 MINVALUE 1

 START WITH 1

 INCREMENT BY 1;

示例2：

IF c_CursorName%ISOPEN THEN

 CLOSE c_CursorName;

END IF;

 

规则：缩进必须使用空格键，不允许使用TAB键。

 

规则：同一条语句占用多于一行时，每行的第一个关键字应当右对齐，但INSERT...SELECT语句例外。

示例1：

FETCH c_Cursor

 INTO v_Field1, v_Field2, ...

     ↑

关键字右对齐

示例2：

SELECT FIELD1, FIELD2, ...

  FROM T_CSP_TABLENAME

 WHERE FIELD1 > 1

   AND (FIELD2 < SYSDATE OR FIELD2 > SYSDATE + 3)

   AND FIELD3 LIKE 'HUAWEI%'

 GROUP BY FIELD1, FIELD2

 ORDER BY FIELD1, FIELD2;

      ↑

 关键字右对齐

 

规则：在INSERT...SELECT语句中，如果需要换行时，应使INSERT INTO部分的字段与SELECT部分的字段一一对应，以增强可读性。

示例：

错误的写法：

BEGIN

 INSERT INTO T_DEST_TABLENAME (DESTFIELDNAME1, DESTFIELDNAME2,

          DESTFIELDNAME3, DESTFIELDNAME4, DESTFIELDNAME5,

          DESTFIELDNAME6, DESTFIELDNAME7)

 SELECT SRCFIELDNAME1, SRCFIELDNAME2, SRCFIELDNAME3,

          SRCFIELDNAME4, SRCFIELDNAME5, SRCFIELDNAME6,

          SRCFIELDNAME7

    FROM T_SRC_TABLENAME;

END;

正确的写法A：

BEGIN

 INSERT INTO T_DEST_TABLE_NAME (DESTFIELDNAME1, DESTFIELDNAME2,

          DESTFIELDNAME3, DESTFIELDNAME4, DESTFIELDNAME5,

          DESTFIELDNAME6, DESTFIELDNAME7)

 SELECT SRCFIELDNAME1, SRCFIELDNAME2,

          SRCFIELDNAME3, SRCFIELDNAME4, SRCFIELDNAME5,

          SRCFIELDNAME6, SRCFIELDNAME7

    FROM T_SRC_TABLE_NAME;

END;

正确的写法B：

BEGIN

 INSERT INTO T_DEST_TABLE_NAME (DESTFIELDNAME1, -- 注释

                                       DESTFIELDNAME2, -- 注释

                                       DESTFIELDNAME3, -- 注释

                                       DESTFIELDNAME4, -- 注释

                                       DESTFIELDNAME5, -- 注释

                                       DESTFIELDNAME6, -- 注释

                                       DESTFIELDNAME7) -- 注释

 SELECT SRCFIELDNAME1, -- 注释

          SRCFIELDNAME2, -- 注释

          SRCFIELDNAME3, -- 注释

          SRCFIELDNAME4, -- 注释

          SRCFIELDNAME5, -- 注释

          SRCFIELDNAME6, -- 注释

          SRCFIELDNAME7 -- 注释

    FROM T_SRC_TABLE_NAME;

END;

正确的写法C：

BEGIN

 INSERT INTO T_DEST_TABLE_NAME (

         DESTFIELDNAME1, -- 注释

          DESTFIELDNAME2, -- 注释

          DESTFIELDNAME3, -- 注释

          DESTFIELDNAME4, -- 注释

          DESTFIELDNAME5, -- 注释

          DESTFIELDNAME6, -- 注释

          DESTFIELDNAME7) -- 注释

 SELECT SRCFIELDNAME1, -- 注释

          SRCFIELDNAME2, -- 注释

          SRCFIELDNAME3, -- 注释

          SRCFIELDNAME4, -- 注释

          SRCFIELDNAME5, -- 注释

          SRCFIELDNAME6, -- 注释

          SRCFIELDNAME7 -- 注释

    FROM T_SRC_TABLE_NAME;

END;

说明：

1、 在错误的写法中，虽然SELECT语句部分的SRCFIELDNAME1, SRCFIELDNAME2, SRCFIELDNAME3可以写在一行中，但由于INSERT INTO语句部分中DESTFIELDNAME1, DESTFIELDNAME2写在一行中，而DESTFIELDNAME3在下一行，因此SELECT语句中每行的字段应与INSERT INTO语句中的字段一一对应（如正确的写法A）；

2、 INSERT INTO语句中的各个字段折行后，应缩进并与上一字段左对齐（如正确的写法B），或者与INTO关键字左对齐（如正确的写法C）；

3、 SELECT语句中折行后的第一个字段名应缩进并与上一行的第一个字段名左对齐。

 

规则：INSERT INTO语句中，如果需要对每个字段增加注释，应将每个字段单独列为一行，并在行尾增加注释。

示例1：

INSERT INTO T_DEST_TABLE_NAME (DESTFIELDNAME1, -- 注释1

                                    DESTFIELDNAME2, -- 注释2

                                    DESTFIELDNAME3) -- 注释3

VALUES (FieldValue1,

        FieldValue2,

        FieldValue3);

或者

INSERT INTO T_DEST_TABLE_NAME (

       DESTFIELDNAME1, -- 注释1

       DESTFIELDNAME2, -- 注释2

       DESTFIELDNAME3) -- 注释3

VALUES (

 FieldValue1,

 FieldValue2,

 FieldValue3);

或者

INSERT INTO T_DEST_TABLE_NAME

(

 DESTFIELDNAME1, -- 注释1

 DESTFIELDNAME2, -- 注释2

 DESTFIELDNAME3 -- 注释3

)

VALUES

(

 FieldValue1,

 FieldValue2,

 FieldValue3

);

 

其他说明：

1、 在规范的代码模板中详细给出了各种情况下的范例写法，请编码过程参考；

2、 在数据库脚本的编码过程中，请严格按照代码模板进行书写；

对于模板中未涉及的情况，应在保证符合上述规范原则，同时和代码模板中的风格保持一致的前提下，根据实际情况灵活处理。

 

2.2.3 空格及空行

规则：不允许把多个短语句写在一行中，即一行只写一条语句。

示例：

v_Variable1 := 1; v_Variable2 := 'abc';

应写成：

v_Variable1 := 1;

v_Variable2 := 'abc';

说明：两个赋值语句不能写在一行中，必须分两行写。

 

规则：相对独立的程序块之间、变量说明之后必须加空行。

示例：

v_Variable1 := 1;

IF v_BeginDate IS NULL THEN

 v_BeginDate := SYSDATE - 15;

END IF;

应写成：

-- 初始化局部变量

v_Variable1 := 1;

 

-- 判断开始时间

IF v_BeginDate IS NULL THEN

 v_BeginDate := SYSDATE - 15;

END IF;

说明：两个程序块在逻辑上相对独立，应用空行加以分隔，同时增加注释。

 

建议：建议对超过120字符的语句要分行书写，长表达式应在低优先级操作符处换行，操作符或关键字放在新行之首。划分出的新行应适当地缩进，使排版整齐、语句可读。是否分行应根据实际情况而定，原则是保证代码整齐、语句可读。

分行示例：

                                                        120字符

                                                           ↓

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

应写成：

                             …… (a * b * c * d)

 + (e * f) + ……

说明：

1、 加法的优先级低于乘法，因此应在加号处换行；

2、 两组乘法虽然在逻辑上会先于加法执行，但显式加上括号使可读性更强。

 

不分行示例：

具体内容请参见代码模板“创建初始化数据模板”部分，有时初始化数据的语句不分行情况下可读性更强。

 

规则：调用函数或过程时，如果参数列表超过120字符，应根据逻辑内容进行换行，或者每个参数占用一行。

示例：

错误的写法：

                                                              120字符

                                                                 ↓

P_PROCEDURE(i_Param1, i_Param2, i_Param3, o_Param1, oParam2, ...);

正确的写法A：

P_PROCEDURE(i_Param1, i_Param2, i_Param3,

              o_Param1, o_Param2, ...);

正确的写法B：

P_PROCEDURE(i_Param1, -- 注释

              i_Param2, -- 注释

              i_Param3, -- 注释

              o_Param1, -- 注释

              o_Param2, -- 注释

              ...)       -- 注释

说明：

1、 因为前三个为输入参数，后两个为输出参数，因此在第3、4参数之间换行比较清晰（如正确的写法A）；

2、 有时为了增加注释，可以使每个参数皆占用一行（如正确的写法B）；

3、 参数换行以后与上一行的第一个参数对齐。

 

规则：双目运算符、操作符前后应以空格分隔，间隔符之后应以空格分隔。

示例：

v_DateVar:=TO_DATE('2001-01-01 01:30:00','YYYY-MM-DD HH24:MI:SS');

 

v_IntegerVar1:=v_IntegerVar2+v_IntegerVar3;

 

IF v_Number>0 THEN

应写成：

v_DateVar := TO_DATE('2001-01-01 01:30:00', 'YYYY-MM-DD HH24:MI:SS');

            ↑                                    ↑

     赋值符前后加空格                       逗号后面加空格

 

v_IntegerVar1 := v_IntegerVar2 + v_IntegerVar3;

                 ↑                  ↑

          赋值符前后加空格     加号前后加空格

 

IF v_Number > 0 THEN

              ↑

      比较符号前后加空格

 

建议：不同优先级的操作符混合使用时，建议使用括号进行隔离。

示例：

     …… a * b + c ……

应写成：

     …… (a * b) + c ……

说明：使用括号使代码的优先级更加清晰，而且可以避免犯错。

 

建议：SQL语句中存在不同优先级的关系运算符时，建议使用括号。

示例：

WHERE FIELD1 > 1 AND FIELD2 < SYSDATE OR FIELD3 > SYSDATE + 3

应写成：

WHERE (FIELD1 > 1 AND FIELD2 < SYSDATE) OR (FIELD3 > SYSDATE + 3)

说明：如果SQL语句的条件复杂，很容易出现问题，因此增加括号可以提高可阅读性。

2.2.4 创建表

规则：脚本中的字段名缩进为2个空格。

 

规则：脚本中字段名称、字段类型、DEFAULT关键字应左对齐，NULL/NOT NULL应右对齐（也可以认为就是NULL关键字左对齐）。

示例：

CREATE TABLE T_WF_TABLE_NAME

(

 INTFIELD      INT                                 NOT NULL,

 FLOATFIELD NUMBER(4, 2)    DEFAULT 1.23        NOT NULL,

 DATEFIELD     DATE            DEFAULT SYSDATE         NULL,

 VARCHAR2FIELD VARCHAR2(200)                           NULL

)

TABLESPACE SERVICE_MAIN_DAT;

说明：至于每行中的每个项目之间使用多少个空格没有明确要求，只要求从纵向上各个字段的名称、数据类型、缺省值、是否为空等对齐美观即可。

2.2.5 创建过程/函数/包

 

规则：脚本中的参数缩进为2个空格。

 

规则：脚本中的参数名称、数据类型、注释信息应左对齐，IN/OUT/IN OUT关键字左对齐右对齐皆可。

示例：

CREATE OR REPLACE PROCEDURE P_CSP_PROCEDURENAME

(

 i_DateParam               DATE,       -- 注释

 o_IntParam    IN OUT INT,        -- 注释

 o_VarcharParam   OUT VARCHAR2,   -- 注释

 o_RetCode            OUT INT         -- 注释

)

或者

CREATE OR REPLACE PROCEDURE P_CSP_PROCEDURENAME

(

 i_DateParam               DATE,       -- 注释

 o_IntParam    IN OUT INT,        -- 注释

 o_VarcharParam OUT     VARCHAR2,   -- 注释

 o_RetCode         OUT     INT         -- 注释

)

说明：至于每行中的每个项目之间使用多少个空格没有明确要求，只要求从纵向上各个参数的名称、数据类型、IN/OUT关键字等对齐美观即可。

 

2.3 常用语法

2.3.1 变量声明

规则：代码中声明与表的字段相对应的变量时，应保证变量名和字段名相同。

说明：这样要求的目的是增强可读性。

示例：

DECLARE

 v_DateField T_TABLENAME.DATEFIELD%TYPE;

BEGIN

 SELECT DATEFIELD INTO v_DateField

   FROM T_TABLENAME

  WHERE ROWNUM = 1;

END;

 

规则：代码中声明与表的字段相对应的变量时，对类型的定义需要使用%TYPE方式。

说明：这样表结构的变动不会影响存储过程，避免表结构变更后出现变量长度或类型不一致的问题。

2.3.2 返回值

规则：函数的返回值定义应遵从下列规范：成功出口返回0，失败出口返回大于0整数。过程中定义的用于返回错误码的OUTPUT参数，其定义应遵从下列规范：成功出口返回0，失败出口返回非0整数

2.3.3 包的使用

建议：ORACLE的包类似C++中的Class，有包头和包体两部分组成；包能够把相关的功能封装性在一个包中，包里面里可以有函数和过程。PACKAGE与PROCEDURE和FUNCTION的优点是封装性比较好；在开发过程中，可以把功能点紧密相关的PROCEDURE、FUNCTION封装在一个包里面。在对包体进行编译时，调用该包的存储过程不会失效

2.3.4 代码规模

建议：每个存储过程和函数的规模不宜超过500行。

2.4 设计约束和建议

2.4.1 严禁用系统表空间作为用户默认表空间；严禁在系统表空间上创建用户数据库对象；严禁在SYSTEM/SYS等系统用户下，创建用户数据库对象

 

2.4.2 禁止在没有事务的存储过程和代码中，随意使用COMMIT和ROLLBACK

说明：对于后台shell调度的存储过程，在存储过程中进行控制事务时，可以使用COMMIT和ROLLBACK

对于后台服务程序中调用的存储过程，需要在服务程序中控制事务，不能在存储过程中控制事务。

 

2.4.3 禁止在触发器中使用COMMIT和ROLLBACK

 

2.4.4 创建组合索引时，要注意组合索引的顺序和字段的选择性，把经常出现在WHERE条件中同时选择性比较好的字段放在复合索引的第一个位置。

说明：SQL语句在使用复合索引时，与该复合索引字段的组合顺序有关，当索引的第一个字段出现在WHERE条件中，这时候对该表的查询能够用到该索引，当该索引的第一个字段没有出现在SQL语句的查询条件中，但该索引的其他的字段出现在WHERE条件中时，在基于规则的优化模式中，SQL引擎会用全表扫描方式查询，在基于成本的优化模式中的（First Rows）方式查询，SQL引擎会对该索引全索引扫描方式查询。

2.4.5 创建分区表的索引时，必须创建本地（LOCAL）索引

说明：如果创建的是全局索引，在对分区表的某个分区TRUNCATE时，导致该索引失效；需要对该全局索引进行重新编译。

2.4.6 不要在经常被修改的字段上建索引。

说明：在对索引列进行此操作时，会占用大量存储空间和索引计算工作，索引会降低update ,insert ,delete等操作的效率

2.4.7 选择常用来关联表的字段做索引字段

说明：在表关联操作中，能够很容易上该字段的索引

2.4.8 尽量避免在业务表的参数及状态字段上创建索引

说明：业务表的参数或状态字段，取值较少，创建索引不能真正意义上提高性能，对于一些工单队列表，可以在工单状态字段上创建索引

2.4.9 无特殊情况，不允许定义Undefined、nvarchar2等类型的字段

 如：CS_REC_BATCHCHECKSELF的OPRCODE和ORGID为NVARCHAR2类型，但是实际意义不大，完全可以用VARCHAR2类型

2.4.10 对访问比较频繁的字典表，需要加载到共享内存中

说明：加载到共享内存，提高效率

2.4.11 如果要创建的表需要高级复制，建表时必须创建唯一索引

说明：主要是字典用户下，如果需要高级复制，主要是根据表的唯一索引来作为数据差异的分析和同步。

2.4.12 如果表中有REGION字段，此表必须按照REGION进行分区

说明：创建的表中存在REGION字段，则必须按照REGION字段进行分区

2.4.13 未按照REGION分区的表中有REGION字段的（历史创建的表），如果联合索引中且包含了REGION字段，REGION字段需要放在最后一位

说明：NGCRM系统中，历史创建的表可能存在有REGION字段，但是没有按照REGION字段分区的情况，这种情况下，如果联合索引中包含了REGION字段，由于REGION取值较少，将数据取值离散的字段放在联合索引前面，提高索引定位效率

如正确的做法：CREATE INDEX IDX_GROUPINCOMEANALYSE_CUSTID ON MM_ST_GROUPINCOMEANALYSE(CUSTID,REGION)；

 错误的做法：CREATE INDEX IDX_GROUPINCOMEANALYSE_CUSTID ON MM_ST_GROUPINCOMEANALYSE(REGION,CUSTID)；

2.4.14 对于分区表中，如果索引是唯一索引，分区字段必须被包含进去，且放在联合唯一索引的最后；否则，不能包含分区字段

说明：1、如果在分区表上创建的是唯一索引，分区字段必须是索引字段，否则ORACLE会报错

         同时分区字段放在索引的最后，主要是为了提高索引的定位效率

        2、如果在分区表上创建非唯一索引时，分区字段是唯一值，则不能包含在分区字段中，如按照REGION或BILLCYCLE分区的表，如账单表BILL，按照BILLCYCLE分区；如果分区字段是时间型的，如RECDATE，则分区字段可以包含在非唯一索引中，如RECEPTION。     

        

2.4.15 关于非表类的数据库对象的存放

说明：字典用户和业务用户中只有业务用户对外提供服务，字典用户对外面的应用来说是透明的。这样，在应用中用到的那些包、存储过程、函数等都要放在业务用户下面，这些不再使用同义词的方式引用

2.4.16 同义词的使用

首先一点需要明确，就是涉及到与别的用户交互的，必须先建一个同义词，将数据库用户名屏蔽下来。如创建视图segment_type可能涉及到几个用户，这样在不同地方的实施过程中，由于用户名的不同会导致视图创建不成功或者视图不可用

 

3 SQL脚本开发规范和建议

3.1 SQL语句涉及region字段的不绑定(数据库分库分用户的情况下).

对于涉及到按region（地区）分区的表的检索条件，不要使用region=:region的方式，要求使用sprintf(sql,"region = %s",region)的方式，将region的取值拼到SQL中。如：

CDBConnect qryDB;

char tmpSQL[1024]={0};

sprintf(tmpSQL,” select * from subscriber where region = %s and oid = :subsid”,region);

qryDB.SetSQL(tmpSQL);

qryDB.Bind(“:subsid”,subsid);

……………………….

l      减少同一条SQL语句在数据库中的多个版本，减少字典表的竞争，提高执行效率。

 

3.2 SQL绑定

在营业开发中，尽量使用绑定变量，避免使用动态拼接的，以提高系统性能。

动态拼接SQL语句，会导致数据库频繁解析SQL语句，而导致数据库主机CPU使用居高不下。

绑定用法：

        sql.SetSQL(" update subscriber set score=:score "

            " where region=XXX and subsid=:subsoid ");

        sql.Bind(":score",Kqrw_Score);

        sql.Bind(":subsoid",subsoid);

        sql.Execute();

非绑定用法（拼接）：

        sql.SetSQL(" update subscriber set score=100 "

            " where region=XXX and subsid=7501234412334 ");

        sql.Execute();

非绑定用法中，由于用户相关，那这条语句根本不可能重用，下次其他用户做业务时，数据库就会重新进行解析。

l      当一个SQL语句被提交至Oracle去执行时，Oracle自动地执行以下内存分配步骤：
Oracle检查共享池，看是否在共享SQL区中已存在相同的语句。 若有，则该共享SQL区被用于执行该语句的新实例的后续操作。 相应地，若在共享池中无该语句，则Oracle在共享池中分配一新的共享SQL区,其尺寸决定于该语句的复杂性。 若一个SQL语句要求新的共享SQL区而整个共享池已被分配完毕，则 Oracle 可通过一个最近最少修改机理从共享池中释放部分项目，直至可为新语句的共享SQL区提供足够的空间。 若 Oracle释放了一个共享 SQL 区，则与该区相关联的SQL语句在下次重执行时，须重新解析并重新分配至另一共享SQL区。 在两种情况下，用户专用SQL区与包含该语句的共享SQL区相关联。
因而，若能使语句得到共享，则其将减少内存的占用，同时，减少了cpu 的占用，加快了语句执行的速度。
即使一个光标仍处于打开状态，若其很久未被使用了，则其共享区也可能被从共享池中移出。若该光标以后又被用于执行其语句，则Oracle重解析该语句并且在共享池中分配一新的共享SQL区。

3.3 SQL语句设置

DBConnect设置SQL语句，如果SQL语句无参数，优先选用SetSQL后跟字符串；如果SQL语句有参数，优先选用带变参的函数SetSQLEx，也可以选用XString先整理Sql，不建议使用字符串数组，自己组织SQL语句。

例如：

CDBConnect db;

db.SetSQLEx("select status from subscriber where region=%d and servnumber=:v1", 311);

3.4 SQL优化建议

该节主要摘自互联网，要求每个开发人员熟练掌握及理解下述建议，在开发阶段就开发高效的SQL，防止后续维护过程中出现大的系统性能瓶颈，因此在SQL评审中，检视人员参照下述建议去评审开发人员提交的SQL语句。

3.4.1 SELECT子句中避免使用‘*’

ORACLE在解析的过程中，会将'*'依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

3.4.2 减少访问数据库的次数

ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等。

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

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

3.4.4 删除重复记录

最高效的删除重复记录方法（因为使用了ROWID）例子：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

3.4.5 用Where子句替换HAVING子句

避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。

l      这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。（非oracle中）on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。

3.4.6 使用表的别名(Alias)

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

SELECT A.PRODID),A.PRODNAME,A.PRODUCTTYPE,A.NetType,A.MAINPROD,A.PRODCLASS,

…………………………

FROM PRODUCT A, PRODUCT_ORG B

WHERE A.PRODID = B.PRODID AND A.MngCatID = :CatalogID

3.4.7 用EXISTS替代IN、用NOT EXISTS替代NOT IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

例子：

(高效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

3.4.8 识别'低效执行'的SQL语句

虽然目前各种关于SQL优化的图形化工具层出不穷，但是写出自己的SQL工具来解决问题始终是一个最好的方法：

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC;

3.4.9 用索引提高效率

索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构。通常,通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时，ORACLE优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证。那些LONG或LONG RAW数据类型，你可以索引几乎所有的列。通常，在大型表中使用索引特别有效。当然，你也会发现，在扫描小表时，使用索引同样能提高效率。虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的：

ALTER INDEX INDEX_NAME REBUILD；

3.4.10 用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换，EXISTS使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。例子：

(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E　WHERE D.DEPT_NO = E.DEPT_NO

(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

3.4.11 sql语句用大写的，因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

3.4.12 避免在索引列上使用NOT

我们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相同的影响。当ORACLE”遇到”NOT，他就会停止使用索引转而执行全表扫描。

3.4.13 避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。举例：

低效：SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效：SELECT … FROM DEPT WHERE SAL > 25000/12;

3.4.14 用>=替代>

高效：

SELECT * FROM EMP WHERE DEPTNO >=4

低效：

SELECT * FROM EMP WHERE DEPTNO >3

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

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

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。举例：如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null)，ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入)。然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空。因此你可以插入1000条具有相同键值的记录，当然它们都是空！因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

低效：(索引失效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

高效：(索引有效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

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

如果索引是建立在多个列上，只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。这也是一条简单而重要的规则，当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引。

3.4.17 用UNION-ALL 替换UNION ( 如果有可能的话)

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION， 这样排序就不是必要了。效率就会因此得到提高。需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。因此各位还是要从业务需求分析使用UNION ALL的可行性。UNION将对结果集合排序，这个操作会使用到SORT_AREA_SIZE这块内存。对于这块内存的优化也是相当重要的。

3.4.18 用WHERE替代ORDER BY

l           ORDER BY 子句只在两种严格的条件下使用索引。

l           ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。

l           ORDER BY中所有的列必须定义为非空。

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列。

例如，表DEPT包含以下列：

DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL

低效：(索引不被使用)

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE

高效：(使用索引)

SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

3.4.19 避免改变索引列的类型

当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。

假设 EMPNO是一个数值类型的索引列。SELECT … FROM EMP WHERE EMPNO = ‘123' 实际上,经过ORACLE类型转换，语句转化为：SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')。幸运的是，类型转换没有发生在索引列上，索引的用途没有被改变。现在，假设EMP_TYPE是一个字符类型的索引列。

SELECT … FROM EMP WHERE EMP_TYPE = 123 这个语句被ORACLE转换为：SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 因为内部发生的类型转换，这个索引将不会被用到！为了避免ORACLE对你的SQL进行隐式的类型转换，最好把类型转换用显式表现出来。注意当字符和数值比较时，ORACLE会优先转换数值类型到字符类型。

3.4.20 需要当心的WHERE子句

某些SELECT语句中的WHERE子句不使用索引。如下四种情况：

l           ‘!=' 将不使用索引。记住，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。

l           ‘||'是字符连接函数。就象其他函数那样，停用了索引。

l           ‘+'是数学函数。就象其他数学函数那样，停用了索引。

l           相同的索引列不能互相比较,这将会启用全表扫描。

3.4.21 如果检索数据量超过30%的表中记录数，使用索引将没有显著的效率提高

在特定情况下，使用索引也许会比全表扫描慢，但这是同一个数量级上的区别。而通常情况下，使用索引比全表扫描要块几倍乃至几千倍！

3.4.22 避免使用耗费资源的操作

带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。通常，带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写。如果你的数据库的SORT_AREA_SIZE调配得好，使用UNION , MINUS, INTERSECT也是可以考虑的，毕竟它们的可读性很强。

3.4.23 存储过程中取sysdate

在编写存储过程、触发器、package的时候，禁止使用下面的办法取时间：

 

select sysdate into dtNow from dual; 

 

请这样写：

 

dtNow := sysdate;

3.4.24 禁止对VARCHAR(2000)之类的大字段值进行ORDER BY、DISTINCT、GROUP BY、UNION之类的操作。

说明：此类操作将消耗大量的CPU和内存资源。

3.4.25 如果要对整个表或分区的数据删除，建议使用TRUNCATE替代DELETE。

3.4.26 选择最有效的WHERE子句中的连接顺序

说明：ORACLE采用自下而上的顺序解析WHERE子句,表之间的连接应该写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件应该写在WHERE子句的末尾

如：SELECT … FROM EMP A WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND EXISTS(SELECT 1 FROM EMP1 B WHERE B.EMPNO=A.EMPNO);

应修改为：

如：SELECT … FROM EMP A WHERE EXISTS(SELECT 1 FROM EMP1 B WHERE B.EMPNO=A.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’;

 

3.4.27 两个表连接查询时，数据量小的表做为基础表（离FROM最远的表），三个及以上的表连接查询时，交叉表作为基础表

例如:

表 TAB1 16,384 条记录

表 TAB2 1 条记录

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

select count(*) from tab1,tab2 执行时间0.96秒

选择TAB2作为基础表 (不佳的方法)

select count(*) from tab2,tab1 执行时间26.09秒

如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

例如:

EMP表描述了LOCATION表和CATEGORY表的交集.

SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN

将比下列SQL更有效率

SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000

3.4.28 INSERT语句是否指定了列名

说明：INSERT语句中必须制定操作的列名，这样可以防止表结构变更引起程序问题