システム期間テンポラル表の作成

表の現行データと履歴データを保守するために使用される 1 対の表を定義できます。これらの表は、システム期間テンポラル表および履歴表と呼ばれます。

システム期間テンポラル表は、追加で 3 つのタイム・スタンプ列と 1 つのシステム期間が必要であることを除いて、他の任意の表と同じように定義されます。履歴表は、システム期間テンポラル表と同じ列を持つように定義されます。これら 2 つの表は、ALTER TABLE ステートメントを使用してバージョン管理関係で接続されます。

例えば、DEPARTMENT 表に対するすべての変更をトラッキングすると想定します。その場合、以下のように表を定義します。

CREATE OR REPLACE TABLE DEPARTMENT
      (DEPTNO    CHAR(3)       NOT NULL,
       DEPTNAME  VARCHAR(36)   NOT NULL,
       MGRNO     CHAR(6),
       ADMRDEPT  CHAR(3)       NOT NULL, 
       LOCATION  CHAR(16),
       START_TS  TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
       END_TS    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
       TS_ID     TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
      PERIOD SYSTEM_TIME (START_TS, END_TS),
      PRIMARY KEY (DEPTNO))

対応する履歴表は以下のように定義します。

CREATE TABLE DEPARTMENT_HIST LIKE DEPARTMENT

次に、以下のようにバージョン管理関係でこれらの表を結合します。

ALTER TABLE DEPARTMENT ADD VERSIONING USE HISTORY TABLE DEPARTMENT_HIST

システム期間テンポラル表にバージョン管理が定義されると、それ以降、その表に対して更新や削除が行われると、変更前バージョンの行が履歴表に 1 つの行として挿入されます。この履歴行のデータがアクティブなデータだった期間を示すため、行の開始と終了の特別なタイム・スタンプ列がシステムによって設定されます。

システム期間テンポラル表と履歴表の両方から自動的にデータを返す照会を作成できます。

例えば、DEPARTMENT 表が 6 カ月前はどうだったかを確認するには、次の照会を実行します。

SELECT * FROM DEPARTMENT FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP - 6 MONTHS