MINUS の代替 SQL #SQL

Oracle で同じ構造の 2 つのテーブルの値を比較し、差の有無を確認する必要があった。

そのときに MINUS を用いて手軽に比較を行おうとしたのだが、これだと処理時間がかかりすぎるということで替わりになる方法を探すことになった。

MINUS は重い。

よく聞く言葉なので解決策はすぐに見つかるだろうと検索したら、いくつか見つかった。

環境

Oracle 11g

参考

検索対象データ

テーブル

create_tables

CREATE TABLE ITEMS_1 (
  ID INTEGER
 ,DIVISION  VARCHAR2(1)  NOT NULL
 ,PARAM1    VARCHAR2(10) NOT NULL
 ,PARAM2    VARCHAR2(20) NOT NULL
 ,PARAM3    VARCHAR2(30) NOT NULL
 ,PARAM4    VARCHAR2(40) NOT NULL
 ,PARAM5    VARCHAR2(50) NOT NULL
 ,PARAM6    VARCHAR2(60) NOT NULL
 ,PARAM7    VARCHAR2(70) NOT NULL
 ,PARAM8    VARCHAR2(80) NOT NULL
 ,PARAM9    VARCHAR2(90) NOT NULL
 ,PRIMARY KEY (ID)
);

CREATE TABLE ITEMS_2
AS
    SELECT
      *
    FROM
      ITEMS_1
;

サンプルデータ

200 万件

fill-in-tables

INSERT INTO ITEMS_1
    SELECT 
      ROWNUM
     ,DBMS_RANDOM.STRING('U', 1)
     ,DBMS_RANDOM.STRING('u', 10)
     ,DBMS_RANDOM.STRING('u', 20)
     ,DBMS_RANDOM.STRING('u', 30)
     ,DBMS_RANDOM.STRING('u', 40)
     ,DBMS_RANDOM.STRING('u', 50)
     ,DBMS_RANDOM.STRING('u', 60)
     ,DBMS_RANDOM.STRING('u', 70)
     ,DBMS_RANDOM.STRING('u', 80)
     ,DBMS_RANDOM.STRING('u', 90)
    FROM
      DUAL
    CONNECT BY
      (SELECT 0 FROM ALL_CATALOG WHERE ROWNUM <= 1000)
     ,(SELECT 0 FROM ALL_CATALOG WHERE ROWNUM <= 2000)
;

INSERT INTO ITEMS_2
    SELECT 
      ROWNUM
     ,DBMS_RANDOM.STRING('U', 1)
     ,DBMS_RANDOM.STRING('u', 10)
     ,DBMS_RANDOM.STRING('u', 20)
     ,DBMS_RANDOM.STRING('u', 30)
     ,DBMS_RANDOM.STRING('u', 40)
     ,DBMS_RANDOM.STRING('u', 50)
     ,DBMS_RANDOM.STRING('u', 60)
     ,DBMS_RANDOM.STRING('u', 70)
     ,DBMS_RANDOM.STRING('u', 80)
     ,DBMS_RANDOM.STRING('u', 90)
    FROM
      DUAL
    CONNECT BY
      (SELECT 0 FROM ALL_CATALOG WHERE ROWNUM <= 1000)
     ,(SELECT 0 FROM ALL_CATALOG WHERE ROWNUM <= 2000)
;

比較 SQL

2 つのテーブルに同じ条件の検索をかけて属性値を比較、差があるかを知ることが目的。

差があった行の値を得ることが目的ではない。

MINUS

diff-with-minus

SELECT COUNT(*) FROM (
    -- -- -- -- -- -- -- -- -- -- -- --
    -- ITEMS_1 MINUS ITEMS_2

    SELECT
      PARAM1 ,PARAM2 ,PARAM3 ,PARAM4 ,PARAM5
     ,PARAM6 ,PARAM7 ,PARAM8 ,PARAM9
    FROM
      ITEMS_1
    WHERE
      DIVISION = 'A' -- 検索条件
    MINUS

    SELECT
      PARAM1 ,PARAM2 ,PARAM3 ,PARAM4 ,PARAM5
     ,PARAM6 ,PARAM7 ,PARAM8 ,PARAM9
    FROM
      ITEMS_2
    WHERE
      DIVISION = 'A' -- 検索条件

    UNION ALL

    -- -- -- -- -- -- -- -- -- -- -- --
    -- ITEMS_2 MINUS ITEMS_1

    SELECT
      PARAM1 ,PARAM2 ,PARAM3 ,PARAM4 ,PARAM5
     ,PARAM6 ,PARAM7 ,PARAM8 ,PARAM9
    FROM
      ITEMS_2
    WHERE
      DIVISION = 'A' -- 検索条件
    MINUS

    SELECT
      PARAM1 ,PARAM2 ,PARAM3 ,PARAM4 ,PARAM5
     ,PARAM6 ,PARAM7 ,PARAM8 ,PARAM9
    FROM
      ITEMS_1
    WHERE
      DIVISION = 'A' -- 検索条件
);

処理時間

検索条件の値が存在

1 回目	2 回目	3 回目
16.582 秒	16.692 秒	16.213 秒

検索条件の値が不在

1 回目	2 回目	3 回目
15.223 秒	15.237 秒	15.384 秒

EXISTS > NOT EXISTS

diff-2

SELECT
  COUNT(*)
FROM
  DUAL
WHERE
  EXISTS (
    SELECT
      *
    FROM
      ITEMS_1
    WHERE
      1 = 1
      AND ITEMS_1.DIVISION = 'A' -- 検索条件
      AND NOT EXISTS (
          SELECT
            *
          FROM
            ITEMS_2
          WHERE
            1 = 1

            -- DIVISION が任意の値で、比較対象の属性値が一致
            AND ITEMS_1.DIVISION = ITEMS_2.DIVISION
            AND ITEMS_1.PARAM1 = ITEMS_2.PARAM1
            AND ITEMS_1.PARAM2 = ITEMS_2.PARAM2
            AND ITEMS_1.PARAM3 = ITEMS_2.PARAM3
            AND ITEMS_1.PARAM4 = ITEMS_2.PARAM4
            AND ITEMS_1.PARAM5 = ITEMS_2.PARAM5
            AND ITEMS_1.PARAM6 = ITEMS_2.PARAM6
            AND ITEMS_1.PARAM7 = ITEMS_2.PARAM7
            AND ITEMS_1.PARAM8 = ITEMS_2.PARAM8
            AND ITEMS_1.PARAM9 = ITEMS_2.PARAM9
      )
  )
;

処理時間

検索条件の値が存在

1 回目	2 回目	3 回目
7.845 秒	7.922 秒	8.021 秒

検索条件の値が不在

1 回目	2 回目	3 回目
3.237 秒	3.212 秒	3.244 秒

EXISTS > LEFT OUTER JOIN

diff-3

SELECT
  COUNT(*)
FROM
  DUAL
WHERE
  EXISTS (
    SELECT
      *
    FROM
      ITEMS_1
    LEFT OUTER JOIN ITEMS_2
    ON (
      1 = 1
      AND ITEMS_1.DIVISION = ITEMS_2.DIVISION
      AND ITEMS_1.PARAM1 = ITEMS_2.PARAM1
      AND ITEMS_1.PARAM2 = ITEMS_2.PARAM2
      AND ITEMS_1.PARAM3 = ITEMS_2.PARAM3
      AND ITEMS_1.PARAM4 = ITEMS_2.PARAM4
      AND ITEMS_1.PARAM5 = ITEMS_2.PARAM5
      AND ITEMS_1.PARAM6 = ITEMS_2.PARAM6
      AND ITEMS_1.PARAM7 = ITEMS_2.PARAM7
      AND ITEMS_1.PARAM8 = ITEMS_2.PARAM8
      AND ITEMS_1.PARAM9 = ITEMS_2.PARAM9
    )
    WHERE
      1 = 1

      -- ON 句内に書かれた比較対象の属性値が一致し DIVISION が任意の値。
      AND ITEMS_1.ID IS NOT NULL
      AND ITEMS_1.DIVISION = 'A' -- 検索条件
  )
;

処理時間

検索条件の値が存在

1 回目	2 回目	3 回目
4.657 秒	4.516 秒	4.570 秒

検索条件の値が不在

1 回目	2 回目	3 回目
7.840 秒	7.725 秒	7.785 秒