【SQL】WITHを使って遅い場合のチューニング方法


ここではORACLEデータベースのSQLでWITHを使って遅いSQLの特徴とチューニング方法を紹介します。

SQLの実行計画の見方や確認方法については↓で紹介していますので参考にしてください。
>>【ORACLE】SQLの実行計画の見方
>>【ORACLE】SQLの実行計画を取得する方法

WITHを使って遅い場合のチューニング方法

一般的に、WITHは可読性が高くなること、SQLの中で繰り返し登場するサブクエリー(副問合せ)を1つにまとめて記述しておけるという優れものです。

しかし、そんなWITHも使い方を間違えると遅くなる場合があります。

次のSQLのように、複数のWITHが存在する場合や、WITHの中のSELECT文が複雑になるケースだと遅くなることがあります。
見て目的にはSQLがシンプルなように見えても、結局、たくさんのテーブルを参照していること、たくさんのテーブルからデータをとることに変わりはありません。

そのためORACLEオプティマイザが適切な実行計画を作成できず、遅くなる場合があります。

こういう場合は、WITHを使うのを素直に諦めた方が得策です。
WITHではなく、ワークテーブルを作成して、SQLを分割し、実行していきましょう。

これによりSQLがシンプルに、簡潔になるため、オプティマイザが適切な実行計画を作成しやすくなり、結果的に処理時間の短縮が狙えます。バッチ処理などで、処理時間が長いSQLほど効果が出せると思います。

手間もありますし、プログラムの改修も必要かもしれませんが、やることは簡単なので、ぜひ検討してみてください。

◆WITHを使って遅い場合 例

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 table2 a
)
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 table5;

–対象データの抽出
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チューニング方法まとめ