当前位置:首页 >> 小学教育 >>

ORACLE SQL编写规范(v1.1)


ORACLE SQL编写规范 v1.1
武明明

2012年11月

技术规划部 数据库组 wumingming@boco .com.cn

1.SQL(PL/SQL)编码格式规范
? 统一的SQL编码格式规范不但可以使阅读者感到清晰明了,而且可以最大程度 上避免同一SQL语句在不同地方处理时由于书写格式的不统一,而造成无法共享从 而增加SQL解析负担的问题。

比如,如下的三条SQL,其达到的目的是一样的,但是在ORACLE看来这是三条
完全不同的语句,所以要进行三次硬解析。 select * from employees where department_id = 60; SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60; select /*+ PARALLEL */ * from employees where department_id = 60; 对于联机交易型系统来说,SQL的软解析率是非常关注的一个指标。而引起 SQL不能共享的主要因素包括:大小写、空格、注释、提示等。
2

1.SQL(PL/SQL)编码格式规范
一些第三方开发工具,例如TOAD等,都有比较好的格式化功能,大家可以用 其做风格统一和美化使用,以下是自定义的一套规范,可以供大家参考。

?
?

大小写风格
所有数据库的关键字和保留字均使用大写,对象名称、列名称则使用小写。 缩进风格

程序块以及SQL均采取统一的缩进风格书写,保持代码的清晰易懂,风格一致,
缩进格式保持2到4个。缩进使用空格,而不要使用【Tab】键。 当一条SQL中的谓词和子句比较多时,尽量断开成多行,可以采用使子句开头 保持一行的方式,谓词关键字保持右侧对齐,左侧缩进的方式。
3

1.SQL(PL/SQL)编码格式规范
IF flag = 1 THEN SELECT username INTO v_userinfo FROM userinfo WHERE userid = :iuserid; END IF;

?

空格及换行 不允许把多个语句写在一行,即一行只写一条语句; 避免将复杂的语句写在同一行,建议在谓词和关键字处换行; 相对独立的程序块之间必须加空行; BEGIN、END独立成行; 太长的表达式应在低优先级操作符处换行,操作符或关键字放在新行之首, 划分出新行应适当地缩进,使排列整齐,语句可读;

1.SQL(PL/SQL)编码格式规范
不同类型的操作符混合使用时,建议使用括号进行隔离,以使代码清晰; 减少控制语句的检查次数,例如,在IF…ELSE控制语句中,应将最常用的符 合条件前置以被检查到。
DECLARE --定义局部变量 vFlag 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;

2.ANSI SQL标准规范
1989年,美国国家标准协会(ANSI)第一次发布了SQL标准规范,92年被修

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

考虑向不同数据库平台做移植时减少兼容性带来的问题,可以参考一下此规范。

2.ANSI SQL标准规范
为了对比上的清晰,分别列出ORACLE老版本上的书写方式(也称ORACLE

方言)与ANSI规范的主要不同之处。
? 交叉连接(CROSS JOIN),也称笛卡尔连接,是指不限定关联条件的连接

ORACLE方言写法:
SELECT * FROM instructor, course;

ANSI写法:
SELECT * FROM instructor CROSS JOIN course;

2.ANSI SQL标准规范
? 等价连接(EQUI JOIN),也称内连接(INNER JOIN)或规则连接 (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.first_name, s.last_name, zip, z.city, z.state FROM student s JOIN zipcode z USING (zip);

注意:
ANSI写法中没有使用WHERE语句列出连接条件,而是使用了ON或者USING 子句。 在使用USING子句的方式中,SELECT语句针对表间连接字段是不能用别名加 限定的,如例中的zip字段。

2.ANSI SQL标准规范
多表连接的ORACLE方言写法:
SELECT s.section_no, c.course_no, c.description,i.first_name,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_id = s.instructor_id);
10

2.ANSI SQL标准规范
? 外连接(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_name, 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;

3.提示(HINT)的书写注意
提示的作用主要是SQL编写人员将自己对执行计划的意愿做表达的一种方式, 同时也是稳定执行计划的一种最简单的手段。但在提示的写法上要有一定的注意, 不正确的提示放置位置可能会使优化器将其忽略从而失去了其存在的意义。
SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary FROM employees e1, (SELECT /*+ INDEX(e2 idx_employees) */ department_id, avg(salary) avg_salary FROM employees e2 GROUP BY department_id) v WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary; INSERT /*+ APPEND */ INTO big_emp(department_id) SELECT department_id FROM employees;

3.提示(HINT)的书写注意
? 提示中的+不能少,如果少了,会被优化器当做注释来处理。

?

提示必须跟在语句的第一个关键字后面,如第二个语句中如果在INTO关键字

后面放置提示的话,将会被优化器忽略。

?

如需要写多个提示,则可以合在一起,中间使用空格隔开。

?

提示中所引用的表如果定义了别名,则提示中必须引用别名,否则该提示也

起不到作用。

4.尽量避免笛卡尔连接
产生笛卡尔连接的原因就是在多张表进行关联的操作中缺少了表间的连接条
件。由于笛卡尔积产生的结果集将是多表记录的乘积关系,因此当哪怕只有一张 表的记录数比较大时,其结果集都将被数倍以上地放大,这势必给数据库性能带

来严重影响。
因此,除一些特殊原因外,要尽量避免笛卡尔连接的产生,也就是说在写关 联语句时要严格检查连接条件是否有遗漏。 请编码及检查人员注意,不带连接条件的多表连接语句是严格禁止的,如果 有则必须要说明原因。

5.语句中尽量避免使用*
当对表中的所有字段不做筛选地全选择时,可以使用*来替代所有字段。但这
样做有两个缺点,一是ORACLE优化器在执行SQL前有一步是查询转换,这种情 况将会被改写以具体的字段来替代*,查询转换的操作势必要加重语句处理的负担。 二是这种省事的方式可能会使编码人员忽略检查是否有必要查询表中的所有字段, 而在优化SQL中有一条原则就是尽量避免多余部分被处理。所以说,不使用*,并

且认真检查所提取的每一个字段都是否有必要将是严谨而避免低效的方法。
INSERT语句中列出具体的字段还有一个好处就是可以避免当表结构发生变 化时产生编译性的错误。

6.使用TRUNCATE替代不含过滤条件的DELETE
不带WHERE语句的DELETE,其作用相当于全删除操作。如果表的记录数比

较多,速度将比较慢,可以使用TRUNCATE语句来替代,TRUNCATE是DDL语句,
直接截断表的空间存储与表定义之间的关系。 因为是字典一级的操作,所以速度会非常快,而且无论表记录数的多少,正 常情况下TRUNCATE语句都会在数秒内完成。但要注意其与DELETE操作的区别 是不需要提交操作,且无法回滚。 TRUNCATE操作可以针对表以及表分区级,在对分区数据做清理时还是非常 有用的。

7. FOR UPDATE语句使用注意
FOR UPDATE语句的作用在于并发环境下,某用户将查询到的数据加锁,以
便后续的操作过程中,该数据不会被其他用户所修改,这通常用于一些公共模块的 公共处理场景。

由于锁定操作势必会影响到并发性,所以原则就是尽量把条件限定严格,使
锁定的记录数最少,并且在后续的操作完成后尽快提交或回滚事务,以便其他用户 能尽快得到锁资源。 除特殊原因外,严格禁止不加过滤条件的SELECT语句中使用FOR UPDATE 子句。

8.提交语句(commit)使用的原则
事务设计中,为了保证事务的完整性和有效性必然使用到提交语句。但提交 语句在使用上也有一些需要注意的地方。 ? 必须及时提交。 这主要是针对高并发的联机事务型(OLTP)数据库所考虑的,因为提交会 使本事务所锁定的资源释放给其它事务,以提高并发性。锁的设计是并发联机事 务所必须要考虑的东西,提交操作又是其中最重要的部分之一。 ? 提交的动作不宜过于频繁。 在不违背第一条原则的前提下,提交又不宜过于频繁。比如一些循环语句的 内部。这一点主要是从性能角度所考虑的。因为提交动作本身会使ORACLE后台 产生一系列操作,将会消耗掉很大的系统资源。究竟循环处理多少条做一次提交, 需要做一些对比测试来最终决定,但过于频繁的提交必定会影响整体性能。

8.提交语句(commit)使用的原则
? 提交操作也不宜过少。
这一条是相对于第二条原则而言的。处理的数据量过大而不做提交的话,会 消耗比较大的数据库回滚段,甚至有可能导致“回滚段不足”的系统级错误发生。 因此在处理数据量过大的情况时,中间完全不提交而只是等到最后才做提交的方 式也是需要慎重评估和考虑的。最重要的还是做性能与可靠性的综合测试来确定

大数据量处理中提交的位置与频率。

9.子查询语句与关联语句的转换
ORACLE优化器做查询转换这一步时通常会将用户所编写的子查询语句改写 为关联语句,因为在很多情况下关联方式效率要高于子查询方式。

因此,我们在编写SQL语句时尽量将子查询语句改写成关联语句。

10.关联表个数限制的基本原则
在报表数据库或批处理数据库中经常会有需要关联多张表做查询的操作,而 这些表有的可能会是大表。过多的表做关联可能给性能带来严重的影响,因此, 原则上关联表的个数规定不超过4个。 如果不能满足这个限定条件,可以考虑如下的两种处理方式: ? 对于经常被关联使用的个别字段,可以考虑在一边增加冗余字段的方式来减 少关联,这是一种反范式化的处理方式,但经常被用于报表查询类型的系统中。 增加冗余字段的方式会给数据导入或表插入操作带来负载上的增加,因此这种方 式也要综合评估和取舍。 ? 使用中间结果落地的方式。这种方式就是将原来一个SQL完成的操作拆开成 多个SQL进行,将某两张或三张表的关联结果先取出,然后再拿结果集与剩下的 表继续做关联,得到最终完整的结果。在做分拆过程中表的选取时要遵循的一个 原则是,分拆出的两个或多个SQL其处理的结果集要尽量均衡,否则就使分拆的 作用打了折扣。

11.列放置顺序的原则
在设计时,表中各个列的放置顺序要有一定的考虑。通常情况是按照操作 的频繁程度为判定,操作频繁的列尽量往前放置,因为放置越靠前的列其处理的 整体效率是越高的。

12.表及分区表的类型
? 堆表(HEAP) ? 索引组织表(IOT) ? 簇表(CLUSTER) ? 外部表(EXTERNAL) 默认类型,适合于大多数情况 以B*Tree索引的形式组织表,适合于只按 照主键进行查询的数据 以簇结构建表并组织数据,适合于经常被 关联使用的表 方便使用数据库直接处理库外的文本文 件数据

? 全局临时表(GLOBAL TEMPORARY) 放置事务处理过程中的数据
且可以做到隔离 ? 高级队列表(AQ) 建AQ时系统自动创建并维护

12.表及分区表的类型
? ORALCE10G中的分区表类型:
适合于有限分布的固定值,比如机构名称
适合于范围取值,比如时间 适合于不好分类的情形,如序列产生的号码

列表 LIST
范围 RANGE 哈希 HASH

组合 RANGE -LIST
组合 RANGE -HASH

?

ORALCE11G中增加的分区表类型:

组合 RANGE-RANGE
组合 LIST-LIST 组合 LIST-RANGE

组合 LIST-HASH

13.索引的类型与选择
? B树索引(B*Tree) 降序索引 反向关键字索引 函数索引 ? 位图索引(BITMAP) ? 全文索引 默认类型,适用范围最广 适用于字段上还需要做降序排序 减少类似SEQUENCE生成数据时其索引上的热块冲突 过滤字段上有函数或隐含转换函数 低基数字段,且表的修改非常少的情况 用于搜索词汇信息等特殊情景,较少使用

索引要在做过滤的字段上考虑,但总体原则是注意选择性,选择性如果超过 10%则要慎重考虑,超过20%则不要建立索引。

如果有多个过滤字段,可以考虑组合索引,但要将选择性强的字段放在前面。
如果查询字段较少,且与过滤字段接近,则可以考虑将查询字段也加入组合 索引中,这时可以只使用索引扫描而不需要再扫描表,执行效率会更高。

13.索引的类型与选择
索引在提高查询语句效率的同时会降低DML语句的效率,如何权衡两者之间

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

作使索引失效的风险。

14.模糊查询语句的使用注意
ORACLE的模糊查询主要有以下三种形式:

后模糊:col like ‘ABC%’
前模糊:col like ‘%ABC’ 全模糊:col like ‘%ABC%’ 后模糊是最好的,在col字段上建立索引是可以被优化器选择的,并 且是效率比较高的索引范围扫描方式,所以要尽量采取或转换成这种形式。 前模糊形式即使在col字段上建立索引通常也还是不会被使用,即使 使用效率也不会太高。所以这种方式是要尽量避免的,或者采取一些变通

的手段比如采取反转函数等,但基本都需要改写原来的代码。

14.模糊查询语句的使用注意
全模糊形式普通索引的效率也会很差,写法上也是需要尽量避免的。虽然可
以采用全文索引的方式来达到提高索引效率的目的,但全文索引相对复杂且占用空 间要比普通索引大很多,且索引维护时要消耗更多资源,设计上需要综合考虑。

15.注意索引无法被使用的情况
索引通常是提高语句查询效率的最常用手段之一,但如下一些容易被大家忽

视的情况会导致索引不被优化器选择(能用但不用),或者根本就不能被使用。

?

被索引字段在过滤谓词中使用了IS NULL或IS NOT NULL

原则上应尽量避免这种写法。 首先,普通的B-TREE索引是根本无法用于IS NULL条件的,虽然可以强制用 于IS NOT NULL条件,但效率往往都比较差。 如果数据分布适合建立位图索引的话,这两个条件是可以使用位图索引的, 但要注意的是位图索引所适用的数据分布,数据的静态性条件,以及联机交易型

系统是严格禁止使用位图索引的。

15.注意索引无法被使用的情况
? 被索引字段在过滤谓词上使用了全模糊查询的方式如col like ‘%ABC%’ 这种情况的处理原则还是尽量避免或者进行改写,具体方法可参照前文有关 模糊查询的部分。

?

被索引字段在过滤谓词中使用了!=,<>,NOT IN等 这种情况的处理原则还是尽量避免或者进行改写,比如说取不等于或NOT

IN部分的反,然后就可以将条件改为等于或者是IN了。但要评估各自占总记录数
的比例,如果占比过高的话,索性就用全表扫描了。

15.注意索引无法被使用的情况
?被索引字段在过滤谓词中使用了函数。 此时除非建立函数索引,否则普通索引是无法被使用的,如例: WHERE TO_CHAR(zip) = ‘94002’;

类似这种情况我们可以将改写一下,将计算移动到等号右侧:
WHERE zip = TO_NUMBER(‘94002’);

改写的原则就是使过滤谓词一边,也就是左侧保持“干净”,这样在zip字 段上的普通索引就可以被使用了。因为毕竟函数索引在做索引维护的时候成本是 比较高的,而且在zip字段上所建立的函数索引只能用于这种使用了该函数的场景, 针对其它没使用函数的zip字段直接做过滤条件的语句是无法共用的,整体使用效 率就打了折扣。

15.注意索引无法被使用的情况
因此,但凡能够通过改写解决问题的情况就不要建立函数索引。如果实在改 写不了,评估建立函数索引后效率会有所提高后,也要考虑建立,毕竟全表扫描 的情况是要尽量避免的。

16.减少REDO日志生成的一些手段
通过一些手段可以达到减少某些SQL的REDO日志生成量,从而达到提高效 率的目的。对于报表系统、批处理系统、大批量数据加载等操作还是有一定的现

实意义。
但切记一点的是,对关键的业务数据库是禁止使用该方法的,因为减少了 REDO日志会使数据库做恢复时可能丢失一些没有被记录日志的数据,在对数据 的完整性要求非常严格的系统中,这种操作是应该被禁止的。

16.减少REDO日志生成的一些手段
? CTAS语句 CREATE TABLE a2 AS SELECT * FROM a1; --未使用 CREATE TABLE a2 NOLOGGING AS SELECT * FROM a1; --使用了 NOLOGGING ? 创建(重建)索引 CREATE INDEX idx_t1 ON t1(col1); --未使用 CREATE INDEX idx_t1 ON t1(col1) NOLOGGING; --使用了NOLOGGING ALTER INDEX idx_t1 REBUILD; --未使用 ALTER INDEX idx_t1 REBUILD NOLOGGING; --使用了NOLOGGING

? 移动表 ALTER TABLE t1 MOVE; ALTER TABLE t1 MOVE NOLOGGING;

--未使用 --使用了NOLOGGING

16.减少REDO日志生成的一些手段
? 直接路径插入 INSERT INTO t2 SELECT * FROM t1; --未使用 ALTER TABLE t2 NOLOGGING; --使用了NOLOGGING INSERT /*+APPEND*/ INTO t2 SELECT * FROM t1; --使用了APPEND 注意:APPEND提示要与NOLOGGING结合使用; 对于INSERT INTO… VALUES语句,不要使用APPEND提示, 因为起不到作用; 使用了APPEDN提示后的插入语句必须及时提交,这也限制了并 发性,因此在联机交易型系统中禁止使用该提示。 ? SQL*Loader 直接路径装载 使用APPEND的方式加载数据。 SQL*Loader另外的三种装载方式为INSERT(默认)、REPLACE、TRUNCATE。

17.SQL中实现并行操作
使用并行操作的主要目的是提高语句的执行效率,但是否考虑使用并行操作 要遵守以下的几点原则: 一定要在多CPU的服务器上测试并行操作的效果,在单CPU的PC机上是很

难看到效果的,容易使测试失真。
针对于报表系统、批处理系统、OLAP系统可以考虑使用并行技术,联机交 易型(OLTP)系统不要轻易使用。 由于数据量比较巨大导致SQL执行非常缓慢,但发现硬件资源CPU、内存、 I/O(尤其是CPU)负担比较轻时,可以考虑使用并行技术。如果硬件资源已经发 现不足甚至已经成为瓶颈时,先不要考虑使用并行技术。

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

17.SQL中实现并行操作
2. 不需要在表上打开并行,而是通过提示的方式,实现并行查询: SELECT /*+ PARALLEL(t1 4) */ COUNT(col1) FROM t1; --提示的方式实现并行, 并设置并行度为4 SELECT /*+ PARALLEL */ COUNT(col1) FROM t1; --提示的方式实现并行,并 设置并行度为默认值 ? 并行DDL 可以并行化的DDL包括如下一些示例,可以针对表、表分区、索引、索引分 区等: CREATE INDEX PARALLEL 4; ALTER 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;

17.SQL中实现并行操作
注意:并行DDL语句是无法通过提示的方式实现的,在ALTER语句中也不需 要被修改的表或者索引在定义时就打开并行属性。只需要在正常的DDL语句的末 尾增加PARALLEL关键字即可。并行度可以跟在PARALLEL关键字的后面,如果 不写并行度,则是取默认值,将由ORACLE自行决定并行度。 关于并行度的选择,可以遵循以下原则: 1. 如果我们自己设置并行度,那么要综合考虑服务器的CPU的颗数,每颗的核 (线程)数,实例个数(RAC系统会有意义),系统CPU资源总体的利用效率, 以及系统中同一时间可能会有多少个启动并行执行的任务。 2. 总的原则是同一时间启动并行执行的所有任务的总的并行度不超过CPU颗数 *CPU核数(线程数)*实例个数。 3. 如果判断当前时间只会有自己这一个大的SQL在执行,那么其上开的并行度 最大值就是以上公式计算出的取值。但考虑系统CPU整体的负载情况后,并行度 一定要小于该取值。而且并行度并非越大越好,达到一定的值后有可能反倒设得 越高性能越差。所以具体的并行度设置还是要谨慎,最好测试对比一下。

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

ALTER SESSION ENABLE PARALLEL DML;

17.SQL中实现并行操作
开启了并行功能之后,就可以在接下来的DML中实现并行执行了。 UPDATE /*+ PARALLEL(test 4) */ t1 SET OBJECT_NAME='abc'; --提示的方式 打开表上的并行,并设置并行度为4

ALTER TABLE t1 PARALLEL 4; --打开表上的并行属性 UPDATE t1 SET OBJECT_NAME='abc'; --无须提示,即可实现并行执行
并行DML的实现方式与并行查询类似,可以使用提示的方式,也可以通过在 表或索引上打开并行,然后直接执行的方式。但为了避免整体并行度的不可控, 建议采取提示的方式。

18.分页查询语句的写法
SELECT * FROM (SELECT a.*, rownum rn FROM (SELECT * FROM table_name) a --内层为原始查询语句 WHERE rownum <= 20) WHERE rn >= 1
这是一个最常见的分页查询的写法,正确的写法可以使查询的性能得到优化, 在表的数据量很大的情况下也能尽快的返回结果集,往后查询时性能即使下降也 不会下降很多。 内层的SELECT * FROM table_name 是最原始的查询语句。

改分页查询时首先在原有查询语句外面包一层,这一层中再取一个伪列

18.分页查询语句的写法
rownum,并为其取一个别名,直接使用rownum取分页的上边界;
然后再在外面包一层,在这一层中用上一层取的别名写分页的下边界。 以下是一个比较典型的分页写法不对的例子。

第一个文件中SQL虽然也是每次取15行数据,但是没有达到性能优化的目的,
第一页就已经很慢了,往后会更慢。

第二个文件是修改后的情况,可以看到效果非常明显。修改前查第一页的耗 时是39.91秒,修改后为0.07秒。通过执行计划和SQL的状态信息都可以明显地看

到两者的差别。

19.尽量减少数据库处理的负担
这条原则看似一句很虚的话,但其实有着很现实的意义。几乎所有的数据库 优化手段其达到的最终目的都是减少了数据库的处理负担。比如说 :
? 使用索引达到优化效果其实是缩减少了所扫描字段的数量以及优化了扫描算 法,最终降低了数据库处理时逻辑读的数量。 ? 使用分区表其实是缩小了原来的扫描范围,从而减轻了数据库的处理负担。

? 使用并行操作其实是利用空闲的CPU资源分担某一时刻某一操作所带来的 过重的负担。 ……. 这些所谓的优化手段我们固然可以在发生性能问题的时候拿来使用, 但有一些工作是我们的开发设计人员或者程序编码人员更加容易控制的。比如 说:

19.尽量减少数据库处理的负担
? 查询、更新语句中严格选择所操作的字段。 设想一下,如果在我们的一个批处理程序中有一个UPDATE语句感觉操作很 慢,但通过程序人员的排查竟然发现,该语句所更新的某些字段竟然从来没有被 查询或使用过。试问一下,这样的更新除了多消耗资源和时间外有何意义? 尽量减少表被访问的次数,看下面这个SQL:
UPDATE tb_target a SET a.col1 = (SELECT b.col1 FROM tb_source b WHERE b.id = a.id), a.col2 = (SELECT b.col2 FROM tb_source b WHERE b.id = a.id), a.col3 = (SELECT b.col3 FROM tb_source b WHERE b.id = a.id), a.col4 = (SELECT b.col4 FROM tb_source b WHERE b.id = a.id) WHERE a.id IN (SELECT b.id FROM tb_source b)

该语句作用其实就是用更新一个表的多个列,但这样写会多次扫描源表。

19.尽量减少数据库处理的负担
如果改写成如下的方式,则只需要扫描一次了:
UPDATE tb_target a SET (col1, a.col2, a.col3, a.col4) = (SELECT b.col1, b.col2, b.col3, b.col4 FROM tb_source b WHERE b.id = a.id) WHERE EXISTS (SELECT 1 FROM tb_source b WHERE b.id = a.id)

所以说,我们的编码人员在实现业务逻辑和功能后如果能够再站在资源消耗 的角度多想一想,想想我们如何能减轻一点数据库的处理负担。哪怕是少连一次 库,少扫描一次表,少做一次关联,少处理一个字段,精简一个处理逻辑,减少 一个并非必做不可的排序操作(比如DISTINCT、ORDER BY等)。能够想一想 优化器会如何处理我们写的SQL,想一想服务器会消耗哪些资源处理一个SQL, 想一想表扫描的范围有多大,想一想索引是否能够缩小扫描的范围,想一想是否 能够通过分区做裁剪,想一想我们这一次的处理是否与别的应用可以共用结果集, 等等这些,那么我们也许就会为将来的优化工作减轻了很多负担。

规范之规范
规范是死的,但场景是活的。ORACLE的优化器一直在不断地变化和改进,
有些规范可能已经不再适合你当前所使用的版本或具体场景。这些规范提出的目 的并非要把SQL编写人员“框死”,而是想让你在练到“无剑”之前先有一把

“剑”,仅此而已。
当有一天你发现这其中有不对的地方或者还不如你的方法更优秀,那么你完 全可以不按照这上面的去做。但如果你还没有发现,那么就请你先参考一下这上 面所云。 规范不是最重要的,最重要的是你求真、严谨、积极探索和挑战规范的心。

如果你有新的发现也请你积极与我们分享你的心得。


赞助商链接
相关文章:
存储过程编写规范V1
V0.1 V1.0 修订日 2014-10-01 修订者 审核者 描述 2014-12-16 备注: ...14 4 1. 概述 1.1 目的本规范用于指导项目编写 Oracle 数据库存储过程 (...
ORACLE-SQL编写规范(详细解释)
ORACLE-SQL编写规范(详细解释)_计算机软件及应用_IT/计算机_专业资料。ORACLE-SQL编写规范(详细解释)数据库 SQL 优化原则 1. SQL 语句的执行过程 Open cursor Foun...
SQL编写规范
1.1 文档说明该文档主要是为编程人员提供 PL/SQL 软件的编写规范。 1.2 ...ORACLE SQL编写规范(v1... 48页 免费 SQL书写规范 5页 免费 SQL编写规范与优化...
Sql编写规范
13.习惯 13.1 EXPLAIN PLAN 是一个很好的分析 SQL 语句的工具,它可以在不...ORACLE SQL编写规范(v1... 48页 免费 客户服务部SQL语句编写规... 4页 ...
Oracle 高效SQL编程指南 V1.1
Oracle 高效SQL编程指南 V1.1_IT认证_资格考试/认证_教育专区。协同办公组数据...数据库高效编程指南 第1章 写在前面的话 规范 SQL 书写方式,让 SQL 可以共享...
Oracle SQL规范V2.0
Oracle SQL 规范 V 2.0 文档信息:文档名称 Oracle SQL 规范 V2.0.doc 文档...版本号 0.1 1.0 2.0 修订人 林强、王凌洁 杨疆湖、林强、汤成 林强 ...
华为Oracle编程规范
数据库编程规范 ORACLE 分则 内部公开 第1章 1.1 简介 范围和简介 本规范是...STAFFNO SECONDS_OF_DAY v_BeginDate、v_FaxNumber、 c_StaffAuth 字段名(...
Oracle数据库开发和设计规范
Oracle 数据库开发和设计规范 1 命名原则 1.1 约定ü 是指对数据库、数据库...sys_UserView_v biz_UserOrderView_v 1.5 索引规则如下: table_name_column_...
ORACLE 系统开发规范_V2.1
ORACLE SQL编写规范(v1.1) 48页 免费 操作规范(三)——Linux 5... 46页 ...Oracle的维使用 15页 免费如要投诉违规内容,请到百度文库投诉中心;如要提出功能...
Oracle开发规范
数据库设计规范 V00.01.000 第 1 页共 25 页 历史版本 版本 V00.00.001...17 第 3 页共 25 页 Oracle 开发规范 1 数据库命名约定 1.1 规则 ? ? ...
更多相关标签: