分析函数二：例子

SQL> set timing on;
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;
 
Table created
 
Executed in 0.391 seconds
 
SQL> create index t_idx on t(deptno, ename);
 
Index created
 
Executed in 0.078 seconds



1.分析函数
SELECT ename, deptno, sal,
   SUM(sal) over
     (ORDER BY deptno, ename) running_total,
   SUM(sal) over
     (PARTITION BY deptno
      ORDER BY ename) department_total,
   row_number() over
     (PARTITION BY deptno
      ORDER BY ename) seq
FROM t emp
ORDER BY deptno, ename;

已选择1000行。

已用时间:  00: 00: 00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   WINDOW (BUFFER)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   3    2       INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        260  consistent gets
          0  physical reads
          0  redo size
      56006  bytes sent via SQL*Net to client
        924  bytes received via SQL*Net from client
         41  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>


2.传统的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.en
  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)
 16  seq
 17  FROM t emp
 18  ORDER BY deptno, ename;

已选择1000行。

已用时间:  00: 00: 01.00

Execution Plan
--------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     CONCATENATION
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T'
   4    3         INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNI
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'T'
   6    5         INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNI
   7    0   SORT (AGGREGATE)
   8    7     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   9    8       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQU
  10    0   SORT (AGGREGATE)
  11   10     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
  12    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
  13   12     INDEX (FULL SCAN) OF 'T_IDX' (NON-UNIQUE)




Statistics
--------------------------------------------------------
          0  recursive calls
          0  db block gets
     139402  consistent gets
          0  physical reads
          0  redo size
      56006  bytes sent via SQL*Net to client
        924  bytes received via SQL*Net from client
         41  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>