CUBE clause with Having condition could be changed GROUPING SETS clause.

CUBE clause produce the subtotals for all possible combinations of a group of dimensions and grand total. For example, group by CUBE (c1,c2, c3) produce the 7 subtotals and 1 grand total. The total number is 8 (= 2^number of columns in CUBE clause). And, many report queries use CUBE clause to provide various Total(s). But, if the CUBE clause was used with “Having condition”, the performance could be bad. Let’s see the following.

1. Create table and gererate records.

create table t1 (c1 number, c2 number, c3 number, amount number);
 
insert into t1
select mod(level,100), mod(level,1000), mod(level,10000), 100
from   dual
connect by level<=100000;

2. Run statement with CUBE clause.

In this case, The user only want to see the total(s) which are [ {group by (c1,c2,c3), group by (), group by(c2,c3)}. So, we use CUBE and HAVING clause. But, as you can see the trace output, it is not good. Because CUBE clause generates 80,000 rows and then finally filter 20001 rows only. 
SELECT GROUPING( c1 ) ,
       GROUPING( c2 ) ,
       GROUPING( c3 ) ,
       c1 ,
       c2 ,
       c3 ,
       SUM( amount )
FROM   t1
GROUP  BY cube ( c1 , c2 , c3)
HAVING GROUPING(c1) || GROUPING(c2) || GROUPING(c3) = '000'
OR     GROUPING(c1) || GROUPING(c2) || GROUPING(c3) = '111'
OR     GROUPING(c1) || GROUPING(c2) || GROUPING(c3) = '100'
ORDER  BY GROUPING( c1 ) desc,
          GROUPING( c2 ) desc,
          GROUPING( c3 ) desc,
          c1
 
call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.08       0.08          0         78          1          0
Exec         1      0.00       0.00          0          0          0          0
Fetch      801      3.83       3.85          0        334          1      20001
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Total      803      3.91       3.94          0        412          2      20001
Rows       Row Source Operation
---------- ---------------------------------------------------
     20001 SORT ORDER BY (cr=334 pr=0 pw=0 time=3611408 us cost=128 )
     20001  FILTER  (cr=334 pr=0 pw=0 time=52150410 us)
     42101   SORT GROUP BY (cr=334 pr=0 pw=0 time=2121544 us cost=128 )
     80000    GENERATE CUBE (cr=334 pr=0 pw=0 time=1550376 us cost=128 )
     10000     SORT GROUP BY (cr=334 pr=0 pw=0 time=988771 us cost=128 )
    100000      TABLE ACCESS FULL T1 (cr=334 pr=0 pw=0 time=157513 us cost=105 )

3. Run statement with GROUP BY GROUPING SETS clause.
Now, let’s try to change CUBE to GROUPING SETS clause and run. The rule of change is very simple. As you can see on the trace ouput, there is no overhead to generate rows and it’s faster than before.

 
SELECT GROUPING( c1 ) ,
       GROUPING( c2 ) ,
       GROUPING( c3 ) ,
       c1 ,
       c2 ,
       c3 ,
       SUM( amount )
FROM   t1
GROUP  BY GROUPING SETS ((c1,c2,c3),(),(c2,c3))
ORDER  BY GROUPING( c1 ) desc,
          GROUPING( c2 ) desc,
          GROUPING( c3 ) desc,
          c1
 
call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.07       0.07          0         76          1          0
Exec         1      0.00       0.00          0          0          0          0
Fetch      801      1.56       1.49          0        334          1      20001
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Total      803      1.63       1.56          0        410          2      20001
 
Rows       Row Source Operation
---------- ---------------------------------------------------
     20001 SORT ORDER BY (cr=334 pr=0 pw=0 time=1250957 us cost=128)
     20001  SORT GROUP BY ROLLUP (cr=334 pr=0 pw=0 time=927616 us cost=128 )
    100000   TABLE ACCESS FULL T1 (cr=334 pr=0 pw=0 time=132277 us cost=105 )