|
SQL>
SQL> set echo on
SQL>
SQL> create table t
2 as
3 select object_name ename,
4 mod(object_id,50) deptno,
5 object_id sal
6 from all_objects
7 where rownum <= 1000
8 /
Table created.
SQL>
SQL> create index t_idx on t(deptno,ename);
Index created.
SQL>
SQL> select ename, deptno, sal,
2 (select sum(sal)
3 from t e2
4 where e2.deptno < emp.deptno
5 or (e2.deptno = emp.deptno and e2.ename <= emp.ename ))
6 running_total,
7 (select sum(sal)
8 from t e3
9 where e3.deptno = emp.deptno
10 and e3.ename <= emp.ename)
11 department_total,
12 (select count(ename)
13 from t e3
14 where e3.deptno = emp.deptno
15 and e3.ename <= emp.ename) seq
16 from t emp
17 order by deptno, ename
18 /
1000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 92139442
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 43000 | 4 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 43 | | |
|* 2 | TABLE ACCESS FULL | T | 50 | 2150 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 43 | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 43 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 30 | | |
|* 7 | INDEX RANGE SCAN | T_IDX | 1 | 30 | 2 (0)| 00:00:01 |
| 8 | SORT ORDER BY | | 1000 | 43000 | 4 (25)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T | 1000 | 43000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E2"."DEPTNO"<:B1 OR "E2"."DEPTNO"=:B2 AND "E2"."ENAME"<=:B3)
5 - access("E3"."DEPTNO"=:B1 AND "E3"."ENAME"<=:B2)
7 - access("E3"."DEPTNO"=:B1 AND "E3"."ENAME"<=:B2)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
15678 consistent gets
4 physical reads
0 redo size
44533 bytes sent via SQL*Net to client
1106 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL>
SQL> set autotrace off
|