Summary in SQL - CUBE Features

ROLLUP and CUBE are two new extensions to the GROUP BY clause of the SELECT statement in Oracle8i to calculate the summary of the data being displayed. In this article we discuss CUBE. Prior to 8i, one method of displaying the summary would be to have two SQL statements and join them with a UNION ALL statement. For example, here we find the space used by SYSTEM and SCOTT users and the type of objects, we also find the total space used by each user.

select owner, segment_type, sum(bytes)
  from dba_segments
  where owner in ('SYSTEM', 'SCOTT')
  group by owner, segment_type
  union all
  select owner, null, sum(bytes)
  from dba_segments
  where owner in ('SYSTEM', 'SCOTT')
  group by owner
  order by owner, segment_type
SQL> /

OWNER                          SEGMENT_TYPE       SUM(BYTES)
------------------------------ ------------------ ----------
SCOTT                          INDEX                  131072
SCOTT                          TABLE                  262144
SCOTT                                                 393216
SYSTEM                         INDEX                 1179648
SYSTEM                         LOBINDEX               589824
SYSTEM                         LOBSEGMENT             589824
SYSTEM                         TABLE                 1048576
SYSTEM                                               3407872

8 rows selected.

The same result can now be accomplished using the CUBE operation. The CUBE operation groups the selected rows based on the values of all possible combinations of expressions for each row, and returns a single row of summary information for each group.

select owner, segment_type, sum(bytes)
  from dba_segments
  where owner in ('SYSTEM', 'SCOTT')
  group by owner, cube(segment_type)
SQL> /

OWNER                          SEGMENT_TYPE       SUM(BYTES)
------------------------------ ------------------ ----------
SCOTT                          INDEX                  131072
SCOTT                          TABLE                  262144
SCOTT                                                 393216
SYSTEM                         INDEX                 1179648
SYSTEM                         LOBINDEX               589824
SYSTEM                         LOBSEGMENT             589824
SYSTEM                         TABLE                 1048576
SYSTEM                                               3407872

8 rows selected.

If you want the summary for each user, and each type of object and a grand total (all possible combinations), all you have to do is:

select owner, segment_type, sum(bytes)
  from dba_segments
  where owner in ('SYSTEM', 'SCOTT')
  group by cube(owner, segment_type)
SQL> /

OWNER                          SEGMENT_TYPE       SUM(BYTES)
------------------------------ ------------------ ----------
SCOTT                          INDEX                  131072
SCOTT                          TABLE                  262144
SCOTT                                                 393216
SYSTEM                         INDEX                 1179648
SYSTEM                         LOBINDEX               589824
SYSTEM                         LOBSEGMENT             589824
SYSTEM                         TABLE                 1048576
SYSTEM                                               3407872
                               INDEX                 1310720
                               LOBINDEX               589824
                               LOBSEGMENT             589824
                               TABLE                 1310720
                                                     3801088

13 rows selected.

Now you see the first column is blank (NULL) for the summary information on the second column, and similarly the second column is blank for the summary information on the first column. To make the result more meaningful, let's use the GROUPING function. The GROUPING function returns a value of 1, if the expression is NULL, else the resulting value is 0. GROUPING function can only be used when using the ROLLUP or CUBE extension in the GROUP BY clause.

select decode(grouping(owner),1, 'ALL OWNERS', owner) owner,
         decode(grouping(segment_type),1, 'ALL SEGMENTS', segment_type) seg_type,
         sum(bytes)
  from dba_segments
  where owner in ('SYSTEM', 'SCOTT')
  group by cube(owner, segment_type)
SQL> /

OWNER                          SEG_TYPE           SUM(BYTES)
------------------------------ ------------------ ----------
SCOTT                          INDEX                  131072
SCOTT                          TABLE                  262144
SCOTT                          ALL SEGMENTS           393216
SYSTEM                         INDEX                 1179648
SYSTEM                         LOBINDEX               589824
SYSTEM                         LOBSEGMENT             589824
SYSTEM                         TABLE                 1048576
SYSTEM                         ALL SEGMENTS          3407872
ALL OWNERS                     INDEX                 1310720
ALL OWNERS                     LOBINDEX               589824
ALL OWNERS                     LOBSEGMENT             589824
ALL OWNERS                     TABLE                 1310720
ALL OWNERS                     ALL SEGMENTS          3801088

13 rows selected.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.