... erklärt Temporäre Tabellen

In den letzten Wochen habe ich einiges zu temporären Tabellen (Global Temporary Tables, kurz GTT) unter Oracle gelernt. Diese sind sehr nützlich wenn komplexe oder aufwendige Berechnungen in einer Datenbank ausgeführt werden müssen, die Zwischenergebnisse aber verworfen werden können.

Wie funktionieren temporäre Tabellen?

Unterschied von normaler und temporärer Tabelle

Dieses Bild zeigt die Unterschiede zwischen einer normalen Tabelle (1, 2) und einer temporären Tabelle (3) anhand eines kleinen Beispiels. Anna und Bert sind zwei Benutzer die auf der Datenbank arbeiten möchten. Sie wollen etwas berechnen und das Ergebnis speichern.

Im Bild kann in der normalen Tabelle “DATEN_TAB” eine Rechnung unter einem Namen abgespeichert werden. Die Daten die in der Tabelle gespeichert sind, können von allen Nutzern gesehen und gelesen werden.

(1) Anna speichert eine Rechnung in der Tabelle ab. Die Datenbank sorgt dafür, dass die Daten sicher gespeichert werden und das auch nur Anna gerade die Tabelle verändert.

Bert möchte auch etwas rechnen, muss aber noch warten bis Anna fertig ist. ( In der Realität geht das jedoch so schnell, dass Bert davon nichts mitbekommt.)

(2) Anna ist fertig mit ihren Berechnungen und teilt das der Datenbank mit: Durch den Befehl COMMIT werden die Änderungen in der Datenbank abgespeichert und jeder kann nun in der Tabelle sehen was Anna gerechnet hat.

Jetzt ist Bert an der Reihe. Er rechnet ebenfalls etwas aus und speichert es in der Datenbank. Seine Rechnung ist nach einem COMMIT ebenfalls für alle Nutzer sichtbar.

(3) Anders läuft das ganze, wenn in der Datenbank eine GTT verwendet wird. Anna und Bert bekommen nun jeweils ihre eigene virtuelle Kopie der temporären Tabelle RECHNUNG.

Beide können gleichzeitig auf diesen Tabellen arbeiten. Allerdings kann Bert nicht sehen was Anna gerechnet hat und umgekehrt.

Nach einem COMMIT gehen alle Berechnungen verloren. Speichern Anna und Bert ihre Ergebnisse vor dem COMMIT in einer normalen Tabelle ab, dann bleiben sie dort erhalten. Aber die temporären Tabellen mit denen sie gearbeitet haben, werden wieder gelöscht.

Fazit:
Eine GTT kann man sich als eine Art Blaupause für eine Tabelle vorstellen. Jeder Datenbankbenutzer der sich an der Datenbank anmeldet bekommt seine eigene Kopie der Tabelle. Er hat dann seine eigene Instanz der Tabelle und sieht auch nur die Daten, die er selber dort eingetragen hat. Alle Änderungen die er vornimmt (auch TRUNCATE ) betreffen nur die eigene Tabelleninstanz und beeinflussen keine Daten die andere Benutzer in ihrer Tabelleninstanz haben.

Die Daten in einer temporären Tabelle sind nicht persistent. Das heißt sie werden nicht dauerhaft gespeichert sonder bestehen maximal bis zum Ende der Datenbanksitzung, also solange der Benutzer auf der Datenbank arbeitet.

Aus diesem Grund arbeiten temporäre Tabellen meist schneller als normale Tabellen, weil die Datenbank keine Logs und Rollback-informationen abspeichern muss.

Wie verwendet man eine GTT?

Eine temporäre Tabelle unter Oracle kann man genauso verwenden wie “normale” Tabellen auch. Sie lassen sich mit INSERT, UPDATE, DELETE bearbeiten und mit SELECT auslesen. Das Erstellen einer GTT ist dem Erstellen einer normalen Tabelle ähnlich, aber nicht ganz gleich:

CREATE GLOBAL TEMPORARY TABLE my_tmp_table ( zahl number )
ON COMMIT DELETE ROWS;

Neben den Stichwörtern “GLOBAL TEMPORARY ” ist vor allem die letzte Zeile ein wichtiger Unterschied: “ON COMMIT DELETE ROWS ” legt fest, dass alle Daten in der Tabelle bei einem Commit gelöscht werden. Die Daten in dieser Tabelle werden also nicht dauerhaft gespeichert. Es werden auch keine Logs und Rollback-Informationen geschrieben wodurch die GTTs schneller sind als “normale” Tabellen.

Wo speichert Oracle die Daten einer GTT?

Die Daten einer GTT werden in einem eigenen temporären Speicherplatz gespeichert (TEMP ).

Wie viele Daten gerade in diesem Speicherplatz vorhanden sind kann man in der Systemtabelle v$sort_segment sehen:

SELECT TABLESPACE_NAME, EXTENT_SIZE, USED_EXTENTS, TOTAL_EXTENTS FROM V$SORT_SEGMENT;

Erklärung der Spalten:

• TABLESPACE_NAME – Name des Speicherplatzes
• EXTENT_SIZE – Größe (in MB) um die der Speicherplatz erweitert wird, wenn der Platz knapp wird.
• USED_EXTENTS – Größe (in MB) des momentan benutzten Speicherplatzes
• TOTAL_EXTENTS – Größe (in MB) des insgesamt verfügbaren Speicherplatzes