Syntaxe et horodatages de la requête de déplacement dans le temps

Syntaxe de requête

Une requête SELECT avec une ou plusieurs clauses temporelles est une requête de déplacement dans le temps. Les requêtes de déplacement dans le temps peuvent apparaître sous la forme de sub-SELECTs dans les instructions INSERT, UPDATE, DELETE, MERGE ou CREATE TABLE AS SELECT (CTAS).

En outre, les requêtes de déplacement temporel peuvent apparaître dans une définition de vue (CREATE VIEW, avec ou sans OR REPLACE) ou dans une définition de procédure mémorisée (CREATE PROCEDURE, avec ou sans OR REPLACE). Dans les deux cas, les expressions d'horodatage dans la syntaxe (par exemple, CURRENT_TIMESTAMP - INTERVAL ‘1 day’) ne sont pas évaluées au moment de la définition de la vue ou de la procédure, mais au moment où un utilisateur ou une application interroge la vue ou appelle la procédure.

Toute référence de table de base (nom de table, avec ou sans nom de base de données et de schéma, et avec ou sans alias) dans une instruction SELECT ou sub-SELECT peut comporter une clause temporelle facultative, composée des mots clés FOR SYSTEM_TIME suivis de l'une des valeurs suivantes:

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

Chaque TIMESTAMP EXPRESSION doit être l'une des suivantes:

Valeur d'horodatage littérale. Par exemple, ‘2022-10-31 20:00:00’.
Paramètre de requête ou variable hôte dont la valeur est un horodatage.
Fonction intégrée qui renvoie ou convertit implicitement en horodatage. Par exemple, CURRENT_DATE, CURRENT_TIMESTAMP ou (de manière équivalente) NOW(), ou CURRENT_TIMESTAMP(subsecond-digits) ou (de manière équivalente) NOW(subsecond-digits).
Expression qui a pour résultat un horodatage unique pour toutes les lignes de la table. Par exemple, CURRENT_TIMESTAMP - INTERVAL ‘1 day’. L'expression ne peut pas faire référence à des colonnes de table ou à une fonction non déterministe (par exemple, RANDOM()) ou être une sous-instruction SELECT.
L'identificateur spécial RETENTION_START_TIMESTAMP, dans les cas particuliers de AS OF, BETWEEN et FROM (mais pas BEFORE, AND ou TO). Il s'agit de l'horodatage de début de conservation, qui correspond à l'horodatage d'insertion de ligne le plus ancien possible ou à l'horodatage de suppression disponible pour les requêtes de déplacement temporel. Pour plus d'informations sur les horodatages de début de conservation, l'insertion d'horodatages et la suppression d'horodatages, voir Timestamps dans les requêtes de déplacement dans le temps.

En date du

Vous pouvez utiliser la sous-clause AS OF lorsque vous souhaitez extraire l'état de vos données tel qu'il était à un moment spécifique dans le passé.

Syntaxe	Description
AS DE < EXPRESSION D'HORODATAGE 1 >	Inclut toutes les lignes qui étaient valides à l'horodatage que l'expression TIMESTAMP EXPRESSION 1 évalue, dont l'horodatage d'insertion est inférieur ou égal à l'expression TIMESTAMP EXPRESSION 1, et dont l'horodatage de suppression est NULL ou supérieur à l'expression TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 est inférieur à l'horodatage de début de conservation de la table, une erreur est renvoyée.

Syntaxe

Description

AS DE < EXPRESSION D'HORODATAGE 1 >

Inclut toutes les lignes qui étaient valides à l'horodatage que l'expression TIMESTAMP EXPRESSION 1 évalue, dont l'horodatage d'insertion est inférieur ou égal à l'expression TIMESTAMP EXPRESSION 1, et dont l'horodatage de suppression est NULL ou supérieur à l'expression TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 est inférieur à l'horodatage de début de conservation de la table, une erreur est renvoyée.

AVANT

Vous pouvez utiliser la sous-clause BEFORE lorsque vous souhaitez extraire l'état de vos données tel qu'il était juste avant une heure spécifique dans le passé.

Syntaxe	Description
Avant<TIMESTAMP EXPRESSION 1>	Inclut toutes les lignes qui étaient valides juste avant l'horodatage évalué par TIMESTAMP EXPRESSION 1. Dont l'horodatage d'insertion est strictement inférieur à TIMESTAMP EXPRESSION 1 et dont l'horodatage de suppression est NULL ou est supérieur à TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 est inférieur ou égal à l'horodatage de début de conservation de la table, une erreur est renvoyée.

Syntaxe

Description

Avant<TIMESTAMP EXPRESSION 1>

Inclut toutes les lignes qui étaient valides juste avant l'horodatage évalué par TIMESTAMP EXPRESSION 1. Dont l'horodatage d'insertion est strictement inférieur à TIMESTAMP EXPRESSION 1 et dont l'horodatage de suppression est NULL ou est supérieur à TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 est inférieur ou égal à l'horodatage de début de conservation de la table, une erreur est renvoyée.

DE ...TO et BETWEEN ...ET

Vous pouvez utiliser l'option FROM ...A et ENTRE ...Sous-clauses AND pour l'audit de données ou l'analyse de tendance. Utilisez-la, lorsque vous avez besoin d'obtenir toutes les transformations historiques, pour certaines ou toutes les lignes, sur une période donnée.

Syntaxe	Description
FROM < TIMESTAMP EXPRESSION 1 > TO < TIMESTAMP EXPRESSION 2 >	Inclut toutes les lignes qui étaient valides à tout moment de TIMESTAMP EXPRESSION 1 à TIMESTAMP EXPRESSION 2 (exclusif), dont l'horodatage d'insertion est strictement inférieur à TIMESTAMP EXPRESSION 2 et dont l'horodatage de suppression est NULL ou supérieur à TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 ou TIMESTAMP EXPRESSION 2 est inférieur ou égal à l'horodatage de début de conservation de la table, une erreur est renvoyée. Si TIMESTAMP EXPRESSION 1 est supérieur ou égal à TIMESTAMP EXPRESSION 2, la requête ne génère aucune ligne.
Entre <TIMESTAMP EXPRESSION 1> et <TIMESTAMP EXPRESSION 2>	Inclut toutes les lignes qui étaient valides à tout moment entre TIMESTAMP EXPRESSION 1 et TIMESTAMP EXPRESSION 2 (inclus), dont l'horodatage d'insertion est inférieur ou égal à TIMESTAMP EXPRESSION 2 et dont l'horodatage de suppression est NULL ou supérieur à TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 ou TIMESTAMP EXPRESSION 2 est inférieur à l'horodatage de début de conservation de la table, une erreur est renvoyée. Si TIMESTAMP EXPRESSION 1 est supérieur à TIMESTAMP EXPRESSION 2, la requête ne génère aucune ligne.

Syntaxe

Description

FROM < TIMESTAMP EXPRESSION 1 > TO < TIMESTAMP EXPRESSION 2 >

Inclut toutes les lignes qui étaient valides à tout moment de TIMESTAMP EXPRESSION 1 à TIMESTAMP EXPRESSION 2 (exclusif), dont l'horodatage d'insertion est strictement inférieur à TIMESTAMP EXPRESSION 2 et dont l'horodatage de suppression est NULL ou supérieur à TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 ou TIMESTAMP EXPRESSION 2 est inférieur ou égal à l'horodatage de début de conservation de la table, une erreur est renvoyée. Si TIMESTAMP EXPRESSION 1 est supérieur ou égal à TIMESTAMP EXPRESSION 2, la requête ne génère aucune ligne.

Entre <TIMESTAMP EXPRESSION 1> et <TIMESTAMP EXPRESSION 2>

Inclut toutes les lignes qui étaient valides à tout moment entre TIMESTAMP EXPRESSION 1 et TIMESTAMP EXPRESSION 2 (inclus), dont l'horodatage d'insertion est inférieur ou égal à TIMESTAMP EXPRESSION 2 et dont l'horodatage de suppression est NULL ou supérieur à TIMESTAMP EXPRESSION 1. Si TIMESTAMP EXPRESSION 1 ou TIMESTAMP EXPRESSION 2 est inférieur à l'horodatage de début de conservation de la table, une erreur est renvoyée. Si TIMESTAMP EXPRESSION 1 est supérieur à TIMESTAMP EXPRESSION 2, la requête ne génère aucune ligne.

Horodatages dans les requêtes de déplacement dans le temps

Intervalle de temps de conservation et durée de conservation

L'intervalle de temps de conservation d'une table définit le nombre de jours après la suppression des horodatages pour lesquels des lignes historiques (supprimées) sont disponibles pour les requêtes de déplacement dans le temps. A tout moment, la durée de conservation se termine à l'horodatage en cours (date et heure) et s'étend sur le nombre de jours indiqué. Il s'agit d'une fenêtre temporelle glissante qui avance au fur et à mesure que l'heure système en cours avance.

Limite inférieure de conservation

Dans la plupart des cas, la limite inférieure de conservation d'une table correspond à la date et à l'heure auxquelles la table a été définie comme table temporelle. Il se peut que vous ayez exécuté la commande CREATE TABLE ou que la dernière fois que vous avez modifié la valeur DATA_VERSION_RETENTION_TIME de la table soit passée de zéro à une valeur différente de zéro.

Horodatage de début de la conservation

Lors de la définition d'une table pour qu'elle soit temporelle (lorsque la limite inférieure de conservation est définie), aucune ligne d'historique n'est disponible sur la période de conservation. Pour capturer la notion de la disponibilité réelle des lignes d'historique de retour arrière (visibles pour les requêtes de parcours temporel), l'horodatage de début de conservation d'une table est défini. L'horodatage de début de conservation correspond à la plus grande des valeurs suivantes:

Début de la période de conservation (date / heure en cours moins l'intervalle de conservation).
Limite inférieure de conservation.

L'horodatage de début de conservation d'une table est pris en compte dans les opérations suivantes:

Requêtes de temps de parcours (SELECT et sub-SELECT)

Si vous tentez d'exécuter des requêtes pour des lignes d'historique qui ont été supprimées avant l'horodatage de début de conservation, une erreur est renvoyée.

Si vous souhaitez interroger les données d'historique aussi loin que possible, vous pouvez utiliser le mot clé RETENTION_START_TIMESTAMP dans les requêtes de voyage dans le temps. Dans ce cas, vous pouvez éviter d'avoir à essayer de calculer vous-même l'horodatage approprié. Par extension, vous éliminez le risque d'erreur si la valeur s'avère trop ancienne (plus ancienne que l'horodatage de début de conservation).
GROOM TABLE Les lignes d'historique qui ont été supprimées avant l'horodatage de début de conservation ne sont plus nécessaires pour les requêtes de déplacement temporel et peuvent être récupérées.

Horodatages de ligne et validité

L'horodatage d'insertion d'une ligne en cours ou historique est la date / heure à laquelle la transaction insère la ligne validée. Il ne s'agit pas de l'heure à laquelle une instruction INSERT, UPDATE ou MERGE particulière qui a inséré la ligne a été exécutée.

Si la transaction d'insertion d'une ligne a été validée avant l'horodatage de début de conservation, la ligne est traitée comme ayant été insérée à l'horodatage de début de conservation. Cela ne s'applique généralement qu'aux lignes existantes au moment de la modification d'une table non temporelle en table temporelle.

Une ligne insérée dont la transaction n'a pas encore été validée n'a pas d'horodatage d'insertion. Une telle ligne ne sera jamais visible pour une requête de déplacement dans le temps.

Dans une requête de déplacement dans le temps, vous pouvez sélectionner l'horodatage d'insertion à l'aide de la colonne virtuelle _SYS_START d'une table temporelle.

L'horodatage de suppression d'une ligne d'historique correspond à la date / heure à laquelle la transaction de suppression de la ligne a été validée. Il ne s'agit pas de l'heure à laquelle une instruction DELETE, UPDATE, MERGE ou TRUNCATE particulière qui a supprimé la ligne a été exécutée.

Si une table temporelle est tronquée, les lignes de la table existante sont disponibles pour les requêtes de parcours temporel et sont traitées comme ayant été supprimées lors de la validation de la transaction de troncature.

Si la transaction de suppression (ou de troncature) a été validée avant l'horodatage de début de conservation, une ligne supprimée est traitée comme ayant été supprimée à l'horodatage de début de conservation. Cela ne s'applique généralement qu'aux lignes supprimées existantes lors de la modification d'une table non temporelle en table temporelle ; ces lignes ne sont pas visibles pour les requêtes de déplacement temporel sur la table.

Une ligne d'historique peut être visible par une requête temporelle sur la table si son horodatage de suppression tombe dans la période de conservation de la table. Si cette condition est vraie, la ligne d'historique ne peut pas être supprimée (avec GROOM TABLE) de la table.

L'horodatage de suppression d'une ligne en cours (non supprimée ou marquée pour suppression mais non validée) est NULL.

Dans une requête de déplacement dans le temps, vous pouvez sélectionner l'horodatage de suppression à l'aide de la colonne virtuelle _SYS_END d'une table temporelle.

Une ligne d'historique est considérée comme valide depuis son horodatage d'insertion jusqu'à juste avant l'horodatage de suppression. Une ligne en cours est considérée comme valide à partir de son horodatage d'insertion en aval. Les requêtes de déplacement temporel utilisent des horodatages ou des expressions d'horodatage pour ne renvoyer que les lignes (actuelles ou historiques) qui sont valides à un moment donné ou à tout moment au cours d'une période donnée.

Les horodatages d'insertion et de suppression des lignes récemment insérées et supprimées peuvent ne pas être disponibles pour les requêtes de temps de trajet jusqu'à une courte durée (généralement inférieure à 3 minutes) après la validation des transactions d'insertion et de suppression.