Analytic Functions bilden eine Familie von SQL-Funktionen, die bereits seit Oracle-Datenbank 8i verfügbar sind. Sie sind auch als Fenster- oder Windowing-Funktionen bekannt, weil sie eine „Frame-Sicht“ auf die Daten bieten. Analytic Functions arbeiten auf der Ergebnismenge einer Abfrage.
Die Oracle-Dokumentation zu Datenbank 12.1 listet insgesamt 46 Funktionen auf, darunter AVG, LAG, MAX und RANK, und definiert Analytic Functions folgendermaßen:
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
Darüber hinaus verfügen Analytic Functions über besondere Eigenschaften:
- Sie bieten Zugriff auf Werte aus anderen Zeilen der Ergebnismenge.
- Das mehrfache Lesen der Daten ist nicht notwendig.
Ein Beispiel mit LAG()
LAG is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.
(Auszug aus der Oracle-Dokumentation)
SELECT hire_date, last_name, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK'
ORDER BY hire_date;
| HIRE_Date | LAST_Name | SALARY | PREV_SAL |
|---|---|---|---|
| 18-MAY-03 | Khoo | 3100 | 0 |
| 24-JUL-05 | Tobias | 2800 | 3100 |
| 24-DEC-05 | Baida | 2900 | 2800 |
| 15-NOV-06 | Himuro | 2600 | 2900 |
| 10-AUG-07 | Colmenares | 2500 | 2600 |
Tuning von SQL-Statements im Projekt
In einem Projekt sollte eine Reihe von SQL-Statements getunt werden. Es handelte sich um „Langläufer“, die das nächtliche Batch-Fenster immer häufiger gesprengt haben. Die Aufgabe der Statements bestand darin, Daten aus Quell-Tabellen in „Materialized Views“ (Snapshots) zu laden. Die Statements waren als Views gekapselt und nach einem gemeinsamen Schema aufgebaut. Ihre Aufgabe bestand darin, die korrekte Version eines Objektes aus der Quell-Tabelle herauszufinden.
Um das Prinzip zu veranschaulichen, betrachten wir folgendes, fiktives Beispiel:
- Lesen von Adressen
- Pro Kunde nur eine Adresse
- Auswahl über TYP der Adresse:
- bevorzugt Adresse mit TYP = FIRMA
- wenn nicht vorhanden, dann Adresse mit TYP = POSTFACH
- wenn nicht vorhanden, dann Adresse mit TYP = PRIVAT
- Tabelle ADRESSEN
- ID (PK)
- KUNDEN_ID (UK)
- TYP [ FIRMA | POSTFACH | PRIVAT ] (UK)
- STRASSE
- HAUSNUMMER
- POSTFACH
- PLZ
- ORT
Beispieldaten:
| KUNDEN_ID | TYP |
|---|---|
| 90810846361 | PRIVAT |
| 90810846361 | POSTFACH |
| 217537839264 | PRIVAT |
| 481491166609 | PRIVAT |
| 481491166609 | POSTFACH |
| 481491166609 | FIRMA |
Das bisherige SQL-Statement lautete:
SELECT A.KUNDEN_ID, A.TYP,
A.STRASSE, A.HAUSNUMMER, A.POSTFACH, A.PLZ, A.ORT
FROM ADRESSEN A
WHERE
( ( A.TYP = 'FIRMA' ) OR
( A.TYP = 'POSTFACH'
AND NOT EXISTS ( SELECT 1 FROM ADRESSEN B
WHERE B.KUNDEN_ID = A.KUNDEN_ID
AND B.TYP = 'FIRMA' ) ) OR
( A.TYP = 'PRIVAT'
AND NOT EXISTS ( SELECT 1 FROM ADRESSEN C
WHERE C.KUNDEN_ID = A.KUNDEN_ID
AND C.TYP IN ( 'FIRMA' , 'POSTFACH' ) ) ) )
Der Execution-Plan des bisherigen SQL-Statements sah folgendermaßen aus:
| Vorgang | Optionen | Objekt |
|---|---|---|
| SELECT STATEMENT | ||
| FILTER | ||
| TABLE ACCESS | STORAGE FULL | ADRESSEN |
| INDEX | UNIQUE SCAN | ADRESSEN_UK |
| INDEX | RANGE SCAN | ADRESSEN_UK |
Hier die Laufzeiten des bisherigen SQL-Statements in Sekunden (Performancetests mit Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit unter VirtualBox auf einem Office Laptop):
| 100 Rows: | 0,02 |
| 10 000 Rows: | 0,21 |
| 1 000 000 Rows: | 12,58 |
| 100 000 000 Rows: | 1190,00 |
Das Problem: Jeder Satz in der Tabelle ADRESSEN musste dahingehend überprüft werden, ob er in der richtigen Version vorliegt, oder ob gegebenenfalls zu dem gleichen Kunden eine andere Adresse vorliegt, die Vorrang vor dem aktuellen Satz hat (daher die beiden „NOT EXISTS“-Klauseln). Durch die Index-Scans skaliert die Antwortzeit zwar recht gut, jedoch waren die Antwortzeiten immer länger geworden, da die Anzahl der zu betrachtenden Sätze stetig gewachsen ist.
Gelöst hat das Problem der Einsatz der Analytic Function RANK():
SELECT KUNDEN_ID, TYP, STRASSE, HAUSNUMMER, POSTFACH, PLZ, ORT
FROM
(
SELECT KUNDEN_ID, TYP,
STRASSE, HAUSNUMMER, POSTFACH, PLZ, ORT,
RANK() OVER (PARTITION BY KUNDEN_ID
ORDER BY CASE TYP WHEN 'FIRMA' THEN 1
WHEN 'POSTFACH' THEN 2
WHEN 'PRIVAT‘ THEN 3 END )
AS RANK
FROM ADRESSEN
)
WHERE RANK = 1
Der Execution-Plan des neuen SQL-Statements:
| Vorgang | Optionen | Objekt |
|---|---|---|
| SELECT STATEMENT | ||
| VIEW | ||
| WINDOW | SORT PUSHED RANK | |
| TABLE ACCESS | STORAGE FULL | ADRESSEN |
Jetzt werden die Daten lediglich in einem „Full Table Scan“ gelesen. Es ist kein weiterer Zugriff auf die Daten der Tabelle ADRESSEN ist notwendig. Der Eintrag WINDOW SORT PUSHED RANK zeigt, dass die Analytic Function ausgeführt wird. Dies geschieht in Memory, abhängig von der Datenmenge gegebenenfalls auch in TEMP-Tablespace. Das spiegelt sich auch in den Ausführungszeiten wider (in Sekunden):
| bisher | neu | Differenz | |
|---|---|---|---|
| 100 Rows | 0,02 | 0,02 | 0% |
| 10 000 Rows | 0,21 | 0,09 | -200% |
| 1 000 000 Rows | 12,58 | 6,23 | -200% |
| 1 000 000 000 Rows | 1190,00 | 630,00 | -200% |
Einige Statistiken dazu:
| bisher | neu | |
|---|---|---|
| consistent gets: | 9.366 | 193 |
| logical read bytes from cache: | 76.726.272 | 1.581.056 |
| CPU used by this session: | 1 | 2 |
Die Ausführungszeit sank signifikant um 200 Prozent. Im Projekt konnten sogar Verbesserungen bis zu 800 Prozent beobachtet werden. Damit konnte das Performance-Problem der zu lange laufenden Batch-Jobs erfolgreich gelöst werden. Die Eingriffe in das bestehende System waren auf die SQL-Statements begrenzt.
Die Tests der neuen Statements waren recht einfach:
altes Select
MINUS
neues Select
und
altes Select
MINUS
neues Select
In beiden Fällen ist die leere Menge als Ergebnis zu erwarten.
Das neue SQL-Statement bietet darüber hinaus noch weitere Vorteile:
- Es ist einfacher aufgebaut, dadurch verständlicher und wartbarer – zwei Subselects wurden eliminiert.
- Es ist in der Ausführung weniger ressourcenintensiv – nur ein „Full Table Scan“ findet jetzt statt.
- Es wird mit wachsender Datenmenge besser zurechtkommen.
