친절한 SQL 튜닝_5장.소트 튜닝_2

 

소트 튜닝

5.2. 소트가 발생하지 않도록 SQL 작성

 

5.2.1 Union vs. Union All

 

SQL에 Union을 사용하면 옵티마이저는 상단과 하단 두 집합 간 중복을 제거하려고 소트 작업을 수행한다. 반면, Union All은 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 작업을 수행하지 않는다. 따라서 될 수 있으면 Union All을 사용해야 한다.

 

그런데 Union을 Union All로 변경하려다 자칫 결과 집합이 달라질 수 있으므로 주의해야 한다. Union 대신 Union All을 사용해도 되는지를 정확히 판단하려면 데이터 모델에 대한 이해와 집합적 사고가 필요하다. 그런 능력이 부족하면 알 수 없는 데이터 중복, 혹시 모를 데이터 중복을 우려해 중복 제거용 연산자를 불필요하게 자주 사용하게 된다.

 

아래 SQL은 Union 상단과 하단 집합 사이에 인스턴스 중복 가능성이 없다. 결제수단코드 조건절에 다른 값을 입력했기 때문이다. 그런데도 Union을 사용함으로 인해 소트 연산을 발생시키고 있다.

 

 

1 2 3 4 5 6 7 8 9

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

cs

 

 

위아래 두 집합이 상호배타적이므로 Union 대신 Union All을 사용해도 된다.

 

 

 

아래 SQL은 Union 상단과 하단 집합 사이에 인스터스 중복 가능성이 있다.

 

 

1 2 3 4 5 6 7 8 9

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

cs

 

 

결제일자와 주문일자 조건은 상호배타적 조건이 아니기 대문이다. 만약 Union을 Union All로 변경하면, 결제일자와 주문일자가 같은 결제 데이터가 중복해서 출력된다.

 

 

소트 연산이 일어나지 않도록 Union All을 사용하면서도 데이터 중복을 피하려면, 아래와 같이 하면 된다.

 

 

1 2 3 4 5 6 7 8 9 10

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

cs

 

 

참고로, 결제일자가 Null 허용 컬럼이면 맨 아래 아래 조건절을 아래와 같이 변경해야 한다.

 

 

1

AND (결제일자 <> ‘20180316’ OR 결제일자 IS NULL)

cs

 

 

아래와 같이 LNNVL 함수를 이용해도 된다.

 

 

1

AND LNNVL(결제일자 = ‘20180316’)

cs

 

 

 

 

5.2.2 Exists 활용

 

중복 레코드를 제거할 목적으로 Distinct 연산자를 종종 사용하는데, 이 연산자를 사용하면 조건에 해당하는 데이터를 모두 읽어서 중복을 제거해야 한다. 부분범위 처리는 당연히 불가능하고, 모든 데이터를 읽는 과정에 많은 I/O가 발생한다.

 

예를 들어, 상품과 계약 테이블이 있다. 계약_X2 인덱스 구성이 『상품번호 + 계약일자』일 때, 아래 쿼리는 상품유형코드 조건절에 해당하는 상품에 대해 계약일자 조건 기간에 발생한 계약 데이터를 모두 읽는 비효율이 있다. 상품 수는 적고 상품별 계약 건수가 많을수록 비효율이 큰 패턴이다.

 

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 )

 

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

 

위 쿼리로 말하면, 상품유형코드 조건절(P.상품유형코드 = :PCLSCD)에 해당하는 상품(C.상품번호 = P.상품번호)에 대해 계약일자 조건 기간(C.계약일자 BETWEEN :DT1 AND :DT2)에 발생한 계약 중 계약구분코드 조건절에 만족하는 (C.계약구분코드 = :ctpcd) 데이터가 한건이라도 존재하는지만 확인한다. Distinct 연산자를 사용하지 않았으므로 상품 테이블에 대한 부분범위 처리도 가능하다.

 

 

 

5.2.3 조인 방식 변경

 

아래 SQL 문에서 계약_X01 인덱스가 『지점ID + 계약일시』 순이며 소트 연산을 생략할 수 있지만, 해시 조인이기 때문에 Sort Order By가 나타났다.

 

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

 

 

아래와 같이 계약 테이블 기준으로 상품 테이블과 NL 조인하도록 조인 방식을 변경하면 소트 연산을 생략할 수 있어 지점ID 조건을 만족하는 데이터가 많고 부분범위 처리 가능한 상황에서 큰 성능 개선 효과를 얻을 수 있다.

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

 

정렬 기준이 조인 키 컬럼이면 소트 머지 조인도 Sort Order By 연산을 생략할 수 있다.

 

출처 : 친절한 SQL 튜닝 - 조시형 지음