グローバルテンポラリーテーブルを使ったSQLチューニング

oreno-admin

6年前

ここでは、Oracleでグローバルテンポラリーテーブルを使ったSQLチューニングについて紹介します。

グローバルテンポラリーテーブル(GLOBAL TEMPORARY TABLE)と言うと馴染みがない方も多いと思いますが、簡単に説明すると、一時的なワークテーブルです。テンポラリーテーブルという名前からも分かるかと思います。

2つの特徴

特徴は２つあります。

1つ目は、グローバルテンポラリーテーブルに登録したデータは、トランザクション／セッション終了時に、切り捨てられます。つまり跡形もなく消し去ります。

2つ目は、グローバルテンポラリーテーブルに登録したデータは、登録したセッションでのみ参照できます。例えば、プログラムAのセッションで登録した100件のデータは、プログラムBのセッションからは参照することが出来ません。もしプログラムBのセッションからSELECT文を発行しても0件の結果が返ってきます。

狙い目のプログラム

私のオススメは、オンラインバッチです。
データの登録が一時的で良く、セッションごとにデータを処理する必要がある、オンラインの処理には要件がマッチします。

もちろん、オンライン画面で利用することも可能ですが、オンラインバッチをオススメします。

ワークテーブルへの登録がある以上、少なからずオーバーヘッドがかかります。そのため、数分間の処理時間の猶予が与えられるオンラインバッチ処理の方が、要件にはマッチすると思います。

グローバルテンポラリーテーブルの使い方

例えば、次のSQLのように、たくさんの副問合せの利用や参照テーブルが多く、実行計画が適切でない場合などです。

SQLが複雑になればなるほど、テーブルの参照数が増えれば増えるほど、実行計画が適切でなくなるケースは多くなります。そこでこれらの副問合せをテーブルに置き換えます。

※１つのSQLで同じテーブルを何度も参照している場合に、グローバルテンポラリーテーブルに置き換えることも有効です。

＞＞１つのSQLにテーブルは何個まで書いて良い？

◆副問合せを使って遅い場合

select
t1.*
from
(
SELECT a.* FROM table1 a
)t1
inner join (
SELECT a.* FROM table2 a
)t2
on ( t1.col1 = t2.col1)
inner join (
SELECT a.* FROM table3 a
) t3
on ( t1.col1 = t3.col1)
inner join (
SELECT a.* FROM table4 a
)t4
on ( t1.col1 = t4.col1)
inner join (
SELECT
a.*
FROM
table5 a
inner join (select b.* from table6 b) b
on (a.col1 = b.col1)
)t5
on ( t1.col1 = t5.col1)
;

もちろん、事前にグローバルテンポラリーテーブルを作成しておき、処理実行時にINSERT文を実行しデータを登録します。
そして登録したテーブルから、データをSELECTします。

◆置き換えの例

–ワークテーブルへのデータ登録
insert into t1 select * from table1;
insert into t2 select * from table2;
insert into t3 select * from table3;
insert into t4 select * from table4;
insert into t5 select * from (
SELECT
a.*
FROM
table5 a
inner join (select b.* from table6 b) b
on (a.col1 = b.col1)
)t5;

–対象データの抽出
select
t1.*
from
t1
inner join t2
on ( t1.col1 = t2.col1)
inner join t3
on ( t1.col1 = t3.col1)
inner join t4
on ( t1.col1 = t4.col1)
inner join t5
on ( t1.col1 = t5.col1)
;

作成方法

グローバルテンポラリーテーブルの作成は下記のようなSQLで簡単に作成できます。

create global temporary table テーブル名
(カラム名データ型・・・)
on commit [delete rows / preserve rows];

まとめ

これはあくまで私見ですが、グローバルテンポラリーテーブルは、バッチ処理とオンライン処理の間に位置する、オンラインバッチ処理にこそ向いていると思います。少しニッチなものではありますが、用法さえ間違わなければかなり便利なものだと思います。

最後に

最後までお読みいただきありがとうございます。

SQLのチューニングについては、「SELECT文のSQLチューニング方法まとめ」で詳しく解説していますので、ぜひ参考にしてください。