Catégories :

Syntaxe de requête

CHANGES

La clause CHANGES permet d’interroger les métadonnées de suivi des modifications d’une table dans un intervalle de temps spécifié sans avoir à créer un flux de table avec un décalage transactionnel explicite. Plusieurs requêtes peuvent récupérer les métadonnées de suivi des modifications entre différents points de départ et points de terminaison transactionnels.

Note

Soit le suivi des modifications doit être activé sur la table, soit un flux doit être créé sur la table. Pour plus de détails, voir les notes sur l’utilisation (dans ce chapitre).

Dans une requête, la clause CHANGES est spécifiée dans la clause FROM immédiatement après le nom de la table.

Le mot clé END facultatif spécifie l’horodatage de fin de l’intervalle de modification.

Syntaxe

SELECT ...
FROM ...
   CHANGES ( INFORMATION => { DEFAULT | APPEND_ONLY } )
   AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) | BEFORE( STATEMENT => <id> )
   [ END( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
[ ... ]
INFORMATION => { DEFAULT | APPEND_ONLY }

Spécifie le type de données de suivi des modifications à renvoyer en fonction des métadonnées enregistrées dans chacune :

DEFAULT

Renvoie toutes les modifications DML de la table source, y compris les insertions, les mises à jour et les suppressions (y compris les troncatures de table). Ce type de données de suivi de modification effectue une jointure sur les lignes insérées et supprimées dans le jeu de modifications pour fournir le delta de niveau ligne. En tant qu’effet net, par exemple, une ligne qui est insérée puis supprimée entre deux points de temps transactionnels dans une table est supprimée dans le delta (c’est-à-dire qu’elle n’est pas renvoyée dans les résultats de la requête).

APPEND_ONLY

Renvoie uniquement les lignes ajoutées ; par conséquent, aucune jointure n’est effectuée. Par conséquent, l’interrogation des modifications ajoutées uniquement peut être beaucoup plus performante que l’interrogation des modifications standard (par défaut) pour l’extraction, le chargement, la transformation (ELT) et des scénarios similaires qui dépendent exclusivement des insertions de ligne.

TIMESTAMP => horodatage

Spécifie une date et une heure exactes à utiliser pour Time Travel. Notez que la valeur doit être explicitement convertie en TIMESTAMP.

OFFSET => différence_heure

Spécifie la différence en secondes par rapport au temps actuel à utiliser pour Time Travel, sous la forme -NN peut être un entier ou une expression arithmétique (par exemple, -120 correspond à 120 secondes, -30*60 correspond à 1800 secondes ou 30 minutes).

STATEMENT => id

Spécifie l’ID d’une requête à utiliser comme point de référence pour Time Travel. Ce paramètre prend en charge toute instruction de l’un des types suivants :

  • DML (par ex. INSERT, UPDATE, DELETE)

  • TCL (transaction BEGIN, COMMIT)

  • SELECT

Notes sur l’utilisation

  • Actuellement, au moins l’un des éléments suivants doit correspondre avant que les métadonnées de suivi des modifications soient enregistrées pour une table :

    • Le suivi des modifications est activé sur la table (en utilisant ALTER TABLE … CHANGE_TRACKING = TRUE).

    • Un flux est créé pour la table (à l’aide de CREATE STREAM).

    L’une ou l’autre option ajoute une paire de colonnes masquées à la table et commence à stocker les métadonnées de suivi des modifications. Les colonnes consomment une petite quantité de stockage.

    Aucune métadonnée de suivi des modifications du tableau n’est disponible pendant la période précédant la satisfaction de l’une de ces conditions.

  • La clause AT | BEFORE est obligatoire et définit le décalage actuel pour les métadonnées de suivi des modifications.

  • La clause END facultative définit l’horodatage de fin de l’intervalle de modification. Si aucune valeur END n’est spécifiée, l’horodatage actuel est utilisé comme fin de l’intervalle de modification.

    Notez que la clause END n’est valide que lorsqu’elle est combinée avec la clause CHANGES pour interroger les métadonnées de suivi des modifications (c’est-à-dire que cette clause ne peut pas être combinée avec AT|BEFORE lors de l’utilisation de Time Travel pour interroger des données historiques pour d’autres objets).

  • La valeur de TIMESTAMP ou OFFSET doit être une expression constante.

  • La résolution temporelle la plus petite pour TIMESTAMP est exprimée en millisecondes.

  • Si les données demandées sont au-delà de la période de conservation de Time Travel (1 jour par défaut), l’instruction échoue.

    De plus, si les données demandées se trouvent dans la période de conservation de Time Travel, mais qu’aucune donnée historique n’est disponible (par exemple, si la période de conservation a été prolongée), l’instruction échoue.

Exemples

L’exemple suivant interroge les métadonnées de suivi des modifications standard (delta) et à ajouter uniquement pour une table. Aucune valeur END() n’est fournie, donc l’horodatage actuel est utilisé comme point de terminaison dans l’intervalle de temps transactionnel :

 CREATE OR REPLACE TABLE t1 (
   id number(8) NOT NULL,
   c1 varchar(255) default NULL
 );

-- Enable change tracking on the table.
 ALTER TABLE t1 SET CHANGE_TRACKING = TRUE;

 -- Initialize a session variable for the current timestamp.
 SET ts1 = (SELECT CURRENT_TIMESTAMP());

 INSERT INTO t1 (id,c1)
 VALUES
 (1,'red'),
 (2,'blue'),
 (3,'green');

 DELETE FROM t1 WHERE id = 1;

 UPDATE t1 SET c1 = 'purple' WHERE id = 2;

 -- Query the change tracking metadata in the table during the interval from $ts1 to the current time.
 -- Return the full delta of the changes.
 SELECT *
 FROM t1
   CHANGES(INFORMATION => DEFAULT)
   AT(TIMESTAMP => $ts1);

 +----+--------+-----------------+-------------------+------------------------------------------+
 | ID | C1     | METADATA$ACTION | METADATA$ISUPDATE | METADATA$ROW_ID                          |
 |----+--------+-----------------+-------------------+------------------------------------------|
 |  2 | purple | INSERT          | False             | 1614e92e93f86af6348f15af01a85c4229b42907 |
 |  3 | green  | INSERT          | False             | 86df000054a4d1dc64d5d74a44c3131c4c046a1f |
 +----+--------+-----------------+-------------------+------------------------------------------+

 -- Query the change tracking metadata in the table during the interval from $ts1 to the current time.
 -- Return the append-only changes.
 SELECT *
 FROM t1
   CHANGES(INFORMATION => APPEND_ONLY)
   AT(TIMESTAMP => $ts1);

 +----+-------+-----------------+-------------------+------------------------------------------+
 | ID | C1    | METADATA$ACTION | METADATA$ISUPDATE | METADATA$ROW_ID                          |
 |----+-------+-----------------+-------------------+------------------------------------------|
 |  1 | red   | INSERT          | False             | 6a964a652fa82974f3f20b4f49685de54eeb4093 |
 |  2 | blue  | INSERT          | False             | 1614e92e93f86af6348f15af01a85c4229b42907 |
 |  3 | green | INSERT          | False             | 86df000054a4d1dc64d5d74a44c3131c4c046a1f |
 +----+-------+-----------------+-------------------+------------------------------------------+

L’exemple suivant consomme les modifications ajoutées uniquement pour une table à partir d’un point de temps transactionnel avant la suppression des lignes de la table :

CREATE OR REPLACE TABLE t1 (
  id number(8) NOT NULL,
  c1 varchar(255) default NULL
);

-- Enable change tracking on the table.
ALTER TABLE t1 SET CHANGE_TRACKING = TRUE;

-- Initialize a session variable for the current timestamp.
SET ts1 = (SELECT CURRENT_TIMESTAMP());

INSERT INTO t1 (id,c1)
VALUES
(1,'red'),
(2,'blue'),
(3,'green');

SET ts2 = (SELECT CURRENT_TIMESTAMP());

DELETE FROM t1;

CREATE OR REPLACE TABLE t2 (
  c1 varchar(255) default NULL
  )
AS SELECT C1
  FROM t1
  CHANGES(INFORMATION => APPEND_ONLY)
  AT(TIMESTAMP => $ts1)
  END(TIMESTAMP => $ts2);

SELECT * FROM t2;

+-------+
| C1    |
|-------|
| red   |
| blue  |
| green |
+-------+