Data / ora e sintassi della query di viaggio nel tempo

Sintassi della query

Una query SELECT con una o più clausole temporali è una query di viaggio nel tempo. Le query di viaggio nel tempo potrebbero essere visualizzate come sub - SELECT nelle istruzioni INSERT, UPDATE, DELETE, MERGE o CREATE TABLE AS SELECT (CTAS).

Inoltre, le query di viaggio temporale possono essere visualizzate in una definizione di vista (CREATE VIEW, con o senza OR REPLACE) o in una definizione di procedura memorizzata (CREATE PROCEDURE, con o senza OR REPLACE). In entrambi i casi, le espressioni di data / ora nella sintassi (ad esempio, CURRENT_TIMESTAMP - INTERVAL ‘1 day’) non vengono valutate al momento della definizione della vista o della procedura, ma al momento in cui un utente o un'applicazione interroga la vista o richiama la procedura.

Qualsiasi riferimento della tabella di base (il nome della tabella, con o senza il database e il nome dello schema e con o senza un alias) in SELECT o sub - SELECT potrebbe avere una clausola temporale facoltativa, costituita dalle parole chiave FOR SYSTEM_TIME seguite da uno dei seguenti valori:

AS OF <TIMESTAMP EXPRESSION>
BEFORE <TIMESTAMP EXPRESSION>
BETWEEN <TIMESTAMP EXPRESSION 1> AND <TIMESTAMP EXPRESSION 2>
FROM <TIMESTAMP EXPRESSION 1> TO <TIMESTAMP EXPRESSION 2>

Ogni TIMESTAMP EXPRESSION deve essere uno dei seguenti:

Un valore data / ora letterale. Ad esempio, ‘2022-10-31 20:00:00’.
Un parametro di query o una variabile host il cui valore è una data / ora.
Una funzione incorporata che restituisce o converte implicitamente in un timestamp. Ad esempio, CURRENT_DATE, CURRENT_TIMESTAMP o (in modo equivalente) NOW() o CURRENT_TIMESTAMP(subsecond-digits) o (in modo equivalente) NOW(subsecond-digits).
Un'espressione che assume il valore di una singola data/ora per tutte le righe nella tabella. Ad esempio, CURRENT_TIMESTAMP - INTERVAL ‘1 day’. L'espressione non può fare riferimento a colonne di tabella o a una funzione non deterministica (ad esempio, RANDOM()) o essere sub - SELECT.
L'identificativo speciale RETENTION_START_TIMESTAMP, nei casi particolari di AS OF, BETWEEN e FROM (ma non BEFORE, AND o TO). Si riferisce alla data / ora di inizio conservazione, che è la data / ora di inserimento riga più vecchia possibile o la data / ora di eliminazione disponibile per le query di viaggio nel tempo. Per ulteriori informazioni sulle date / ore di inizio della conservazione, inserire le date / ore ed eliminare le date / ore, consultare Data / ora nelle query di viaggio temporale.

Alla data

È possibile utilizzare la clausola secondaria AS OF quando si desidera richiamare lo stato dei dati come era in un momento specifico del passato.

Sintassi	Descrizione
AS OF < TIMESTAMP EXPRESSION 1>	Include tutte le righe valide alla data / ora in cui viene valutata TIMESTAMP EXPRESSION 1, la cui data / ora di inserimento è minore o uguale a TIMESTAMP EXPRESSION 1 e la cui data / ora di eliminazione è NULL o maggiore di TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 è inferiore alla data / ora di inizio conservazione della tabella, viene restituito un errore.

Prima

È possibile utilizzare la clausola secondaria BEFORE quando si desidera richiamare lo stato dei dati come era prima di qualsiasi ora specifica nel passato.

Sintassi	Descrizione
Prima<TIMESTAMP EXPRESSION 1>	Include tutte le righe valide appena prima della data / ora in cui TIMESTAMP EXPRESSION 1 assume il valore. La cui data / ora di inserimento è strettamente inferiore a TIMESTAMP EXPRESSION 1 e la cui data / ora di eliminazione è NULL o è maggiore di TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 è inferiore o uguale alla data / ora di inizio della conservazione della tabella, viene restituito un errore.

Sintassi

Descrizione

Prima<TIMESTAMP EXPRESSION 1>

Include tutte le righe valide appena prima della data / ora in cui TIMESTAMP EXPRESSION 1 assume il valore. La cui data / ora di inserimento è strettamente inferiore a TIMESTAMP EXPRESSION 1 e la cui data / ora di eliminazione è NULL o è maggiore di TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 è inferiore o uguale alla data / ora di inizio della conservazione della tabella, viene restituito un errore.

DA ...TO e BETWEEN ...AND

È possibile utilizzare DA ...TO e BETWEEN ...Clausole secondarie AND per il controllo dei dati o l'analisi delle tendenze. Utilizzarlo quando è necessario ottenere tutte le trasformazioni cronologiche, per alcune o tutte le righe, in un periodo di tempo.

Sintassi	Descrizione
DA <ESPRESSIONE DEL TEMPO 1> A <ESPRESSIONE DEL TEMPO 2	Include tutte le righe valide in qualsiasi momento da TIMESTAMP EXPRESSION 1 a TIMESTAMP EXPRESSION 2 (exclusive), la cui data / ora di inserimento è rigorosamente inferiore a TIMESTAMP EXPRESSION 2 e la cui data / ora di eliminazione è NULL o maggiore di TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 o TIMESTAMP EXPRESSION 2 è inferiore o uguale alla data / ora di inizio della conservazione della tabella, viene restituito un errore. Se TIMESTAMP EXPRESSION 1 è maggiore o uguale a TIMESTAMP EXPRESSION 2, la query non produce alcuna riga.
Tra <TIMESTAMP EXPRESSION 1> e <TIMESTAMP EXPRESSION 2>	Include tutte le righe che erano valide in qualsiasi momento tra TIMESTAMP EXPRESSION 1 e TIMESTAMP EXPRESSION 2 (incluso), la cui data/ora di inserimento è minore o uguale a TIMESTAMP EXPRESSION 2 e la cui data / ora di eliminazione è NULL o maggiore di TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 o TIMESTAMP EXPRESSION 2 è inferiore alla data / ora di inizio conservazione della tabella, viene restituito un errore. Se TIMESTAMP EXPRESSION 1 è maggiore di TIMESTAMP EXPRESSION 2, la query non produce alcuna riga.

Sintassi

Descrizione

DA <ESPRESSIONE DEL TEMPO 1> A <ESPRESSIONE DEL TEMPO 2

Include tutte le righe valide in qualsiasi momento da TIMESTAMP EXPRESSION 1 a TIMESTAMP EXPRESSION 2 (exclusive), la cui data / ora di inserimento è rigorosamente inferiore a TIMESTAMP EXPRESSION 2 e la cui data / ora di eliminazione è NULL o maggiore di TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 o TIMESTAMP EXPRESSION 2 è inferiore o uguale alla data / ora di inizio della conservazione della tabella, viene restituito un errore. Se TIMESTAMP EXPRESSION 1 è maggiore o uguale a TIMESTAMP EXPRESSION 2, la query non produce alcuna riga.

Tra <TIMESTAMP EXPRESSION 1> e <TIMESTAMP EXPRESSION 2>

Include tutte le righe che erano valide in qualsiasi momento tra TIMESTAMP EXPRESSION 1 e TIMESTAMP EXPRESSION 2 (incluso), la cui data/ora di inserimento è minore o uguale a TIMESTAMP EXPRESSION 2 e la cui data / ora di eliminazione è NULL o maggiore di TIMESTAMP EXPRESSION 1. Se TIMESTAMP EXPRESSION 1 o TIMESTAMP EXPRESSION 2 è inferiore alla data / ora di inizio conservazione della tabella, viene restituito un errore. Se TIMESTAMP EXPRESSION 1 è maggiore di TIMESTAMP EXPRESSION 2, la query non produce alcuna riga.

Timestamp nelle query di viaggio nel tempo

Intervallo di tempo di conservazione e periodo di tempo di conservazione

L'intervallo di tempo di conservazione di una tabella definisce il numero di giorni dopo la data / ora di eliminazione in cui le righe cronologiche (eliminate) sono disponibili per le query di viaggio nel tempo. In qualsiasi momento, il periodo di conservazione termina alla data / ora corrente (data e ora) e si estende indietro del numero di giorni specificato. Questa è una finestra temporale scorrevole che avanza come avanza l'ora del sistema corrente.

Limite inferiore di conservazione

Per la maggior parte, il limite inferiore di conservazione di una tabella è la data e l'ora in cui la tabella è stata definita come tabella temporale. Ciò potrebbe essere avvenuto quando è stato eseguito il comando CREATE TABLE o l'ultima volta che è stato modificato il valore DATA_VERSION_RETENTION_TIME della tabella da zero a diverso da zero.

Data / ora di inizio conservazione

Al momento della definizione di una tabella in modo che sia temporale (quando è definito il limite inferiore di conservazione), non sono disponibili righe cronologiche nel periodo di tempo di conservazione. Per acquisire la nozione di quanto lontano sono effettivamente disponibili le righe cronologiche (visibili alle query di viaggio nel tempo), viene definita la data / ora di inizio della conservazione della tabella. La data / ora di inizio conservazione è il più grande dei seguenti valori:

L'inizio del periodo di tempo di conservazione (la data / ora corrente meno l'intervallo di conservazione).
Il limite inferiore di conservazione.

La data / ora di inizio della conservazione di una tabella entra in vigore nelle seguenti operazioni:

Query di viaggio nel tempo (SELECT e sub - SELECT)

Se si tenta di eseguire le query per le righe cronologiche che sono state eliminate prima della data / ora di inizio della conservazione, viene restituito un errore.

Se si desidera eseguire la query dei dati cronologici il più lontano possibile, è possibile utilizzare la parola chiave RETENTION_START_TIMESTAMP nelle query di viaggio nel tempo. Se lo fai, puoi evitare di dover provare a calcolare da solo la data / ora corretta. Per estensione, si elimina il rischio di incorrere in un errore se il valore risulta essere troppo vecchio (più vecchio della data / ora di inizio conservazione).
GROOM TABLE Le righe cronologiche che sono state eliminate prima della data / ora di inizio della conservazione non sono più necessarie per le query di viaggio nel tempo e possono essere recuperate.

Data / ora e validità riga

La data / ora di inserimento di una riga corrente o cronologica è la data / ora in cui è stato eseguito il commit della transazione che inserisce la riga. Non è l'ora in cui è stata eseguita una particolare istruzione INSERT, UPDATE o MERGE che ha inserito la riga.

Se la transazione di inserimento per una riga di cui è stato eseguito il commit prima della data / ora di inizio della conservazione, la riga viene considerata come se fosse stata inserita alla data / ora di inizio della conservazione. Ciò generalmente si applica solo alle righe esistenti al momento della modifica di una tabella non temporale in una tabella temporale.

Una riga inserita la cui transazione non ha ancora eseguito il commit non ha una data / ora di inserimento. Tale riga non sarà mai visibile a una query di viaggio nel tempo.

In una query di viaggio nel tempo, è possibile selezionare la data / ora di inserimento utilizzando la colonna virtuale _SYS_START di una tabella temporale.

La data / ora di eliminazione di una riga cronologica è la data / ora in cui la transazione ha eliminato la riga di cui è stato eseguito il commit. Non è l'ora in cui è stata eseguita una particolare istruzione DELETE, UPDATE, MERGE o TRUNCATE che ha eliminato la riga.

Se una tabella temporale viene troncata, le righe della tabella esistenti sono disponibili per le query di viaggio nel tempo e vengono considerate come eliminate al momento del commit della transazione di troncamento.

Se la transazione di eliminazione (o troncamento) è stata sottoposta a commit prima della data / ora di inizio conservazione, una riga eliminata viene considerata come se fosse stata eliminata alla data / ora di inizio conservazione. Ciò generalmente si applica solo alle righe eliminate esistenti al momento della modifica di una tabella non temporale in una tabella temporale; tali righe non sono visibili per le query di viaggio temporale rispetto alla tabella.

Una riga cronologica potrebbe essere visibile a una query temporale rispetto alla tabella se la relativa data / ora di eliminazione rientra nel periodo di conservazione della tabella. Se questa condizione è true, la riga cronologica non può essere rimossa (con GROOM TABLE) dalla tabella.

La data / ora di eliminazione di una riga corrente (non eliminata o contrassegnata per l'eliminazione ma non sottoposta a commit) è NULL.

In una query di viaggio nel tempo, è possibile selezionare la data / ora di eliminazione utilizzando la colonna virtuale _SYS_END di una tabella temporale.

Una riga cronologica viene considerata valida dalla data / ora di inserimento fino a poco prima della data / ora di cancellazione. Una riga corrente viene considerata valida dall'inoltro della data / ora di inserimento. Le query di viaggio temporale utilizzano le espressioni data / ora o data / ora per restituire solo le righe (correnti o cronologiche) che sono valide in un momento temporale o in un punto qualsiasi all'interno di un periodo di tempo.

La data / ora di inserimento ed eliminazione per le righe recentemente inserite ed eliminate potrebbe non essere disponibile per le query di viaggio nel tempo fino a un breve periodo di tempo (generalmente meno di 3 minuti) dopo l'inserimento e l'eliminazione del commit delle transazioni.