《Oracle 高效SQL编程指南 V11.doc》由会员分享，可在线阅读，更多相关《Oracle 高效SQL编程指南 V11.doc（43页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、EI产品线协同办公组数据库高效编程指南 Confidential(秘密)协同办公组数据库高效编程指南2010.10Written By TalkwebTalkweb 湖南拓维信息系统有限公司 1996,2009All Rights ReservedTW_PROC_DS_303第 43 页 共 43 页文档标识：TW_PROC_DS_303当前版本：V1.1当前状态：草稿发布日期：发布修改历史日期版本作者修改内容评审号变更控制号2010/10/111.0熊坚新建 2011/08/231.1陈虹君修改增加完善写在前面的话规范SQL书写方式，让SQL可以共享，这是大家必须遵守的原则，但后续谈到的所有
2、高效SQL编写原则，都只是建议与一般规则，具体执行还得看SQL执行计划,所以高效的可能也不一定高效第1章 常用语法1.1 变量声明原则：代码中声明与表的字段相对应的变量时，应保证变量名和字段名相同。说明：这样要求的目的是增强可读性。示例：DECLARE v_DateField T_TABLENAME.DATEFIELD%TYPE;BEGIN SELECT DATEFIELD INTO v_DateField FROM T_TABLENAME WHERE ROWNUM = 1;END;原则：代码中声明与表的字段相对应的变量时，对类型的定义需要使用%TYPE方式。说明：这样表结构的变动不会影响存储
3、过程，避免表结构变更后因变量长度或类型不一致而导致现有程序报错的问题。1.2 返回值原则：函数的返回值定义应遵从下列规范：成功出口返回0，失败出口返回大于0整数。过程中定义的用于返回错误码的OUTPUT参数，其定义应遵从下列规范：成功出口返回0，失败出口返回非0整数。1.3 符号* 原则：脚本中不允许出现“*”的用法，必须用实际的字段名代替，INSERT语句必须指定要插入的字段名。示例：1、游标定义错误用法：CURSOR c_CursorName IS SELECT * FROM TABLENAME .正确用法：CURSOR c_CursorName IS SELECT FIELD1, FIE
4、LD2, . FROM TABLENAME .说明：ORACLE 在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 需要消耗更多的时间，因此查询时，请在SELECT后把需要查询出的字段列举出来，不要用*代替2、INSERT 语句错误用法：INSERT INTO TABLENAME VALUES .正确用法：INSERT INTO TABLENAME (FIELD1, FIELD2, .) VALUES .1.4 包的使用建议：ORACLE的包类似C+中的Class，有包头和包体两部分组成；包能够把相关的功能封装性在一个包中，包里面里可以有函数和过程。PACK
5、AGE与PROCEDURE和FUNCTION的优点是封装性比较好；在开发过程中，可以把功能点紧密相关的PROCEDURE、FUNCTION封装在一个包里面。在对包体进行编译时，调用该包的存储过程不会失效。1.5 绑定变量原则：使用绑定变量的SQL语句，能使用绑定变量方式的业务逻辑下不允许使用绑定常量实现。绑定变量目的：实现SQL共享，减少硬解析，减小CPU耗时说明：在存储过程拼SQL语句作为动态SQL执行时，尤其需要注意。示例：错误用法：v_SQL := DELETE FROM TABLENAME WHERE FIELD1 SYSDATE AND FIELD2 = 102;EXECUTE IM
6、MEDIATE v_SQL;正确用法：v_ID := 102;v_SQL := DELETE FROM TABLENAME WHERE FIELD1 :1 AND FIELD2 = :2;EXECUTE IMMEDIATE v_SQL USING SYSDATE, v_ID;原则：为提升性能，减少循环的开销，可以使用批量绑定。说明：下面示例中的UPDATE语句可以一次将多条记录进行更新，而避免了通过使用循环同一条SQL执行多次。示例：CREATE OR REPLACE PROCEDURE P_CSP_TEST/* Description: 根据输入的多个工单流水号，更新对应的SERVICECL
7、ASSID值 Author: 姓名 工号 Date: YYYY-MM-DD Version: 版本 Caller: 调用者 Callee: 被调用者 Comments: 注释信息 History: 1.Date: 修改日期，格式为YYYY-MM-DDAuthor: 修改人姓名与工号Modification: 修改说明 2. .*/( i_SerialNos VARCHAR2, - 一次输入多个工单流水号以分隔 i_ServiceClassID VARCHAR2, o_Ret OUT VARCHAR2 - 成功返回0，失败返回1)AS TYPE typ_StrArray IS TABLE OF
8、VARCHAR2(500) INDEX BY BINARY_INTEGER; v_ArrSerial typ_StrArray;BEGIN FOR i IN 1.9999 LOOP - 把输入参数的值取出存放在数组v_ArrSerial中 EXIT WHEN GetParamStr(i_SerialNos, , i, v_ArrSerial(i) 0; END LOOP; - 根据数组中的工单流水号批量更新 FORALL i IN 1.v_ArrSerial.COUNT UPDATE T_WF_SERVICEINFO t SET t.SERVICECLASSID = i_ServiceClas
9、sID WHERE SerialNo = v_ArrSerial(i); COMMIT; o_Ret := 0;EXCEPTION WHEN OTHERS THEN ROLLBACK; o_Ret := 1;END;1.6 异常处理原则：函数/过程中应该有异常处理的代码，除非需要将任何可能的异常都向上抛出。说明：不论代码逻辑是否简单，只要有可能会抛出异常，函数/过程块就应该包括异常处理代码。示例： - 以下代码中的异常是预料中的，所以在这里异常被处理了 BEGIN SELECT DateField INTO v_DateField FROM t_TableName WHERE ROWNUM =
10、 1; EXCEPTION WHEN others THEN v_DateField := SYSDATE; END; 原则：如果需要的话，可以在异常处理部分将异常继续向上抛出给调用者。说明：如果有些异常是预料可能产生，而且脚本需要根据是否有异常来做不同的逻辑处理，这种情况可以在异常处理部分将该异常进行处理，但可能有些意料之外的异常，需要继续向上抛出，便于调用者了解脚本执行是否成功，以便于调用者作下一步的处理如写错误日志等操作。原则：如果需要自定义异常，必须在异常处理块中对其进行处理。说明：如果自定义了异常，却没有相应的代码进行处理，那么就应该去掉该异常的定义。原则：在对容错性要求比较高的情况
11、下，对异常块中的代码还需要判断是否可能触发异常，必要的时候应使用嵌套的异常。说明：在异常处理部分中的写日志之前，如果有事务，一定要先ROLLBACK；异常块中写日志、向表中插入一条初始化记录等语句也可能失败，对这些代码必要时也应该增加异常保护，即使用嵌套的异常处理。1.7 事务控制原则：在任何出口之前，只要存在事务未结束，必须提交或者回滚，除非有特殊设计考虑。说明：存储过程在每个出口前，如果启动了事务必须结束所有事务，以提交（COMMIT）或回滚（ROLLBACK）来结束事务，否则可能会导致表锁等严重问题；如果在存储过程中，没有启动事务，就不必执行COMMIT或ROLLBACK，否则多余的RO
12、LLBACK或COMMIT操作将增加数据库的额外开销。原则：ORACLE的事务是串行的，嵌套调用中内层存储过程的提交会导致外层的存储过程事务被一并提交；诸如生成流水号这类存储过程应使用独立事务，否则在产生流水号时就将之前的业务逻辑操作提交，而后续的操作不能保证在一个事务中。例如现有系统中一个工单循环派给多个部门时，循环体内产生流水号，如最后一个派单失败时回滚操作根本就不能回撤所有操作，导致事务一致性被破坏。建议：尽量分解大事务，事务的大小应视系统的性能和应用的具体情况而定，过多过小的事务造成重做日志同步的等待，比如要往一个表插入100万条记录，如果每条记录提交一次，则事务太小，可以做一个计数器
13、，设置1万或事务5万条作一次提交。1.8 游标使用原则：原则上避免使用游标，尤其是动态游标。说明：1. 游标的效率较低，如果在代码中可以通过不同的条件分支实现的逻辑，尽量不要使用游标。动态游标通常都可以转化为若干个静态游标，因此除非必要，尽量使用静态游标代替动态游标。一般在需要把结果集返回给客户端时需要使用REF游标 type # is ref cursor，游标变量在定义是不指定固定的SQL语句,在Open时才指定SQL语句建议：推荐使用CURSOR FOR LOOP语句，可以隐式打开关闭游标；否则，显式打开游标，就必须在任何出口之前显式关闭游标。下面的示例通过 FOR IN LOOP取数据
14、，不需要显示打开和关闭游标。示例：DECALRE CURSOR c_Dept IS SELECT DEPTNO, DNAME FROM DEPT ORDER BY DEPTNO; v_Tot_Salary EMP.SALARY%TYPE;BEGIN FOR r_Dept IN c_Dept LOOP DBMS_OUTPUT.PUT_LINE(Department: | r_Dept.DEPTNO); v_Tot_Salary := 0; END LOOP;END;原则：如果存储过程返回游标类型参数，必须在任何出口前打开游标。说明：如果存储过程有游标类型的输出参数，而在某个分支中未打开，其调用者
15、使用该游标获取数据时会报错。示例：CREATE OR REPLACE PROCEDURE P_TMP_TESTSYSCURSOR( o_Cur OUT SYS_REFCURSOR - 注释)ASBEGIN IF v_Flag 0 THEN OPEN o_Cur FOR SELECT FIELD1 FROM T_WF_TABLE; RETURN; ELSE RETURN; END IF;END;应写成：CREATE OR REPLACE PROCEDURE P_TMP_TESTSYSCURSOR( o_Cur OUT SYS_REFCURSOR - 注释)ASBEGIN IF v_Flag 0
16、THEN OPEN o_Cur FOR SELECT 0 AS RET, FIELD1 AS MSG FROM T_WF_TABLE; RETURN; ELSE OPEN o_Cur FOR SELECT -1 AS RET, ErrorMsg AS MSG FROM DUAL; RETURN; END IF;END;1.9 代码规模建议：建议每个存储过程和函数的规模不宜超过500行。说明：存储过程和函数的逻辑不宜太过复杂，应当注意对于同样的计算，存储过程执行效率远低于其他应用程序，所以要避免将大量业务逻辑都放到存储过程实现。SQL优化表连接 使用非相等连接将降低执行效率； 多个表连接时，对于
17、在select子句中不出现的表，可用子查询代替表连接； 在from子句中，将小表放在后面，可提高执行效率（注意这条规则只适用于RBO优化器，CBO已不适用）；例如：查询语句A执行效率将高于语句B：（A） select b.polno, b.brno, b.start_date from evaluation a, decrease_sum b where b.start_date is not null and b.finish_date is null and ( b.proc_type in ( select proc_type from evaluation_proc_type wher
18、e evaluation_src = 2 ) or b.proc_type in ( select proc_type from evaluation_proc_type where evaluation_src = 3 ) and a.ctrlno = b.ctrlno and a.proc_type = b.proc_typeand a.client_agree = Y );（B） select b.polno, b.brno, b.start_date from decrease_sum b, evaluation a where b.start_date is not null and
19、 b.finish_date is null and ( b.proc_type in ( select proc_type from evaluation_proc_type where evaluation_src = 2 ) or b.proc_type in ( select proc_type from evaluation_proc_type where evaluation_src = 3 ) and a.ctrlno = b.ctrlno and a.proc_type = b.proc_typeand a.client_agree = Y ); 在where子句中将过滤记录数
20、最多的条件放在最后； 子查询数量较大时，使用表连接代替低效的SQL语句（IN，EXISTS，NOT IN，NOT EXISTS等）。例如：有表一：create table tab1(col1 varchar2(20) primary key,col2 number);和表二：create table tab2(col1 varchar2(20) primary key,col2 number);A 现读取sum(tab1.col2)，且tab1.col1存在于tab2.col1中：使用IN：select sum(col2) from tab1 where col1 in (select col
21、1 from tab2);使用EXISTS:select sum(col2) from tab1 a where exists ( select * from tab2 where col1=a.col1);使用连接：select sum(a.col2) from tab1 a,tab2 b where a.col1=b.col2;B 现读取sum(tab1.col2)，且tab1.col1不存在于tab2.col1中：使用NOT IN：select sum(col2) from tab1 where col1 not in (select col1 from tab2);使用NOT EXIS
22、TS:select sum(col2) from tab1 a where not exists ( select * from tab2 where col1=a.col1);使用外连接：select sum(a.col2) from tab1 a,tab2 b where a.col1=b.col2(+) and b.col1 is null;1.10 DISTNCT、UNION、ORDER BY、GROUP BY建议：在不使用DISTINCT、UNION、ORDER BY、GROUP BY情况下，也能实现业务功能的情况，一定不要使用这些功能。使用这些功能会导致对应的SQL语句排序，增加系
23、统的开销。示例：错误的用法：SELECT COUNT(*) FROM ( SELECT SERIALNO FROM T_PUB_COMMONINFO WHERE A.PARTID = 0127 AND A.PARTID SYSDATE - :B1正确的用法：SELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS WHERE (ACCEPTPHONE = :B4) AND ACCEPTTIME SYSDATE - :B1UNION ALLSELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS WHERE (CALLERNO = :B
24、3) AND ACCEPTTIME SYSDATE - :B1UNION ALLSELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS A WHERE (USERPHONE1 = :B2) AND ACCEPTTIME SYSDATE - :B11.13 TRUNCATE、DELETE建议：如果要对整个表或分区的数据删除，建议使用TRUNCATE替代DELETE。1.14 事务建议：为了提高系统的并发性，尽可能的使事务的时间缩短。原则：禁止在没有事务的存储过程和代码中，随意使用COMMIT和ROLLBACK。说明：过多多余的ROLLBACK和COMMIT容易引起
25、数据库的同步日志等待事件，对系统的性能有影响，下面语句中的COMMIT和ROLLBACK就是多余的。示例：错误的用法：CREATE OR REPLACE PROCEDURE P_MS_QUERYBYSERIALNO( i_SerialNo VARCHAR2, - 工单流水号 rCursor OUT PACK_SERVICE.t_RetDataSet - 返回结果集)ASBEGIN OPEN rCursor FOR SELECT b.STAFFNO AS STAFFNO, DECODE(b.COMMITRESULT, 0, 成功, 失败) AS COMMITRESULT FROM T_MS_SE
26、NDLOGHIS a, T_MS_INTERFACECALLED b WHERE b.SERIALNO = i_SerialNo; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK;END;正确的用法：CREATE OR REPLACE PROCEDURE P_MS_QUERYBYSERIALNO( i_SerialNo VARCHAR2, - 工单流水号 rCursor OUT PACK_SERVICE.t_RetDataSet - 返回结果集)ISBEGIN OPEN rCursor FOR SELECT b.STAFFNO AS STAFFNO, D
27、ECODE(b.COMMITRESULT, 0, 成功, 失败) AS COMMITRESULT FROM T_MS_INTERFACECALLED b WHERE b.SERIALNO = i_SerialNo;EXCEPTION WHEN OTHERS THEN OPEN rCursor FOR SELECT 成功 AS STAFFNO, 成功 AS COMMITRESULT FROM DUAL WHERE 1 = 2;END;1.15 DUAL表建议：避免通过DUAL表赋值。说明：过多的对DUAL表的访问，导致调用该表的等待时间事件比较长。比如取系统时间之类的操作，往一个表插入记录等。示
28、例：错误的用法：SELECT SYSDATE INTO v_Date FROM DUAL正确的用法：v_Date := SYSDATE 错误的用法：INSERT INTO TABLENAME(FIELD1, FIELD2, FIELD3) SELECT 2, SYSDATE, SUSBTR(v_Name, 1, 30) FROM DUAL;正确的用法：INSERT INTO TABLENAME(FIELD1, FIELD2, FIELD3) VALUES(2, SYSDATE, SUSBTR(v_Name, 1, 30); 1.16 系统表空间原则：严禁用系统表空间作为用户默认表空间；严禁在系
29、统表空间上创建用户数据库对象；严禁在SYSTEM/SYS等系统用户下，创建用户数据库对象。1.17 大字段的处理原则：禁止对VARCHAR(2000)之类的大字段值进行ORDER BY、DISTINCT、GROUP BY、UNION之类的操作。说明：此类操作将消耗大量的CPU和内存资源。在前期数据库规划时，最好规划一个独立表空间来专门存放包含大字段的对象。1.18 逻辑处理原则：禁止利用SQL语句做一些业务逻辑的判断或操作。示例：错误的用法： SELECT STAFFNO, STAFFNAME FROM T_PUB_STAFF WHERE (i_StaffNo IS NULL OR STAFF
30、NO = i_StaffNo) AND (i_StaffName IS NULL OR STAFFNAME LIKE % | i_StaffName | %)错误分析：上面的SQL语句中，利用SQL引擎对变量的值进行判断，导致在使用过程中，对该表进行全表扫描。正确的用法：通过代码中对变量的值进行判断然后决定执行对应的SQL语句。另外一些复杂逻辑判断可以用oracle提供的各种函数来代替，例如：decode()、nvl()1.19 用WHERE子句替换HAVING子句原则：避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果
31、能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 示例：错误的用法：SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION != SYDNEYAND REGION != PERTH正确的用法：SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONWHERE REGION != SYDNEYAND REGION != PERTHGROUP BY REGIONHAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中
32、. 1.20 索引合理使用索引：索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。索引的使用要恰到好处，其使用原则如下： 在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引或者用bitmap索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就没有必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。 如果待排序的列有多个，可以在这些列上建立复
33、合索引（compound index）。 在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。建议1：在where子句中应把最具限制性的条件放在最前面（不一定，该规则一般只适用于RBO）示例：在下面两条select语句中: select * from table1 where field1=0; select * from table1 where field1=0 and field1=0,则第一条select语句要比第二条selec
34、t语句效率高得多，因为第二条select语句的第一个条件耗费了大量的系统资源。建议2：在where子句中若出现范围性的条件，尽量使用=示例：select field3,field4 from tb where field1=sdf select field3,field4 from tb where field1sdf 前者运行效率快，因为前者可以迅速定位索引。 建议3：在where子句中若使用like条件，尽量使用后百分号匹配，避免使用前百分号匹配示例：select field3,field4 from tb where field2 like R% select field3,field4
35、 from tb where field2 like %R 前者运行效率快，因为后者不使用索引。 建议4：在where子句中若出现IS NOT NULL或者IS NULL做条件，则不使用索引示例：select field3,field4 from tb where field2 isnot null不使用索引。因为空值不在索引中存储 建议5：在where子句中若出现！=做条件，则不使用索引示例：select field3,field4 from tb where field2 != TOM这个语句不走索引。建议6：如果在select的字段中，对某个字段使用了MAX，MIN等函数，则应该对该字段
36、加索引。建议7：如果在WHERE子句中，用某个数据分布比较集中的列，如;性别，这样的列做条件，推荐为这样的列创建bitmap索引，以达到提高查询速度的效果。建议8：在WHERE条件表达式中，尽可能避免在要使用到索引的字段上使用函数，如果要使用函数建议创建相应的函数索引。示例：错误用法： SELECT FIELD FROM TABLENAME WHERE SUBSTRB(FIELD, 1, 4) = 5378正确用法：SELECT FIELD FROM TABLENAME WHERE FIELD LIKE 5378%建议9：创建分区表的索引时，必须创建本地（LOCAL）索引。说明：如果创建的是全
37、局索引，在对分区表的某个分区TRUNCATE时，导致该索引失效；需要对该全局索引进行重新编译。建议10：创建组合索引时，要注意组合索引的顺序和字段的选择性，把经常出现在WHERE条件中同时选择性比较好的字段放在复合索引的第一个位置。说明：SQL语句在使用复合索引时，与该复合索引字段的组合顺序有关，当索引的第一个字段出现在WHERE条件中，这时候对该表的查询能够用到该索引，当该索引的第一个字段没有出现在SQL语句的查询条件中，但该索引的其他的字段出现在WHERE条件中时，在基于原则的优化模式中，SQL引擎会用全表扫描方式查询，在基于成本的优化模式中的（First Rows）方式查询，SQL引擎会
38、对该索引全索引扫描方式查询。示例：错误的用法：ALTER TABLE T_WF_SERVICEINFOHIS ADD CONSTRAINT PK_WF_SERVICEINFOHIS PRIMARY KEY (MONTHDAY, SERIALNO, SERVICECLASSID);说明：在业务逻辑的查询中，许多时候之间按SERIALNO, SERVICECLASSID查时，在基于原则的优化模式中，用不到索引。正确的用法：ALTER TABLE T_WF_SERVICEINFOHIS ADD CONSTRAINT PK_WF_SERVICEINFOHIS PRIMARY KEY (SERIALNO
39、, SERVICECLASSID, MONTHDAY);建议11：当查询条件选择性很低时使用索引反而降低效率，这种情况下，应该用特殊的方法屏蔽该索引，如果字段为数值型的就在表达式的字段名后+ 0，为字符型的就并上空串。示例：SELECT NUM_FIELD FROM TABLENAME WHERE NUM_FIELD + 0 30SELECT STRING_FIELD FROM TABLENAME WHERE STRING_FIELD | = EXAMPLE原则1：SQL语句的WHERE子句中应尽可能将字段放在等式左边，将计算操作放在等式的右边，除非是要屏蔽该字段的的索引，否则禁止字段参与表达
40、式运算。说明：任何对字段的操作都将造成此字段上的索引被屏蔽，导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等。示例：错误的用法：SELECT SOME_FIELD FROM TABLENAME WHERE NUM_FIELD / 30 1000正确的用法：SELECT SOME_FIELD FROM TABLENAME WHERE NUM_FIELD = TO_DATE(19991201, YYYYMMDD) AND LOGDATE TO_DATE(19991202, YYYYMMDD)原则2：SQL语句的WHERE子句中每个条件的操作符两边类型应相同，禁止潜在的数据类型转换。说明：潜在的字段数据类型转换将造成索引被屏蔽，导致全表扫描。例如将字符型数据与数值型数据比较，ORACLE会自动将字符类型字段用TO_NUMBER函数进行转换。示例：错误的用法：表TABLENAME中的列