【Oracle19C】記載のSQLが遅くなる理屈がわからない

Question

はじめに

下部に記したようなSQLがあります。そのままはコピペできないため、テーブル名等を加工しています。
これを2つの環境（以下、テスト環境・本番環境とする）で実行したときに、本番環境で実行した場合にのみ想定より時間がかかってしまうのですがその理屈がわからず、お力添えいただきたく存じます。

SELECT
    SUB.SEQ_NO
   ,SUB.ID
   ,SUB.PROCESS_DATE
   ,SUB.IMAGE_KEY
FROM
    (
    SELECT
        /* 行番号を振る*/
        ROW_NUMBER() OVER (ORDER BY MAIN.ID) AS SEQ_NO 
       ,MAIN.ID AS ID
       ,MAIN.PROCESS_DATE AS PROCESS_DATE
       ,CASE WHEN MAIN.NO_IMAGE = '1' THEN ''
             WHEN MI.IMAGE_DATA IS NULL AND SI.IMAGE_DATA IS NULL AND BI.IMAGE_DATA IS NULL THEN ''
             ELSE MAIN.ID ||MAIN.SEQUENCE || PROCESS_DATE END AS IMAGE_KEY
             
    FROM
        MAIN_TABLE MAIN
        
    LEFT OUTER JOIN MAIN_IMAGE MI
        ON MAIN.ID = MI.ID
    LEFT OUTER JOIN SUB_IMAGE SI
        ON MAIN.ID = SI.ID
    LEFT OUTER JOIN BACKUP_IMAGE BI
        ON MAIN.ID = BI.ID
    )SUB
WHERE
    SUB.SEQ_NO <= 100000

調べたこと

1. 実行計画
両環境において、全く同じSQLの実行計画を取得しました。すると、トータルコストに大きな差が出ました。SQLを加工している関係上実行計画をそのまま貼付する訳にもいかないのですが、具体的に記載しますと本番環境の方はソート対象行数がおおよそ6000億行ぐらいにまで膨れ上がっており、そこにかなりコストおよび時間がかかっているようでした。検証環境の実行計画は、ソート対象行数は10万行となっており想定通りでした。つまり、テーブルのレコード数に起因することがここでわかるかと思います。

2. SQL本体
実行計画から「どの記述が影響しているか」を特定できなかったため、SQLの結合条件やSELECT句などをコメントアウトしながら本番環境で都度実行し、「どの部分がコメントアウトされた時点でコストが増えるのか」という調査をしました。すると、下部に記載したSQLのうち、15行目の以下部分をコメントアウトするとコストは下がり、実行計画上のソート対象行数も10万行になりました。

AND BI.IMAGE_DATA IS NULL

確かに検証環境と比較すると、BACKUP_IMAGEには大量のレコードが登録されていましたので（検証環境：約100件、本番環境：約1500万件）、そこの件数が影響していることもここでわかるかと思います。

また、BACKUP_IMAGEテーブルは現状登録されているデータの件数から増減が一切ないため、統計情報の更新は行われていません。数年前に1500万件を一気に登録した時点で統計情報を取り、それ以降はデータの増減がないようです。ですのでインデックスが効いていない、というわけでもないかと思います。

不明点

・テーブル同士を結合しただけでは特にコストが増えないのに、CASE文の中で結合先のテーブルカラムを参照するとコストが爆増する理屈がわからない。実行計画を見るに、ソート対象行数が増えているため、たとえば結合条件が間違っていて取得行が増殖するようなSQLになっているとかなら納得はできるが、今回登場するMAINテーブルとBACKUP_IMAGEテーブルの関係は1対1なのでそれも起きえないと考えている。

補足情報

・MAIN_IMAGE、SUB_IMAGE、BACKUP_IMAGEのいずれも、IMAGE_DATAカラムに画像ファイルをBLOB形式で保持しています（平均100KBほど）。
・本文に記載済みですが、MAINと各種IMAGEについては1対1になります。

MAINテーブル

ID	PROCESS_DATE
001	20231123

MAIN_IMAGE/SUB_IMAGE/BACKUPテーブル

ID	IMAGE_DATA
001	(BLOB)

長文になりましたが、ご助力願えればと思います。

宜しくお願い致します。

Comments

[KOZ6.0]

SQLもフェイク、実行計画も載せないではどうしようもない気がしますが・・・
駆動表は MAIN_TABLE になっていますか？

[ikedas]

テスト環境・本番環境各々の、今回のクエリに関係する各テーブルのレコード数を一覧で示してください。

念のたためですがこのコメント欄に書くのではなく質問文を編集して書いてください。

Answer 1

テーブル同士を結合しただけでは特にコストが増えないのに、CASE文の中で結合先のテーブルカラムを参照するとコストが爆増する理屈がわからない。

結合後に行われる参照の際に、その参照項目が、使用したインデックスに含まれなければ実テーブルを参照する事になります。

ですので、質問の内容であれば、インデックスのみのスキャンとなるように、以下のインデックスを追加してみて下さい。

BACKUP_IMAGE (ID, IMAGE_DATA)

※追加したインデックスが使用されないようであれば、ヒント文を使うか、SQLの記述の書き換えを行って下さい

SQLのうち、15行目の以下部分をコメントアウトするとコストは下がり、実行計画上のソート対象行数も10万行になりました。

対象行数までコントロールする場合は、上記以外のテーブルに同様にインデックスを追加して結合条件を以下の様にしてみて下さい。

    LEFT OUTER JOIN MAIN_IMAGE MI
        ON MAIN.ID = MI.ID AND MI.IMAGE_DATE IS NOT NULL
    LEFT OUTER JOIN SUB_IMAGE SI
        ON MAIN.ID = SI.ID AND SI.IMAGE_DATE IS NOT NULL
    LEFT OUTER JOIN BACKUP_IMAGE BI
        ON MAIN.ID = BI.ID AND BI.IMAGE_DATE IS NOT NULL

---

もう一つのアプローチとしては、インデックスに無い参照を止める事です。
具体的にはIMAGE_KEYの編集条件を変える事です。

WHEN MI.IMAGE_DATA IS NULL AND SI.IMAGE_DATA IS NULL AND BI.IMAGE_DATA IS NULL

上記条件がイメージデータが無い事を表しているなら、以下に書き換えると、現状のインデックスのみで解決する事になります。

WHEN MI.ID IS NULL AND SI.ID IS NULL AND BI.ID IS NULL

---

見返してみたところ、最初に抽出する件数を絞り込めば良さそうなので（MAINとIMAGEの各テーブルが1:1という前提）、以下のSQLを試してみて下さい。
※上記に加え、インデックスを追加するか、インデックスに無い項目を参照しないようにすると更に高速になると思います

SELECT
      SUB.SEQ_NO
     ,MAIN.ID 
     ,MAIN.PROCESS_DATE 
     ,CASE WHEN MAIN.NO_IMAGE = '1' THEN ''
           WHEN MI.IMAGE_DATA IS NULL AND SI.IMAGE_DATA IS NULL AND BI.IMAGE_DATA IS NULL THEN ''
           ELSE MAIN.ID ||MAIN.SEQUENCE || PROCESS_DATE END AS IMAGE_KEY
FROM  MAIN_TABLE MAIN
      inner join (
        select ID , ROW_NUMBER() OVER (ORDER BY ID) AS SEQ_NO 
        from MAIN_TABLE
      ) sub
      on MAIN.ID=sub.ID and sub.seq_no<= 100000
      LEFT OUTER JOIN MAIN_IMAGE MI
      ON MAIN.ID = MI.ID
      LEFT OUTER JOIN SUB_IMAGE SI
      ON MAIN.ID = SI.ID
      LEFT OUTER JOIN BACKUP_IMAGE BI
      ON MAIN.ID = BI.ID

※NO_IMAGE = '1'になっていないがバグなのであれば、データを補正すると共に、バグ修正する方が良いのではないでしょうか。