|
18c: Private Temporary Tables
Lang ersehnt und endlich verfügbar: Mit Oracle Database 18c gibt es jetzt private temporäre Tables (engl. private temporary tables).
Wie funktionieren diese? Was muss man berücksichtigen? Welche Anwendungsfälle gibt es?
Seit Oracle8i existieren schon Temporary Tables, nämlich die Global Temporary Tables, die Session- oder auch Transaktions- spezifisch erzeugt werden können. Wie der Name schon anzeigt, ist die Tabellenstruktur global und steht allen berechtigten Sessions zur Verfügung. Auch wurden einige neue Features seit 12c eingeführt: Statistiken auf Global Temporary Tables können beispielsweise seit 12c shared oder session-spezifisch verwendet werden. Seit 18c gibt es darüberhinaus auch die Möglichkeit globale temporäre Tabellen dynamisch für Oracle Active Data Guard Standby zu erzeugen.
Aber was tun wenn nur ein Schema verwendet werden soll und in mehreren Sessions für kurzfristige Berechnungen temporäre Tabellen gleichen Namens genutzt werden sollen? Oder temporäre Tabellen sollen in Read-Only Datenbanken verwendet werden? Die Lösung hierfür liefert das neue Feature der privaten temporären Tabellen.
Zuerst starten wir mit einer kurzen Beschreibung: Eine private temporäre Tabelle ist ein Datenbankobjekt, das explizit mit einem CREATE Kommando erzeugt wird und nur für die Session, die sie erstellt hat, sichtbar ist. Wichtig zu wissen ist dabei, dass die Daten (gemeint sind Metadaten und Zeilen) im Memory (Fixed Table Memory und Temporary Tablespace) gehalten werden.
Erzeugen einer Privaten Temporären Tabelle und einige Eigenschaften
Um eine private temporäre Tabelle (auch kurz PTT) zu erzeugen, muss eine gewisse Namenskonvention eingehalten werden: Der Name beginnt immer mit einem Präfix, der über den Initialisierungsparameter private_temp_table_prefix festgelegt wird.
SQL> col value format a15
SQL> select value, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISPDB_MODIFIABLE
from v$parameter where name='private_temp_table_prefix'
VALUE ISSES ISSYS_MOD ISPDB
--------------- ----- --------- -----
ORA$PTT_ FALSE DEFERRED TRUE
Der Wert von private_temp_table_prefix lässt sich nicht über ein ALTER SESSION Kommando ändern, sondern nur mit ALTER SYSTEM und der Klausel DEFERRED. Wenn Sie einen anderen Präfixwert angeben, muss dieser mit dem String ORA$ beginnen und in der Datenbank eindeutig sein. Folgendes Beispiel ändert den Wert nur für die PDB1.
-- im Container PDB1
SQL> alter system set private_temp_table_prefix='ORA$ULRIKE_' deferred;
System altered.
-- neuer CONNECT
SQL> connect scott/tiger@pdb1
Connected.
SQL> select value
from v$parameter where name='private_temp_table_prefix';
VALUE
-----------
ORA$ULRIKE_
Wie bei der globalen temporären Tabelle gibt es auch hier eine Transaktions- und Session-spezifische Variante - allerdings mit einigen Unterschieden. Die Transaktions-spezifische Variante, die den Default darstellt oder mit den Schlüsselworten DROP DEFINITION erzeugt wird, speichert Daten nur bis zum Ende einer Transaktion. Folgendes Beispiel zeigt das Verhalten.
SQL> connect scott/tiger@pdb1
Connected.
SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction
(time_id DATE,
amount_sold NUMBER(10,2))
ON COMMIT DROP DEFINITION;
Table created.
SQL> desc ORA$PTT_sales_ptt_transaction
Name Null? Type
----------------------------------------- -------- ----------------------------
TIME_ID DATE
AMOUNT_SOLD NUMBER(10,2)
SQL> insert into ORA$PTT_sales_ptt_transaction values (sysdate,1);
1 row created.
SQL> select * from ORA$PTT_sales_ptt_transaction;
TIME_ID AMOUNT_SOLD
--------- -----------
08-MAR-18 1
SQL> commit;
Commit complete.
SQL> desc ORA$PTT_sales_ptt_transaction
ERROR:
ORA-04043: object ORA$PTT_sales_ptt_transaction does not exist
Die Session-spezifische Variante wird mit PRESERVE DEFINITION angelegt. In beiden Fällen erfolgt dabei kein implizites Commit wie folgendes einfaches Beispiel zeigt.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept (deptno, dname) values (50, 'SALES');
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SALES
SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_session
(time_id DATE,
amount_sold NUMBER(10,2))
ON COMMIT PRESERVE DEFINITION;
Table created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SALES
SQL> rollback;
Rollback complete.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Übrigens lässt sich die CREATE PRIVATE TEMPORARY TABLE Definition mit der Klausel TABLESPACE erweitern. So kann man Einfluss auf den temporären Tablespace - der Ablageort für die Zeileninhalte - legen.
SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction
(time_id DATE,
amount_sold NUMBER(10,2))
ON COMMIT DROP DEFINITION
TABLESPACE temp;
Es werden keinerlei Informationen in den üblichen Data Dictionary Views wie USER_SEGMENTS oder USER_OBJECTS abgelegt. Monitoren lassen sich die PTTs ausschliesslich über zwei neue Monitoring Views: user_private_temp_tables und dba_private_temp_tables.
SQL> select table_name, tablespace_name, duration from user_private_temp_tables; TABLE_NAME TABLESPACE_NAME DURATION ------------------------------ -------------------- -------------------- ORA$PTT_SALES_PTT_SESSION TEMP SESSION ORA$PTT_SALES_PTT_TRANSACTION TEMP TRANSACTION
Darüberhinaus stehen die DDL Kommandos DROP und TRUNCATE zur Verfügung.
Zu Beachten
Auch im Falle von privaten temporären Tabellen gelten die Einschränkungen der globalen temporären Tabellen wie zum Beispiel die Nutzung von Partitionierung oder der Einsatz von Foreign Keys. Darüberhinaus sollte noch Folgendes berücksichtigt werden:
- Der Namen einer PTT muss wie schon beschrieben einer bestimmten Konvention folgen
- Keine Indizes, materialisierten Views oder Zone Maps sind möglich.
- Spalten mit Default Werten sind nicht möglich.
- PTTs können nicht in permanenten Objekten (wie z.B. Views oder Trigger) referenziert werden.
- PTTs sind nicht über Database Links sichtbar.
- Manuelles Statistiksammeln über DBMS_STATS ist nicht möglich.
Betrachtet man den letzten Punkt etwas genauer, wird klar, dass der Optimizer in der Regel Dynamic Sampling verwenden wird.
SQL> select * from ORA$PTT_SALES_PTT_SESSION; TIME_ID AMOUNT_SOLD --------- ----------- 07-MAR-18 1 Execution Plan ---------------------------------------------------------- Plan hash value: 3883909658 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 2(0)| 00:00:01 | | 1 | TABLE ACCESS FULL| ORA$PTT_SALES_PTT_SESSION | 1 | 22 | 2(0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2)
Eine Ausnahme bildet übrigens das Statement CREATE TABLE AS SELECT, das seit 12c ein Online Statistics Gathering verwendet.
Einsatzszenarien
Die Funktionalität von privaten temporären Tabellen ist eine sehr einfach anzuwendende Technik, die das Coding erleichtert und hohe Flexibilität bietet. Da bei der Verwendung kein implizites Commit erfolgt, lässt sich PTT immer dann verwenden, wenn das Anlegen einer temporären Tabelle keine neue Transaktion starten oder eine bestehende Transaktion festschreiben darf. PTT ist auch ideal geeignet für verschiedene Sessions desselben Benutzers, wenn dergleiche Namen für eine temporäre Tabelle verwendet werden soll. Oder in dem Fall, wenn eine temporäre Tabelle nur einmal befüllt, mehrfach gelesen und danach wieder gelöscht werden soll. Besonders geeignet ist PTT auch für Read-Only Datenbanken wie zum Beispiel Active Data Guard, da auch die Metadaten nicht festgeschrieben werden.
Weitere Informationen
- SQL Language Reference Guide
- Database Administrator's Guide
- Database Concepts Guide
- Tutorials zu PTT auf livesql.oracle.com
Zurück zur Community-Seite