Published: Sat 07 July 2012
By Friederike
In Programmieren .
tags: Oracle SQL Datenbanken
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: 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 SpeicherplatzesEXTENT_SIZE – Größe (in MB) um die der Speicherplatz erweitert wird, wenn der Platz knapp wird.USED_EXTENT S – Größe (in MB) des momentan benutzten SpeicherplatzesTOTAL_EXTENTS – Größe (in MB) des insgesamt verfügbaren Speicherplatzes