Analytics

Analytics sind mir zuerst bei Tom Kyte in seinen Antworten auf AskTom begegnet, und er hat mich dort schnell überzeugt, dass sie das Tollste seit Erfindung des geschnittenen Brots sind. Mich überrascht aber immer wieder, wie wenig sich analytische Funktionen in der Zwischenzeit herumgesprochen haben, obwohl sie eigentlich nicht mehr ganz neu sind. Deshalb hier noch mal eine kurze Erläuterung (bei der ich wieder so tue, als hätte ich hier Leser): der grundsätzliche Unterschied zwischen den Analytics und einer GROUP BY-Operation ist, dass durch ein GROUP BY die Anzahl der Ergebnissätze reduziert wird, während die Analytics alle Sätze der ursprünglichen Query erhalten und um gruppierte Ergebnisse ergänzen. Dazu ein harmloses Beispiel mit der allseits beliebten EMP-Tabelle, aus der ich mir zu jedem Mitarbeiter den Job und die Gesamtzahl der Mitarbeiter mit diesem Job anzeigen lasse:

-- zunächst die analytische Variante
select ENAME
     , job
     , count(*) over (partition by job) job_count
  from emp
 order by ename;

ENAME      JOB        JOB_COUNT
---------- --------- ----------
ADAMS      CLERK              4
ALLEN      SALESMAN           4
BLAKE      MANAGER            3
CLARK      MANAGER            3
FORD       ANALYST            2
JAMES      CLERK              4
JONES      MANAGER            3
KING       PRESIDENT          1
MARTIN     SALESMAN           4
MILLER     CLERK              4
SCOTT      ANALYST            2
SMITH      CLERK              4
TURNER     SALESMAN           4
WARD       SALESMAN           4

14 Zeilen ausgewählt.

Man kann das gleiche Ergebnis auch dadurch erreichen, dass man EMP mit einer gruppierenden Unterabfrage joint:

select e1.ename
     , e1.job
     , e2.job_count
  from emp e1
     , (select JOB
             , count(*) job_count
          from emp
         group by job) e2
 where e1.job = e2.job
 order by e1.ename;

ENAME      JOB        JOB_COUNT
---------- --------- ----------
ADAMS      CLERK              4
ALLEN      SALESMAN           4
BLAKE      MANAGER            3
CLARK      MANAGER            3
FORD       ANALYST            2
JAMES      CLERK              4
JONES      MANAGER            3
KING       PRESIDENT          1
MARTIN     SALESMAN           4
MILLER     CLERK              4
SCOTT      ANALYST            2
SMITH      CLERK              4
TURNER     SALESMAN           4
WARD       SALESMAN           4

14 Zeilen ausgewählt.

Da ist die analytische Variante auf jeden Fall kompakter; ob sie verständlicher ist, hängt sicher vom Standpunkt des Betrachters ab. Zumindest aber ist sie in der Regel effizienter als die traditionelle Lösung:

-- der Plan für die analytische Variante
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   182 |     6  (34)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |    14 |   182 |     6  (34)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    14 |   182 |     6  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   182 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        831  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

-- der Plan der klassischen Variante
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    39 |  1248 |    11  (28)| 00:00:01 |
|   1 |  SORT ORDER BY        |      |    39 |  1248 |    11  (28)| 00:00:01 |
|*  2 |   HASH JOIN           |      |    39 |  1248 |    10  (20)| 00:00:01 |
|   3 |    VIEW               |      |    14 |   266 |     5  (20)| 00:00:01 |
|   4 |     HASH GROUP BY     |      |    14 |    84 |     5  (20)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| EMP  |    14 |    84 |     4   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL  | EMP  |    14 |   182 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - access("E1"."JOB"="E2"."JOB")

Note
-----
   - dynamic sampling used for this statement


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

Für dieses harmlose Beispiel benötigt die analytische Query nur 7 statt 14 Blockzugriffen, allerdings sind zwei statt einer Sortieroperation erforderlich. Auf die Details will ich jetzt nicht weiter eingehen (weil ich sie noch mal nachlesen müsste...), aber in der Regel sind die Analytics ein extrem nützliches Werkzeug, das die Möglichkeiten von SQL deutlich erweitert.