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> set autotrace traceonly
SQL> set timing on
SQL> select ename, deptno, sal,
2 sum(sal) over
3 (order by deptno, ename) running_total,
4 sum(sal) over
5 (partition by deptno
6 order by ename) department_total,
7 row_number() over
8 (partition by deptno
9 order by ename) seq
10 from t emp
11 order by deptno, ename
12 /
1000 rows selected.
Elapsed: 00:00:00.19
Execution Plan
----------------------------------------------------------
Plan hash value: 2335850315
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 43000 | 3 (0)| 00:00:01 |
| 1 | WINDOW SORT | | 1000 | 43000 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 1000 | 43000 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
15 consistent gets
0 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 timing off
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>
|