CHANGES¶

Syntaxe¶

SELECT ...
FROM ...
   CHANGES ( INFORMATION => { DEFAULT | APPEND_ONLY } )
   AT ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) | 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 l’objet source, y compris les insertions, les mises à jour et les suppressions (y compris les troncatures de table). Ce type de suivi de modification effectue une comparaison entre 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 => timestamp

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

OFFSET => time_difference

Spécifie la différence en secondes par rapport au temps actuel à utiliser pour Time Travel, sous la forme -N où N 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

STREAM => 'name'

Spécifie l’identificateur (c’est-à-dire le nom) d’un flux existant sur la table ou la vue interrogée. Le décalage actuel dans le flux est utilisé comme le point AT dans le temps pour renvoyer les données de changement pour l’objet source.

Notes sur l’utilisation¶

• La clause CHANGES n’est pas prise en charge lors de l’interrogation de modifications (qui sont résolues à l’aide de métadonnées de suivi des modifications) pour des tables de répertoire ou des tables externes.

• 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).

  Les deux options ajoutent des colonnes cachées à la table qui stockent les métadonnées de suivi des modifications. Les colonnes consomment une petite quantité de stockage.

  Pour interroger les données de modification d’une vue, le suivi des modifications doit être activé sur la vue source et ses tables sous-jacentes. Pour obtenir des instructions, voir Activation du suivi des modifications sur les vues et les tables sous-jacentes.

• 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 'start timestamp' variable for the current timestamp.
SET ts1 = (SELECT CURRENT_TIMESTAMP());

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

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

DELETE FROM t1;

-- Create a table populated by the change data between the start and end timestamps.
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 |
+-------+

L’exemple suivant est similaire à l’exemple précédent. Cet exemple utilise le décalage actuel d’un flux sur la table source comme point de départ dans le temps pour alimenter la nouvelle table avec les données de modification de la table source. Comme un flux est créé sur l’objet source, il n’est pas nécessaire d’activer explicitement le suivi des modifications sur l’objet :

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

-- Create a stream on the table.
CREATE OR REPLACE STREAM s1 ON TABLE t1;

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

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

DELETE FROM t1;

-- Create a table populated by the change data between the current
-- s1 offset and the end timestamp.
CREATE OR REPLACE TABLE t2 (
  c1 varchar(255) default NULL
  )
AS SELECT C1
  FROM t1
  CHANGES(INFORMATION => APPEND_ONLY)
  AT(STREAM => 's1')
  END(TIMESTAMP => $ts2);

SELECT * FROM t2;

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