ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 增加Distinct后查询效率反而提高——SQL优化之Everything is possible

增加Distinct后查询效率反而提高——SQL优化之Everything is possible

原创 Linux操作系统 作者:yangtingkun 时间:2009-11-20 16:22:07 0 删除 编辑

只有增加DISTINCT关键字,Oracle必然需要对后面的所有字段进行排序。以前也经常发现由于开发人员对SQL不是很理解,在SELECT列表的20多个字段前面添加了DISTINCT,造成查询基本上不可能执行完成,甚至产生ORA-7445错误。所以一直向开发人员强调DISTINCT给性能带来的影响。

没想到开发人员在测试一条大的SQL的时候,告诉我如果加上了DISTINCT,则查询大概需要4分钟左右可以执行完,如果不加DISTINCT,则查询执行了10多分钟,仍然得不到结果。

首先想到的是可能DISTINCT是在子查询中,由于加上了DISTINCT,将第一步结果集缩小了,导致查询性能提高,结果一看SQL,发现DISTINCT居然是在查询的最外层。


由于原始SQL太长,而且牵扯的表太多,很难说清楚,这里模拟了一个例子,这个例子由于数据量和SQL的复杂程度限制,无法看出二者执行时间上的明显差别。这里从两种情况的逻辑读对比来说明问题。

首先建立模拟环境:

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS
  2  WHERE WNER = 'SYS'
  3  AND OBJECT_TYPE NOT LIKE '%BODY'
  4  AND OBJECT_TYPE NOT LIKE 'JAVA%';

Table created.

SQL> CREATE TABLE T2 AS SELECT * FROM DBA_SEGMENTS WHERE WNER = 'SYS';

Table created.

SQL> CREATE TABLE T3 AS SELECT * FROM DBA_INDEXES WHERE WNER = 'SYS';

Table created.

SQL> ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (OBJECT_NAME);

Table altered.

SQL> CREATE INDEX IND_T2_SEGNAME ON T2(SEGMENT_NAME);

Index created.

SQL> CREATE INDEX IND_T3_TABNAME ON T3(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
  3  WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
  4  AND T1.OBJECT_NAME IN 
  5     (
  6             SELECT INDEX_NAME FROM T3
  7             WHERE T3.TABLESPACE_NAME = T2.TABLESPACE_NAME
  8     );

311 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=12 Card=668 Bytes=62124)
   1    0   HASH JOIN (SEMI) (Cost=12 Card=668 Bytes=62124)
   2    1     HASH JOIN (Cost=9 Card=668 Bytes=39412)
   3    2       TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668 Bytes=21376)
   4    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806 Bytes=102762)
   5    1     TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=340 Bytes=11560)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         93  consistent gets
          0  physical reads
          0  redo size
       8843  bytes sent via SQL*Net to client
        723  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        311  rows processed

SQL> SELECT DISTINCT T1.OBJECT_NAME, T1.OBJECT_TYPE, T2.TABLESPACE_NAME
  2  FROM T1, T2
  3  WHERE T1.OBJECT_NAME = T2.SEGMENT_NAME
  4  AND T1.OBJECT_NAME IN 
  5     (
  6             SELECT INDEX_NAME FROM T3
  7             WHERE T3.TABLESPACE_NAME = T2.TABLESPACE_NAME
  8     );

311 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=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=340 Bytes=11560)
   5    3         TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=668 Bytes=21376)
   6    2       TABLE ACCESS (FULL) OF 'T1' (Cost=6 Card=3806 Bytes=102762)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         72  consistent gets
          0  physical reads
          0  redo size
       8843  bytes sent via SQL*Net to client
        723  bytes received via SQL*Net from client
         22  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        311  rows processed

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

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

而添加了DISTINCT之后,Oracle知道最终肯定要进行排序去重的操作,因此在连接的时候就选择了HASH JOIN作为了连接方式。这就是为什么加上了DISTINCT之后,逻辑读反而减少了。但是同时,加上了DISTINCT之后,语句增加了一个排序操作,而在不加DISTINCT的时候,是没有这个操作的。

当连接的表数据量很大,但是SELECT的最终结果不是很多,且SELECT列的个数不是很多的时候,加上DISTINCT之后,这个排序的代价要小于SEMI JOIN连接的代价。这就是增加一个DISTINCT操作查询效率反而提高,这个似乎不可能发生的情况的真正原因。

最后需要说明一下,这篇文章意在说明,优化的时候没有什么东西是一成不变的,几乎任何事情都有可能发生,不要被一些所谓死规则限制住。明白了这一点就可以了。这篇文章并不是打算提供一种优化SQL的方法,严格意义上将,加上DISTINCT和不加DISTINCT是两个完全不同的SQL语句。虽然在这个例子中,二者是等价的,但是这是表结构、约束条件和数据本身共同限制的结果。换了另一个环境,这两个SQL得到的结果可能会相去甚远,所以,不要试图将本文的例子作为优化时的一种方法。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69132/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10455842