viernes, 18 de julio de 2008

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.

3 comentarios:

jcastro dijo...

Hola:

Justamente estaba buscando informacion al respecto y NO SON IGUALES.

Supongamos que tenemos

*select funcionpesada(campo1) from tabla1

y devuelve 100 registros, de ello son iguales 1 de cada 10. O sea 10 registros unicos. La funcionpesada es solo un ejemplo, puede ser una funcion que busca en una tabla con varios registros y sin indices.

*con distinct (unique):
select distinct funcionpesada(campo1) from tabla1

la funcionpesada se evalua en cada iteraccion (100 veces) y devuelve los 10 registros.

*con group by:
select funcionpesada(campo1) from tabla1 group by funcionpesada(campo1)

la funcionpesada se evalua en cada iteraccion (100 veces) y devuelve los 10 registros unicos.

*PERO con group by puede hacerse tambien:

select funcionpesada(campo1) from tabla1 group by campo1

y la funcionpesada evalua 10 veces para los 10 registros unicos. O sea es un incremento notable en el rendimiento. En mi caso era entre 15 segundos versus 0.5 segundos. Quizas la diferencia sea la sintaxis pero no creo que es posible realizar la version mas optimizada utilizando distinct. Aunque logicamente esta "optimizacion" significa que la query puede dar diferentes resultado (depende de lo que se busque).

Es decir, group by la gracia es que evalua antes de procesar los valores de regreso.

Hugo E. Contreras Gamiño dijo...

hola Jcastro:

Gracias por el comentario, yo creo que hay mucho por investigar con esto (prometo hacer las pruebas), pero qué pasaría si la función no fuera determinística? es decir, aunque recibiera el mismo valor de entrada, no siempre fuera la misma salida? la función debería de evaluarse siempre.

Hugo E. Contreras Gamiño dijo...

Mis comentarios adicionales en la siguiente entrada.