[펌] 오라클에서 not exists, not in, minus의 성능차이

http://cafe.daum.net/Lovers10/MYx/16?docid=XklB|MYx|16|20110207143426&q=oracle%20minus

오라클에서 not exists, not in, minus의 성능차이

 

질문자 인사

좋은 답변 감사합니다. 많은 도움이 되었습니다.

이 문제는 오라클만의 문제가 아니고 거의 모든 RDBMS 제품들에 대해 공통적인 문제입니다.

 

일단 SQL 튜닝에서는 모든 상황에 항상 맞는 것은 없습니다. 

즉, SQL 튜닝엔 왕도가 없다는 말입니다. 수학공식 외우듯이 외워서 튜닝을 하는 것은 아니며 그때 그때 데이터의 분포, 서버의 상태, 인덱스의 유무 및 SQL trace나 tkprof결과 등의 각종 참조가능한 수치들을 분석하여 튜닝방향을 정합니다.

상황에 따라 다른 모든 경우엔 가장 안 좋던 방법이 특정 경우엔 최적의 솔루션이 될 수 있습니다.

참고하세요...;

 

질문의 3가지 + @ 방법의 두드러진 특징만 구별할 수 있어도 판단에 많은 도움이 되겠지요.

A 집합에서 B집합의 데이터를 제외한 나머지만 구하는 방법은 질문의 3가지를 포함하여 상황에 따라 보통 크게 5가지정도를 주로 쓰게 됩니다. 하나씩 특징만 간단히 적겠습니다...자세한 내용은 직접 공부하세요...;

 

1. not in ...

SELECT * FROM A WHERE a.key not in (SELECT b.key FROM B)

형태의 구문이며, B쪽을 먼저 access하여 b.key로 a.key에 공급자역할을 하는 서브쿼리로 쓰고 싶을 때 주로 사용합니다.

 

2. not exists ...

SELECT * FROM A WHERE not exists (SELECT * FROM B WHERE b.key = a.key)

형태의 구문이며, A쪽을 먼저 access하고 나서 a의 각 row들을 not exists로 조사하여 filtering하는 처리를 할 때 주로 사용합니다. 즉, B를 access하기 전에 A쪽의 전체범위가 먼저 access됩니다.

이 때의 서브쿼리는 공급자가 아닌 확인자역할만 해 줄 수 있습니다.

 

3. minus ...

SELECT key, col1, col2 FROM A

MINUS

SELECT key, col1, col2 FROM B

형태의 구문이며, 테스트 해 보면 아시겠지만 MINUS는 특성 상 sort와 중복제거 수행을 동반합니다.

그러므로 가장 이해하기는 간단하나 대용량에서는 사용 시 주의해야 합니다.

A나 B집합의 access대상이 대량인 경우 대량의 sort와 중복제거가 발생하므로 이들 처리에 많은 시간이 소요될 수 있는 쿼리입니다.

 

4. Outer + Null Check ...

SELECT * FROM A, B WHERE A.key = B.Key(+) AND B.Key IS NULL

형태의 구문이며, 위의 not in이나 not exists가 주로 Nested Loop Join 또는 Nested Loop Anti Join 방법을 수행하는데 비해 대용량의 경우 Hash Join이나 Merge Join을 유도하여 성능을 보장받을 수 있는 방법입니다.

단, 각 DBMS 마다 A LEFT OUTER JOIN B ON ~ , (*)등으로 아우터조인에 대한 표현은 약간 씩 다릅니다.

 

5. UNION ALL + Group count 또는 count() over() 분석함수 이용등 ...

SELECT *

FROM(

    SELECT a.*

        , COUNT(DISTINCT gbn) OVER(PARTITION BY key) AS cnt

        , COUNT(DISTINCT DECODE(gbn, 'A', 1)) OVER(PARTITION BY key) AS a_cnt

    FROM(

        SELECT 'A' AS gbn, key, col1, col2 FROM A UNION ALL

        SELECT 'B' AS gbn, key, col1, col2 FROM B

        ) a

    )

WHERE cnt < 2 AND a_cnt = 1

형태의 구문이며, UNION ALL은 MINUS와 달리 sort나 중복제거를 하지 않고 별다른 조인도 없기 때문에 양쪽집합에 scan할 마땅한 인덱스가 없거나 하는 상황에서 위력을 발휘할 수 있는 솔루션입니다.

GROUP BY와 COUNT 함수로도 위의 의미를 그대로 만들 수 있습니다...분석함수나 통계함수를 지원하지 않는 DBMS들은 COUNT() OVER() 대신 GROUP BY / COUNT로 변경해야겠지요...;

 

이외에도 구현할 수 있는 방법들이야 더 있겠지만, 대부분의 상황들에서 위의 예시들이 주로 많이 쓰인다는 것을 거듭 밝힙니다.

 

건승하시길...수고하세요~~