|
Oracle Database und Temporal Validity
Temporal Validity (wörtlich übersetzt auch zeitliche Gültigkeit) ist eine Standardfunktion der Oracle Datenbank, die in jeder Edition
der Datenbank und in jedem Offering - ob Cloud oder On-Premises Installationen - zur Verfügung steht. Vielen Oracle Datenbank
Usern und Entwicklern ist diese Funktion allerdings nicht bekannt, obwohl sie seit 12.1 in der Oracle Datenbank enthalten ist.
Was genau versteht man nun unter Temporal Validity in der Oracle Datenbank? Temporal Validity ermöglicht es, einer Tabelle eine
gültige Zeitdimension zuzuordnen und Daten in Abhängigkeit von ihrer zeitlichen Gültigkeit als valide einzustufen. Die gültige Zeitdimension wird dabei durch das Beginn- und Endedatum oder
den Zeitstempel des Zeitraums bestimmt.
Einsatzszenarien könnten beispielsweise das Gültigkeitsdatum der Deckung für eine Versicherungspolice, Kündigungsdaten eines Mitarbeiters in einer Personalanwendung, oder auch das
Gültigkeitsdatum einer Adressänderung für einen Kunden oder Klienten sein. Quasi jede Anwendung, bei der es wichtig ist zu wissen, wann bestimmte Daten
gültig wurden (aus der Sicht der Anwendung) und wann sie ungültig wurden. Ein weiteres Beispiel wäre Datenkorrekturen, bei denen falsche Daten aufbewahrt werden müssen
und mit dem Zeitraum gekennzeichnet werden, in dem sie als gültig erachtet wurden, und bei der die richtigen Daten als aktuell gültig angesehen werden müssen.
Temporal Validity Abfragen werden typischerweise im Zusammenhang mit der Oracle Flashback-Technologie verwendet. So gibt es AS OF- und VERSIONS BETWEEN
Abfragen, die den gültigen Zeitraum angeben. Darüberhinaus gibt es die Möglichkeit mit DBMS_FLASHBACK_ARCHIVE auf Session Ebene die Sichtbarkeit von Tabellendaten
zu einem bestimmten Zeitpunkt anzugeben.
Die Implementierung ist ganz einfach. Die Datenbanktabelle wird &uul;ber CREATE TABLE oder ALTER TABLE mit einem speziellen Attribut ausgestattet werden. Dieses fügt zusätzliche Spalten hinzu,
damit man das entsprechende Anfangs- und Endedatum einfügen kann. Die Spalten sind dabei auf "invisible" (unsichtbar) gesetzt. Zur Erinnerung:
Sobald eine Spalte einer Tabelle invisible ist, ist sie bei SELECT * FROM, DESCRIBE Anweisung oder in einer %ROWTYPE in PL/SQL nicht sichtbar.
Unsichtbare (invisible) Spalten stehen allerdings weiterhin für die Indizierung zur Verfügung.
Im Folgenden werden die Funktionen an Beispielen illustriert. Dabei wird eine Tabelle EMPLOYEES verwendet, die man beispielsweise hier laden kann.
Es wird zuerst eine Testtabelle erzeugt, die aus 10 Zeilen der EMPLOYEES Tabelle besteht.
SQL> create table ot.emp_valid as
select employee_id, first_name, last_name
from ot.employees
where rownum<=10;
Table OT.EMP_VALID created.
SQL> select * from ot.emp_valid;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- --------------------
107 Summer Payne
106 Rose Stephens
101 Annabelle Dunn
1 Tommy Bailey
3 Blake Cooper
2 Jude Rivera
11 Tyler Ramirez
10 Ryan Gray
14 Elliot Brooks
12 Elliott James
10 rows selected.
Im nächsten Schritt schalten wir die Funktion Temporal Validity ein. Dies kann entweder mit einem CREATE TABLE oder auch nachträglich mit ALTER TABLE durchgeführt werden. Dabei ist es möglich entweder die Spalten selbst zu benennen oder dies Oracle zu überlassen. In unserem Fall wird ALTER TABLE ohne Angabe der Spaltennamen verwendet. Wie zu erkennen ist, wurden 3 invisible Spalten hinzugefügt. Um diese Spalten anzuzeigen, kann man in SQL Developer oder SQL*Plus die Variable COLINVISIBLE auf ON setzen.
SQL> alter table ot.emp_valid add PERIOD FOR VALID_TIME; Table OT.EMP_VALID altered. SQL> desc ot.emp_valid Name Null? Type ----------- -------- ------------- EMPLOYEE_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(255) LAST_NAME NOT NULL VARCHAR2(255) SQL> set colinvisible on SQL> desc ot.emp_valid; Name Null? Type ---------------------------- -------- --------------------------- EMPLOYEE_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(255) LAST_NAME NOT NULL VARCHAR2(255) VALID_TIME_START (INVISIBLE) TIMESTAMP(6) WITH TIME ZONE VALID_TIME_END (INVISIBLE) TIMESTAMP(6) WITH TIME ZONE VALID_TIME (INVISIBLE) NUMBER(38)Vorbereitung
Um Temporal Validity zu verwenden, werden im ersten Schritt die Zeitspannen definiert. Dazu wird ein Anfangs- und Endedatum eingegeben. Belässt man den Wert NULL, bleibt das Datum offen.
SQL> select first_name,
to_char(valid_time_start,'dd-mon-yyyy') "Start",
to_char(valid_time_end,'dd-mon-yyyy') "End"
from ot.emp_valid;
FIRST_NAME Start End
-------------------- -------------------- --------------------
Summer
Rose
Annabelle
Tommy
Blake
Jude
Tyler
Ryan
Elliot
Elliott
10 rows selected.
SQL> update ot.emp_valid
set valid_time_start = to_date('01-JUN-1995','dd-MON-yyyy'), valid_time_end = to_date('15-SEP-2010','dd-MON-yyyy')
where first_name in ('Summer','Rose','Annabelle','Tommy','Blake');
5 rows updated.
SQL> update ot.emp_valid
set valid_time_start = to_date('01-AUG-1999','dd-MON-yyyy'), valid_time_end = to_date('01-MAR-2012','dd-MON-yyyy')
where first_name in ('Jude','Tyler');
3 rows updated.
SQL> update ot.emp_valid
set valid_time_start = to_date('20-MAY-1998','dd-MON-yyyy')
where first_name in ('Ryan','Elliot','Elliott');
2 rows updated.
SQL> commit;
Das Ergebnis sieht dann folgendermassen aus.
SQL> select first_name,
to_char(valid_time_start,'dd-mon-yyyy') "Start",
to_char(valid_time_end,'dd-mon-yyyy') "End"
from ot.emp_valid
order by 2;
FIRST_NAME Start End
-------------------- -------------------- --------------------
Jude 01-aug-1999 01-mar-2012
Tyler 01-aug-1999 01-mar-2012
Annabelle 01-jun-1995 15-sep-2010
Tommy 01-jun-1995 15-sep-2010
Rose 01-jun-1995 15-sep-2010
Summer 01-jun-1995 15-sep-2010
Blake 01-jun-1995 15-sep-2010
Ryan 20-may-1998
Elliot 20-may-1998
Elliott 20-may-1998
10 rows selected.
Temporal Validity in Aktion
Nun führen wir einige Abfragen auf die Tabelle durch und demonstrieren mit der erweiterten Syntax AS OF PERIOD FOR VALID_TIME wie man Zeilen
aus gültigen Zeiträumen selektieren kann. Das Resultat hängt immer vom validen Anfangs- und Endedatum ab.
Im ersten Beispiel selektieren wir die Zeilen in gültigen Zeiträumen mit Endedatum 11. Juni 2011. Das Resultat enthält folgende 5 Zeilen. Die Zeilen mit Endedatum NULL werden natürlich auch ausgegeben.
SQL> select first_name,
to_char(valid_time_start,'dd-mon-yyyy') "Start",
to_char(valid_time_end,'dd-mon-yyyy') "End"
from ot.emp_valid
AS OF PERIOD FOR VALID_TIME to_date('01-JUN-2011')
order by 2;
FIRST_NAME Start End
-------------------- -------------------- --------------------
Tyler 01-aug-1999 01-mar-2012
Jude 01-aug-1999 01-mar-2012
Elliot 20-may-1998
Elliott 20-may-1998
Ryan 20-may-1998
Im nächsten Beispiel wird die Zeitspanne mit Anfangs- und Endezeitpunkt angegeben. Hierbei findet die Syntax-Erweiterung VERSIONS PERIOD FOR VALID_TIME BETWEEN eine Verwendung. Das Ergebnis besteht dann aus folgenden 5 gültigen Zeilen.
SQL> select first_name,
to_char(valid_time_start,'dd-mon-yyyy') "Start",
to_char(valid_time_end,'dd-mon-yyyy') "End"
from ot.emp_valid
VERSIONS PERIOD FOR VALID_TIME BETWEEN
to_date('01-SEP-1995') and to_date('01-SEP-1996')
order by 2;
FIRST_NAME Start End
-------------------- -------------------- --------------------
Summer 01-jun-1995 15-sep-2010
Rose 01-jun-1995 15-sep-2010
Blake 01-jun-1995 15-sep-2010
Tommy 01-jun-1995 15-sep-2010
Annabelle 01-jun-1995 15-sep-2010
In den letzten Beispielen soll eine Session-Einstellung bewirken, dass nur bestimmte Zeilen ausgegeben werden. Dazu wird die Prozedur DBMS_FLASHBACK_ARCHIVE.ENABLE_VALID_TIME verwendet, die die 3 Argumente CURRENT, ALL und ASOF nutzt. ALL (dies ist übrigens der Default) zeigt dabei immer alle Zeilen an, CURRENT die zum aktuell gültigen Zeitpunkt und ASOF die ab dem angegebenen Zeitpunkt gültig sind. DISABLE_ASOF_VALID_TIME hebt die Einstellung wieder auf. Folgende Beispiele zeigen die Verwendung.
SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL');
PL/SQL procedure successfully completed.
SQL> select first_name,
to_char(valid_time_start,'dd-mon-yyyy') "Start",
to_char(valid_time_end,'dd-mon-yyyy') "End"
from ot.emp_valid;
FIRST_NAME Start End
-------------------- -------------------- --------------------
Summer 01-jun-1995 15-sep-2010
Rose 01-jun-1995 15-sep-2010
Annabelle 01-jun-1995 15-sep-2010
Tommy 01-jun-1995 15-sep-2010
Blake 01-jun-1995 15-sep-2010
Jude 01-aug-1999 01-mar-2012
Tyler 01-aug-1999 01-mar-2012
Ryan 20-may-1998
Elliot 20-may-1998
Elliott 20-may-1998
10 rows selected.
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');
PL/SQL procedure successfully completed.
SQL> select first_name,
to_char(valid_time_start,'dd-mon-yyyy') "Start",
to_char(valid_time_end,'dd-mon-yyyy') "End"
from ot.emp_valid;
FIRST_NAME Start End
-------------------- -------------------- --------------------
Ryan 20-may-1998
Elliot 20-may-1998
Elliott 20-may-1998
SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF', '01-SEP-1996');
PL/SQL procedure successfully completed.
SQL> select first_name,
to_char(valid_time_start,'dd-mon-yyyy') "Start",
to_char(valid_time_end,'dd-mon-yyyy') "End"
from ot.emp_valid;
FIRST_NAME Start End
-------------------- -------------------- --------------------
Summer 01-jun-1995 15-sep-2010
Rose 01-jun-1995 15-sep-2010
Annabelle 01-jun-1995 15-sep-2010
Tommy 01-jun-1995 15-sep-2010
Blake 01-jun-1995 15-sep-2010
Weitere Informationen zum Thema findet man in den Handbüchern und unter folgenden Links.
Weitere Informationen
Zurück zur Community-Seite