ここでは、ORACLE SQLでワークテーブルを使ったSQLのチューニング方法を紹介します。
遅いSQLや実行回数の多いSQLを確認する方法は↓で紹介していますので参考にしてください。
＞＞【SQL】長時間処理や遅いSQLを確認する方法
＞＞【SQL】実行回数の多いSQLを確認する方法
ワークテーブルを使ってSQLチューニングする方法
一般的に、ORACLEは１つのSQLでたくさんの副問合せがあったり、5つ以上のテーブルを参照したりすると、適切でない実行計画を作る可能性が高くなります。これまでの経験でもそうでした。ヒントが効かなくなることもありました。
しかし、現場のプログラムやSQLを見ると、たくさんの副問合せや５つ以下のケースって意外と少ないです。
特に業務アプリで、日々改修が入っているようなSQLだと１０とか２０、最近見たものだと４０くらいのテーブルを参照していました。当然、副問合せもたくさん紛れ込んでいました。
例えば、下記のSQLのように、複数の副問合せが存在したSQLやWITHを使ったSQLです。たくさんのテーブルを参照し、SQLが複雑になっている状態です。
特に副問合せやWITHの中で更に副問合せがされているケースは、実行計画がより複雑になって、遅くなっているケースがあります。
こういった場合に、ワークテーブルの活用がオススメです。
副問合せやWITHの代わりとなるワークテーブルを事前に作成しておきます。当該処理が実行されたタイミングで、ワークテーブルへデータを登録します。
そしてデータを登録したワークテーブルを参照してSELECT文を実行するように改修します。もちろん、処理の始めに、ワークテーブルをTRUNCATEすることも必要になります。
これによりSQLがシンプルに、簡潔になるため、オプティマイザが適切な実行計画を作成しやすくなり、結果的に処理時間の短縮が狙えます。バッチ処理などで、処理時間が長いSQLほど効果が出せると思います。
SQLが分割できるので、後々メンテするときに修正箇所が分かりやすいという副次的な効果もあります。
またどこかのSQLが遅くなれば、個別に対応できますし、実行計画もシンプルなので、メンテはしやすくなります。
副問合せを使って遅い場合
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)
;
WITHを使って遅い場合 例
t1 AS (
SELECT a.* FROM table1 a
)
,t2 AS (
SELECT a.* FROM table2 a
)
,t3 AS (
SELECT a.* FROM table2 a
)
,t4 AS (
SELECT a.* FROM table2 a
)
,t5 AS (
SELECT
a.*
FROM
table5 a
inner join (select b.* from table6 b) b
on (a.col1 = b.col1)
)
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)
;
置き換えの例
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のチューニング方法は↓で紹介していますので参考にしてください。
＞＞SELECT文のSQLチューニング方法まとめ