• Artículo

Planes de ejecución para GROUPING SETS, ROLLUP y CUBE

En este tema se describen los planes de ejecución de consulta para consultas que tengan varias agrupaciones en una sola cláusula GROUP BY. Los planes descritos los generan los siguientes tipos de instrucciones GROUP BY:

  • GROUP BY...WITH ROLLUP

  • GROUP BY...WITH CUBE

  • GROUP BY..., GROUPING SETS (...), ...

  • GROUP BY..., CUBE**(...),** ...

  • GROUP BY..., ROLLUP**(...),** ...

Agregados en planes de ejecución de GROUP BY

Cuando está presente una cláusula GROUP BY, los agregados complejos presentes se descomponen en agregados simples al principio de la fase de optimización, independientemente del tipo de cláusula GROUP BY. Las siguientes instrucciones Transact-SQLproporcionan una ilustración simplificada de la transformación que se produce internamente. En el siguiente ejemplo, la primera instrucción SELECT se transforma en el segundo:

SELECT a, AVG(x) FROM t GROUP BY a WITH ROLLUP;
SELECT a, SUM(x)/COUNT(x) FROM t GROUP BY a WITH ROLLUP;

Los agregados escalares no se pueden expresar mediante sintaxis de varias agrupaciones. La sintaxis de varias agrupaciones se puede usar para especificar un conjunto de agrupaciones único. Las siguientes instrucciones son iguales:

SELECT a, b, c, SUM(x) 
FROM v53
GROUP BY GROUPING SETS ((a, b, c));
 
SELECT a, b, c, SUM(x) 
FROM v53
GROUP BY a, b, c;

Los planes de ejecución de las siguientes instrucciones serán iguales que los planes de ejecución de GROUP BY, excepto que los agregados escalares no se puede expresar utilizando sintaxis de varias agrupaciones.

La instrucción siguiente, un agregado escalar, devuelve una fila que tiene el valor NULL:

SELECT SUM(x) 
FROM v53 
WHERE a = a + 1 ;

Las instrucciones siguientes no devuelven ninguna fila:

SELECT SUM(x) 
FROM v53 
WHERE a = a + 1 
GROUP BY GROUPING SETS (());

SELECT SUM(x) 
FROM v53 
WHERE a = a + 1
GROUP BY(x-x);

Planes de ejecución de ROLLUP

En las versiones de SQL Server anteriores a SQL Server 2008, el operador ROLLUP es indistinguible de los operadores Stream Aggregate normales del plan de presentación. A partir de SQL Server 2008, RollupInfo se agrega al nodo Stream Aggregate en Showplan como un elemento distintivo. <RollupInfo> aparece en XML Showplan y en la ventana de propiedades, pero no al pasar el puntero sobre el operador. Un operador ROLLUP tiene un elemento denominado <RollupInfo>, por ejemplo:

<RollupInfo HighestLevel="3">

<ROLLUPLevel Number="0" />

<ROLLUPLevel Number="2" />

<ROLLUPLevel Number="3" />

</RollupInfo>

El elemento <RollupInfo> estará presente en el plan de presentación XML siempre que el operador Stream Aggregate tenga un valor de tipo ROLLUP. Es decir, genere varias agrupaciones. El texto del plan de presentación no sufre modificaciones.

Consulta de ejemplo y plan de ejecución para ROLLUP

La siguiente consulta del ejemplo, Q1, devuelve las ventas agregadas para Adventure Works Cycles durante el año 2004. Las ventas están agrupadas por región y día. Para generar un plan de ejecución real, en el Editor de consultas de SQL Server Management Studio, ejecute la siguiente consulta. El plan se describe en la sección "Plan de ejecución real para la consulta Q1" a continuación.

USE AdventureWorksDW;
GO
WITH v1 AS
(
SELECT
    t.SalesTerritoryRegion theregion
    ,d.WeekNumberOfYear theweek
    ,d.DayNumberOfYear theday
    ,f.SalesAmount sales
FROM  dbo.FactInternetSales AS f
    INNER JOIN dbo.DimTime AS d 
        ON f.OrderDateKey = d.TimeKey
    INNER JOIN dbo.DimSalesTerritory AS t 
        ON f.SalesTerritoryKey = t.SalesTerritoryKey
WHERE d.calendaryear = 2006
)
SELECT theregion, theweek, theday, SUM(sales) AS sumsales
FROM v1
GROUP BY GROUPING SETS ((), (theregion), (theregion, theweek, theday))
OPTION (MAXDOP 1);
GO

Plan de ejecución real para la consulta Q1

La consulta Q1 se puede satisfacer con una única operación ROLLUP. Sin embargo, las consultas GROUPING SETS frecuentemente requieren varias operaciones ROLLUP. (Los resultados de estas operaciones se combinan mediante UNION).

El plan de ejecución gráfico para Q1 tiene dos operadores Stream Aggregate en una fila. El operador Stream Aggregate de la izquierda es un operador ROLLUP y tiene el elemento <RollupInfo>. La información del elemento <RollupInfo> describe totalmente el comportamiento de la operación ROLLUP. Hay tres elementos de agrupación en el operador Stream Aggregate que se enumeran en el elemento GroupBy; por consiguiente, HighestLevel = 3. Existen cuatro posibles ROLLUPLevels: del 0 al HighestLevel. En este ejemplo, el nivel más alto es 3. El operador ROLLUP de nivel cero es la agrupación base, (theregion, theweek, theday). Esto es lo que generaría un operador Stream Aggregate típico en esta posición del plan. Cada nivel más alto (nivel L) es una agrupación en el prefijo de la lista GROUP BY de tamaño HighestLevel-L. El nivel 1 representa (theregion, theweek) y no es necesario en este caso. Los niveles 2 y 3 indican aquí agrupaciones de salida (theregion) y (), respectivamente.

Planes de ROLLUP único

El plan de ejecución anterior para la consulta Q1 es típico para una operación ROLLUP única:

  1. Existe una consulta de entrada que corresponde a las cláusulas FROM y WHERE.

  2. Existe un operador Stream Aggregate que calcula GROUP BY a, b, c.

    1. Este agregado puede ser local o global y paralelo con particiones.

    2. Este agregado puede estar precedido por una ordenación.

    3. Este agregado puede hacerse coincidir con una vista indizada.

  3. Stream Aggregate.

  4. A continuación, el procesador de consultas filtra y calcula los escalares para la cláusula HAVING y la lista <select> de la cláusula SELECT.

Se puede obtener un plan ROLLUP único de las siguientes formas:

  • Use la palabra clave ROLLUP:

    SELECT a, b, c, SUM(x) 
FROM v53
GROUP BY ROLLUP(a, b, c);

SELECT a, b, c, SUM(x) 
FROM v53
GROUP BY a, b, c WITH ROLLUP;

  • Use otra sintaxis de varias agrupaciones para especificar una operación ROLLUP o parte de una operación ROLLUP, con dos o más agrupaciones. SQL Server analiza las agrupaciones para ver si están cubiertas por una sola operación ROLLUP, es decir, si forman una cadena de conjuntos contenedores.

    SELECT a, b, c, SUM(x) 
FROM v53
GROUP BY GROUPING SETS ((), (a), (a, b, c));

    Un cubo de una sola dimensión es una operación ROLLUP:

    SELECT a, SUM(x) 
FROM v53
GROUP BY a WITH CUBE;

Agregar primero y después ROLLUP

En SQL Server, un operador ROLLUP se implementa siempre como operador independiente, además de un GROUP BY ordinario. Esto no es estrictamente necesario. Considere el siguiente ejemplo de código.

SELECT MAX(x) z1, COUNT(x) z2 
FROM v53
GROUP BY a, b WITH ROLLUP;

Esta consulta se procesa en dos pasos. Observe que un max de varios max de agregación base es un max, y un sum de varios count de agregación base es un count:

'base aggregation':   compute y1 = max(x), y2 = count(x)
Rollup:   compute z1 = max(y1), z2 = sum(y2)

La ventaja de dividir siempre la agregación base es que se puede hacer coincidir con una vista indizada, paralelizarse o forzarse a realizar una combinación.

Planes de ROLLUP múltiples

Si las consultas GROUPING SETS no están cubiertas por un operador ROLLUP, SQL Server compilará un conjunto de operadores ROLLUP prácticamente mínimo que, cuando el conjunto se combine mediante UNION ALL, cubra las consultas GROUPING SETS. La consulta siguiente requiere tres operaciones ROLLUP para cubrir las consultas GROUPING SETS:

SELECT a, b, c, SUM(x) 
FROM v53
GROUP BYGROUPING SETS ((), (a), (b), (c), (a, b), (a, c), (b,c));

A continuación, se muestra una posible combinación de operaciones ROLLUP:

ROLLUP 1 covers (), (a), (a, b)
ROLLUP 2 covers (c), (a, c)
ROLLUP 3 covers (b), (b, c)

Plan de ROLLUP único

Como se describió anteriormente en la sección "Plan de ejecución real para la consulta Q1", un plan de ROLLUP único tiene dos operadores agregados en secuencia. El agregado stream o hash de la derecha es la agregación base. El segundo agregado stream de la izquierda calcula el operador ROLLUP.

Planes de ejecución para CUBE

En esta sección se proporciona el código muestra que puede ejecutarse para generar planes de ejecución reales de ejemplo para el operador CUBE.

Plan de consulta y ejecución de ejemplo para CUBE

Para generar un plan de ejecución real, ejecute la siguiente consulta Q2 desde el editor de consultas en Management Studio. 

USE AdventureWorksDW2008R2;
GO
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
    ,SUM (SalesAmount)
FROM dbo.FactInternetSales
GROUP BY CUBE (ProductKey, OrderDateKey, DueDateKey, ShipDateKey);
GO

Plan de ejecución real para la consulta Q2

La primera línea del plan de ejecución real contiene la entrada. La entrada se ordena y se pasa a la agregación base. Se pone en cola y la leen todas las bifurcaciones. Cada bifurcación calcula una operación ROLLUP precedida por una ordenación opcional.

Esta consulta contiene una columna más que la consulta Q3 que se muestra a continuación. Es más rentable poner en cola el agregado base que releer la entrada en cada bifurcación. Las consultas con más bifurcaciones favorecerán los planes de los agregados base.

Plan de consulta y ejecución para CUBE sin agregación base ni spool

Para generar un plan de ejecución real, ejecute la siguiente consulta Q3 desde el editor de consultas en Management Studio.

USE AdventureWorksDW2008R2;
GO
SELECT ProductKey, OrderDateKey, DueDateKey, SUM (SalesAmount) 
FROM dbo.FactInternetSales
GROUP BY CUBE (ProductKey, OrderDateKey, DueDateKey);
GO

Plan de ejecución real para la consulta Q3

Esta consulta crea un estilo diferente de plan de varias agrupaciones que es básicamente la solución UNION ALL. En este plan, no hay ninguna agregación base ni spool. Los conjuntos de agrupación están divididos en varios planes ROLLUP concatenados.

Plan de consulta y ejecución de ejemplo para un operador CUBE naive

Para generar un plan de ejecución real, ejecute la siguiente consulta Q4 desde el editor de consultas en Management Studio.

USE AdventureWorksDW2008R2;
GO
SELECT ProductKey, OrderDateKey, DueDateKey, SUM (DISTINCT SalesAmount) 
FROM dbo.FactInternetSales
GROUP BY CUBE (ProductKey, OrderDateKey, DueDateKey);
GO

Plan de ejecución real para la consulta Q4

Esta consulta genera un plan de ejecución real del operadorCUBE naive que tiene varias agrupaciones y cualquier agregado Distinct.

Cada bifurcación calcula una agrupación individual. No hay ninguna agregación base. Este plan no es muy eficaz. Si hay varios agregados en la lista <select> de SELECT, algunos de los cuales son Distinct, cada bifurcación puede tener una combinación y un spool. Esto es similar a una agrupación única sobre agregados Distinct.