INとEXISTSの実行計画の違い

公開日： 2009/03/28 : 最終更新日：2016/03/30 programing

Visual SQL Tuning / wetribe

最近行った現場で見ました。何故か昔から(特にOracle界隈？)盲目的に「INよりEXISTSのほうが速い」という迷信があって、猫も杓子もみーんなEXISTSになっている現場があったりします。

※以下はOracle10g相手にあれこれチューニングした経験を踏まえての記述ですが、大筋は他のDBでも変わらないと思います。

INもEXISTSもそれぞれ、一概にどっちが良いと決まっている訳じゃないので、ケースバイケースでどっちを使うかちゃんと考えないといけません。

結論

・INが適している場合とは→選択的述語が副問合せに有る

・EXISTSが適している場合とは→選択的述語が親問合せに有る

選択的述語なんてふにゃっとした単語がでてきたけど、要は副問合せ側で条件絞込みできる場合はIN、親問合せ側で条件絞込みできる場合はEXISTS(のほうが適している可能性が高い)てことです。

※以下はOracle10gパフォーマンスチューニングガイドからの引用

INのほうが圧倒的に速いパターン

SELECT /* EXISTS example */
　　　　　e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o
WHERE e.employee_id = o.sales_rep_id
AND o.customer_id = 144);

SELECT /* EXISTS example */

　　　　　e.employee_id, e.first_name, e.last_name, e.salary

FROM employees e

WHERE EXISTS (SELECT 1 FROM orders o

WHERE e.employee_id = o.sales_rep_id

AND o.customer_id = 144);

EXISTSの場合、employeesテーブルを全てスキャン後、ordersテーブルを逐一評価するため無駄が多い。

SELECT /* IN example */
e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.employee_id IN (SELECT o.sales_rep_id
FROM orders o
WHERE o.customer_id = 144);

SELECT /* IN example */

e.employee_id, e.first_name, e.last_name, e.salary

FROM employees e

WHERE e.employee_id IN (SELECT o.sales_rep_id

FROM orders o

WHERE o.customer_id = 144);

INの場合は、副問合せ内を先に評価した後(ここではカスタマーIDで絞込み)、employeesテーブルと結合するため無駄が少ない。

EXISTSのほうが速いパターン

SELECT /* EXISTS example */
e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE e.department_id = 80
AND e.job_id        = 'SA_REP'
AND EXISTS (SELECT 1
FROM orders o
WHERE e.employee_id = o.sales_rep_id);

SELECT /* EXISTS example */

e.employee_id, e.first_name, e.last_name, e.salary

FROM employees e

WHERE e.department_id = 80

AND e.job_id = 'SA_REP'

AND EXISTS (SELECT 1

FROM orders o

WHERE e.employee_id = o.sales_rep_id);

EXISTSの場合は、employessテーブルを索引であらかじめ絞り込んだ後、ordersテーブルにアクセスするため効率的。

SELECT /* IN example */
e.employee_id, e.first_name, e.last_name, e.department_id, e.salary
FROM employees e
WHERE e.department_id = 80
AND e.job_id        = 'SA_REP'
AND e.employee_id IN (SELECT o.sales_rep_id FROM orders o);

SELECT /* IN example */