1、如何编写高效的SQL代码,潘胜荣,主题架构,主题分为三部分：一.问题提出二.优化的一些基本规则三.工作中常遇问题的解决方案，工作经验交流，讨论 计划用时：45分钟60分钟,一.优化问题的提出,1. SQL人人能写 电信公司非计算机专业的搞财务的美女都能写一个简单的查询用户资料的SQL脚本。2. 能写并不代表会写 满足功能需求是较低要求，效率高才是关键,二.优化的一些基本规则,1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMI
2、ZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性.,如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器.
3、在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.,2. 访问Table的方式 ORACLE 采用两种访问表中记录的方式: a. 全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.,b. 通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据
4、的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.,3. 共享SQL语句为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省
5、了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).,共享的语句必须满足三个条件:A. 字符级的比较:当前被执行的语句和共享池中的语句必须完全相同.B. 两个语句所指的对象必须完全相同:
6、C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 说明：所谓绑定变量，就是给变量赋值。,4. 选择最有效率的表名顺序(只在基于规则的优化器中有效)ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.,当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表
7、中检索出的记录与第一个表中合适记录进行合并,例如: 表 TAB1 16,384 条记录 表 TAB2 1000 条记录 选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 执行时间0.96秒 选择TAB1作为基础表 (不佳的方法) select count(*) from tab2,tab1 执行时间26.09秒,如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.例如: EMP表描述了LOCATION表和CATEGORY表的交集.SELECT * FROM L
8、OCATION 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 CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000说明：大家在写SQL时，最好将表之间的关联条件写在最前面，也就是写在紧接WHERE 关键字之后。这也是一种比较好
9、的编码风格。,5. WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.,6. SELECT子句中避免使用 * 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 * 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.,7. 删除重复记录最高效的删除重复记录方法 ( 因为使用了ROWID)DE
10、LETE FROM EMP EWHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);,8.用TRUNCATE替代DELETE当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短 说明：TRUNCATE只在删
11、除全表适用,TRUNCATE是DDL不是DML,9. 尽量多使用COMMIT 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源:a. 回滚段上用于恢复数据的信息.b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE为管理上述3种资源中的内部花费 说明：在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼,10. Update 多个Column 例子: 低效: UPDATE EMP SET EMP_CAT = (SELECT MAX
12、(CATEGORY) 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;,11. 使用表的别名(Alias)当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就
13、可以减少解析的时间并减少那些由Column歧义引起的语法错误. (译者注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属),12. 用EXISTS替代IN在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.低效:SELECT * FROM EMP (基础表)WHERE EMPNO 0AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB)
14、 高效:SELECT * FROM EMP (基础表)WHERE EMPNO 0AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB),13. 用NOT EXISTS替代NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.,例如:SELECT FROM EMPWHERE DEPT_NO NO
15、T IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A);为了提高效率.改写为: (方法一: 高效)SELECT .FROM EMP A,DEPT BWHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = A,(方法二: 最高效)SELECT .FROM EMP EWHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A);,14 用表连接替换EXISTS 通常来说
16、, 采用表连接的方式比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 ;,15. 用EXISTS替换DISTINCT当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换例如:低
17、效: 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); EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.,16. 用索引提高效率索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结
18、构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着
19、每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.,17. 基础表的选择基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问). 根据优化器的不同, SQL语句中基础表的选择是不一样的.如果你使用的是CBO (COST BASED OPTIMIZER),优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径.如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的连接条件都有索引对应, 在这
20、种情况下, 基础表就是FROM 子句中列在最后的那个表.举例: SELECT A.NAME , B.MANAGER FROMWORKER A, LODGING B WHEREA.LODGING = B.LODING;由于LODGING表的LODING列上有一个索引, 而且WORKER表中没有相比较的索引, WORKER表将被作为查询中的基础表.,18. 避免在索引列上使用计算低效：SELECT FROM DEPTWHERE SAL * 12 25000;高效:SELECT FROM DEPTWHERE SAL 25000/12;,19. 强制索引失效 如果两个或以上索引具有相同的等级,你可以强
21、制命令ORACLE优化器使用其中的一个(通过它,检索出的记录数量少) .举例: SELECT ENAMEFROM EMPWHERE EMPNO = 7935 AND DEPTNO + 0 = 10 /*DEPTNO上的索引将失效*/AND EMP_TYPE | = A /*EMP_TYPE上的索引将失效*/这是一种相当直接的提高查询效率的办法. 但是你必须谨慎考虑这种策略,一般来说,只有在你希望单独优化几个SQL时才能采用它.,20. 避免在索引列上使用IS NULL和IS NOT NULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 对于单列索引，如果列包含空值，索引中将不
22、存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.举例:低效: (索引失效)SELECT FROM DEPARTMENTW
23、HERE DEPT_CODE IS NOT NULL;高效: (索引有效)SELECT FROM DEPARTMENTWHERE DEPT_CODE =0;,21. 总是使用索引的第一个列如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.,22. 用UNION-ALL 替换UNION ( 如果有可能的话)当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高.
24、,23. 避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.通常, 带有UNION,
25、MINUS , INTERSECT的SQL语句都可以用其他方式重写,24. Oracle的硬解析和软解析 提到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：1、语法检查(syntax check)检查此sql的拼写是否语法。2、语义检查(semantic check)诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。3、对sql语句进行解析(parse)利用内部算法对sql进行解析，生成解析树(parse t
26、ree)及执行计划(execution plan)。4、执行sql，返回结果(execute and return),其中，软、硬解析就发生在第三个过程里。Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；假设存在，则将此sql与cache中的进行比较；假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，
27、应当极力避免硬解析，尽量使用软解析。这就是在很多项目中，倡导开发设计人员对功能相同的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因。,25. 表的三种基本连接方式NESTED LOOP:对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表，使用USE_NL(table_name1 table
28、_name2)可是强制CBO 执行嵌套循环连接。,HASH JOIN :散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。,也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使
29、用散列连接HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。,排序合并连接通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接,三.工作经验交流,独乐乐与众乐乐，孰乐？一些经验，与大家共享，希望共同进步！多访问一些技术专业网站。比如OR
30、ACLE数据库的(HTTP:/WWW.ITPUB.NET)新接触一个数据库环境时，首先要了解 工作用户的表空间情况，包括默认表空间，数据表空间和索引表空间。建索引时，最好能指定索引表空间，严格避免把索引和数据放在同一个表空间。使用CREATE TABLE TABLE_NAME AS SELECT语句建表时，最好能加一个NOLOGGING 关键字，能加快速度和不写重做日志。（建索引时亦然）,5. ORACLE hint的使用 一般情况下，不建议人为指定HINT. 非要指定时需要遵循的原则： 大表关联，用USE_HASH比较快； 大表和小表关联，USE_NL.,6.关于取某个字段最大值对应的记录，
31、是否可以考虑使用下面的第一条语句。第二条是常规写法。第一条只作了一次全表扫描，第二条两次。分别用时见注释。 仅抛砖引玉，供大家参考。 SELECT T.* FROM (SELECT AB.*,RANK() OVER(partition by ab.SERV_id ORDER BY AB.ACCT_ITEM_ID DESC) RANKSFROM PANSR_ACCTITEMBILLED_0610 ABWHERE AB.SERV_ID = 280000571501 ) T where t.ranks = 1;-:22.683select * from PANSR_ACCTITEMBILLED_06
32、10 Twhere t.ACCT_ITEM_ID in (select max(ACCT_ITEM_ID) from PANSR_ACCTITEMBILLED_0610 tt where tt.SERV_ID = 280000571501 ); - 33.158,7. 查询某个字段中是否全部为数字select T.ACCT_ITEM_ID, REPLACE(translate(T.ACCT_ITEM_ID,1234567890,*),*,) from TIBS_ACCT_ITEM_BILLED_0610 T WHERE REPLACE(translate(T.ACCT_ITEM_ID,1234
33、567890,*),*,) IS NULL;,8. 多熟悉使用ORACLE 系统函数。 例如DECODE,NVL,REPLACE,TRANSLATE,TRUNC，SUBSTR,LENGTH,INSTR等等。 要求：深入理解内涵！小心用错了！,9. Max 函数和 ORDER BY 的使用误区 SELECT MAX(OBJECT_NAME),MAX(OBJECT_TYPE) FROM USER_OBJECTS; 思考：？ SELECT * FROM USER_OBJECTS ORDER BY 1,2 DESC; 思考：？,10. FORALL 和BULK COLLECT的使用 在PL/SQL 和
34、SQL引擎(engines)中，太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。 然而使用批挷定能显著提高性能。 使用FORALL和BULK COLLECT 在处理大数据量的INSERT,UPDATE,DELETE时效果非常显著，常有数倍效率的提高。 难点：掌握这个语句需要较高的数据库理论知识。,使用FORALL 和BULK COLLECT 进行UPDATE操作的一个示例 declare TYPE TYPE_id IS TABLE OF number(20) INDEX BY binary_integer; vT
35、ab_acct_item_id TYPE_id; vTab_acct_item_type_id type_id; cursor c_get_rec is select t.acct_item_id,16420 from acct_item_owe_map t; v_limit_rows number := 10000;,begin open c_get_rec; loop fetch c_get_rec bulk collect into vTab_acct_item_id,vTab_acct_item_type_id limit v_limit_rows; forall i in vTab_acct_item_id.first . vTab_acct_item_id.count update pansr_acct_item_owe o set o.acct_item_type_id = vTab_acct_item_type_id(i) where o.acct_item_id = vTab_acct_item_id(i) ; commit; exit when c_get_rec%notfound; end loop; commit; close c_get_rec; DBMS_OUTPUT.PUT_LINE(OK!); end;,OVER! THANK YOU ！,
