DISTINCT和GROUP BY的差异ITeye - 超凡娱乐

DISTINCT和GROUP BY的差异ITeye

2019年02月28日15时19分28秒 | 作者: 昊嘉 | 标签: 差异,计划,索引 | 浏览: 2866

有时分进行分组查询的时分 能够用DISTINCT和GROUP BY 、所以不免需求比较一下、
搜了一下 感觉这个比较威望 记一下

http://yangtingkun.itpub.net/post/468/227628
-
其实二者没有什么可比性,可是关于不包括集合函数的GROUP BY操作来说,和DISTINCT操作是等价的。不过尽管二者的结果是相同的,可是二者的履行计划并不相同。


在Oracle9i中:

SQL SELECT * FROM V$VERSION;

BANNER

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创立。

SQL CREATE INDEX IND_T_CREATED ON T (CREATED);

索引已创立。

SQL ALTER TABLE T MODIFY CREATED NOT NULL;

表已更改。

SQL ALTER SESSION SET NLS_DATE_FORMAT = YYYY-MM-DD HH24:MI:SS;

会话已更改。

SQL EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, T)

PL/SQL 进程已成功完结。

SQL SET AUTOT ON EXP
SQL SELECT COUNT(*) FROM (SELECT DISTINCT CREATED FROM T);

COUNT(*)

4794

履行计划

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=65 Card=4794)
3 2 SORT (UNIQUE) (Cost=65 Card=4794 Bytes=38352)
4 3 INDEX (FAST FULL SCAN) OF IND_T_CREATED (NON-UNIQUE) (Cost=4 Card=41802 Bytes=334416)

SQL SELECT COUNT(*) FROM (SELECT CREATED FROM T GROUP BY CREATED);

COUNT(*)

4794

履行计划

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=65 Card=4794 Bytes=9588)
3 2 SORT (GROUP BY) (Cost=65 Card=4794 Bytes=38352)
4 3 INDEX (FAST FULL SCAN) OF IND_T_CREATED (NON-UNIQUE) (Cost=4 Card=41802 Bytes=334416)

从履行计划上看,DISTINCT的操作是SORT (UNIQUE),而GROUP BY是SORT (GROUP BY)。DISTINCT操作只需求找出一切不同的值就能够了。而GROUP BY操作还要为其他集合函数进行准备作业。从这一点大将,GROUP BY操作做的作业应该比DISTINCT所做的作业要多一些。

除了这一点,基本上看不到DISTINCT和GROUP BY(没有集合函数的状况)有什么区别,并且从履行功率上也看不到显着的差异。

不过从10g开端,二者的差异开端体现出来了。

SQL CONN YANGTK/YANGTK@YTK已衔接。
SQL SET AUTOT OFF
SQL SET TIMING OFF
SQL CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创立。

SQL CREATE INDEX IND_T_CREATED ON T (CREATED);

索引已创立。

SQL ALTER TABLE T MODIFY CREATED NOT NULL;

表已更改。

SQL ALTER SESSION SET NLS_DATE_FORMAT = YYYY-MM-DD HH24:MI:SS;

会话已更改。

SQL EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, T)

PL/SQL 进程已成功完结。

SQL SET AUTOT ON
SQL SET TIMING ON

建立好测验环境后,看一看规范分页函数中,两个操作的差异:

SQL SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT CREATED
8 FROM T
9 GROUP BY CREATED
10 ) A
11 WHERE ROWNUM 20
12 )
13 WHERE RN = 10;

RN CREATED
-
10 2005-12-19 17:07:57
11 2005-12-19 17:07:58
12 2005-12-19 17:08:24
13 2005-12-19 17:08:25
14 2005-12-19 17:08:26
15 2005-12-19 17:08:27
16 2005-12-19 17:08:28
17 2005-12-19 17:08:29
18 2005-12-19 17:08:33
19 2005-12-19 17:08:35

已挑选10行。

已用时刻: 00: 00: 00.06

履行计划

Plan hash value: 3639065582

-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-
| 0 | SELECT STATEMENT | | 19 | 418 | 1 (0)|
|* 1 | VIEW | | 19 | 418 | 1 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 969 | 8721 | 1 (0)|
|* 4 | SORT GROUP BY STOPKEY| | 969 | 7752 | 1 (0)|
| 5 | INDEX FULL SCAN | IND_T_CREATED | 969 | 7752 | 1 (0)|
-

Predicate Information (identified by operation id):
-

1 - filter("RN" =10)
2 - filter(ROWNUM 20)
4 - filter(ROWNUM 20)

计算信息

1 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
642 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

SQL SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT DISTINCT CREATED
8 FROM T
9 ) A
10 WHERE ROWNUM 20
11 )
12 WHERE RN = 10;

RN CREATED
-
10 2005-12-19 17:07:57
11 2005-12-19 17:07:58
12 2005-12-19 17:08:24
13 2005-12-19 17:08:25
14 2005-12-19 17:08:26
15 2005-12-19 17:08:27
16 2005-12-19 17:08:28
17 2005-12-19 17:08:29
18 2005-12-19 17:08:33
19 2005-12-19 17:08:35

已挑选10行。

已用时刻: 00: 00: 00.03

履行计划

Plan hash value: 1650124153

-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-
| 0 | SELECT STATEMENT | | 19 | 418 | 14 (36)|
|* 1 | VIEW | | 19 | 418 | 14 (36)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 987 | 8883 | 14 (36)|
|* 4 | SORT GROUP BY STOPKEY| | 987 | 7896 | 14 (36)|
| 5 | INDEX FAST FULL SCAN| IND_T_CREATED | 50333 | 393K| 10 (10)|
-

Predicate Information (identified by operation id):
-

1 - filter("RN" =10)
2 - filter(ROWNUM 20)
4 - filter(ROWNUM 20)

计算信息

1 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
642 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

出人意料的是,GROUP BY操作的COST更低,并且逻辑读也小,这好像与二者的作业量成反比。仔细调查履行计划发现,问题的本源来自于GROUP BY运用INDEX FULL SCAN,而DISTINCT运用了INDEX FAST FULL SCAN。或许有人会感到古怪,索引的快速全扫描不是要比索引全扫描功率更高吗?关于读取一切数据的状况下,确实是索引快速全扫功率更高。可是因为这儿采用了分页,只取前20条数据,并且Oracle的10g增加了GROUP BY STOPKEY这种新的履行途径,因而在这儿GROUP BY操作的功率更高。

调查履行计划中的处理行数能够发现,索引全扫描因为是依照索引的次序扫描,因而利用了STOPKEY,只是处理了969条记载就停了下来。而关于DISTINCT操作的快速索引全速而言,明显没有运用STOPKEY,读取了一切的50333条记载。这就是GROUP BY和DISTINCT的功能差异原因。

-
相对而言  我仍是比较支持用GROUP BY
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表超凡娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章