The query that uses partition OVER
PARTITION BY analytic function
The below example shows the cumulative
salary within a departement row by row, with each row including a
summation of the prior rows salary.
SELECT ename "Ename", deptno "Deptno", sal "Sal",
SUM(sal)
OVER (ORDER BY deptno, ename) "Running Total",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename) "Dept Total",
ROW_NUMBER()
OVER (PARTITION BY deptno
ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename
Result:
-------
Ename Deptno Sal Running Total Dept
Total Seq
------ ------ ------ -------------
---------- ----
CLARK 10 2450 2450
2450 1
KING 5000 7450
7450 2
MILLER 1300 8750
8750 3
ADAMS 20 1100 9850
1100 1
FORD 3000 12850
4100 2
JONES 2975 15825
7075 3
SCOTT 3000 18825
10075 4
SMITH 800 19625
10875 5
ALLEN 30 1600 21225
1600 1
BLAKE 2850 24075
4450 2
JAMES 950 25025
5400 3
MARTIN 1250 26275
6650 4
TURNER 1500 27775
8150 5
WARD 1250 29025
9400 6
Execution Plan
---------------------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE
1 0 WINDOW (SORT)
2 1 TABLE ACCESS (FULL) OF
'EMP'
Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1658 bytes sent via SQL*Net to
client
503 bytes received via SQL*Net
from client
2 SQL*Net roundtrips to/from
client
1 sorts (memory)
0 sorts (disk)
14 rows processed
The example shows how to calculate a
"Running Total" for the entire query. This is done using
the entire ordered result set, via SUM(sal) OVER (ORDER BY deptno,
ename).
Further, we were able to compute a
running total within each department, a total that would be reset at
the beginning of the next department. The PARTITION BY deptno in that
SUM(sal) caused this to happen, a partitioning clause was specified
in the query in order to break the data up into groups.
The ROW_NUMBER() function is used to
sequentially number the rows returned in each group, according to our
ordering criteria (a "Seq" column was added to in order to
display this position).
The execution plan shows, that the
whole query is very well performed with only 3 consistent gets, this
can never be accomplished with standard SQL or even PL/SQL.
