性能优化--文摘1

好的命名规范，可以明显的增强程序的可读性和可维护性：

(1)、保障程序一致性

(2)、增强程序的可读性

(3)、增强程序的可维护性

(4)、降低程序的BUG概率

(5)、提升程序可扩展性和性能

本规范主要适合于所有基于DB2、ETL数据仓库设计开发的项目。

表1. 术语

l   规则1：任何对象的命名均不能使用DB2中的保留字。

l   规则2：所有数据库对象命名必须大写。

l   规则3：实例名长度不能超过8位。

l   规则4：数据库名长度不能超过8位。

l   规则5：数据库对象的名字由字母、数字和连接符（_）组成。

多个单词之间用连接符分开，连接符用“_”字符，不建议使用其他的字符；

例如：存储过程：P_CREATE_ACCBAK_TABLE

l   规则6：数据库对象的名字长度不超过32个字符。

  如果超过30个字符，可以缩写，但一定要采用标准的缩写。如果表或者是字段的名称仅有一个单词，那么建议不使用缩写，而是用完整的单词；

l   规则7：在创建数据库对象时，不要使用引号。

说明：数据库自动转换为大写字符；避免使用系统保留字作为数据库对象的名称。

例：建存储过程P_SET_PROCESS_STATUS时写法

正确写法：

CREATE OR REPLACE PROCEDURE SNBI.P_SET_PROCESS_STATUS … …

END SP_SET_PROCESS_STATUS;

错误写法：

CREATE OR REPLACE PROCEDURE "SNBI"."SP_SET_PROCESS_STATUS" … …

END SP_SET_PROCESS_STATUS;

l   规则8：名称应该清晰明了，能够准备表达事务的含义，最好可读，遵循“见名知意”的原则。

l   规则1：要求表的命名尽量采用标准的翻译，能准确地表达该表的中文含义，能根据英文猜测到表的用途。

l   规则2：对于一些特定作用的表，除了遵循普通表命名规范外，增加特定后缀表示。

l   规则1：字段的命名由英文单词(或缩写)和下划线“_”组成，多个单词之间用下划线分开。

例：USER_ID、PACKAGE_CODE等。

l   规则2：要求字段名能表达字段的含义。

例如： FAIL（FAIL）： 失败记录表 HISTORY（ HIS）：历史 IDENTITY（ID）：

l   规则3：字段长度，根据业务需要定义字典长度。对主键里的字符型字段，尽量用CHARACTER类型

l   规则4：如果相同字段在不同的表中出现，要使用相同的命名，且必须保证他们的类型和精度是相同的。

l   规则5：字段长度小于32，推荐CHAR；大于32，推荐VARCHAR

l   规则1：普通索引：IX{$N}_表名缩写

例如： JV_AUTH_DEAL_TD表CHECKOUT_SRL列上的索引命名：IX1_COD_CHECKOUT_SRL。

l   规则2：唯一索引：UX{$N}_表名缩写。

例如： JV_AUTH_DEAL_TD表（EMPLOYEE_NO）列上的唯一索引命名为：UX1_JV_AUTH_DEAL_TD。

l   规则3：簇族索引：AX{$N}_表名缩写。

例如： JV_AUTH_DEAL_TD表（DEP_CD）列上的簇族索引命名为：AX1_JV_AUTH_DEAL_TD。

l   规则1：create table 时，约束必须指定PK名称。

说明： 如果不指定名称，则DBMS会自动配置名称。

例如：

-- 不好的例子：

CREATE TABLE SNBI.SNUSER(

UID INT NOT NULL, UNAME VARCHAR(20), PRIMARY KEY(UID) );

-- 正确的例子：

CREATE TABLE SNBI.SNUSER(

UID INT NOT NULL, UNAME VARCHAR(20), CONSTRAINT PK_SNUSER PRIMARY KEY(UID) );

l   规则2：主键: “PK_”为前缀，后跟表名

例如：BA_SUPPLIER表(SUPID)上的主键索引命名为：PK_BA_SUPPLIER

l   规则3：外键以 FK{$N}_为前缀，后跟表名

例如：PROJACT表(PROJNO)上的创建外键约束：FK1_PROJACT

ALTER TABLE SNBI.PROJACT

ADD CONSTRAINT FK1_PROJACT

FOREIGN KEY(PROJNO)

REFERENCES DAVID.PROJECT(PROJNO)

ON DELETE RESTRICT

ON UPDATE NO ACTION ;

l   规则4：给表增加约束时要显式指定约束的名字。

例：ALTER TABLE CLERK_OPERATING_DETAIL

ALTER TABLE DW_RPT.JV_AUTH_DEAL_TD

ADD CONSTRAINT PK_JV_AUTH_DEAL_TD PRIMARY KEY

(EMPLOYEE_NO); 不能写成 ALTER TABLE DW_RPT.JV_AUTH_DEAL_TD

ADD CONSTRAINT PRIMARY KEY (EMPLOYEE_NO);

注意：如果不显式指定约束名系统将随机分配一个约束名字。

l   规则5：主键与对应的外键名字要一致

l   规则1：视图的命名规范遵循普通表的命名规范，并在视图前加上“V_”前缀，表示是视图。

例：V_CARDINFO

l   规则2：物化视图命名规则：MV_源表名

例：MV_GSM_USER 表示从表GSM_USER获取数据。

/*- Create CDC DB View Tables

- Author                   :      Peak Yi

- Creation Date          :  2013-05-06

- Description        :      获取投保人与其它人的关系数据

- History     Version       :     

- */

CREATE VIEW SDTS.V_ORDUOI

AS

SELECT   FLD0001                  AS Company_Code     --公司编码

              ,FLD0002                    AS Policy_No                --保单号

              ,FLD0003                    AS Owner_No                --投保人客户ID

              ,FLD0004                    AS Insured_No               --被保人客户ID

              ,FLD0007                    AS Relation                --关系描述

              ,FLD0017                    AS Status                         --投保、被保状态

              ,OBSOLETE_FLAH       AS OBSOLETE_FLAG     --当前记录是否有效

              ,TIMESTAMP_ID                                   --更新时间用于抽取数据       

FROM  SDTS.CI_ORDUOI WITH UR

l   规则1：存储过程的命名以“SP_”打头

说明：其他规范遵循普通表的命名规范，要求名字能表达存储过程的用途

l   规范2：存储过程的In、out参数应按类别分开书写，不要交叉。

CREATE OR REPLACE PROCEDURE SNBI.SP_CREATE_SCP(

IN i_gsm_user_id INTEGER, --用户号码

IN i_order_code INTEGER, --命令代码

IN i_operation_type INTEGER, --工单类型

IN i_operating_srl INTEGER, --操作流水

IN i_operating_srl INTEGER, --操作流水

OUT o_operation_srl INTEGER, --工单流水（返回参数）

OUT o_flag INTEGER, --成功0 ，失败1

OUT o_msg VARCHAR(200))

BEGIN

......

END

--增加处理异常demo

SET SCHEMA = 'ETL_STAGE';

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2INST1";

CREATE PROCEDURE "ETL_STAGE"."SP_ETL_TEST_CYJ2" ( IN "P_LST_LOAD_TM" TIMESTAMP )

  SPECIFIC "SP_ETL_MODEL"

  LANGUAGE SQL

  NOT DETERMINISTIC

  EXTERNAL ACTION

  MODIFIES SQL DATA

  OLD SAVEPOINT LEVEL

BEGIN

        DECLARE V_LST_LOAD_TM           TIMESTAMP;--

        DECLARE V_LOAD_COUNTRY      VARCHAR(2) DEFAULT 'CN';--

        DECLARE V_LOAD_SYS VARCHAR(10)  DEFAULT 'CI';--

        DECLARE V_LOAD_GRP VARCHAR(10)  DEFAULT 'IVR';--

        DECLARE SQLCODE         INT DEFAULT 0;--

        DECLARE SQLSTATE        CHAR(5) DEFAULT '00000';--

        DECLARE V_SP_NM     VARCHAR(50)     DEFAULT 'SP_ETL_TEST_CYJ2';

        DECLARE V_STEP_DET   VARCHAR(255) DEFAULT '';

        DECLARE V_ROWCNT    INTEGER DEFAULT 0;

    --描述作用(注释)

    UPDATE ETL.TEST_CYJ2

    SET COL1= 'AA';

    COMMIT;

    --UPDATE或者INSERT 完写LOG

    GET DIAGNOSTICS V_ROWCNT = ROW_COUNT;

            SET  V_STEP_DET='ETL.TEST_CYJ2 - '||CHAR(V_ROWCNT)||' rows are UPDATED';

            CALL ETL.SP_ETL_LOAD_LOG_DET(V_LOAD_SYS,V_LOAD_GRP,'I',V_SP_NM,V_STEP_DET,CURRENT_TIMESTAMP);

    --描述作用(注释)

    --ALTER TABLE ETL.TEST_CYJ2 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

    DELETE FROM ETL.TEST_CYJ2;

    INSERT INTO ETL.TEST_CYJ2

    (

    COL1,

    COL2

    )

    SELECT

    COL1,

    COL2

    FROM ETL.TEST_CYJ1;

    COMMIT;

    --UPDATE或者INSERT 完写LOG

    GET DIAGNOSTICS V_ROWCNT = ROW_COUNT;

            SET  V_STEP_DET='ETL.TEST_CYJ2 - '||CHAR(V_ROWCNT)||' rows are UPDATED';

            CALL ETL.SP_ETL_LOAD_LOG_DET(V_LOAD_SYS,V_LOAD_GRP,'I',V_SP_NM,V_STEP_DET,CURRENT_TIMESTAMP);

    ---SP exception process---

    GET DIAGNOSTICS V_ROWCNT = ROW_COUNT;

    IF (SQLCODE = 100) THEN

      SET V_STEP_DET='0 Finish - '||CHAR(V_ROWCNT) || ' rows are inserted in TEST_CYJ2 with timestamp after';--

      CALL ETL.SP_ETL_LOAD_LOG_DET(V_LOAD_SYS,V_LOAD_GRP,'I',V_SP_NM,V_STEP_DET,CURRENT_TIMESTAMP);--

    ELSEIF (SQLCODE <> 0) THEN

      SET V_STEP_DET='INIT_SP_IVR_LGN ERROR SQLCODE'||CHAR(SQLCODE)||'SQLSTATE'||CHAR(SQLSTATE);--

      CALL ETL.SP_ETL_LOAD_LOG_DET(V_LOAD_SYS,V_LOAD_GRP,'I',V_SP_NM,V_STEP_DET,CURRENT_TIMESTAMP);--

    ELSE

      SET V_STEP_DET='Finish - '||CHAR(V_ROWCNT)||' rows are inserted in TEST_CYJ2 with timestamp after '|| CHAR(P_LST_LOAD_TM);--

      CALL ETL.SP_ETL_LOAD_LOG_DET(V_LOAD_SYS,V_LOAD_GRP,'I',V_SP_NM,V_STEP_DET,P_LST_LOAD_TM);--

    END IF;

    COMMIT;

END;

GRANT EXECUTE ON PROCEDURE "ETL_STAGE"."SP_ETL_TEST_CYJ2"( TIMESTAMP ) TO USER "DB2INST1" WITH GRANT OPTION;

l   规范3：存储过程中变量的声明应集中，不可分散于各处。

l   规范4：尽可能使用相关表字段类型来定义相关的变量，如%type，%rowtype。

说明：一般变量，都可能会用于表的查询条件，或增加记录的值；当字段数据类型发生变更时，对应的变量无需修改。例：

CREATE OR REPLACE PROCEDURE SNBI.EMP_INFO

IS

CURSOR empcur IS SELECT ename, deptno FROM emp;

 myvar empcur%ROWTYPE;

BEGIN

OPEN empcur;

LOOP

FETCH empcur INTO myvar; EXIT WHEN empcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE( myvar.ename || ' works in department ' || myvar.deptno );

END LOOP;

CLOSE empcur;

END

当ename 或deptno 的类型变成字符型时，该段代码无需修改。

l   规范5：确保所有的变量和参数都用到，没有用到的变量和参数要删除。

l   规范6：存储过程有多个分支返回时，若有事务控制，需确保各个分支都结束事务。异常时，应该在Exception中捕捉异常，并进行事务处理。

l   规范7：存储过程：不要在异常部分，进行正常的业务处理。

l   规范8：除非应用特别需要控制commit和rollback的提交时机，否则必须在存储过程结束时执行显式的commit或者rollback操作

CREATE FUNCTION ETL.ISDATE ( "YYYYMMDD" VARCHAR(8) )

  RETURNS INTEGER

  SPECIFIC "SQL100205121547600"

  LANGUAGE SQL

  DETERMINISTIC

  NO EXTERNAL ACTION

  CONTAINS SQL

RETURN

CASE

WHEN TRANSLATE(yyyymmdd, '*', ' 0123456789') = '' THEN

CASE

WHEN SUBSTR(yyyymmdd,1,4) BETWEEN '0001' AND '9999'

AND SUBSTR(yyyymmdd,5,2) BETWEEN '01' AND '12'

AND INT(SUBSTR(yyyymmdd,7,2)) BETWEEN

1 AND

DAY(DATE(CHAR(INSERT(SUBSTR(yyyymmdd,1,6),5,0,'-')||'-01',10))

+ 1 MONTH - 1 DAY)

THEN 1

ELSE 0

END

ELSE 0

END;

l   传入参数以“i_”为前缀；

l   传出参数以“o_”为前缀；

l   变量以“v_”为前缀。

l   规范1：所有注释必须采用UTF-8编码

l   规范2：所有变量定义都要加上注释，说明变量的用途及含义。

例：

CREATE OR REPLACE PROCEDURE SNBI.SP_SUM_MN2 (

IN i_start INT, ---- 起始数值

IN i_end INT, ---- 结束数值

OUT o_sum INT ---- 返回值)

SPECIFIC sum_mn2

LANGUAGE SQL

BEGIN ......

END

l   规范3：注释内容要清晰、明了，含义准确，防止注释二义性。

例如：

 正确： ret_code out integer, --返回代码(0 成功,其它：错误,参见：错误代码定义)；

 错误： ret_code out integer, --返回代码

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

例：

 /** 修改原因：修改积分兑换和积分转赠

修改人：***

修改时间：2006-01-05 */

l   规范5：对程序分支必须书写注释。

l   规范6：在代码的功能、意图层次上进行注释，帮助维护人员理解代码。

CASE RDM_TYPE1

WHEN 1 THEN --积分奖励

... ... WHEN 2 THEN --扣减(停用) ... ...

WHEN 3 THEN --积分兑换

... ...

END CASE;

l   规范7：代码注释应放在描述的代码上方或右方相近位置，不可放在下面。

l   规范8：注释与所描述的内容进行同样的缩排。

l   规范9：函数应对返回的代码进行详细描述

l   规范10：在程序块的结束行右方加注释，以表示程序块结束。

l   规范11：统一文件头的注释。

例：

CREATE PROCEDURE DW_DM.SP_PDM_MEM_CONSM_Q

( IN i_date VARCHAR(8),

OUT o_return INTEGER )

 /** head

* @name dw_dm#pdm_mem_consm_q

* @caption 会员消费季分析

* @type 季汇总

* @parameter iv_date in varchar(8) 统计日期，格式：yyyymmdd/yyyymm

* @parameter oi_return out integer 执行状态码，0 成功，其他失败

* @description 会员消费季分析

* @target dw_dm#tdm_mem_consm_q 会员消费季分析

* @source dw_dpa#tdpa_mem_consm_q 会员消费季汇总

* @version 1.0

* @author wuc

 * @create-date 2012-05-10

 * @copyright suning

 * 日期 修改者更改简述 ------------------------------------------------------------------------------------ * */

BEGIN

.......

END@

l   规范1：统一使用UTF-8编码，所有关键字统一使用大写字母书写，所有参数和变量使用小写字母书写

l   规范2：确保变量和参数在类型和长度上与表数据列类型和长度相匹配。

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

l   规范3：程序块中的begin、end独立成行。

l   规范4：程序块采用缩进风格书写，保证代码清晰易读，风格一致，缩进格数统一

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

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

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

例：

INSERT INTO TABLE_NAME(

USER_ID, --用户ID，主键

USER_NAME, --用户名

LOGIN_NAME --登录名)

VALUES(

v_user_id,

v_user_name,

v_login_name);

l  规范8：相对独立的程序块之间需加空行。

l  规范9：超过110列的语句要分行书写，长表达式应在低优先级操作符处换行，操作符或关键字放在新行之首。

例：

SET v_exchangeable_score = (v_score_phone_score

+ v_score_bounty_score

+ v_score_gift_score

- v_score_alr_excg_score);

l   规则1：批量操作，选择合适的COMMIT频率

不合适的COMMIT频率会导致物理I/O增大，降低性能，但长时间不提交将带来更多的性能问题。建议小于3秒的事务可以一次提交，大于3秒的操作尽可能3秒左右提交一次。实际应用中使用COMMIT时必须保证事务的完整性。

l   规则2：避免动态SQL，要使用绑定变量方式

为了不重复解析相同的SQL语句，在第一次解析之后，数据库将SQL语句缓存在内存中，当你执行一个SQL语句时，如果它和之前的执行过的语句完全相同，数据库就能很快获得内存中相同语句的执行计划，然后就直接执行该语句，而不需要进行解析。

如果没有使用绑定变量，虽然语句结构相同，但因传入的变量不一样导致语句不相同，数据库还是需要对每条结构相同的语句重新解析一遍，同时在内存中缓存该语句，导致共享池中存放了N条结构相同的语句，达不到共享的目的，又浪费了大量的内存空间，如果语句执行的太频繁，会导致数据库无法申请共享内存而出错。

l   规则3：避免不必要的排序

对查询结果进行排序会大大的降低系统的性能

l   规则4：用WHERE子句替换HAVING子句

例如：

SELECT NAME, SUM (AGE)  

       FROM EMPLOYEE  

       GROUP BY NAME HAVING NAME! = 'ABC'

修改为以下语句效果更好：

SELECT  NAME，SUM(AGE)  

       FROM EMPLOYEE  

       WHERE NAME! ='ABC' GROUP BY NAME  

l   规则5：用“>=”替代“>”

如：在ID列上建有索引，则语句SELECT * FROM EMPLOYEE WHERE ID >= 9 要比语句SELECT * FROM EMPLOYEE WHERE ID > 8高效。这是由于前者DBMS将直接跳到第一个ID等于9的记录，而后者将首先定位到8的记录并且向前扫描到第一个DEPT大于9的记录。

l   规则6：用UNION-ALL 代替UNION

说明：UNION-ALL不过虑重复记录，UNION 过滤重复记录，所以需要先排序。如果不需要过滤重复的记录，

UNION-ALL 比UNION 性能更好。

l   规则7：用(NOT) EXISTS替代(NOT) IN。

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

例如：

SELECT *

    FROM EMP WHERE EMPNO > 0

    AND DEPTNO IN

       (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')

没有以下语句高效

SELECT *

    FROM EMP WHERE EMPNO > 0

    AND EXISTS (SELECT ‘X’ FROM DEPT

         WHERE DEPT.DEPTNO = EMP.DEPTNO

           AND LOC = 'MELB')

l   规则8：使用CASE函数来减少处理时间。

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

例：

SELECT EMPNO, FIRSTNME,'男' AS SEX_CN

    FROM EMPLOYEE WHERE SEX='M'

UNION 

    SELECT EMPNO, FIRSTNME,'女' AS SEX_CN

FROM EMPLOYEE WHERE SEX='F'

可以改成：

SELECT 

    EMPNO, FIRSTNME,

    (CASE SEX

        WHEN 'M' THEN '男'         WHEN 'F' THEN '女'

        ELSE '未设定'

    END) AS SEX_CN

FROM EMPLOYEE

l   规则9：表链接时，使用表的别名(Alias)。

当在SQL语句中连接多个表时，应使用表的别名并把别名前缀于每个列上，好处是：

•       使用表的别名可以减少解析的时间

•       使用表的别名可以减少那些由COLUMN歧义引起的语法错误

l   规则10：尽量减少对表的查询次数。

说明：在含有子查询的SQL语句中，要特别注意减少对表的查询。

例如：

UPDATE EMP SET 

    EMP_CAT = (SELECT MAX (CTEGORY) FROM EMP_CATEGORIES),

    SAL_RANGE = (SELECT MAX (SAL_RANGE) FROM EMP_CATEGORIES)

WHERE EMP_DEPT=’0020’

改写成下面这样会更高效:

UPDATE EMP SET 

    (EMP_CAT, SAL_RANGE) = (SELECT MAX (CATEGORY), MAX (SAL_RANGE) FROM

EMP_CATEGORIES) 

WHERE EMP_DEPT = ‘0020’;

l   规则11：用表连接替换EXISTS。

通常来说 , 采用表连接的方式比EXISTS更有效率

例：

SELECT ENAME   FROM EMP E

 WHERE EXISTS (SELECT 'X'

          FROM DEPT

         WHERE DEPT_NO = E.DEPT_NO

           AND DEPT_CAT = 'A');

改写成下面这样会更高效:

SELECT ENAME

  FROM DEPT D, EMP E

 WHERE E.DEPT_NO = D.DEPT_NO

   AND DEPT_CAT = 'A';

l   规则12：避免使用DISTINCT，可以用EXISTS替换DISTINCT。

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换例:

SELECT DISTINCT DEPT_NO, DEPT_NAME

    FROM DEPT D, EMP E

    WHERE D.DEPT_NO = E.DEPT_NO

改写成下面这样会更高效:

SELECT D.DEPT_NO, D.DEPT_NAME

    FROM DEPT D

    WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

l   规则13：避免使用耗费资源的操作

带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY，GROUP BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能,如果可能，应尽量避免使用或改写。

l   规则14：避免对索引列使用数据库函数、计算表达式等等

对查询索引列使用数据库函数、计算表达式会造成对索引失效

例：

SELECT T.RESERVER1, COUNT (*) COUT

FROM CUSTCARE.PASSWORD_LOGIN_FAIL T

WHERE T.OPERATING_SOURCE = 1 

AND TO_CHAR (T.LOGIN_TIME,'YYYYMMDD') BETWEEN 20080904 AND 20080918

GROUP BY T.RESERVER1

HAVING COUNT (*) > 30

custcare.password_login_fail表的login_time列上建了索引，上面语句to_char函数会造成索引失效，把

And to_char (t.login_time,'yyyymmdd') between 20080904 and 20080918

修改成：

AND T.LOGIN_TIME BETWEEN TO_DATE('20080904','YYYYMMDD') AND  TO_DATE('20080918','YYYYMMDD')  可以正确索引。

查询时尽可能将操作移至等式的右边，甚至去掉函数。

l   规则15：在查询条件中，避免不必要的类型转换。

字符类型列赋予数值型数据时会使用隐含类型转换，而数值型列赋予字符型数据时不会隐含类型转换，而是将右边值转换为数值类型。例：

CFG_TYPE是字符型列，则：

SELECT * FROM ATP_PARA_CFG WHERE CFG_TYPE=35

上面语句会造成隐含类型转换TO_NUMBER(CFG_TYPE)=35

l   规则16：尽量避免字段与“NULL”比较

索引并不存储null值，使用NULL作为条件将不会使用索引。

例：

SELECT * FROM STAFF_MEMBERWHERE ADDRESS IS  NULL；（或NOT NULL）该语句将不会使用索引

l   规则17：在索引列上使用<>(!=)和 LIKE 将不会使用索引。

如果在索引列上使用<>(!=) 和 LIKE，往往无法使用索引，从而导致SQL性能下降。因此，应避免在索引列上使用<>(!=) 和 LIKE。

例：

SELECT * FROM STAFF_MEMBER WHERE DEPT_NO<>2012;

SELECT * FROM STAFF_MEMBER WHERE FIRST_NAME LIKE '%DON’;

通常，以上语句都不会用到索引

注：LIKE语句改为SELECT * FROM STAFF_MEMBER WHERE FIRST_NAME LIKE 'DON%'; 当前面没有%时，将使用索引，数据库内部转换为范围操作

l   规则18：用CASE语句合并多重扫描

例：

SELECT COUNT (*) AS COUNT_LOW FROM EMP WHERE SALARY < 50000;

SELECT COUNT (*) AS COUNT_NOM FROM EMP WHERE SAL BETWEEN 50000 AND 90000; SELECT COUNT (*) AS COUNT_HIGH FROM EMP WHERE SAL > 90000;

这样我们需要进行三次全表查询，但是如果我们使用case语句：

SELECT 

    SUM (CASE WHEN SALARY < 50000 THEN 1 ELSE 0 END) AS COUNT_LOW,     SUM (CASE WHEN SALARY BETWEEN 50000 AND 90000 THEN 1 ELSE 0 END)     AS COUNT_NOM,

    SUM (CASE WHEN SALARY > 90000 THEN 1 ELSE 0 END)           AS COUNT_HIGH FROM EMPLOYEE;

这样查询的结果一样，但是执行计划只进行了一次全表查询。

l   规则19：操作符和操作数之间使用一个空格间隔

l   规则20：多表连接时，需要定义表的别名，并使用表的别名来引用列。

l   规则21：使用LIKE的时候，应该使用右匹配(A%)

l   规则22：使用"AND"时，筛选结果集小的表达式应该置于"AND"左侧

l   规则23：使用"OR"语句时，筛选结果集大的表达式应该列于"OR"左边

l   规则24：避免对3个以上的字段排序

l   规则25：避免对长度超过30的字符字段进行排序

l   规则26：避免在排序字段上添加表达式

l   规则27：不使用任何聚合函数情况下，使用DISTINCT替代GROUP BY

l   规则28：不应该使用通配符“*”，查询语句的结果字段应该书写明确的列名

l   规则29：查询语句应该设置有效的限定条件，返回预期的结果集，避免返回过大的结果集

l   规则30：如果查询需要的行数远远小于结果集返回的行数（比如数据采样），必须使用FETCH FIRST …ONLY或者OPTIMIZE FOR N子句。

l   规则31：尽量避免使用空操作表达式（COALESCE），DB2优化器无法详细分析该谓词。

l   规则32：尽量避免使用外连接，除非应用上要求必须使用。

l   规则33：合理的使用谓词关联，杜绝多表关联时出现笛卡尔积运算以及冗余谓词。

若FROM子句中包含N个表，WHERE子句中至少存在N-1个关联条件。

l   规则34：善用 FOR READ ONLY选项和 FOR UPDATE选项

使用游标时明确表达语句的目的，对于只读操作，使用FOR READ ONLY选项；对于更新操作，使用FOR

UPDATE选项。

l   规则35：只读查询操作，绑定（BIND/PREP）时使用BLOCKING ALL选项，利

用行分块提高性能。

l   原则1：限制表连接操作所涉及的表的个数

对于数据库的连接操作操作，我们可以简单的将其想象为一个循环匹配的过程，每一次匹配相当于一次循环，每一个连接相当于一层循环，则N个表的连接操作就相当于一个N-1层的循环嵌套。

一般的情况下在数据库的查询中涉及的数据表越多，则其查询的执行计划就越复杂，其执行的效率就越低，为此我们需要尽可能的限制参与连接的表的数量。

3-5个表的处理方法

对于较少的数据表的连接操作，数据库优化器会比较好的选择执行计划，这种连接操作只需要遵循上面描述的基本的连接方法就可以。

5个表以上的处理方法

对于涉及较多的表(>5+)的数据连接查询，其查询的复杂度迅速增加，其连接的存取路径的变化更大，存取路径的个数与连接的表的个数的阶乘有关：当n=5时存取路径＝1X2X3X4X5=120个，而当连接的表的个数为6时存取路径变为1X2X3X4X5X6=720个，数据库优化器对于数据的存取路径的判断近乎为不可能，此时完全依赖与用户的语句书写方式。

对于较多的表的连接，要求开发人员查询返回的结果能够有所预测，同时判断出各个参与连接的表中符合条件的记录的数量，从而控制查询的运行时间。同时为了提高查询的效率，此时可以把部分表的一些连接所形成的中间结果来代替原来的连接表，从而减少连接的表的数目。

对表连接操作涉及的表数目不应多于8个表

如果查询语句拥有过多的表连接，那么它的执行计划过于复杂且可控性降低，容易引起数据库的运行效率低下，即使在开发测试环境中已经经过充分的测试验证，也不能保证在生产系统上由于数据量的变化而引发的相关问题。应该在应用设计阶段就避免这种由于范式过高而导致的情况出现。

l   原则2：限制嵌套查询的层数

应用中影响数据查询的效率的因素除了参与查询连接的表的个数以外，还有查询的嵌套层数。对于非关联查询，嵌套的子查询相当于使查询语句的复杂度在算术级数的基础上增长，而对于关联查询而言，嵌套的子查询相当于使查询语句的复杂度在几何级数的基础上增长。

因此，降低查询的嵌套层数有助于提高查询语句的效率。

对嵌套查询层数的限制要求：如果查询语句拥有过多的嵌套层数，那么会使该查询语句的复杂度高速增加，应该在数据库设计阶段就避免这种情况出现，不应多于5层。

l   原则3：灵活应用中间表或临时表

在对涉及较多表的查询和嵌套层数较多的复杂查询的优化过程中，使用中间表或临时表是优化、简化复杂查询的一个重要的方法。

通过使用一些中间表，我们可以把复杂度为M*N的操作转化为复杂度为M+N的操作，当M和N都比较大时M+N <<M*N，则查询的复杂度被大大地降低。

l   原则4：使用一些改写复杂查询的技巧

•       转换连接类型，最好保证连接类型和字段长度精度的完全一致。

•       区分不同的情况使用IN或EXISTS

•       对于主查询中包含较多条件而子查询条件较少的表使用EXISTS，对于主查询中包含较少条件而子查询条件较多的表使用IN。

•       使用并行查询

•       如果查询的数据在表中所占的比例较大，可以考虑使用并行查询来提高查询的执行速度。

•       使用SQL PL过程和临时表代替复杂查询。

•       对于涉及巨大的表的连接的统计查询，由于可能会造成大量的排序统计工作，使得查询的速度变慢，此时可以考虑使用SQL PL将原来的复杂查询修改为多个小的查询。

采用26个英文字母和0-9这十个自然数，加上下划线_组成，共36个字符。不出现其它字符。

采用英文单词或英文短语(包括缩写)作为名称，不使用无意义的字符或拼音。

名称应该清晰明了，能够准备表达事务的含义，最好可读，遵循“见名知意”的原则。

1、<项目名称>_Main 程序入口文件

2、一般包的名称跟表名保持一致，特殊的特殊处理

3、组件命名的规范是先动词后名词再方法（方法可选）Verb_Noun_[Method] (每个字母都要大写)   (如：Get Customer Data By Date)

下面是ETL的目录层次结构

ODW Workspace          àDeploy                      àSSISà(ODW_Staging、ODW、IDS_CMB)

                                                àSourceCode à(SQL Script – Staging、SQL Script - ODW)     

                        àRelease         à(M1、M2)

                                                àReference Files         

参数名统一以小写p开头，再加上实际参数名组成

1、  整个包的运行错误信息记录到SSIS自带的错误表(dbo.sysssislog)

2、  记录在哪个组件出错，调用存储过程EXEC dbo.USP_ErrorLog ?,?,?,?,?,?,?,? OUTPUT

参数：System::PageageName

      System::TaskName

      System::SoureName

      System::ErrorCode

      System::ErrorDescription

      System::StartTime

      System::MachineName

      User::pErrorLogID        

记录到dbo.ErrorLog表

3、  字段数据值不合法引起的错误信息，记录到<表名>_Error