SQLのIN関数が性能わるい場合、EXISTSを使う

１．背景

SQLのIN関数はよく使いますが、場合によって性能がわるいと感じました。

▼例：下記の場合

SELCT * FROM TABLE WHERE KEY IN (SELECT KEY FROM TABLE2);
このSQLについて、SELECT KEY FROM TABLE2がレコードない場合、SQL全体の結果出力はすごく遅いです。

２．分析

この問題は、IN句を使用したサブクエリで、サブクエリの結果が空になるとパフォーマンスが悪化するケースが考えられます。特に、以下のような原因が関連している可能性があります：

①．NULLの扱い

サブクエリの結果が空の場合、Oracleは暗黙的にNULLとの比較を試みることがあります。これは、IN句がNULLを考慮し、条件評価に不要なコストを追加する場合があります。

②．インデックスの使用不足

外側のクエリ（SELECT * FROM TABLE）がインデックスを使用できていない場合、テーブル全体をスキャン（フルテーブルスキャン）するため、パフォーマンスが低下します。

③．非効率なクエリ計画

Oracleのオプティマイザがサブクエリの結果が空であることを事前に認識できず、無駄な処理を行うことがあります。

３．解決策

①．EXISTS句を使用する

INの代わりにEXISTSを使用することで、サブクエリの結果が空の場合の処理が効率化される場合があります。EXISTSは結果を確認するだけで、全体のデータを返しません。

SELECT * 
FROM TABLE t1
WHERE EXISTS (
    SELECT 1 
    FROM TABLE2 t2
    WHERE t1.KEY = t2.KEY
);

実は、INの中に、もっと複雑な条件があり、またこの条件は別のところにも流用する予定があるため、
EXISTSで試しました。性能アップしました。

②．サブクエリの結果を事前に確認

サブクエリの結果が空かどうかを事前に判定し、クエリを分岐させる方法です。

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM TABLE2;

    IF v_count > 0 THEN
        EXECUTE IMMEDIATE 'SELECT * FROM TABLE WHERE KEY IN (SELECT KEY FROM TABLE2)';
    ELSE
        DBMS_OUTPUT.PUT_LINE('No results in TABLE2');
    END IF;
END;

③．WITH句を利用

WITH句を使用してサブクエリの結果を事前に計算し、結果が空の場合のオーバーヘッドを削減します。

WITH table2_keys AS (
    SELECT KEY FROM TABLE2
)
SELECT *
FROM TABLE
WHERE KEY IN (SELECT KEY FROM table2_keys);

この場合、table2_keysを事前に評価し、その結果を使用するため、無駄なフルテーブルスキャンを回避できます。

④．インデックスの確認

両方のテーブルでKEY列に適切なインデックスが設定されていることを確認してください。インデックスがないと、フルテーブルスキャンが発生してクエリが遅くなる原因になります。

以下のようにインデックスを作成できます：

CREATE INDEX idx_table_key ON TABLE(KEY);
CREATE INDEX idx_table2_key ON TABLE2(KEY);

⑤．JOINを使用する

IN句の代わりにJOINを使用して、データを結合する方法もパフォーマンス向上に役立つことがあります。

SELECT t1.*
FROM TABLE t1
JOIN TABLE2 t2 ON t1.KEY = t2.KEY;

まとめ

最も効果的な方法はEXISTS句の使用やJOINへの切り替えです。
インデックスがない場合は、インデックスを追加してパフォーマンスを向上させます。
サブクエリの結果が空である場合の処理を明確にすることで、無駄な計算を削減できます。
クエリの実行計画（EXPLAIN PLAN）を確認して、どの部分がボトルネックになっているかを特定することもお勧めします。

目次CONTENT