Las bases de datos SQL usan dos algoritmos totalmente distintos para group by
. El primero, un algoritmo
de hash que agrega los registros de entrada en una tabla hash temporal. Una vez que todos los registros de entrada están
procesados, es la tabla hash la que en esencia se entrega como resultado. El segundo, el algoritmo ordena/agrupa, que primero ordena
todas los datos de entrada vía la clave de agrupación, de forma que todas las filas de cada grupo están a continuación de las otras, de forma encadenada.
Después, la base de datos solamente necesita unirlos. En general, ambos algoritmos necesitan materializar un
estado intermedio, así que no se puede ejecutar en pipeline. Sin embargo, el algoritmo ordena/agrupa puede utilizar
un índice para evitar una operación de ordenación, habilitando un group by
en pipeline.
Nota
MySQL 5.7 no utiliza el algoritmo de hash. Sin embargo, la optimización del algoritmo ordena/agrupa funciona como se describe a continuación.
Consideramos la siguiente sentencia: obtener los ingresos del día de ayer agrupados por PRODUCT_ID
:
SELECT product_id, sum(eur_value)
FROM sales
WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
GROUP BY product_id
Como sabemos desde la sección anterior, que existe el índice sobre
SALE_DATE
y PRODUCT_ID
, el algoritmo ordena/agrupa es el más apropiado porque
INDEX RANGE SCAN
entrega automáticamente las filas en el orden requerido. Eso significa que la base de datos
se libra de la materialización porque no necesita una operación de ordenación explícita; group by
se
ejecuta en pipeline.
- Db2 (LUW)
Explain Plan ------------------------------------------------------------ ID | Operation | Rows | Cost 1 | RETURN | | 675 2 | GRPBY (COMPLETE) | 25 of 387 ( 6.46%) | 675 3 | FETCH SALES | 387 of 387 (100.00%) | 675 4 | IXSCAN SALES_DT_PR | 387 of 1009326 ( .04%) | 24 Predicate Information 4 - START (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS)) STOP (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
- Oracle
--------------------------------------------------------------- |Id |Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 |SELECT STATEMENT | | 17 | 192 | | 1 | SORT GROUP BY NOSORT | | 17 | 192 | | 2 | TABLE ACCESS BY INDEX ROWID| SALES | 321 | 192 | |*3 | INDEX RANGE SCAN | SALES_DT_PR | 321 | 3 | ---------------------------------------------------------------
El plan de ejecución de Oracle señala una operación SORT GROUP BY
en pipeline agregando
NOSORT
en el plan. El plan de ejecución de otras bases de datos no mencionan ninguna operación de ordenación.
Group by
en pipeline tiene los mismos prerequisitos que order by
en pipeline, salvo que no existen los modificadores ASC
y DESC
. Eso significa que si se define
un índice con los modificadores ASC
/DESC
, no afectaría la ejecución del group by
en pipeline. Lo mismo ocurre para los NULLS FIRST
/LAST
. Sin embargo, existen bases de datos
que no pueden utilizar correctamente un índice ASC
/DESC
para un group by
en pipeline.
Aviso
PostgreSQL no ejecuta automáticamente group by
en pipeline en el caso que el índice
maneja el valor NULL
como la más baja posible. Agregando la clausura order by
en el orden del índice permite evitar ese problema.
La base de datos Oracle no puede leer un índice hacia atrás con la finalidad de ejecutar un group by
en pipeline que esté seguido por un order by
.
Hay más información disponible en los apéndices respectivos: PostgreSQL, Oracle.
Si se extiende la sentencia para considerar todas las ventas desde ayer, como se hizo en el ejemplo
con order by
en pipeline, se evita un group by
en pipeline por las
mismas razones que antes: INDEX RANGE SCAN
no entrega las filas ordenadas por la clave de agrupación
(comparando con la Figura 6.1).
SELECT product_id, sum(eur_value)
FROM sales
WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
GROUP BY product_id
- Db2 (LUW)
Explain Plan -------------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 12527 2 | GRPBY (FINAL) | 25 of 25 (100.00%) | 12527 3 | TBSCAN | 25 of 25 (100.00%) | 12527 4 | SORT (INTERMEDIATE) | 25 of 25 (100.00%) | 12527 5 | GRPBY (HASHED PARTIAL) | 25 of 8050 ( .31%) | 12527 6 | FETCH SALES | 8050 of 8050 (100.00%) | 12526 7 | RIDSCN | 8050 of 8050 (100.00%) | 375 8 | SORT (UNIQUE) | 8050 of 8050 (100.00%) | 375 9 | IXSCAN SALES_DT_PR | 8050 of 1009326 ( .80%) | 372
El siguiente filtro
where
no se ha utilizado para obtener este resultado:WHERE sale_date >= CURRENT_DATE - 1 MONTH
.Al contrario de lo que sucede con el plan de ejecución de Oracle, este parece bastante complejo. Se debe a dos cosas:
Db2 muestra explícitamente una operación de ordenación de la posición física de almacenamiento entre el acceso al índice y el acceso a la tabla (operaciones 7 y 8).
Db2 lleva a cabo dos fases de agregación: primero se hace una agrupación parcial para reducir la cantidad de datos para ser ordenadas lo antes posible (operación 5) y después se hace un
SORT
+GRPBY
habitual.
- Oracle
--------------------------------------------------------------- |Id |Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 |SELECT STATEMENT | | 24 | 356 | | 1 | HASH GROUP BY | | 24 | 356 | | 2 | TABLE ACCESS BY INDEX ROWID| SALES | 596 | 355 | |*3 | INDEX RANGE SCAN | SALES_DT_PR | 596 | 4 | ---------------------------------------------------------------
En cambio, la base de datos Oracle usa el algoritmo de hash. La ventaja del algoritmo de hash es que solamente necesita almacenar en memoria el resultado agregado, mientras que el algoritmo ordena/agrupa materializa el conjunto completo de entrada. En otras palabras, el algoritmo hash necesita menos memoria.
Como con order by
en pipeline, una ejecución rápida no es el aspecto más importante de la
ejecución del group by
en pipeline. Es más importante que la base de datos lo ejecute de una manera
pipeline y entregue el primer resultado antes de haber leído la entrada entera. Ése es el prerequisito para los
métodos de optimización avanzados que se explican en el siguiente capítulo.
Si te gusta mi manera de explicar, te encantará mi libro.
Piénsalo
¿Debe asumirse que ninguna operación de la base de datos (con excepción de la ordenación y de la agrupación) pueda utilizar un índice para evitar un ordenación?