Tijdelijke data opslaan in de database: verschillende opties op een rij

De database is bekend als de manier voor langdurig opslag van data. We zien steeds vaker dat gedurende dataverwerking er tussen data wordt geproduceerd die ook enige tijd vast gehouden moet worden. Hiervoor zijn verschillende manieren te bedenken:

  • Geheugen gebaseerde structuur zoals een PL/SQL collectiones en arrays.
    Nadeel van deze structuur is dat de ruimte in de PGA gealloceerd moet worden waardoor de bruikbaarheid sterk afhangt van de beschikbare geheugen. Als de hoeveelheid data groot worden is een collection niet goed doorzoekbaar.
  • Normale tabel.
    Door de data tijdelijk op te slaan in een normale tabel ontstaan automatisch alle voordelen die een tabel ook heeft. De doorzoekbaarheid is daar een van. Nadeel is dat de Oracle database ook alle overhead in stelling brengt die nodig is voor recovery. Iets wat niet nodig is.
    Een andere beperking zit in het feit dat de data ook zichtbaar wordt voor andere sessies. Omdat het hier om tussenresultaten gaat, is dit niet van belang.
  • Temporary tabel
    in Oracle versie 12 heeft Oracle de Global Temporary Table geintroduceerd, In Oracle 18 is de Private Temporary Table daarbij gekomen. Beiden bieden de mogelijkheid tijdelijk data op te slaan zonder dat deze volledig leunt op het recovery mechanisme van de Oracle database.

Ondanks dat de global en temporary tables hetzelfde doel dienen hebben ze andere karakteristieken. Laten we deze achtereenvolgens bekijken.

De global temporary table
Deze is een normale table vanuit het perspectief van de definitie. Dit betekent dat de definitie in dba|user_tables te bevragen is en er ook indexen en dergelijke te definiëren zijn op deze tabel. De data in de tabel heeft een beperkte levensduur: de transactie (on commit delete rows) of de sessie (on commit preserve rows). De data is alleen beschikbaar in de specifieke sessie, de definitie is echte toegankelijk in alle sessies (met voldoende rechten).

E.e.a is met de volgende demo waar te nemen

create global temporary table GTTEST2 ON COMMIT preserve ROWS 
as 
select * from user_objects;

Table created.

select table_name
, TEMPORARY 
from user_tables 
where table_name='GTTEST';

TABLE_NAME                   T
---------------------------- -
GTTEST                       Y

select count(*) from GTTEST;
  COUNT(*)
----------
     52214

session2> select count(*) from GTTEST;
  COUNT(*)
----------
         0

De private temporary table
Deze in Oracle 18 geintroduceerde structuur verschilt op een aantal aspecten fundamenteel van de global temporary table. Alleereerst is de ele structuur sessie specifiek. Daarmee is niet alleen de data exclusief voor de huidige sessie maar ook de definitie. Bijkomend voordeel daarvan is dat het mogelijk is in verschillende sessies tegelijk een table aan te maken met dezelfde naam, maar een andere structuur.
De tabel en data verdwijnen automatisch bij het einde van de transactie of sessie afhankelijk van de definitie. Doordat de definitie sessie specifiek is, en er dus geen entry in de data dictionary komt, is het niet mogelijk indexen en statistieken voor dit object te maken. Wel zijn deze structuren uitermate geschikt voor een read only omgeving.

Twee bijkomende beperking zijn :

  • de naar moet prefixed zijn met private_temp_table_prefix, deze heeft een default waarde van ORA$PTT_
  • het is niet mogelijk een private temporary table te maken onder het sys schema

De private temporary table werkt dus als een table maar heeft een paar speciale aspecten:

CREATE PRIVATE TEMPORARY TABLE ORAS$PTT_PTTEST
on COMMIT PRESERVE DEFINITION 
as select * from dba_objects;

Table created.

select * from user_tables where table_name='ORA$PTT_PTTEST';

no rows selected

desc ora$ptt_pttest;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(128)
 …
 MODIFIED_VSNID                                     NUMBER

select count(*) from  ORAS$PTT_PTTEST;
  COUNT(*)
----------
     72360

session2>  select count(*) from  ORAS$PTT_PTTEST;

ERROR at line 2:
ORA-00942: table or view does not exist

De private en temporary table structuren bieden bruikbare structuren om tijdelijk data op te slaan in een batch job. Welke van beiden het meest geschikt is , hangt sterk af van de setting. Belangrijk is te beseffen dat de private temporay table slechts vanaf Oracle 19 beschikbaar is.

Scroll to Top