テーブルＡに存在していてテーブルＢにないデータを高速に抽出したい

Question

###前提・実現したいこと
テーブルＡに存在していてテーブルＢにないデータを高速に抽出したい。
テーブルAとBはいずれも１００万件以上のデータを持つ別々のテーブル。

###発生している問題・エラーメッセージ

処理に１０分以上かかってしまう。

###該当のソースコード

select *
from tableA, tableB
where tableA.key1 = tableB.key1(+)
 and tableA.key2 = tableB.key2(+)
 and tableB.key1 is null

###試したこと
「not exist」を使用する方法も試しましたが遅かったです。

###補足情報(言語/FW/ツール等のバージョンなど)
特になし。

Comments

[SVC34]

表と索引のDDLを追記してください

[koji2017]

現在出先でＤＤＬが手元にないです。申し訳ありません。

[SVC34]

目標とする応答時間はどれくらいでしょうか。10分で遅いということはオンライン処理？

[koji2017]

日次のバッチ処理です。今後データが増えても（２００万件ずつくらい）１０分以下にしたいです。

[Zuishin]

質問の SQL で正しい答えが得られますか？ どちらの key1 も null ではないですか？

[koji2017]

SQLを外部結合に修正しました。申し訳ありません。

Answer 1

100万件オーダーのテーブル同士の差分を求めるという要件で10分超を10分以下に抑えるチューニングというのは、改善の余地が小さく労力に対する見返りが少ないと思われます。しかもバッチ処理であればなおさらです。バッチライン上に他にチューニングの余地がないか分析した方が生産的と思いますが、何か事情があるのでしょうか。

おそらく質問者さんが示されているSQLで既に、このようなケースで最も高速と想定されるHASH結合をオプティマイザは選択しているはずです（統計情報がきちんと採られていれば）。実行計画を確認してみてください。なお、ハッシュ結合の場合は結合キーに索引は不要です。

ただし、PGA上のHASHテーブルが溢れてしまうと一時表領域を使用するようになるのでパフォーマンスが低下します。実行計画のTempSpc項目を確認し、一時表領域が使用されていればPGAの拡張を検討してください。

ハッシュ結合

--追記--
key1,key2に索引を追加しても、今のSQLでは結局HASH結合が選択され索引は使用されないと思われます。表の大部分にアクセスするのであれば、1行ずつ索引を見るのはむしろ効率が良くないためです。

索引を追加して性能が改善する可能性があるのは、以下のようにnot exitsと相関複照会を用いたSQLに変更した場合です。こちらであればtableBの表アクセスが不要になるため、性能が向上するかもしれません。ただし、今度は索引へのアクセスが新たにコストとして追加されるため、実測して比較する必要があります。

select *
from tableA tA
where not exists (
  select tB.key1
  from tableB tB
  where
    tA.key1 = tB.key1
    and tA.key2 = tB.key2
)

Answer 2

出先で詳細情報が出せないため一旦終了します。
申し訳ありません。

Answer 3

SQLのJOIN文を視覚的に理解する

これを当てはめると

select * from tableA
left outer join tableB
on tableA.key2 = tableB.key2
where tableB.key1 is null

こうなるのではないでしょうか。
SQL だけではこれで精いっぱいで、高速化にはインデックスが必須だと思います。
逆に言えば、インデックスを張りさえすれば、劇的に高速化すると思います。

Comments

[SVC34]

結合条件key1が不足しています。またそれを加えても質問者さんのSQLと同じSQLになります。Oracleでは独自構文として(+)をつけることで外部結合にすることが出来ます。

[Zuishin]

key1 に関してはわざと外しました。私が間違っているのかもしれませんが、よくわかりません。tableA.key1 = tableB.key1 = null で正しく出ますか？

[SVC34]

テーブルAを基準とした左外部結合のため、結合された結果行のうちテーブルBからのカラムが全てNULLになっているものがあれば、その行の左側のテーブルAの部分が「テーブルＡに存在していてテーブルＢにないデータ」となります。and tableB.key1 is null は結合条件ではなく、結合した結果行に対して評価されます。

図でイメージするOracle DatabaseのSQL全集 第1回 さまざまな結合
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image1-308625-ja.html#p01c

[Zuishin]

おっしゃる通り私が間違えていました。

Answer 4

定期不定期に何度も実行する必要があるってことですかね

単発ならkey1、key2をテーブル別にテキストファイルに出力したものをlinuxのcommコマンドとかで比較すれば速そうですが

Comments

[koji2017]

お返事ありがとうございます。
日次のバッチです。

Answer 5

Oracle はよく知らないのですが、このクエリを実行して時間がかかっとしても期待する結果は返ってきますか？

---

それはさておき、インデックスを使って外部結合させると高速に抽出ができると思います。

まず tableA および tableB にインデックスを張ります（すでに張ってあるなら作業不要)。

ALTER TABLE tableA ADD KEY `keys` (`key1`,`key2`);
ALTER TABLE tableB ADD KEY `keys` (`key1`,`key2`);

で実際のクエリは次のようになります。

SELECT * FROM tableA 
LEFT OUTER JOIN tableB ON 
tableA.key1=tableB.key1 AND 
tableA.key2=tableB.key2 
WHERE tableB.key1 IS NULL;

MySQL で試しているので、もし方言があったらごめんなさい。

Comments

[koji2017]

SQLが外部結合になっていなかったので修正しました。
申し訳ありません。
インデックスを追加するのは難しいです。
できればＳＱＬの変更で対応したいです。

[miyahan]

インデックスが使えないとなると難しいですね。
そもそも tableA と tableB の主キーは何でしょうか？
また、key1, key2 を含むインデックス・ユニーク制約・外部キー制約等があれば併せて教えてください。

Answer 6

SQLに詳しくはありませんが、学生時に習った大雑把な記憶です。

「条件判定の順番として、大きく絞り込めるものを先にすべき。特に、テーブルが膨らむ（例えば総組み合わせの計算をするような）場合は絞り込みをしてからでないと時間とメモリ量が大変になる。

といったことがあったと思います。ご参考までに。