ORACLESQL编写规范(v1.1).ppt

《ORACLESQL编写规范(v1.1).ppt》由会员分享，可在线阅读，更多相关《ORACLESQL编写规范(v1.1).ppt（48页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、ORACLESQL编写规范编写规范v1.12012年11月武明明技术规划部 数据库组2 1.1.SQL（PL/SQL）编码格式规范u统一的SQL编码格式规范不但可以使阅读者感到清晰明了，而且可以最大程度上避免同一SQL语句在不同地方处理时由于书写格式的不统一，而造成无法共享从而增加SQL解析负担的问题。比如，如下的三条SQL，其达到的目的是一样的，但是在ORACLE看来这是三条完全不同的语句，所以要进行三次硬解析。select*fromemployeeswheredepartment_id=60;SELECT*FROMEMPLOYEESWHEREDEPARTMENT_ID=60;select/

2、*+PARALLEL*/*fromemployeeswheredepartment_id=60;对于联机交易型系统来说，SQL的软解析率是非常关注的一个指标。而引起SQL不能共享的主要因素包括：大小写、空格、注释、提示等。31.SQL（PL/SQL）编码格式规范一些第三方开发工具，例如TOAD等，都有比较好的格式化功能，大家可以用其做风格统一和美化使用，以下是自定义的一套规范，可以供大家参考。u大小写风格大小写风格所有数据库的关键字和保留字均使用大写，对象名称、列名称则使用小写。u缩进风格缩进风格程序块以及SQL均采取统一的缩进风格书写，保持代码的清晰易懂，风格一致，缩进格式保持2到4个。缩进

3、使用空格，而不要使用【Tab】键。当一条SQL中的谓词和子句比较多时，尽量断开成多行，可以采用使子句开头保持一行的方式，谓词关键字保持右侧对齐，左侧缩进的方式。IFflag=1THENSELECTusernameINTOv_userinfoFROMuserinfoWHEREuserid=:iuserid;ENDIF;u空格及换行空格及换行不允许把多个语句写在一行，即一行只写一条语句；避免将复杂的语句写在同一行，建议在谓词和关键字处换行；相对独立的程序块之间必须加空行；BEGIN、END独立成行；太长的表达式应在低优先级操作符处换行，操作符或关键字放在新行之首，划分出新行应适当地缩进，使排列整齐

4、，语句可读；1.SQL（PL/SQL）编码格式规范1.SQL（PL/SQL）编码格式规范不同类型的操作符混合使用时，建议使用括号进行隔离，以使代码清晰；减少控制语句的检查次数，例如，在IFELSE控制语句中，应将最常用的符合条件前置以被检查到。DECLARE-定义局部变量vFlagVARCHAR2(10);-判断标志.BEGINIF(a=bANDa=cANDa=d)OR-在OR处断行，可使得逻辑更为清晰(a=eANDe=f)THEN-处理部分IFvFlag=1THEN-vFlag=1为经常出现的条件，可有效减少判断检查次数-处理部分ELSIFvFlag=2THEN-vFlag=2为次之出现的条

5、件-处理部分ELSE-处理部分ENDIF;END;2.ANSI SQL标准规范1989年，美国国家标准协会（ANSI）第一次发布了SQL标准规范，92年被修订，简称SQL-92，目前这个标准为SQL-99。各主要数据库厂商均宣布支持该标准，但因历史遗留问题，大多也有一些各自的特性在产品中。如果我们在SQL编写规范上面做到符合ANSI的标准，在产品向不同的数据库平台移植的过程中就不会遇到由于某一产品的特殊语法与其他产品不兼容而需要调整的问题。ORACLE由于早于ANSI有一套SQL语法定义方式，其与ANSI的不同主要体现在“关联”（JOIN）语句的书写方式上。一些使用过ORACLE老版本的SQL

6、编码人员还是非常习惯于ORACLE那套老的书写方式。是否使用ANSISQL规范并非强制，可以由项目管理人员决定。如果考虑向不同数据库平台做移植时减少兼容性带来的问题，可以参考一下此规范。2.ANSI SQL标准规范为了对比上的清晰，分别列出ORACLE老版本上的书写方式（也称ORACLE方言）与ANSI规范的主要不同之处。u交叉连接（CROSSJOIN），也称笛卡尔连接，是指不限定关联条件的连接ORACLE方言写法：SELECT*FROMinstructor,course;ANSI写法：SELECT*FROMinstructorCROSSJOINcourse;2.ANSI SQL标准规范u等价

7、连接（EQUIJOIN），也称内连接（INNERJOIN）或规则连接（REGULARJOIN），是指表间有等价连接条件的连接方式。ORACLE方言写法：SELECTs.first_name,s.last_name,z.zip,z.city,z.stateFROMstudents,zipcodezWHEREs.zip=z.zip;ANSI写法1：SELECTs.first_name,s.last_name,z.zip,z.city,z.stateFROMstudentsJOINzipcodezON(s.zip=z.zip)；2.ANSI SQL标准规范ANSI写法2：SELECTs.first_

8、name,s.last_name,zip,z.city,z.stateFROMstudentsJOINzipcodezUSING(zip);注意注意：ANSI写法中没有使用WHERE语句列出连接条件，而是使用了ON或者USING子句。在使用USING子句的方式中，SELECT语句针对表间连接字段是不能用别名加限定的，如例中的zip字段。102.ANSI SQL标准规范多表连接的ORACLE方言写法：SELECTs.section_no,c.course_no,c.description,i.first_name,i.last_nameFROMcoursec,sections,instructo

9、riWHEREs.course_no=c.course_noANDi.instructor_id=s.instructor_id多表连接的ANSI写法：SELECTs.section_no,c.course_no,c.description,i.first_name,i.last_nameFROMcoursecJOINsectionsON(s.course_no=c.course_no)JOINinstructoriON(i.instructor_id=s.instructor_id);2.ANSI SQL标准规范u外连接（OUTERJOIN）ORACLE的方言写法：使用（+）来实现外连接SE

10、LECTi.first_name,i.last_name,z.stateFROMinstructori,zipcodezWHEREi.zip(+)=z.zipGROUPBYi.first_name,i.last_name,z.state;ANSI标准写法：使用RIGHT(LEFT)OUTERJOINON来实现外连接SELECTi.first_name,i.last_name,z.stateFROMinstructoriRIGHTOUTERJOINzipcodezONi.zip=z.zipGROUPBYi.first_name,z.state;3.提示（HINT）的书写注意提示的作用主要是SQL

11、编写人员将自己对执行计划的意愿做表达的一种方式，同时也是稳定执行计划的一种最简单的手段。但在提示的写法上要有一定的注意，不正确的提示放置位置可能会使优化器将其忽略从而失去了其存在的意义。SELECT/*+MERGE(v)*/e1.last_name,e1.salary,v.avg_salaryFROMemployeese1,(SELECT/*+INDEX(e2 idx_employees)*/department_id,avg(salary)avg_salaryFROMemployeese2GROUPBYdepartment_id)vWHEREe1.department_id=v.depart

12、ment_idANDe1.salaryv.avg_salary;INSERT/*+APPEND*/INTObig_emp(department_id)SELECTdepartment_idFROMemployees;3.提示（HINT）的书写注意u提示中的+不能少，如果少了，会被优化器当做注释来处理。u提示必须跟在语句的第一个关键字后面，如第二个语句中如果在INTO关键字后面放置提示的话，将会被优化器忽略。u如需要写多个提示，则可以合在一起，中间使用空格隔开。u提示中所引用的表如果定义了别名，则提示中必须引用别名，否则该提示也起不到作用。4.尽量避免笛卡尔连接产生笛卡尔连接的原因就是在多张表进

13、行关联的操作中缺少了表间的连接条件。由于笛卡尔积产生的结果集将是多表记录的乘积关系，因此当哪怕只有一张表的记录数比较大时，其结果集都将被数倍以上地放大，这势必给数据库性能带来严重影响。因此，除一些特殊原因外，要尽量避免笛卡尔连接的产生，也就是说在写关联语句时要严格检查连接条件是否有遗漏。请编码及检查人员注意，不带连接条件的多表连接语句是严格禁止的，如果有则必须要说明原因。5.语句中尽量避免使用*当对表中的所有字段不做筛选地全选择时，可以使用*来替代所有字段。但这样做有两个缺点，一是ORACLE优化器在执行SQL前有一步是查询转换，这种情况将会被改写以具体的字段来替代*，查询转换的操作势必要加重

14、语句处理的负担。二是这种省事的方式可能会使编码人员忽略检查是否有必要查询表中的所有字段，而在优化SQL中有一条原则就是尽量避免多余部分被处理。所以说，不使用*，并且认真检查所提取的每一个字段都是否有必要将是严谨而避免低效的方法。INSERT语句中列出具体的字段还有一个好处就是可以避免当表结构发生变化时产生编译性的错误。6.使用TRUNCATE替代不含过滤条件的DELETE不带WHERE语句的DELETE，其作用相当于全删除操作。如果表的记录数比较多，速度将比较慢，可以使用TRUNCATE语句来替代，TRUNCATE是DDL语句，直接截断表的空间存储与表定义之间的关系。因为是字典一级的操作，所以

15、速度会非常快，而且无论表记录数的多少，正常情况下TRUNCATE语句都会在数秒内完成。但要注意其与DELETE操作的区别是不需要提交操作，且无法回滚。TRUNCATE操作可以针对表以及表分区级，在对分区数据做清理时还是非常有用的。7.FOR UPDATE语句使用注意FORUPDATE语句的作用在于并发环境下，某用户将查询到的数据加锁，以便后续的操作过程中，该数据不会被其他用户所修改，这通常用于一些公共模块的公共处理场景。由于锁定操作势必会影响到并发性，所以原则就是尽量把条件限定严格，使锁定的记录数最少，并且在后续的操作完成后尽快提交或回滚事务，以便其他用户能尽快得到锁资源。除特殊原因外，严格禁

16、止不加过滤条件的SELECT语句中使用FORUPDATE子句。8.提交语句(commit)使用的原则事务设计中，为了保证事务的完整性和有效性必然使用到提交语句。但提交语句在使用上也有一些需要注意的地方。u必须及时提交。这主要是针对高并发的联机事务型（OLTP）数据库所考虑的，因为提交会使本事务所锁定的资源释放给其它事务，以提高并发性。锁的设计是并发联机事务所必须要考虑的东西，提交操作又是其中最重要的部分之一。u提交的动作不宜过于频繁。在不违背第一条原则的前提下，提交又不宜过于频繁。比如一些循环语句的内部。这一点主要是从性能角度所考虑的。因为提交动作本身会使ORACLE后台产生一系列操作，将会消

17、耗掉很大的系统资源。究竟循环处理多少条做一次提交，需要做一些对比测试来最终决定，但过于频繁的提交必定会影响整体性能。8.提交语句(commit)使用的原则u提交操作也不宜过少。这一条是相对于第二条原则而言的。处理的数据量过大而不做提交的话，会消耗比较大的数据库回滚段，甚至有可能导致“回滚段不足”的系统级错误发生。因此在处理数据量过大的情况时，中间完全不提交而只是等到最后才做提交的方式也是需要慎重评估和考虑的。最重要的还是做性能与可靠性的综合测试来确定大数据量处理中提交的位置与频率。9.9.子查询语句与关联语句的转换ORACLE优化器做查询转换这一步时通常会将用户所编写的子查询语句改写为关联语句

18、，因为在很多情况下关联方式效率要高于子查询方式。因此，我们在编写SQL语句时尽量将子查询语句改写成关联语句。10.关联表个数限制的基本原则在报表数据库或批处理数据库中经常会有需要关联多张表做查询的操作，而这些表有的可能会是大表。过多的表做关联可能给性能带来严重的影响，因此，原则上关联表的个数规定不超过4个。如果不能满足这个限定条件，可以考虑如下的两种处理方式：u对于经常被关联使用的个别字段，可以考虑在一边增加冗余字段的方式来减少关联，这是一种反范式化的处理方式，但经常被用于报表查询类型的系统中。增加冗余字段的方式会给数据导入或表插入操作带来负载上的增加，因此这种方式也要综合评估和取舍。u使用中

19、间结果落地的方式。这种方式就是将原来一个SQL完成的操作拆开成多个SQL进行，将某两张或三张表的关联结果先取出，然后再拿结果集与剩下的表继续做关联，得到最终完整的结果。在做分拆过程中表的选取时要遵循的一个原则是，分拆出的两个或多个SQL其处理的结果集要尽量均衡，否则就使分拆的作用打了折扣。11.列放置顺序的原则在设计时，表中各个列的放置顺序要有一定的考虑。通常情况是按照操作的频繁程度为判定，操作频繁的列尽量往前放置，因为放置越靠前的列其处理的整体效率是越高的。12.表及分区表的类型u堆表（HEAP）默认类型，适合于大多数情况u索引组织表（IOT）以B*Tree索引的形式组织表，适合于只按照主键

20、进行查询的数据u簇表（CLUSTER）以簇结构建表并组织数据，适合于经常被关联使用的表u外部表（EXTERNAL）方便使用数据库直接处理库外的文本文件数据u全局临时表（GLOBALTEMPORARY）放置事务处理过程中的数据且可以做到隔离u高级队列表（AQ）建AQ时系统自动创建并维护12.表及分区表的类型uORALCE10G中的分区表类型：列表LIST适合于有限分布的固定值，比如机构名称范围RANGE适合于范围取值，比如时间哈希HASH适合于不好分类的情形，如序列产生的号码组合RANGE-LIST组合RANGE-HASHuORALCE11G中增加的分区表类型：组合RANGE-RANGE组合LI

21、ST-LIST组合LIST-RANGE组合LIST-HASH13.索引的类型与选择uB树索引（B*Tree）默认类型，适用范围最广降序索引适用于字段上还需要做降序排序反向关键字索引减少类似SEQUENCE生成数据时其索引上的热块冲突函数索引过滤字段上有函数或隐含转换函数u位图索引（BITMAP）低基数字段，且表的修改非常少的情况u全文索引用于搜索词汇信息等特殊情景，较少使用索引要在做过滤的字段上考虑，但总体原则是注意选择性，选择性如果超过10%则要慎重考虑，超过20%则不要建立索引。如果有多个过滤字段，可以考虑组合索引，但要将选择性强的字段放在前面。如果查询字段较少，且与过滤字段接近，则可以考

22、虑将查询字段也加入组合索引中，这时可以只使用索引扫描而不需要再扫描表，执行效率会更高。13.索引的类型与选择索引在提高查询语句效率的同时会降低DML语句的效率，如何权衡两者之间关系需要多做考虑和测试。在生产系统中如果发现没有使用过或者效率比较差的索引，最好考虑删除掉。对于有大量DML操作的表，其索引增长可能会较快，而索引的页块中又可能存在大量的空隙使得索引的使用效率逐渐降低。所以针对这种情况，应该在非业务时间定期做索引重建等维护工作。大表做分区后，原则上分区的索引都要考虑使用本地（LOCAL）索引。即使可能会牺牲某些全局查询时检索的效率，但考虑到这种情况相对较少，而本地索引在应用能够进入分区后

23、的查询效率要高很多，并且不会产生某些分区维护操作使索引失效的风险。14.模糊查询语句的使用注意ORACLE的模糊查询主要有以下三种形式：后模糊：collikeABC%前模糊：collike%ABC全模糊：collike%ABC%后模糊是最好的，在col字段上建立索引是可以被优化器选择的，并且是效率比较高的索引范围扫描方式，所以要尽量采取或转换成这种形式。前模糊形式即使在col字段上建立索引通常也还是不会被使用，即使使用效率也不会太高。所以这种方式是要尽量避免的，或者采取一些变通的手段比如采取反转函数等，但基本都需要改写原来的代码。14.模糊查询语句的使用注意全模糊形式普通索引的效率也会很差，写

24、法上也是需要尽量避免的。虽然可以采用全文索引的方式来达到提高索引效率的目的，但全文索引相对复杂且占用空间要比普通索引大很多，且索引维护时要消耗更多资源，设计上需要综合考虑。15.注意索引无法被使用的情况索引通常是提高语句查询效率的最常用手段之一，但如下一些容易被大家忽视的情况会导致索引不被优化器选择（能用但不用），或者根本就不能被使用。u被索引字段在过滤谓词中使用了ISNULL或ISNOTNULL原则上应尽量避免这种写法。首先，普通的B-TREE索引是根本无法用于ISNULL条件的，虽然可以强制用于ISNOTNULL条件，但效率往往都比较差。如果数据分布适合建立位图索引的话，这两个条件是可以使

25、用位图索引的，但要注意的是位图索引所适用的数据分布，数据的静态性条件，以及联机交易型系统是严格禁止使用位图索引的。15.注意索引无法被使用的情况u被索引字段在过滤谓词上使用了全模糊查询的方式如collike%ABC%这种情况的处理原则还是尽量避免或者进行改写，具体方法可参照前文有关模糊查询的部分。u被索引字段在过滤谓词中使用了!=，NOTIN等这种情况的处理原则还是尽量避免或者进行改写，比如说取不等于或NOTIN部分的反，然后就可以将条件改为等于或者是IN了。但要评估各自占总记录数的比例，如果占比过高的话，索性就用全表扫描了。15.注意索引无法被使用的情况u被索引字段在过滤谓词中使用了函数。此

26、时除非建立函数索引，否则普通索引是无法被使用的，如例：WHERETO_CHAR(zip)=94002;类似这种情况我们可以将改写一下，将计算移动到等号右侧：WHEREzip=TO_NUMBER(94002);改写的原则就是使过滤谓词一边，也就是左侧保持“干净”，这样在zip字段上的普通索引就可以被使用了。因为毕竟函数索引在做索引维护的时候成本是比较高的，而且在zip字段上所建立的函数索引只能用于这种使用了该函数的场景，针对其它没使用函数的zip字段直接做过滤条件的语句是无法共用的，整体使用效率就打了折扣。15.注意索引无法被使用的情况因此，但凡能够通过改写解决问题的情况就不要建立函数索引。如果

27、实在改写不了，评估建立函数索引后效率会有所提高后，也要考虑建立，毕竟全表扫描的情况是要尽量避免的。16.减少REDO日志生成的一些手段通过一些手段可以达到减少某些SQL的REDO日志生成量，从而达到提高效率的目的。对于报表系统、批处理系统、大批量数据加载等操作还是有一定的现实意义。但切记一点的是，对关键的业务数据库是禁止使用该方法的，因为减少了REDO日志会使数据库做恢复时可能丢失一些没有被记录日志的数据，在对数据的完整性要求非常严格的系统中，这种操作是应该被禁止的。16.减少REDO日志生成的一些手段uCTAS语句CREATETABLEa2ASSELECT*FROMa1;-未使用CREATE

28、TABLEa2NOLOGGINGASSELECT*FROMa1;-使用了NOLOGGINGu创建（重建）索引CREATEINDEXidx_t1ONt1(col1);-未使用CREATEINDEXidx_t1ONt1(col1)NOLOGGING;-使用了NOLOGGINGALTERINDEXidx_t1REBUILD;-未使用ALTERINDEXidx_t1REBUILDNOLOGGING;-使用了NOLOGGINGu移动表ALTERTABLEt1MOVE;-未使用ALTERTABLEt1MOVENOLOGGING;-使用了NOLOGGING16.减少REDO日志生成的一些手段u直接路径插入I

29、NSERTINTOt2SELECT*FROMt1;-未使用ALTERTABLEt2NOLOGGING;-使用了NOLOGGINGINSERT/*+APPEND*/INTOt2SELECT*FROMt1;-使用了APPEND注意：APPEND提示要与NOLOGGING结合使用；对于INSERTINTOVALUES语句，不要使用APPEND提示，因为起不到作用；使用了APPEDN提示后的插入语句必须及时提交，这也限制了并发性，因此在联机交易型系统中禁止使用该提示。uSQL*Loader直接路径装载使用APPEND的方式加载数据。SQL*Loader另外的三种装载方式为INSERT（默认）、REPL

30、ACE、TRUNCATE。17.SQL中实现并行操作使用并行操作的主要目的是提高语句的执行效率，但是否考虑使用并行操作要遵守以下的几点原则：一定要在多CPU的服务器上测试并行操作的效果，在单CPU的PC机上是很难看到效果的，容易使测试失真。针对于报表系统、批处理系统、OLAP系统可以考虑使用并行技术，联机交易型（OLTP）系统不要轻易使用。由于数据量比较巨大导致SQL执行非常缓慢，但发现硬件资源CPU、内存、I/O（尤其是CPU）负担比较轻时，可以考虑使用并行技术。如果硬件资源已经发现不足甚至已经成为瓶颈时，先不要考虑使用并行技术。17.SQL中实现并行操作从语句类型上来分，并行操作可以分为如

31、下的几种类型：u并行查询可以有两种方式实现并行查询：1.将表的并行属性打开，并行度可以设置，也可以不设置。如果不设置并行度，则会使用默认值，也就是ORACLE自己来计算并行度。打开并行的方式如下：ALTERTABLEt1PARALLEL4;-打开t1表的并行属性，并且设置并行度为4ALTERTABLEt1PARALLEL;-打开t1表的并行属性，并且设置并行度为默认值CREATETABLEt1PARALLEL4;-创建t1表时就打开了并行属性，并且设置了并行度，不写并行度时也同样是设置了默认值打开了表上的并行属性后，查询语句不需要做任何修改，即实现了并行查询，SELECTCOUNT(col1)

32、FROMt1;17.SQL中实现并行操作2.不需要在表上打开并行，而是通过提示的方式，实现并行查询：SELECT/*+PARALLEL(t1 4)*/COUNT(col1)FROMt1;-提示的方式实现并行，并设置并行度为4SELECT/*+PARALLEL*/COUNT(col1)FROMt1;-提示的方式实现并行，并设置并行度为默认值u并行DDL可以并行化的DDL包括如下一些示例，可以针对表、表分区、索引、索引分区等：CREATEINDEXPARALLEL4;ALTERINDEXREBUILDPARALLEL4;ALTERINDEXSPLITPARTITIONPARALLEL4;CREAT

33、ETABLEASSELECTPARALLEL4;ALTERTABLEMOVEPARALLEL4;ALTERTABLESPLIT|COALESCEPARALLEL4;17.SQL中实现并行操作注意：并行DDL语句是无法通过提示的方式实现的，在ALTER语句中也不需要被修改的表或者索引在定义时就打开并行属性。只需要在正常的DDL语句的末尾增加PARALLEL关键字即可。并行度可以跟在PARALLEL关键字的后面，如果不写并行度，则是取默认值，将由ORACLE自行决定并行度。关于并行度的选择，可以遵循以下原则：1.如果我们自己设置并行度，那么要综合考虑服务器的CPU的颗数，每颗的核（线程）数，实例个

34、数（RAC系统会有意义），系统CPU资源总体的利用效率，以及系统中同一时间可能会有多少个启动并行执行的任务。2.总的原则是同一时间启动并行执行的所有任务的总的并行度不超过CPU颗数*CPU核数（线程数）*实例个数。3.如果判断当前时间只会有自己这一个大的SQL在执行，那么其上开的并行度最大值就是以上公式计算出的取值。但考虑系统CPU整体的负载情况后，并行度一定要小于该取值。而且并行度并非越大越好，达到一定的值后有可能反倒设得越高性能越差。所以具体的并行度设置还是要谨慎，最好测试对比一下。17.SQL中实现并行操作4.如果不好估计系统的其它任务情况以及CPU负载情况也不稳定，则可以采取默认值的方

35、式。这时ORACLE会根据系统的资源使用情况动态地调整每个并行SQL并行度的取值。5.最重要的一个原则就是对于一个CPU数量很低或者CPU负载已经很高（比如已经长时间超过了70%）的情况，则严格禁止再启动语句的并行。已经开启的并行，可以考虑降低并行度或者关闭并行。u并行DML并行DML主要是针对INSERT、DELETE、UPDATE、MERGE等DML语句上实现并行执行。但与并行查询以及并行DDL都有区别的一点是，并行DML默认是不开启的，在执行语句之前需要执行如下一句作为并行功能的开启。ALTERSESSIONENABLEPARALLELDML;17.SQL中实现并行操作开启了并行功能之后

36、，就可以在接下来的DML中实现并行执行了。UPDATE/*+PARALLEL(test 4)*/t1SETOBJECT_NAME=abc;-提示的方式打开表上的并行,并设置并行度为4ALTERTABLEt1PARALLEL4;-打开表上的并行属性UPDATEt1SETOBJECT_NAME=abc;-无须提示，即可实现并行执行并行DML的实现方式与并行查询类似，可以使用提示的方式，也可以通过在表或索引上打开并行，然后直接执行的方式。但为了避免整体并行度的不可控，建议采取提示的方式。18.分页查询语句的写法SELECT*FROM(SELECTa.*,rownumrnFROM(SELECT*FRO

37、Mtable_name)a-内层为原始查询语句WHERErownum=1这是一个最常见的分页查询的写法，正确的写法可以使查询的性能得到优化，在表的数据量很大的情况下也能尽快的返回结果集，往后查询时性能即使下降也不会下降很多。内层的SELECT*FROMtable_name是最原始的查询语句。改分页查询时首先在原有查询语句外面包一层，这一层中再取一个伪列18.分页查询语句的写法rownum，并为其取一个别名，直接使用rownum取分页的上边界；然后再在外面包一层，在这一层中用上一层取的别名写分页的下边界。以下是一个比较典型的分页写法不对的例子。第一个文件中SQL虽然也是每次取15行数据，但是没有

38、达到性能优化的目的，第一页就已经很慢了，往后会更慢。第二个文件是修改后的情况，可以看到效果非常明显。修改前查第一页的耗时是39.91秒，修改后为0.07秒。通过执行计划和SQL的状态信息都可以明显地看到两者的差别。19.尽量减少数据库处理的负担这条原则看似一句很虚的话，但其实有着很现实的意义。几乎所有的数据库优化手段其达到的最终目的都是减少了数据库的处理负担。比如说：u使用索引达到优化效果其实是缩减少了所扫描字段的数量以及优化了扫描算法，最终降低了数据库处理时逻辑读的数量。u使用分区表其实是缩小了原来的扫描范围，从而减轻了数据库的处理负担。u使用并行操作其实是利用空闲的CPU资源分担某一时刻某

39、一操作所带来的过重的负担。.这些所谓的优化手段我们固然可以在发生性能问题的时候拿来使用，但有一些工作是我们的开发设计人员或者程序编码人员更加容易控制的。比如说：19.尽量减少数据库处理的负担u查询、更新语句中严格选择所操作的字段。设想一下，如果在我们的一个批处理程序中有一个UPDATE语句感觉操作很慢，但通过程序人员的排查竟然发现，该语句所更新的某些字段竟然从来没有被查询或使用过。试问一下，这样的更新除了多消耗资源和时间外有何意义？尽量减少表被访问的次数，看下面这个SQL：UPDATEtb_targetaSETa.col1=(SELECTb.col1FROMtb_sourcebWHEREb.i

40、d=a.id),a.col2=(SELECTb.col2FROMtb_sourcebWHEREb.id=a.id),a.col3=(SELECTb.col3FROMtb_sourcebWHEREb.id=a.id),a.col4=(SELECTb.col4FROMtb_sourcebWHEREb.id=a.id)WHEREa.idIN(SELECTb.idFROMtb_sourceb)该语句作用其实就是用更新一个表的多个列，但这样写会多次扫描源表。19.尽量减少数据库处理的负担如果改写成如下的方式，则只需要扫描一次了：UPDATEtb_targetaSET(col1,a.col2,a.col3

41、,a.col4)=(SELECTb.col1,b.col2,b.col3,b.col4FROMtb_sourcebWHEREb.id=a.id)WHEREEXISTS(SELECT1FROMtb_sourcebWHEREb.id=a.id)所以说，我们的编码人员在实现业务逻辑和功能后如果能够再站在资源消耗的角度多想一想，想想我们如何能减轻一点数据库的处理负担。哪怕是少连一次库，少扫描一次表，少做一次关联，少处理一个字段，精简一个处理逻辑，减少一个并非必做不可的排序操作（比如DISTINCT、ORDERBY等）。能够想一想优化器会如何处理我们写的SQL，想一想服务器会消耗哪些资源处理一个SQL，

42、想一想表扫描的范围有多大，想一想索引是否能够缩小扫描的范围，想一想是否能够通过分区做裁剪，想一想我们这一次的处理是否与别的应用可以共用结果集，等等这些，那么我们也许就会为将来的优化工作减轻了很多负担。规范是死的，但场景是活的。ORACLE的优化器一直在不断地变化和改进，有些规范可能已经不再适合你当前所使用的版本或具体场景。这些规范提出的目的并非要把SQL编写人员“框死”，而是想让你在练到“无剑”之前先有一把“剑”，仅此而已。当有一天你发现这其中有不对的地方或者还不如你的方法更优秀，那么你完全可以不按照这上面的去做。但如果你还没有发现，那么就请你先参考一下这上面所云。规范不是最重要的，最重要的是你求真、严谨、积极探索和挑战规范的心。如果你有新的发现也请你积极与我们分享你的心得。规范之规范