※当サイトの記事には、広告・プロモーションが含まれます。

IN句よりEXISTS句のほうが高速と言う話を聞くけれど

japan.zdnet.com

⇧ 悪用する輩がおるせいで、余計な仕事が増えるんですかね...

IN句よりEXISTS句のほうが高速と言う話を聞くけれど

SQLのIN句とEXISTS句どっち使ったら高速かと言う話ですが、

www.oratable.com

The short answer, post-Oracle 9i is:
Both are pretty much the same!

https://www.oratable.com/in-vs-exists/

Oracle Database 9i以降のバージョンであれば、ほとんど変わらないらしい。

ただ、

forums.oracle.com

⇧ 状況に依りけりという話もあり...

www.shift-the-oracle.com

パフォーマンスチューニングにおいて、すべての副問い合わせを EXISTS に変更するという Tips は誤った認識である。ただし、EXIST による相関副問い合わせは オプティマイザ にとってチューニングしやすいのは パフォーマンス・チューニングマニュアルから確かな事である。

  • 副問い合わせの選択性が高い*1場合にはIN (<副問い合わせ>)
  • 親問い合わせの選択性が高く、副問い合わせのコストも低い場合には、EXISTS( <副問い合わせ> )
    を選択する。

 *1 検索結果レコード数が少ない

2つの副問い合わせの違い - オラクル・Oracleをマスターするための基本と仕組み

⇧ やっぱり、EXISTSを使っておいた方が良いのかな?

www.dba-oracle.com

www.dba-oracle.com

The EXISTS clause is much faster than IN when the subquery results is very large. Conversely, the IN clause is faster than EXISTS when the subquery results is very small.

https://www.dba-oracle.com/t_exists_clause_vs_in_clause.htm

⇧ 副問い合わせ(サブクエリ)の結果が大量レコードになる場合はEXISTS句が高速で、副問い合わせ(サブクエリ)の結果が少量レコードになる場合はIN句が高速になるらしい。

で、肝心の大量に該当するのか少量に該当するのかのレコード量の基準を知りたいんだが...

話は変わり、statckoverflowによりますと、

stackoverflow.com

IN picks the list of matching values. EXISTS returns the boolean values like true or false. Exists is faster than in.

Example

IN

select ename from emp e where mgr in(select empno from emp where ename='KING');

EXISTS

select ename from emp e 
  where exists (select 1 from emp where e.mgr = empno and ename = 'KING'); 

https://www.oratable.com/in-vs-exists/

⇧ そもそも、返す結果が異なりますと。

試してみた。

SELECT * FROM USERS 
WHERE user_id
IN (
  SELECT us.USER_ID FROM USERS us
    INNER JOIN ADDRESS_DETAIL ad
    ON us.user_id = ad.user_id
    WHERE us.user_id = 1
);

SELECT * FROM USERS 
WHERE EXISTS (
  SELECT * FROM USERS us
    INNER JOIN ADDRESS_DETAIL ad
    ON us.user_id = ad.user_id
    WHERE us.user_id = 1
);

EXISTS句はtrueかfalseを返すということで、いくら絞り込みの条件を記述したところでその条件で絞れるということではないということなんですかね?
と思ったら、

stackoverflow.com

oracle.programmer-reference.com

⇧ DELETEする時は、EXISTS句の絞り込んだ条件で削除してくれるらしい、紛らわし過ぎる...

で、結局のところ、DELETEする時にどれが早いのか?

atmarkit.itmedia.co.jp

stackoverflow.com

kkoudev.github.io

⇧ う~む、そもそも、IN句とEXISTS句の比較で話を考えてたけども、EXISTS句が高速なわけではないというコペルニクス的展開に震える...

できる限りINNER JOINを使えるならINNER JOINを使うのが良い模様。

ただ、

stackoverflow.com

oreno-it.info

⇧ DELETE文の場合は、EXISTS + INNER JOINを利用していく感じになるんかね?

結局のところ、ベストプラクティスが分からん...

docs.oracle.com

実行計画は、手動SQLチューニングの主要な診断ツールです。たとえば、計画を表示して、オプティマイザが想定どおりの計画を選択しているかどうかを確認したり、表での索引作成の効果を特定したりすることができます。

https://docs.oracle.com/cd/F19136_01/tgsql/introduction-to-sql-tuning.html#GUID-BBB0D5F7-70EC-4C43-A3C1-7EE8C85631CB

qiita.com

qiita.com

www.shift-the-oracle.com

⇧ 実行計画を取得したりして解析せなアカンのかな?

EXPLAIN PLAN FORで実行するSQLは、実際にSQLの処理は実行されないということで、試しに実行計画を取得してみたのだけど、

■IN句を使ったSELECT文の実行計画

EXPLAIN PLAN FOR SELECT * FROM USERS 
WHERE user_id
IN (
  SELECT us.USER_ID FROM USERS us
    INNER JOIN ADDRESS_DETAIL ad
    ON us.user_id = ad.user_id
    WHERE us.user_id = 1
);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());    

PLAN_TABLE_OUTPUT
Plan hash value: 4265531122
 
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    46 |     3  (34)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |     1 |    46 |     3  (34)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |     1 |    46 |     3  (34)| 00:00:01 |
|   3 |    VIEW                      | VW_NSO_1    |     1 |    15 |     1   (0)| 00:00:01 |
|   4 |     HASH UNIQUE              |             |     1 |    28 |            |          |
|   5 |      NESTED LOOPS SEMI       |             |     1 |    28 |     1   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN        | SYS_C007982 |     3 |    42 |     1   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN      | SYS_C007959 |     1 |    14 |     0   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN         | SYS_C007959 |     1 |       |     0   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID| USERS       |     1 |    31 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("US"."USER_ID"="AD"."USER_ID")
       filter(TO_NUMBER("US"."USER_ID")=1)
   8 - access("USER_ID"="USER_ID")
 
Note
-----
   - this is an adaptive plan

■EXISTS句を使ったSELECT文の実行計画

EXPLAIN PLAN FOR SELECT * FROM USERS 
WHERE EXISTS (
  SELECT * FROM USERS us
    INNER JOIN ADDRESS_DETAIL ad
    ON us.user_id = ad.user_id
    WHERE us.user_id = 1
);
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
Plan hash value: 4038829935
 
-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     3 |    93 |    36   (0)| 00:00:01 |
|*  1 |  FILTER             |             |       |       |            |          |
|   2 |   TABLE ACCESS FULL | USERS       |     3 |    93 |    35   (0)| 00:00:01 |
|   3 |   NESTED LOOPS SEMI |             |     1 |    28 |     1   (0)| 00:00:01 |
|   4 |    INDEX FULL SCAN  | SYS_C007982 |     3 |    42 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN| SYS_C007959 |     1 |    14 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( EXISTS (SELECT 0 FROM "ADDRESS_DETAIL" "AD","USERS" "US" 
              WHERE "US"."USER_ID"="AD"."USER_ID" AND TO_NUMBER("US"."USER_ID")=1))
   5 - access("US"."USER_ID"="AD"."USER_ID")
       filter(TO_NUMBER("US"."USER_ID")=1)

⇧ う~む、レコード数が少ないせいか、EXISTS句のほうがCostが高くなってるのは分かるのだけど、単純にTimeを全部合計して比較すれば良いのか分からんのだけど、

 IN句 = 00:00:01 \times 9 = 00:00:09

 EXISTS句 = 00:00:01 \times 5 = 00:00:05

EXISTS句のほうが処理が高速って考えて良いんですかね?

cosol.jp

⇧ Timeは予想処理時間ということで、処理時間の見積りってことみたいね。

DELETE文の実行計画を取得してみた。

■IN句でDELETEする実行計画

EXPLAIN PLAN FOR DELETE FROM USERS 
WHERE user_id
IN (
  SELECT us.USER_ID FROM USERS us
    INNER JOIN ADDRESS_DETAIL ad
    ON us.user_id = ad.user_id
    WHERE us.user_id = 1
);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
Plan hash value: 3668362358
 
--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT       |             |     1 |    29 |     2  (50)| 00:00:01 |
|   1 |  DELETE                | USERS       |       |       |            |          |
|   2 |   NESTED LOOPS         |             |     1 |    29 |     2  (50)| 00:00:01 |
|   3 |    VIEW                | VW_NSO_1    |     1 |    15 |     1   (0)| 00:00:01 |
|   4 |     SORT UNIQUE        |             |     1 |    28 |            |          |
|   5 |      NESTED LOOPS SEMI |             |     1 |    28 |     1   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN  | SYS_C007982 |     3 |    42 |     1   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN| SYS_C007959 |     1 |    14 |     0   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN   | SYS_C007959 |     1 |    14 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("US"."USER_ID"="AD"."USER_ID")
       filter(TO_NUMBER("US"."USER_ID")=1)
   8 - access("USER_ID"="USER_ID")

■EXISTS句でDELETEする実行計画

EXPLAIN PLAN FOR DELETE FROM USERS 
WHERE EXISTS (
  SELECT * FROM USERS us
    INNER JOIN ADDRESS_DETAIL ad
    ON us.user_id = ad.user_id
    WHERE us.user_id = 1
);
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
Plan hash value: 2258361336
 
------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT     |             |     3 |    42 |     2   (0)| 00:00:01 |
|   1 |  DELETE              | USERS       |       |       |            |          |
|*  2 |   FILTER             |             |       |       |            |          |
|   3 |    INDEX FULL SCAN   | SYS_C007959 |     3 |    42 |     1   (0)| 00:00:01 |
|   4 |    NESTED LOOPS SEMI |             |     1 |    28 |     1   (0)| 00:00:01 |
|   5 |     INDEX FULL SCAN  | SYS_C007982 |     3 |    42 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN| SYS_C007959 |     1 |    14 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter( EXISTS (SELECT 0 FROM "ADDRESS_DETAIL" "AD","USERS" "US" 
              WHERE "US"."USER_ID"="AD"."USER_ID" AND TO_NUMBER("US"."USER_ID")=1))
   6 - access("US"."USER_ID"="AD"."USER_ID")
       filter(TO_NUMBER("US"."USER_ID")=1)
    

⇧ う~む、

Rows統計はSQLチューニングにおいて重要な統計値です。 一般にRows統計は小さい値であることが望ましいとされています。可能であれば、実行計画の処理ステップの早い段階で、Rows統計値を小さい値にできるような、実行計画でSQLを実行することが望ましいです。

Oracle SQL実行計画の読み方 | コーソルDatabaseエンジニアのBlog

⇧ 結局、各ステップを合算した値で比較すれば良いかどうかが分からん...

ちなみに、

onefact.jp

⇧ 実際にSQL文を実行して、「実行統計」を取得するとより正確な情報が入手できるそうな。参照系のSQLであれば、試せそうだけども、更新系は難しそう。

キャッシュとかもクリアするので影響は大きそうね...

事前に了承を得てから、テスト環境のDBなどで試す感じになるということでしょうかね。

毎度モヤモヤ感が半端ない...

今回はこのへんで。