Group by VS. Distinct

Mucho se habla sobre la diferencia entre group by y Distinct, la verdad es que en oracle parece no tener diferencia.

Hice algunas pruebas para poder decir que son prácticamente lo mismo.

Empecé con los siguientes queries:

SQL> explain plan for 
  2  SELECT DISTINCT campo1 FROM prueba;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

Plan hash value: 643035693

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |     5 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE       |        |     1 |     5 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PRUEBA |     1 |     5 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


SQL> explain plan for
  2  SELECT campo1 FROM prueba GROUP BY campo1;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

Plan hash value: 287650557

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |     5 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |        |     1 |     5 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PRUEBA |     1 |     5 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Como se puede ver lo único que cambia es el "HASH UNIQUE" por "HASH GROUP", el resto del plan parece ser igual.

Al ser una función hash, decidí incrementar la prueba a un set de datos más grande (suficiente para que mi PGA se quedara corta y se tuviera que pasar la tabla de hash a disco). Decidí también poner un trace nivel 10104 al proceso para ver la creación de la tabla de hash.

Las sentencias SQL son las siguientes:

WITH registros AS
(SELECT /*+MATERIALIZE*/ 
     owner,
     object_type
   FROM dba_objects)
SELECT COUNT(*)
FROM
  (SELECT owner,
     object_type || rownum
     FROM
      (SELECT a.owner,
              b.object_type
       FROM registros a,
            registros b
       WHERE rownum < 1000000)
   GROUP BY owner,
     object_type || rownum);


WITH registros AS
  (SELECT /*+MATERIALIZE*/
     owner,
     object_type
   FROM dba_objects)
SELECT COUNT(*)
FROM
  (SELECT DISTINCT owner,
     object_type || rownum
   FROM
    (SELECT a.owner,
       b.object_type
     FROM registros a,
       registros b
     WHERE rownum < 1000000))
;

Las pruebas que realicé fueron las siguientes:

SQL> oradebug setmypid
Sentencia procesada.
SQL> oradebug event 10104 trace name context forever, level 12;
Sentencia procesada.
SQL> WITH registros AS
  2    (SELECT /*+MATERIALIZE*/
...
 15       WHERE rownum < 1000000));

  COUNT(*)
----------
    999999

SQL> oradebug tracefile_name
c:\oracle\product\admin\orcl\udump\orcl_ora_5272.trc

En ambas situaciones, las tablas de hash fueron exactamente las mismas...

*** RowSrcId: 6 HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Total number of partitions: 8
Number of partitions which could fit in memory: 8
Number of partitions left in memory: 8
Total number of slots in in-memory partitions: 8
Total number of rows in in-memory partitions: 69
   (used as preliminary number of buckets in hash table)
Estimated max # of build rows that can fit in avail memory: 81720
*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Requested size of hash table: 16
Actual size of hash table: 16
Number of buckets: 128
Match bit vector allocated: FALSE
kxhfResize(enter): resize to 14 slots (numAlloc=8, max=12)
kxhfResize(exit): resized to 14 slots (numAlloc=8, max=14)
  freeze work area size to: 2321K (14 slots)
### Hash table overall statistics ###
Total buckets: 128 Empty buckets: 73 Non-empty buckets: 55
Total number of rows: 69
Maximum number of rows in a bucket: 3
Average number of rows in non-empty buckets: 1.254545

En ambas ocasiones, la tabla de hash fue exactamente la misma, con el mismo número de operaciones. A nivel SQL trace, las ejecuciones también fueron similares:

Group By

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.31       0.38          0       5395        143           0
Fetch        2      2.89       4.07       2615        144          1           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.20       4.45       2615       5539        144           1

Distinct

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.28       0.33          0       5395        143           0
Fetch        2      2.92       4.01       2615        144          1           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.20       4.34       2615       5539        144           1

Intenté hacer algunas pruebas para ver si alguno era más eficiente que otros en Joins y no encontré ninguna diferencia.

Después de muchas pruebas sólo encontré una diferencia...

SQL> select sql_text,
  2    sharable_mem
  3  FROM v$sql
  4  WHERE sql_text LIKE 'SELECT%campo1%prueba%';

SQL_TEXT                                  SHARABLE_MEM
----------------------------------------- ------------
SELECT DISTINCT campo1 FROM prueba                8535
SELECT campo1 FROM prueba GROUP BY campo1         8542

La sentencia sql ocupa más caracteres en el group by, por lo mismo ocupa más memoria dentro del shared_pool.

Ya que esto es insignificante, se puede decir que el "group by" y el "disticnt" son similares.