SQL优化之为何增加DISTINCT后查询效率反而提高？
只要增加了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对第二个连接采用了HASHJOIN SEMI，这种方式相对于普通的HASHJOIN来说代价要大一些。
如果添加了DISTINCT：CBO清楚知道在最后一步肯定要进行排序去重的操作，因此在连接时就选择了HASHJOIN作为连接方式。这就是加上了DISTINCT后，逻辑读反而减少的原因。不过加上DISTINCT后，执行计划增加了一个排序操作；而在不加DISTINCT时是没有这个操作的。
当连接的表数据量很大，但SELECT的最终结果并不是很多，且SELECT列数也不是很多的时候，加上DISTINCT后，增加的排序的代价要小于SEMIJOIN连接的代价。这就是增加一个DISTINCT操作，查询效率反而提高的真正原因。
最后要说明一点，举这个例子意在说明优化时没有什么东西是一成不变的，几乎任何事情都有可能发生，不要被一些所谓规则限制住。这篇文章并不是在介绍一种优化SQL的方法，严格意义上讲，加上DISTINCT和不加DISTINCT是两个完全不同的SQL语句。虽然在这个例子中二者是等价的，但这是表结构、约束条件和数据本身共同限制的结果，换成另一个环境，这两个SQL得到的结果可能会相去甚远。因此这两个SQL实际上并不等价，不要试图将本文的例子作为优化时的一种方法。
文章来源：《Oracle DBA手记1》第13章 SQL优化之Everything is possible 作者：杨廷琨
配图来源：http://www.yinliseo.com/wp-content/uploads/2012/07/images.jpg
回复“手记1”可下载本书前两篇内容：DBA工作手记/诊断案例篇(建议复制链接至电脑端）