|
只有增加DISTINCT关键字Oracle必然需要对后面的所有字段进行排序以前也经常发现由于开发人员对SQL不是很理解在SELECT列表的多个字段前面添加了DISTINCT造成查询基本上不可能执行完成甚至产生ORA错误所以一直向开发人员强调DISTINCT给性能带来的影响 没想到开发人员在测试一条大的SQL的时候告诉我如果加上了DISTINCT则查询大概需要分钟左右可以执行完如果不加DISTINCT则查询执行了多分钟仍然得不到结果 首先想到的是可能DISTINCT是在子查询中由于加上了DISTINCT将第一步结果集缩小了导致查询性能提高结果一看SQL发现DISTINCT居然是在查询的最外层 由于原始SQL太长而且牵扯的表太多很难说清楚这里模拟了一个例子这个例子由于数据量和SQL的复杂程度限制无法看出二者执行时间上的明显差别这里从两种情况的逻辑读对比来说明问题 首先建立模拟环境 SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS WHERE OWNER = SYS AND OBJECT_TYPE NOT LIKE %BODY AND OBJECT_TYPE NOT LIKE JAVA%; Table created SQL> CREATE TABLE T AS SELECT * FROM DBA_SEGMENTS WHERE OWNER = SYS; Table created SQL> CREATE TABLE T AS SELECT * FROM DBA_INDEXES WHERE OWNER = SYS; Table created SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (OBJECT_NAME); Table altered SQL> CREATE INDEX IND_T_SEGNAME ON T(SEGMENT_NAME); Index created SQL> CREATE INDEX IND_T_TABNAME ON T(TABLE_NAME); Index created SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T METHOD_OPT => FOR ALL INDEXED COLUMNS SIZE CASCADE => TRUE) PL/SQL procedure successfully completed SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T METHOD_OPT => FOR ALL INDEXED COLUMNS SIZE CASCADE => TRUE) PL/SQL procedure successfully completed SQL> EXEC DBMS_STATSGATHER_TABLE_STATS(USER T METHOD_OPT => FOR ALL INDEXED COLUMNS SIZE CASCADE => TRUE) PL/SQL procedure successfully completed 仍然沿用上面例子中的结构看看原始SQL和增加DISTINCT后的差别 SQL> SET AUTOT TRACE SQL> SELECT TOBJECT_NAME TOBJECT_TYPE TTABLESPACE_NAME FROM T T WHERE TOBJECT_NAME = TSEGMENT_NAME AND TOBJECT_NAME IN ( SELECT INDEX_NAME FROM T WHERE TTABLESPACE_NAME = TTABLESPACE_NAME ); rows selected Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) HASH JOIN (SEMI) (Cost= Card= Bytes=) HASH JOIN (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=) Statistics
recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed SQL> SELECT DISTINCT TOBJECT_NAME TOBJECT_TYPE TTABLESPACE_NAME FROM T T WHERE TOBJECT_NAME = TSEGMENT_NAME AND TOBJECT_NAME IN ( SELECT INDEX_NAME FROM T WHERE TTABLESPACE_NAME = TTABLESPACE_NAME ); rows selected Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) SORT (UNIQUE) (Cost= Card= Bytes=) HASH JOIN (Cost= Card= Bytes=) HASH JOIN (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T (Cost= Card= Bytes=) Statistics
recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed 从统计信息可以看出添加了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得到的结果可能会相去甚远所以不要试图将本文的例子作为优化时的一种方法 |
