Oracle Illustrated

Jun 25 2010   9:10AM GMT

Analytical way – TO FIND RUNNING TOTAL

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

To find the running total using the non-analytical way minimum the same table needs to be joined twice. Using Analytical functions the same can be achieved with single table hit. In a single query running total can be computed in no time.

NON ANALYTICAL WAY -

Select emp1.empno, emp1.sal, sum(emp2.sal) from scott.emp emp1, scott.emp emp2
where emp2.empno <= emp1.empno group by emp1.empno, emp1.sal
order by emp1.empno

EMPNO SAL SUM(EMP2.SAL)
———- ———- ————-
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025

14 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1016356015
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 1 | SORT GROUP BY | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 2 | MERGE JOIN | | 10 | 160 | 6 (17)| 0
0:00:01 |
| 3 | SORT JOIN | | 14 | 112 | 2 (0)| 0
0:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 112 | 2 (0)| 0
0:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 0
0:00:01 |
|* 6 | SORT JOIN | | 14 | 112 | 4 (25)| 0
0:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 112 | 3 (0)| 0
0:00:01 |

Predicate Information (identified by operation id):
—————————————————

6 – access(INTERNAL_FUNCTION(“EMP2″.”EMPNO”)<=INTERNAL_FUNCTION(“EMP1″.”EMPNO
” )) filter(INTERNAL_FUNCTION(“EMP2″.”EMPNO”)<=INTERNAL_FUNCTION(“EMP1″.”EMPNO
” ))

Statistics
———————————————————-
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
790 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>

ANALYTICAL WAY

SQL> set autotrace on
SQL> Select e1.empno, e1.sal, sum(e1.sal) OVER (ORDER BY rownum) from scott.emp
e1 order by e1.empno;

EMPNO SAL SUM(E1.SAL)OVER(ORDERBYROWNUM)
———- ———- ——————————
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025

14 rows selected.

Execution Plan
———————————————————-
Plan hash value: 3990564813

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 112 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 112 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 112 | 5 (40)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
—————————————————————————–

Statistics
———————————————————-
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
807 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: