Oracle で同じ構造の 2 つのテーブルの値を比較し、差の有無を確認する必要があった。
そのときに MINUS を用いて手軽に比較を行おうとしたのだが、これだと処理時間がかかりすぎるということで替わりになる方法を探すことになった。
MINUS は重い。
よく聞く言葉なので解決策はすぐに見つかるだろうと検索したら、いくつか見つかった。
環境
- Oracle 11g
参考
- optimization - optimize query with minus oracle - Stack Overflow
- You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough – Java, SQL and jOOQ.
検索対象データ
テーブル
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 秒 |