怎么增加Distinct提高查询效率

109次阅读

共计 3887 个字符，预计需要花费 10 分钟才能阅读完成。

自动写代码机器人，免费开通

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

只有增加 DISTINCT 关键字，Oracle 必然需要对后面的所有字段进行排序。以前也经常发现由于开发人员对 SQL 不是很理解，在 SELECT 列表的 20 多个字段前面添加了 DISTINCT，造成查询基本上不可能执行完成，甚至产生 ORA-7445 错误。所以一直向开发人员强调 DISTINCT 给性能带来的影响。下面丸趣 TV 丸趣 TV 小编来讲解下如何增加 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 提高查询效率”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。

向 AI 问一下细节

正文完