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

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

一般的に、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)
;

Oracle SQLチューニング パフォーマンス改善と事前対策に役立つ (DB selection) [ 加藤祥平 ]

価格:2,808円
(2018/11/19 19:23時点)

WITHを使って遅い場合のチューニング方法” に対して1件のコメントがあります。

この投稿はコメントできません。