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.
 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. |