Ø Some features for query processing
in oracle include the use of online analytical processing (OLAP) upon the
database.
Ø OLAP features are useful for data
warehousing and data mart applications.
Ø The OLAP operations are performance
enhancements.
- Top N Queries
- Group By
- CUBE
- ROLLUP
ROLLUP Option:
Ø It is a group by operation and is
used to produce subtotals at any level of the aggregation.
Ø The generated sub totals “Rolled Up”
to produce grant total.
Ø The totaling is based on a one
dimensional data hierarchy of grouped information.
Syntax:
GROUP BY ROLLUP (column 1, column 2,…)
Illustrations:
- Select Deptno, Sum(sal) From
Emp group by Rollup(Deptno);
- Select Job, Sum(sal) From Emp
Group by Rollup(Job);
- Select Job,Avg(Sal) From Emp
Group by Rollup(Job);
Passing Multiple Columns To Rollup:
Ø When multiple columns are passed to
Rollup, the Rollup, groups the rows into blocks with the same column values.
Ex:
- Select Deptno, Job, Sum(sal)
Salary From Emp Group by Rollup(Deptno, Job);
- Select Job, Deptno, Sum(sal) Salary
From Emp Group by Rollup(Job, Deptno);
- Select Job, Deptno, Avg(sal)
Average From Emp Group by Rollup(Job, Deptno);
Ø NULL Values in the output of Rollup
operations typically mean that the row contains subtotal or grant total
information.
Ø Use NVL () function for proper
meaning.
CUBE Option:
Ø It is an extension similar to
rollup.
Ø Cube allows specified set of
grouping columns and creates sub totals for all possible combinations of them.
Ø The Result of cube is a summary that
shows subtotals for every combination of columns or expressions in the group by
clause.
Ø The implementation of cube is also
called as N-dimensional cross tabulation.
Ex:
- Select Deptno, Job, Sum(sal)
Salary From Emp Group by Cube(Deptno, Job);
- Select Job, Deptno, Sum(Sal)
Salary From Emp Group by Cube(Job,
Deptno);
Applying Grouping() Function:
Ø The Grouping() Function accepts a
column and returns 0 or 1.
Ø Grouping() Function returns 1 when
the column value is NULL, and returns 0 when the column value is NOT NULL.
Ø Grouping() Function is used only
upon queries that use ROLLUP or CUBE.
Ex:
- Select Grouping(Deptno),
Deptno, Sum(sal) From Emp Group by Rollup(Deptno);
- Select Grouping(Job), Job,
Sum(sal) From Emp Group by Rollup(Job);
Grouping() with DECODE():
Ø The Decode() function can be used to
convert 1 and 0 returned through Grouping() into a meaningful output.
Ex:
- Select Decode(Grouping(deptno),
1,’ALL Departments’, Deptno) Departments, Sum(sal) From Emp Group by
Rollup(deptno);
- Select Decode(Grouping(Job),
1,’All Designations’, Job) Designations, Sum(sal) From Emp Group by Rollup(Job);
Decode() and Grouping() To Converting Multiple
Column Values:
Ex:
- Select Decode(Grouping(Deptno),
1,’All Department’, Deptno) Departments, Decode(Grouping(Job), 1,’All
Designations’, Job) Designations, Sum(sal) From Emp Group by
Rollup(Deptno, Job);
Grouping() with Decode() and CUBE:
Ex:
- Select Decode(Grouping(Deptno),
1,’All Departments’, Deptno) Departments, Decode(Grouping(Job), 1,’All
Designation’, Job) Designations, Sum(sal) From Emp Group by CUBE(Deptno,
Job);
Applying Grouping SETS Clause:
Ø The Grouping Sets clause is used to
get subtotal rows.
Ex:
- Select Deptno,
Job, Sum(sal) From Emp Group by Grouping Sets(Deptno, Job);
Grouping ID() Function:
Ø The Function is used to filter rows
using a having clause to exclude rows that do not contain a subtotal or grand
total.
Ø The Function accepts one or more
columns and returns the decimal equivalent of the grouping bit vector.
Ø The Grouping BIT Vector is computed
by combining the results or a call to the grouping() function for each column
in order.
Computing the Grouping BIT Vector:
Ø Grouping() Function returns 1 when
the column value is NULL, Else returns 0, based on this concept.
Ø Grouping_ID() returns 0, when deptno
and job are not null’s.
Ø Grouping_ID() returns 1, If Deptno
is not null and job is null.
Ø Grouping_ID() returns 2, if Deptno
is NULL and Job is not null.
Ø Grouping_ID() returns 3, if Deptno
is null and job is null.
Ex:
- Select Deptno, Job,
Grouping(deptno) GDPT, Grouping(Job) GJOB, Grouping_ID(Deptno, Job) GRPID,
Sum(sal) From Emp Group by CUBE(Deptno, Job);
Grouping_ID() and Having Clause:
Ex:
- Select Deptno, Job,
Grouping_ID(Deptno, Job) GRPID, Sum(sal) From Emp Group by CUBE(Deptno,
Job) Having Grouping_ID(Deptno, Job) > 0;
Representing Column Multiple Times in a Group
by clause:
Ø A column can be represented multiple
times in a group by clause.
Ex:
- Select Deptno, Job, Sum(sal)
From Emp Group by Deptno, Rollup(deptno, Job);
- Select Deptno, Job, Sum(sal)
From Emp Group by Deptno, Cube(Deptno, Job);
Applying Group_ID Function:
Ø The Group_ID() Function is used to
remove the duplicate rows returned by group by clause.
Ø The Group_ID() Does not accept any
parameters.
Ø If ‘N’ Duplicates exist for a
particular grouping, group_ID() returns numbers in the range 0 to N-1.
Ex:
- Select Deptno, Job, Group_ID(),
Sum(sal) From Emp Group by Deptno, Rollup(Deptno, Job);
- Select Deptno, Job, Group_ID(),
Sum(sal) From Emp Group by Deptno, CUBE(Deptno, Job);
- Select Deptno, Job, Group_ID(),
Sum(sal) From Emp Group by Deptno, Rollup(Deptno, Job) Having Group_ID() =
0;