ORACLESQL编写规范(v1.1)课件_第1页
ORACLESQL编写规范(v1.1)课件_第2页
ORACLESQL编写规范(v1.1)课件_第3页
ORACLESQL编写规范(v1.1)课件_第4页
ORACLESQL编写规范(v1.1)课件_第5页
已阅读5页,还剩43页未读, 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

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

2、0; SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60; select /*+ PARALLEL */ * from employees where department_id = 60; 对于联机交易型系统来说,SQL的软解析率是非常关注的一个指标。而引起SQL不能共享的主要因素包括:大小写、空格、注释、提示等。ORACLESQL编写规范(v1.1)31.SQL(PL/SQL)编码格式规范 一些第三方开发工具,例如TOAD等,都有比较好的格式化功能,大家可以用其做风格统一和美化使用,以下是自定义的一套规范,可以供大家参考。u 大小写风格大小写风

3、格 所有数据库的关键字和保留字均使用大写,对象名称、列名称则使用小写。u 缩进风格缩进风格 程序块以及SQL均采取统一的缩进风格书写,保持代码的清晰易懂,风格一致,缩进格式保持2到4个。缩进使用空格,而不要使用【Tab】键。 当一条SQL中的谓词和子句比较多时,尽量断开成多行,可以采用使子句开头保持一行的方式,谓词关键字保持右侧对齐,左侧缩进的方式。ORACLESQL编写规范(v1.1)IF flag = 1 THEN SELECT username INTO v_userinfo FROM userinfo WHERE userid = :iuserid;END IF;u 空格及换行空格及换

4、行 不允许把多个语句写在一行,即一行只写一条语句; 避免将复杂的语句写在同一行,建议在谓词和关键字处换行; 相对独立的程序块之间必须加空行; BEGIN、END独立成行; 太长的表达式应在低优先级操作符处换行,操作符或关键字放在新行之首,划分出新行应适当地缩进,使排列整齐,语句可读;1.SQL(PL/SQL)编码格式规范ORACLESQL编写规范(v1.1)1.SQL(PL/SQL)编码格式规范 不同类型的操作符混合使用时,建议使用括号进行隔离,以使代码清晰; 减少控制语句的检查次数,例如,在IFELSE控制语句中,应将最常用的符合条件前置以被检查到。 DECLARE -定义局部变量 vFla

5、g VARCHAR2(10); -判断标志 .BEGIN IF (a=b AND a=c AND a=d) OR -在OR处断行,可使得逻辑更为清晰 (a=e AND e=f) THEN-处理部分 IF vFlag = 1 THEN -vFlag=1为经常出现的条件,可有效减少判断检查次数-处理部分 ELSIF vFlag =2 THEN -vFlag=2为次之出现的条件-处理部分 ELSE -处理部分 END IF;END;ORACLESQL编写规范(v1.1)2.ANSI SQL标准规范 1989年,美国国家标准协会(ANSI)第一次发布了SQL标准规范,92年被修订,简称SQL-92,目

6、前这个标准为SQL-99。各主要数据库厂商均宣布支持该标准,但因历史遗留问题,大多也有一些各自的特性在产品中。如果我们在SQL编写规范上面做到符合ANSI的标准,在产品向不同的数据库平台移植的过程中就不会遇到由于某一产品的特殊语法与其他产品不兼容而需要调整的问题。 ORACLE由于早于ANSI有一套SQL语法定义方式,其与ANSI的不同主要体现在“关联”(JOIN)语句的书写方式上。 一些使用过ORACLE老版本的SQL编码人员还是非常习惯于ORACLE那套老的书写方式。是否使用ANSI SQL规范并非强制,可以由项目管理人员决定。如果考虑向不同数据库平台做移植时减少兼容性带来的问题,可以参考

7、一下此规范。ORACLESQL编写规范(v1.1)2.ANSI SQL标准规范 为了对比上的清晰,分别列出ORACLE老版本上的书写方式(也称ORACLE方言)与ANSI规范的主要不同之处。u 交叉连接(CROSS JOIN),也称笛卡尔连接,是指不限定关联条件的连接ORACLE方言写法: SELECT * FROM instructor, course; ANSI写法: SELECT * FROM instructor CROSS JOIN course;ORACLESQL编写规范(v1.1)2.ANSI SQL标准规范u 等价连接(EQUI JOIN),也称内连接(INNER JOIN)或

8、规则连接(REGULAR JOIN),是指表间有等价连接条件的连接方式。ORACLE方言写法: SELECT s.first_name, s.last_name, z.zip, z.city, z.state FROM student s, zipcode z WHERE s.zip = z.zip; ANSI写法1: SELECT s.first_name, s.last_name, z.zip, z.city, z.state FROM student s JOIN zipcode z ON (s.zip=z.zip);2.ANSI SQL标准规范ANSI写法2: SELECT s.fir

9、st_name, s.last_name, zip, z.city, z.state FROM student s JOIN zipcode z USING (zip); 注意注意: ANSI写法中没有使用WHERE语句列出连接条件,而是使用了ON或者USING子句。 在使用USING子句的方式中,SELECT语句针对表间连接字段是不能用别名加限定的,如例中的zip字段。ORACLESQL编写规范(v1.1)102.ANSI SQL标准规范多表连接的ORACLE方言写法: SELECT s.section_no, c.course_no, c.description,i.first_name,

10、i.last_name FROM course c,section s,instructor i WHERE s.course_no = c.course_no AND i.instructor_id = s.instructor_id 多表连接的ANSI写法: SELECT s.section_no, c.course_no, c.description,i.first_name, i.last_name FROM course c JOIN section s ON (s.course_no = c.course_no) JOIN instructor i ON (i.instructor

11、_id = s.instructor_id); ORACLESQL编写规范(v1.1)2.ANSI SQL标准规范u 外连接(OUTER JOIN) ORACLE的方言写法:使用(+)来实现外连接 SELECT i.first_name, i.last_name, z.state FROM instructor i, zipcode z WHERE i.zip (+) = z.zip GROUP BY i.first_name, i.last_name, z.state; ANSI标准写法:使用RIGHT(LEFT) OUTER JOIN ON来实现外连接 SELECT i.first_nam

12、e, i.last_name, z.state FROM instructor i RIGHT OUTER JOIN zipcode z ON i.zip = z.zip GROUP BY i.first_name, z.state; ORACLESQL编写规范(v1.1)3.提示(HINT)的书写注意 提示的作用主要是SQL编写人员将自己对执行计划的意愿做表达的一种方式,同时也是稳定执行计划的一种最简单的手段。但在提示的写法上要有一定的注意,不正确的提示放置位置可能会使优化器将其忽略从而失去了其存在的意义。 SELECT /*+ MERGE(v) */ e1.last_name, e1.sa

13、lary, v.avg_salary FROM employees e1, (SELECT /*+ INDEX(e2 idx_employees) */ department_id, avg(salary) avg_salary FROM employees e2 GROUP BY department_id) vWHERE e1.department_id = v.department_id AND e1.salary v.avg_salary; INSERT /*+ APPEND */ INTO big_emp(department_id) SELECT department_id FRO

14、M employees;ORACLESQL编写规范(v1.1)3.提示(HINT)的书写注意 u 提示中的+不能少,如果少了,会被优化器当做注释来处理。 u 提示必须跟在语句的第一个关键字后面,如第二个语句中如果在INTO关键字后面放置提示的话,将会被优化器忽略。 u 如需要写多个提示,则可以合在一起,中间使用空格隔开。 u 提示中所引用的表如果定义了别名,则提示中必须引用别名,否则该提示也起不到作用。ORACLESQL编写规范(v1.1)4.尽量避免笛卡尔连接 产生笛卡尔连接的原因就是在多张表进行关联的操作中缺少了表间的连接条件。由于笛卡尔积产生的结果集将是多表记录的乘积关系,因此当哪怕只有

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

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

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

18、能尽快得到锁资源。 除特殊原因外,严格禁止不加过滤条件的SELECT语句中使用FOR UPDATE子句。ORACLESQL编写规范(v1.1)8.提交语句(commit)使用的原则 事务设计中,为了保证事务的完整性和有效性必然使用到提交语句。但提交语句在使用上也有一些需要注意的地方。 u 必须及时提交。 这主要是针对高并发的联机事务型(OLTP)数据库所考虑的,因为提交会使本事务所锁定的资源释放给其它事务,以提高并发性。锁的设计是并发联机事务所必须要考虑的东西,提交操作又是其中最重要的部分之一。 u 提交的动作不宜过于频繁。 在不违背第一条原则的前提下,提交又不宜过于频繁。比如一些循环语句的内

19、部。这一点主要是从性能角度所考虑的。因为提交动作本身会使ORACLE后台产生一系列操作,将会消耗掉很大的系统资源。究竟循环处理多少条做一次提交,需要做一些对比测试来最终决定,但过于频繁的提交必定会影响整体性能。ORACLESQL编写规范(v1.1)8.提交语句(commit)使用的原则u 提交操作也不宜过少。 这一条是相对于第二条原则而言的。处理的数据量过大而不做提交的话,会消耗比较大的数据库回滚段,甚至有可能导致“回滚段不足”的系统级错误发生。因此在处理数据量过大的情况时,中间完全不提交而只是等到最后才做提交的方式也是需要慎重评估和考虑的。最重要的还是做性能与可靠性的综合测试来确定大数据量处

20、理中提交的位置与频率。ORACLESQL编写规范(v1.1)9.9.子查询语句与关联语句的转换 ORACLE优化器做查询转换这一步时通常会将用户所编写的子查询语句改写为关联语句,因为在很多情况下关联方式效率要高于子查询方式。 因此,我们在编写SQL语句时尽量将子查询语句改写成关联语句。ORACLESQL编写规范(v1.1)10.关联表个数限制的基本原则 在报表数据库或批处理数据库中经常会有需要关联多张表做查询的操作,而这些表有的可能会是大表。过多的表做关联可能给性能带来严重的影响,因此,原则上关联表的个数规定不超过4个。 如果不能满足这个限定条件,可以考虑如下的两种处理方式:u 对于经常被关联

21、使用的个别字段,可以考虑在一边增加冗余字段的方式来减少关联,这是一种反范式化的处理方式,但经常被用于报表查询类型的系统中。增加冗余字段的方式会给数据导入或表插入操作带来负载上的增加,因此这种方式也要综合评估和取舍。 u 使用中间结果落地的方式。这种方式就是将原来一个SQL完成的操作拆开成多个SQL进行,将某两张或三张表的关联结果先取出,然后再拿结果集与剩下的表继续做关联,得到最终完整的结果。在做分拆过程中表的选取时要遵循的一个原则是,分拆出的两个或多个SQL其处理的结果集要尽量均衡,否则就使分拆的作用打了折扣。ORACLESQL编写规范(v1.1)11.列放置顺序的原则 在设计时,表中各个列的

22、放置顺序要有一定的考虑。通常情况是按照操作的频繁程度为判定,操作频繁的列尽量往前放置,因为放置越靠前的列其处理的整体效率是越高的。ORACLESQL编写规范(v1.1)12.表及分区表的类型u 堆表(HEAP) 默认类型,适合于大多数情况u 索引组织表(IOT) 以B*Tree索引的形式组织表,适合于只按 照主键进行查询的数据u 簇表(CLUSTER) 以簇结构建表并组织数据,适合于经常被 关联使用的表 u 外部表(EXTERNAL) 方便使用数据库直接处理库外的文本文 件数据u 全局临时表(GLOBAL TEMPORARY) 放置事务处理过程中的数据 且可以做到隔离u 高级队列表(AQ) 建

23、AQ时系统自动创建并维护ORACLESQL编写规范(v1.1)12.表及分区表的类型u ORALCE10G中的分区表类型:列表 LIST 适合于有限分布的固定值,比如机构名称范围 RANGE 适合于范围取值,比如时间哈希 HASH 适合于不好分类的情形,如序列产生的号码组合 RANGE -LIST 组合 RANGE -HASHu ORALCE11G中增加的分区表类型:组合 RANGE-RANGE 组合 LIST-LIST组合 LIST-RANGE组合 LIST-HASHORACLESQL编写规范(v1.1)13.索引的类型与选择u B树索引(B*Tree) 默认类型,适用范围最广 降序索引 适

24、用于字段上还需要做降序排序 反向关键字索引 减少类似SEQUENCE生成数据时其索引上的热块冲突 函数索引 过滤字段上有函数或隐含转换函数u 位图索引(BITMAP) 低基数字段,且表的修改非常少的情况u 全文索引 用于搜索词汇信息等特殊情景,较少使用 索引要在做过滤的字段上考虑,但总体原则是注意选择性,选择性如果超过10%则要慎重考虑,超过20%则不要建立索引。 如果有多个过滤字段,可以考虑组合索引,但要将选择性强的字段放在前面。 如果查询字段较少,且与过滤字段接近,则可以考虑将查询字段也加入组合索引中,这时可以只使用索引扫描而不需要再扫描表,执行效率会更高。ORACLESQL编写规范(v1

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

26、LESQL编写规范(v1.1)14.模糊查询语句的使用注意ORACLE的模糊查询主要有以下三种形式: 后模糊:col like ABC% 前模糊:col like %ABC 全模糊:col like %ABC% 后模糊是最好的,在col字段上建立索引是可以被优化器选择的,并且是效率比较高的索引范围扫描方式,所以要尽量采取或转换成这种形式。 前模糊形式即使在col字段上建立索引通常也还是不会被使用,即使使用效率也不会太高。所以这种方式是要尽量避免的,或者采取一些变通的手段比如采取反转函数等,但基本都需要改写原来的代码。 ORACLESQL编写规范(v1.1)14.模糊查询语句的使用注意 全模糊形

27、式普通索引的效率也会很差,写法上也是需要尽量避免的。虽然可以采用全文索引的方式来达到提高索引效率的目的,但全文索引相对复杂且占用空间要比普通索引大很多,且索引维护时要消耗更多资源,设计上需要综合考虑。ORACLESQL编写规范(v1.1)15.注意索引无法被使用的情况 索引通常是提高语句查询效率的最常用手段之一,但如下一些容易被大家忽视的情况会导致索引不被优化器选择(能用但不用),或者根本就不能被使用。 u 被索引字段在过滤谓词中使用了IS NULL或IS NOT NULL原则上应尽量避免这种写法。 首先,普通的B-TREE索引是根本无法用于IS NULL条件的,虽然可以强制用于IS NOT

28、NULL条件,但效率往往都比较差。 如果数据分布适合建立位图索引的话,这两个条件是可以使用位图索引的,但要注意的是位图索引所适用的数据分布,数据的静态性条件,以及联机交易型系统是严格禁止使用位图索引的。ORACLESQL编写规范(v1.1)15.注意索引无法被使用的情况u 被索引字段在过滤谓词上使用了全模糊查询的方式如col like %ABC% 这种情况的处理原则还是尽量避免或者进行改写,具体方法可参照前文有关模糊查询的部分。u 被索引字段在过滤谓词中使用了!=,NOT IN等 这种情况的处理原则还是尽量避免或者进行改写,比如说取不等于或NOT IN部分的反,然后就可以将条件改为等于或者是I

29、N了。但要评估各自占总记录数的比例,如果占比过高的话,索性就用全表扫描了。ORACLESQL编写规范(v1.1)15.注意索引无法被使用的情况u被索引字段在过滤谓词中使用了函数。此时除非建立函数索引,否则普通索引是无法被使用的,如例: WHERE TO_CHAR(zip) = 94002; 类似这种情况我们可以将改写一下,将计算移动到等号右侧: WHERE zip = TO_NUMBER(94002); 改写的原则就是使过滤谓词一边,也就是左侧保持“干净”,这样在zip字段上的普通索引就可以被使用了。因为毕竟函数索引在做索引维护的时候成本是比较高的,而且在zip字段上所建立的函数索引只能用于这

30、种使用了该函数的场景,针对其它没使用函数的zip字段直接做过滤条件的语句是无法共用的,整体使用效率就打了折扣。 ORACLESQL编写规范(v1.1)15.注意索引无法被使用的情况 因此,但凡能够通过改写解决问题的情况就不要建立函数索引。如果实在改写不了,评估建立函数索引后效率会有所提高后,也要考虑建立,毕竟全表扫描的情况是要尽量避免的。ORACLESQL编写规范(v1.1)16.减少REDO日志生成的一些手段 通过一些手段可以达到减少某些SQL的REDO日志生成量,从而达到提高效率的目的。对于报表系统、批处理系统、大批量数据加载等操作还是有一定的现实意义。 但切记一点的是,对关键的业务数据库

31、是禁止使用该方法的,因为减少了REDO日志会使数据库做恢复时可能丢失一些没有被记录日志的数据,在对数据的完整性要求非常严格的系统中,这种操作是应该被禁止的。ORACLESQL编写规范(v1.1)16.减少REDO日志生成的一些手段u CTAS语句CREATE TABLE a2 AS SELECT * FROM a1; -未使用CREATE TABLE a2 NOLOGGING AS SELECT * FROM a1; -使用了 NOLOGGING u 创建(重建)索引CREATE INDEX idx_t1 ON t1(col1); -未使用CREATE INDEX idx_t1 ON t1(c

32、ol1) NOLOGGING; -使用了NOLOGGINGALTER INDEX idx_t1 REBUILD; -未使用ALTER INDEX idx_t1 REBUILD NOLOGGING; -使用了NOLOGGING u 移动表ALTER TABLE t1 MOVE; -未使用ALTER TABLE t1 MOVE NOLOGGING; -使用了NOLOGGINGORACLESQL编写规范(v1.1)16.减少REDO日志生成的一些手段u 直接路径插入INSERT INTO t2 SELECT * FROM t1; -未使用ALTER TABLE t2 NOLOGGING; -使用了N

33、OLOGGINGINSERT /*+APPEND*/ INTO t2 SELECT * FROM t1; -使用了APPEND 注意:APPEND提示要与NOLOGGING结合使用; 对于INSERT INTO VALUES语句,不要使用APPEND提示, 因为起不到作用; 使用了APPEDN提示后的插入语句必须及时提交,这也限制了并 发性,因此在联机交易型系统中禁止使用该提示。 u SQL*Loader 直接路径装载使用APPEND的方式加载数据。SQL*Loader另外的三种装载方式为INSERT(默认)、REPLACE、TRUNCATE。ORACLESQL编写规范(v1.1)17.SQL

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

35、以分为如下的几种类型: u 并行查询可以有两种方式实现并行查询: 将表的并行属性打开,并行度可以设置,也可以不设置。如果不设置并行度,则会使用默认值,也就是ORACLE自己来计算并行度。打开并行的方式如下:ALTER TABLE t1 PARALLEL 4; -打开t1表的并行属性,并且设置并行度为4ALTER TABLE t1 PARALLEL;-打开t1表的并行属性,并且设置并行度为默认值CREATE TABLE t1 PARALLEL 4; -创建t1表时就打开了并行属性,并且设置了并行度,不写并行度时也同样是设置了默认值 打开了表上的并行属性后,查询语句不需要做任何修改,即实现了并行查

36、询,SELECT COUNT(col1) FROM t1;ORACLESQL编写规范(v1.1)17.SQL中实现并行操作2. 不需要在表上打开并行,而是通过提示的方式,实现并行查询:SELECT /*+ PARALLEL(t1 4) */ COUNT(col1) FROM t1; -提示的方式实现并行,并设置并行度为4SELECT /*+ PARALLEL */ COUNT(col1) FROM t1; -提示的方式实现并行,并设置并行度为默认值 u 并行DDL 可以并行化的DDL包括如下一些示例,可以针对表、表分区、索引、索引分区等: CREATE INDEX PARALLEL 4; AL

37、TER INDEX REBUILD PARALLEL 4;ALTER INDEX SPLIT PARTITION PARALLEL 4;CREATE TABLE AS SELECT PARALLEL 4;ALTER TABLE MOVE PARALLEL 4;ALTER TABLE SPLIT|COALESCE PARALLEL 4;ORACLESQL编写规范(v1.1)17.SQL中实现并行操作 注意:并行DDL语句是无法通过提示的方式实现的,在ALTER语句中也不需要被修改的表或者索引在定义时就打开并行属性。只需要在正常的DDL语句的末尾增加PARALLEL关键字即可。并行度可以跟在PAR

38、ALLEL关键字的后面,如果不写并行度,则是取默认值,将由ORACLE自行决定并行度。关于并行度的选择,可以遵循以下原则:1. 如果我们自己设置并行度,那么要综合考虑服务器的CPU的颗数,每颗的核(线程)数,实例个数(RAC系统会有意义),系统CPU资源总体的利用效率,以及系统中同一时间可能会有多少个启动并行执行的任务。2. 总的原则是同一时间启动并行执行的所有任务的总的并行度不超过CPU颗数*CPU核数(线程数)*实例个数。3. 如果判断当前时间只会有自己这一个大的SQL在执行,那么其上开的并行度最大值就是以上公式计算出的取值。但考虑系统CPU整体的负载情况后,并行度一定要小于该取值。而且并

39、行度并非越大越好,达到一定的值后有可能反倒设得越高性能越差。所以具体的并行度设置还是要谨慎,最好测试对比一下。ORACLESQL编写规范(v1.1)17.SQL中实现并行操作4. 如果不好估计系统的其它任务情况以及CPU负载情况也不稳定,则可以采取默认值的方式。这时ORACLE会根据系统的资源使用情况动态地调整每个并行SQL并行度的取值。最重要的一个原则就是对于一个CPU数量很低或者CPU负载已经很高(比如已经长时间超过了70%)的情况,则严格禁止再启动语句的并行。已经开启的并行,可以考虑降低并行度或者关闭并行。u 并行DML 并行DML主要是针对INSERT、DELETE、UPDATE、ME

40、RGE等DML语句上实现并行执行。 但与并行查询以及并行DDL都有区别的一点是,并行DML默认是不开启的,在执行语句之前需要执行如下一句作为并行功能的开启。 ALTER SESSION ENABLE PARALLEL DML;ORACLESQL编写规范(v1.1)17.SQL中实现并行操作 开启了并行功能之后,就可以在接下来的DML中实现并行执行了。 UPDATE /*+ PARALLEL(test 4) */ t1 SET OBJECT_NAME=abc; -提示的方式打开表上的并行,并设置并行度为4 ALTER TABLE t1 PARALLEL 4; -打开表上的并行属性UPDATE t

41、1 SET OBJECT_NAME=abc; -无须提示,即可实现并行执行 并行DML的实现方式与并行查询类似,可以使用提示的方式,也可以通过在表或索引上打开并行,然后直接执行的方式。但为了避免整体并行度的不可控,建议采取提示的方式。ORACLESQL编写规范(v1.1)18.分页查询语句的写法SELECT * FROM (SELECT a.*, rownum rn FROM (SELECT * FROM table_name) a -内层为原始查询语句 WHERE rownum = 1 这是一个最常见的分页查询的写法,正确的写法可以使查询的性能得到优化,在表的数据量很大的情况下也能尽快的返回

42、结果集,往后查询时性能即使下降也不会下降很多。 内层的SELECT * FROM table_name 是最原始的查询语句。 改分页查询时首先在原有查询语句外面包一层,这一层中再取一个伪列ORACLESQL编写规范(v1.1)18.分页查询语句的写法rownum,并为其取一个别名,直接使用rownum取分页的上边界;然后再在外面包一层,在这一层中用上一层取的别名写分页的下边界。 以下是一个比较典型的分页写法不对的例子。 第一个文件中SQL虽然也是每次取15行数据,但是没有达到性能优化的目的,第一页就已经很慢了,往后会更慢。 第二个文件是修改后的情况,可以看到效果非常明显。修改前查第一页的耗时是39.91秒,修改后为0.07秒。通过执行计划和SQL的状态信息都可以明显地看到两者的差别。ORACLESQL编写规范(v1.1)19.尽量减少数据库处理的负担 这条原则看似一句很虚的话,但其实有着很现实的意义。几乎所有的数据库优化手段其达到的最终目的都是减少了数据库的处理负担。比如说 : u 使用索引达到优化效果其实是缩减少了所扫描字段的数量以及优化了扫描算法,最终降低了数据库处理时逻辑读的数量。 u 使用分区表其实是缩小了原来的扫描范围,从而减轻了数据库的处理负担。 u 使用并行操作其实是利用空闲的CPU资源分担

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论