Summary in SQL - ROLLUP Features
ROLLUP and CUBE are two new extensions to the GROUP BY clause of the SELECT statement in Oracle8i to cacluate the summary of the data being displayed. In this article we discuss ROLLUP. 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.
select owner, segment_type, sum(bytes)
from dba_segments
where owner in ('SYSTEM', 'SCOTT')
group by owner, segment_type
union all
select 'Total', null, sum(bytes)
from dba_segments
where owner in ('SYSTEM', 'SCOTT')
SQL> /
OWNER SEGMENT_TYPE SUM(BYTES)
------------------------------ ------------------ ----------
SCOTT INDEX 131072
SCOTT TABLE 262144
SYSTEM INDEX 1179648
SYSTEM LOBINDEX 589824
SYSTEM LOBSEGMENT 589824
SYSTEM TABLE 1048576
Total 3801088
7 rows selected.
The same result can now be accomplished using the ROLLUP operation. ROLLUP groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions for each row, and returns a single row of summary for each group. ROLLUP operation can be used to produce subtotal values. The following query produces subtotals and a grand total.
select owner, segment_type, sum(bytes)
from dba_segments
where owner in ('SYSTEM', 'SCOTT')
group by rollup(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
3801088
9 rows selected.
Now you see the column is blank (NULL) for the summary information. 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 rollup(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 ALL SEGMENTS 3801088
9 rows selected.
SQL>
 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. |