突破常识:SQL增加DISTINCT后查询效率反而提高

杨廷琨,网名 yangtingkun

云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家

只要增加了DISTINCT关键字,Oracle就会对随后跟着的所有字段进行排序去重。以前也经常发现由于开发人员对SQL不是很理解,在SELECT列表的20多个字段前面添加了DISTINCT,造成查询的执行异常缓慢,基本上很难在ORA-1555错误出现之前得到查询的结果,甚至有些SQL会产生ORA-7445错误。所以在给开发人员培训的时候还着重介绍了一下DISTINCT的功能以及不正确地使用DISTINCT所带来的性能方面的负面影响。

不过这次碰到了一个有趣的现象:开发人员在测试一个比较复杂的SQL时发现如果SQL中加上了DISTINCT,则查询大概要花费4分钟左右;而如果不加DISTINCT,则查询执行了10多分钟仍然没有返回结果。

根据这样的描述,首先想到的是可能DISTINCT是在查询的最内层,由于加上DISTINCT使得第一步的结果集缩小了,从而导致查询性能的提高。但一看SQL才发现,DISTINCT居然是在查询的最外层。

由于原始SQL很复杂,牵扯太多的表,很难表述清楚。因此这里模拟了一个例子,这个例子由于受到数据量和SQL复杂程度的限制,所以是否添加DISTINCT对SQL执行时间没有太大的影响,但是两个SQL逻辑读的差异还是可以说明一定问题的。

首先建立模拟环境:

SQL> CREATE TABLE T1 AS SELECT * FROMDBA_OBJECTS 2 WHERE OWNER = 'SYS' 3 AND OBJECT_TYPE NOT LIKE '%BODY' AND OBJECT_TYPENOT LIKE 'JAVA%'; Table created. SQL> CREATE TABLE T2 AS SELECT * FROMDBA_SEGMENTS WHERE OWNER = 'SYS'; Table created. SQL> CREATE TABLE T3 AS SELECT * FROMDBA_INDEXES WHERE OWNER = 'SYS'; Table created. SQL> ALTER TABLE T1 ADD CONSTRAINT PK_T1PRIMARY KEY (OBJECT_NAME); Table altered. SQL> CREATE INDEX IND_T2_SEGNAME ONT2(SEGMENT_NAME); Index created. SQL> CREATE INDEX IND_T3_TABNAME ONT3(TABLE_NAME); Index created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T1', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE) PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T2', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE) PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T3', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100', CASCADE => TRUE) PL/SQL procedure successfully completed.

下面看看原始SQL和增加DISTINCT后的差别:

SQL> SET AUTOT TRACE SQL> SELECT T1.OBJECT_NAME, T1.OBJECT_TYPE,T2.TABLESPACE_NAME 2 FROM T1, T2 WHERE T1.OBJECT_NAME = T2.SEGMENT_NAMEAND T1.OBJECT_NAME IN 3 (SELECT INDEX_NAME FROM T3 WHERE T3.TABLESPACE_NAME= T2.TABLESPACE_NAME); 311 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE(Cost=12 Card=668 Bytes=62124) 1 0 HASH JOIN (SEMI) (Cost=12 Card=668 Bytes=62124) 2 1 HASHJOIN (Cost=9 Card=668 Bytes=39412) 3 2 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668Bytes=21376) 4 2 TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806Bytes=102762) 5 1 TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=340Bytes=11560) Statistics ---------------------------------------------------------- 93 consistentgets 0 sorts (memory) 0 sorts (disk) 311 rowsprocessed SQL> SELECT DISTINCT T1.OBJECT_NAME,T1.OBJECT_TYPE, T2.TABLESPACE_NAME 2 FROM T1, T2 WHERE T1.OBJECT_NAME= T2.SEGMENT_NAME AND T1.OBJECT_NAME IN 3 ( SELECT INDEX_NAME FROM T3 WHERET3.TABLESPACE_NAME = T2.TABLESPACE_NAME); 311 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE(Cost=16 Card=1 Bytes=93) 1 0 SORT (UNIQUE) (Cost=16 Card=1 Bytes=93) 2 1 HASH JOIN (Cost=12 Card=1 Bytes=93) 3 2 HASH JOIN (Cost=5 Card=668 Bytes=44088) 4 3 TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=340Bytes=11560) 5 3 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668Bytes=21376) 6 2 TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806Bytes=102762) Statistics ---------------------------------------------------------- 72 consistent gets 1 sorts (memory) 311 rows processed

从SQL执行的统计信息可以看出,添加DISTINCT后,语句的逻辑读数量反而比不加DISTINCT要低。为什么会产生这种情况,这还要从执行计划说起。

对于不加DISTINCT的情况:由于使用IN子查询,Oracle对第二个连接采用了HASH JOIN SEMI,这种方式相对于普通的HASHJOIN来说代价要大一些。

如果添加了DISTINCT:CBO清楚知道在最后一步肯定要进行排序去重的操作,因此在连接时就选择了HASH JOIN作为连接方式。这就是加上了DISTINCT后,逻辑读反而减少的原因。不过加上DISTINCT后,执行计划增加了一个排序操作;而在不加DISTINCT时是没有这个操作的。

当连接的表数据量很大,但SELECT的最终结果并不是很多,且SELECT列数也不是很多的时候,加上DISTINCT后,增加的排序的代价要小于SEMIJOIN连接的代价。这就是增加一个DISTINCT操作,查询效率反而提高的真正原因。

最后要说明一点,举这个例子意在说明:优化时没有什么东西是一成不变的,几乎任何事情都有可能发生,不要被一些所谓规则限制住

这篇文章并不是在介绍一种优化SQL的方法,严格意义上讲,加上DISTINCT和不加DISTINCT是两个完全不同的SQL语句。虽然在这个例子中二者是等价的,但这是表结构、约束条件和数据本身共同限制的结果,换成另一个环境,这两个SQL得到的结果可能会相去甚远。因此这两个SQL实际上并不等价,不要试图将本文的例子作为优化时的一种方法。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-04-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

SQL之美 - 分页查询的排序问题

编辑手记:前面我们分享过分页查询的基础知识,其目的就是控制输出结果集大小,将结果尽快的返回。主要有两种方式,一种是嵌套的查询方式,一种是通过范围控制分页的最大值...

3706
来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

3068
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第二篇)(r3笔记第82天)

在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我们大体介绍了下问题的情况,已经初步根据awr能...

3497
来自专栏杨建荣的学习笔记

循序渐进调优union相关的sql(r2笔记23天)

今天在生产中发现一条sql语句消耗了大量的cpu资源。使用top -c来查看。 PID USER PR NI VIRT RES SHR S...

3666
来自专栏乐沙弥的世界

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。由...

2392
来自专栏杨建荣的学习笔记

sql_profile的使用(一) (r2笔记29天)

今天看了老熊关于sql_profile的讲解,受益匪浅,自己在本机也做了一通,感觉好记性不如烂笔头还是得多总结总测试才能真正理解。 准备的数据如下,创建两个表,...

2759
来自专栏Albert陈凯

理解SQL原理SQL调优你必须知道的10条铁律

原文地址: http://www.nowamagic.net/librarys/veda/detail/1502 我们做软件开发的,大部分人都离不开跟数据库打...

3635
来自专栏乐沙弥的世界

当心外部连接中的ON子句

       在SQL tuning中,不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右...

1384
来自专栏杨建荣的学习笔记

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

27511
来自专栏杨建荣的学习笔记

关于统计信息过期的性能落差(r5笔记第36天)

今天客户反馈某一个应用部署补丁的时候,执行了一个脚本一个多小时还没有执行完。 语句是下面这样的形式。 insert into em1_rater_00068_0...

4086

扫码关注云+社区