oracle中distinct和group by的区别

最新推荐文章于 2024-01-24 10:08:03 发布
最新推荐文章于 2024-01-24 10:08:03 发布
阅读量5k 收藏 4
点赞数
分类专栏： oracle

其实二者没有什么可比性，但是对于不包含聚集函数的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的性能差异原因。

客 人
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle中使用group by优化distinct
09-10
主要介绍了oracle中使用group by优化distinct的相关资料,需要的朋友可以参考下
数据库去重，group by、distinct、rowid的用法，oracle和mysql如何数据去重保留一条
凌晨三点不下班的博客
06-04 2512
数据库去重，group by、distinct、rowid的用法，oracle和mysql如何数据去重保留一条
group by 和 distinct 去重比较
weixin_30532973的博客
09-14 278
distinct方式就是两两对比，需要遍历整个表。group by分组类似先建立索引再查索引，所以两者对比，小表destinct快，不用建索引。大表group by快。一般来说小表就算建索引，也不会慢到哪去，但是如果是TB级大表，遍历简直就是灾难。所以很多ORACLE项目都禁止使用distinct语句，全部要求替换成group by。 转载于:https://www.cnblogs.com/wue...
distinct和group by的功能、使用和底层原理
最新发布
weixin_64752717的博客
01-24 1870
紧凑索引扫描（Tight Index Scan）是指 MySQL 在使用索引进行查询时，如果索引中的数据连续，MySQL 将会按照顺序读取索引数据块，直到找到符合条件的记录。松散索引扫描（Loose Index Scan）是指 MySQL 在使用索引进行查询时，如果索引中的数据不连续，MySQL 将会扫描整个索引树，直到找到符合条件的记录。但是，如果使用的是 MyISAM 存储引擎，或者查询条件中包含了不等于或不包含操作符，MySQL 将会使用松散索引扫描，这时候就需要注意查询的效率。
Oracle中distinct和group by去重性能的比较
Ray Song 的 blog
05-29 7478
之前去重都是使用distinct，后来在项目上优化SQL的时候发现我司有个开发人员喜欢使用group by来去重，他的解释是group by性能好，那么实际情况到底是怎样的呢？我在测试环境下进行了模拟，毕竟，实践是检验真理的唯一标准嘛。 首先创建测试表，使用了dba_objects视图中的数据 -- 创建测试用表obj create table obj as select * from dba_objects; alter session set statistics_level=all; -- 执行两
Distinct 和Group by的区别
Chelin Tsien的专栏
04-17 1707
 其实二者没有什么可比性，但是对于不包含聚集函数的GROUP BY操作来说，和DISTINCT操作是等价的。不过虽然二者的结果是一样的，但是二者的执行计划并不相同。在Oracle9i中：SQL> SELECT * FROM V$VERSION;BANNER--------------------------------------------------------------
oracle之优化一用group by或exists优化distinct
极客神殿
12-30 6223
今天mentor给了一个sql语句优化的任务。（环境是sql developer）有一个语句执行很慢，查询出来的结果有17544条记录，但需970秒，速度很慢。语句是这样的： SELECT DISTINCT 'AMEND_NEW', reporttitle, reportsubtitle, ...
oracle sql 去重复记录不用distinct如何实现
01-19
select distinct id,name from t1 可以取多个字段，但只能消除这2个字段值全部相同的记录 所以用distinct达不到想要的效果，用group by 可以解决这个问题。 例如要显示的字段为A、B、C三个，而A字段的内容不能重复...
ORACLE查询删除重复记录三种方法
09-10
本文列举了3种删除重复记录的方法，分别是rowid、group by和distinct，小伙伴们可以参考一下。
Oracle 查找与删除表中重复记录的步骤方法
12-16
方法：group by XX having count(*)>1,rowid,distinct,temporary table,procedure 1、查询表中的重复数据a.重复一个字段 b.重复多个字段 c.重复一整行 创建测试表： 代码如下:create table cfa (businessid number,...
oracle优化distinct,oracle中使用group by优化distinct
weixin_35545135的博客
04-04 1129
今天mentor给了一个sql语句优化的任务。(环境是sql developer)有一个语句执行很慢，查询出来的结果有17544条记录，但需970秒，速度很慢。语句是这样的：SELECT DISTINCT 'AMEND_NEW',reporttitle,reportsubtitle,cab_cab_transactions.branchcode,cab_cab_transactions.prtf...
oracle在distinct代表的意义,关于distinct和group by的效率问题
weixin_31554959的博客
04-06 1600
一直以来都有这么个疑问，关于distinct和group by的效率问题比如下面这个2个语句：select distinct type from BBTEST;和select type from BBTEST group by type两个语句的结果是一样的，就是取出表中的type值，去处重复记录。但是这两者的执行效率如何呢，是否有差异？在10gR2以前，distinct走的是SORT UNIQU...
distinct 、group by 的区别
热门推荐
qq_36061501的博客
05-13 1万+
select distinct(university) from user_profile; select university from user_profile group by university;二者返回的结果式样的。但是既然一样，为什么又要做两个函数呢？来看看它们的区别 ———————————————— distinct 支持单列、多列的去重 单列去重的方式简明易懂，即相同值只保留1个；多列的去重则是根据指定的去重的列信息来进行，即只有所有指定的列信息都相同，才会被认为是重复的信息。 dis
oracle sql select distinct,SQL性能：SELECT DISTINCT与GROUP BY
weixin_39904809的博客
04-04 596
我一直在尝试改进现有的Oracle数据库驱动的应用程序的查询时间，该应用程序运行有点迟钝。应用程序执行几个大型查询，例如下面的一个，可能需要一个小时才能运行。在查询中使用GROUP BY子句替换DISTINCT将执行时间从100分钟缩短到10秒。我的理解是，SELECT DISTINCT和GROUP BY以相同的方式运行。为什么执行时间之间如此巨大的差距？在后端如何执行查询有什么区别？有没有一种情...
面试突击63：distinct 和 group by有什么区别？
磊哥聊Java
07-07 5643
作者 | 磊哥来源 | Java面试真题解析（ID：aimianshi666）转载请联系授权（微信ID：GG_Stone）在 MySQL 中，最常见的去重方法有两个：使用 distinct 或使用 group by，那它们有什么区别呢？接下来我们一起来看。1.创建测试数据--创建测试表 droptableifexistspageview; createtabl...
MySQL去重中 distinct 和 group by 的区别
qq_654603797
01-06 7058
开题：SQL中的 group by 和 distinct 了解吗，简单介绍一下？ group by 是按照指定的规则对数据进行分组，所谓的分组就是将一个数据划分成若干个组，然后针对若干个组进行数据处理。 distinct 是一个关键字，常用于select之后，用于取出去重之后的值。 所以，group by 和 distinct 都可以用于去重。那有什么区别呢？ 测试数据： CREATE TABLE student ( stu_no VARCHAR(40) NOT NULL, n.
group by与distinct有何区别
༺ bestcxx的专栏 ༻
06-14 2824
select name from rate where channelname is not null group by name; select distinct name from rate
distinct 和 group by 的区别
贾廷帅的博客
09-03 3932
前言 我们在sql里面经常看到 distinct 和 group by,那么两者到底有何区别呢？下面就来一探究竟。 首先来看官方描述 链接: Mysql distinct 传送门 其中有这么一段描述： Generally speaking, the DISTINCT clause is a special case of the GROUP BY clause. The difference between DISTINCT clause and GROUP BY clause is that the G
oracle distinct和groupby哪个效率高
06-08
在 Oracle 中，使用 `DISTINCT` 和 `GROUP BY` 都可以实现去重的功能，但是它们的实现方式是不同的，因此它们的效率也是不同的。 如果只是简单的去重，那么使用 `DISTINCT` 的效率会更高一些，因为 `DISTINCT` 可以...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值