oracle中distinct和group by的区别

朱雀 2021-06-26 16:06 479阅读 0赞

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