Union vs Union All

• Union : 옵티마이저는 상단과 하단 두 집합간 중복을 제거하려고 소트 작업을 수행한다.
• Union All : 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 작업을 수행하지 않는다.

성능을 위해서 Union All을 주로 사용하자.

그러나 Union All을 사용하려면 데이터 모델에 대한 이해 및 집학적 사고가 필요하다. 잘못하면 데이터 중복용 연산자를 잘못 사용할 수 있다.

SELECT 결제번호, 주문번호, 결제금액, 주문일자 …
  FROM 결제
 WHERE 결제수단코드 = 'M' AND 결제일자 = '20180316'
 UNION
SELECT 결제번호, 주문번호, 결제금액, 주문일자 …
  FROM 결제
 WHERE 결제수단코드 = 'C' AND 결제일자 = '20180316'

위의 쿼리처럼 두 집합이 서로 겹치는 내용이 없이 상호배타적이면 Union All을 사용해도 된다!

SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 …
  FROM 결제
 WHERE **결제일자** = '20180316'
 UNION
SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 …
 FROM 결제
 WHERE **주문일자** = '20180316'

그런데 이 쿼리처럼 두 집합은 상호배타적(겹치지 않는) 조건이 아니기 때문에 Union All을 쓰면 결제일자와 주문일자가 같은 결제 데이터가 중복되어 출력!

Union All을 사용하려면 아래 쿼리처럼 중복을 피하고 사용하자.

SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 …
  FROM 결제
 WHERE 결제일자 = '20180316'
 UNION ALL
SELECT 결제번호, 결제수단코드, 주문번호, 결제금액, 결제일자, 주문일자 …
  FROM 결제
 WHERE 주문일자 = '20180316'
   AND 결제일자 <> '20180316'

오라클은 LNNVL 이라는 함수를 사용하여 NULL과 중복을 피할 수 있음.

Exists 활용

Distinct 연산자는 조건에 해당하는 데이터를 모두 읽어 중복을 제거해야해 성능에 안좋음. (I/O 많음, 부분범위 처리 불가)

SELECT DISTINCT P.상품번호, P.상품명, P.상품가격, ...
  FROM 상품 P, 계약 C
 WHERE P.상품유형코드 = :pclscd
   AND C.상품번호 = P.상품번호
   AND C.계약일자 BETWEEN :DT1 AND :DT2
   AND C.계약구분코드 = :CTPCD

SELECT P.상품번호, P.상품명, P.상품가격, ...
  FROM 상품 P
 WHERE P.상품유형코드 = :pclscd
   AND EXISTS ( SELECT 'X' FROM 계약 C
                 WHERE C.상품번호 = P.상품번호
                   AND C.계약일자 BETWEEN :DT1 AND :DT2
                   AND C.계약구분코드 = :CTPCD
							)

위의 쿼리는 Distinct를 Exists로 바꿔본 쿼리이다.

Exists 서브쿼리는 데이터 존재 여부만 확인하면 되기 때문에 조건절에 해당하는 데이터를 모두 읽지 않음.

Distinct 뿐만 아니라 Minus 연산자를 사용한 쿼리도 Exists 서브쿼리로 변환이 가능하다.

조인 방식 변경

인덱스를 사용해 소트 연산을 생략할 수 있지만, 조인문이면 조인 방식도 잘 선택해야 한다.

두 테이블이 NL 조인 하도록 조인 방식을 변경하면 소트 연산을 생략해 데이터가 많고 부분 범위처리가 가능한 상황에서 큰 성능 개선을 할 수 있다.

예시 : 계약_X01 인덱스 ⇒ [지점 ID + 계약일시]

SELECT C.계약번호, C.상품코드, P.상품명, P.상품구분코드, C.계약일시, C.계약금액
  FROM 계약 C, 상품 P
 WHERE C.지점ID = :brch_id
   AND P.상품코드 = C.상품코드
 ORDER BY C.계약일시 DESC

위의 쿼리 같은 경우는 인덱스를 사용한다고 해도 해시 조인이라 Sort Order by가 발생한다.

SELECT **/*+ leading(C) use_nl(P) */**
       C.계약번호, C.상품코드, P.상품명, P.상품구분코드, C.계약일시, C.계약금액
  FROM 계약 C, 상품 P
 WHERE C.지점ID = :brch_id
   AND P.상품코드 = C.상품코드
 ORDER BY C.계약일시 DESC

하지만 이 쿼리의 경우 힌트로 NL 조인을 유도했다. 이로서 인덱스를 사용하여 sort 연산을 생략했다.