怎么增加Distinct提高查询效率

这篇文章将为大家详细讲解有关怎么增加Distinct提高查询效率，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

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

　　如何增加Distinct提高查询效率

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

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

　　首先建立模拟环境：

　　SQL> CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS

　　2 WHERE OWNER = '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 OWNER = 'SYS';

　　Table created.

　　SQL> CREATE TABLE T3 AS SELECT * FROM DBA_INDEXES WHERE OWNER = '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后的差别：

　　如何增加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 Optimizer=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 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=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

　　从统计信息可以看出，添加了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得到的结果可能会相去甚远，所以，不要试图将本文的例子作为优化时 的一种方法。

关于“怎么增加Distinct提高查询效率”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。