一時表の作成について

【概要】
一時表とは、トランザクションの終了時、または、セッション終了時にデータが切り捨てられる表のことです。
複雑なクエリを複数のステップに分割する場合に利用します。

以下に一時表の簡単な作成方法を記載します。

【構文】

CREATE [ GLOBAL | LOCAL ] TEMPORARY | TEMP
… TABLE [ IF NOT EXISTS ] [[db-name.]schema.].table-name {
… ( Column-Definition (temp table) [ , … ] )
… | [ column-name-list (create table) ] }
… [ ON COMMIT { DELETE | PRESERVE } ROWS ]
… [ AS [ { AT EPOCH { integer | LATEST } | AT TIME ‘timestamp’} ]
… [ /*+ direct */ ] query ]
… [ ORDER BY table-column [ , … ] ]
… [ ENCODED BY column-definition [ , … ] ]
….[ Hash-Segmentation-Clause
……| UNSEGMENTED { NODE node
……| ALL NODES } ]
….[ KSAFE [ k-num ] ]
….| [ NO PROJECTION ] ]

【主なパラメータの説明】

その他のパラメータに関しては「SQL Reference Manual」を参照してください。

【作成例】
■更新されたデータがトランザクション内で有効である例

１．一時表作成（on commit delete rows)
dbadmin=> create global temporary table tmp_tbl(col1 int) on commit delete rows;
CREATE TABLE

２．一時表にデータを挿入します。
dbadmin=> insert into tmp_tbl values(1);
 OUTPUT
--------
      1
(1 row)

３．一時表を検索しデータが挿入されていることを確認します。
dbadmin=> select * from tmp_tbl;
 col1
------
    1
(1 row)

４．COMMITを行います。
dbadmin=> commit;
COMMIT

５．一時表を検索し挿入されたデータが削除されていることを確認します。
dbadmin=> select * from tmp_tbl;
 col1
------
(0 rows)

■更新されたデータがセッション内で有効である例

１．一時表作成（on commit preserve rows)
dbadmin=> create global temporary table tmp_tbl(col1 int) on commit preserve rows;
CREATE TABLE

２．一時表にデータを挿入します。
dbadmin=> insert into tmp_tbl values(1);
 OUTPUT
--------
      1
(1 row)

３．一時表を検索しデータが挿入されていることを確認します。
dbadmin=> select * from tmp_tbl;
 col1
------
    1
(1 row)

４．COMMITを行います。
dbadmin=> commit;
COMMIT

５．一時表を検索し挿入されたデータが存在することを確認します。
dbadmin=> select * from tmp_tbl;
 col1
------
    1
(1 row)

６．セッションを終了します。
dbadmin=> \q

７．セッションを開始します。
$ vsql -w dbadmin
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

８．一時表を検索し挿入されたデータが削除されていることを確認します。
dbadmin=> select * from tmp_tbl;
 col1
------
(0 rows)

■作成した一時表を他セッションでも使えるようにする例

＜セッションＡ＞

１．一時表作成（global)
dbadmin=> create global temporary table tmp_tbl(col1 int) on commit preserve rows;
CREATE TABLE

２．一時表にデータを挿入します。
dbadmin=> insert into tmp_tbl values(1);
 OUTPUT
--------
      1
(1 row)

３．COMMITを行います。
dbadmin=> commit;
COMMIT

４．一時表を検索し挿入されたデータが存在することを確認します。
dbadmin=> select * from tmp_tbl;
 col1
------
    1
(1 row)


＜セッションＢ＞

１．一時表を検索し挿入されたデータが存在しないことを確認します。
dbadmin=> select * from tmp_tbl;
 col1
------
(0 rows)

２．一時表にデータを挿入します。
dbadmin=> insert into tmp_tbl values(2);
 OUTPUT
--------
      1
(1 row)

３．COMMITを行います。
dbadmin=> commit;
COMMIT

４．一時表を検索し挿入されたデータが存在することを確認します。
dbadmin=> select * from tmp_tbl;
 col1
------
    2
(1 row)

■作成した一時表を該当セッションのみで使えるようにする例

＜セッションＡ＞

１．一時表作成（local)
dbadmin=> create local temporary table tmp_tbl(col1 int) on commit preserve rows;
CREATE TABLE

２．一時表にデータを挿入します。
dbadmin=> insert into tmp_tbl values(1);
 OUTPUT
--------
      1
(1 row)

３．COMMITを行います。
dbadmin=> commit;
COMMIT

４．一時表を検索し挿入されたデータが存在することを確認します。
dbadmin=> select * from tmp_tbl;
 col1
------
    1
(1 row)

＜セッションＢ＞

１．一時表を検索しても存在しないことを確認します。
dbadmin=> select * from tmp_tbl;
ERROR 4566:  Relation "tmp_tbl" does not exist

※一時表の最適化は行えません。

検証バージョンについて

この記事の内容はVertica 9.1で確認しています。