[친절한 SQL 튜닝] 5. 소트 튜닝 [2]

 

SQL을 작성할 때 불필요한 소트가 발생하지 않도록 주의해야 한다. Union, Minus, Distinct 연산자는 중복 레코드를 제거하기 위한 소트 연산을 발생시키므로 꼭 필요한 경우에만 사용하고, 성능이 느리다면 소트 연산을 피할 방법이 있는지 찾아봐야 한다.

 

5.2.1 Union vs Union All

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

성능을 위해서라면 Union All을 사용하자.

 

하지만 Union을 Union All로 변경하려다 결과 집합이 달라질 수 있으므로 주의해야 한다. Union All을 사용하려면 데이터 모델에 대한 이해와 집합적 사고가 필요하다.

 

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

 

위 SQL은 두 집합 사이에 인스턴스 중복 가능성이 없다. 그런데도 Union을 사용함으로 인해 소트 연산을 발생시킨다.

 

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

 

SELECT 결제번호, 주문번호, 결제금액, 주문일자 ...
  FROM 결제
 WHERE 결제일자 = '20180316'
UNION
SELECT 결제번호, 주문번호, 결제금액, 주문일자 ...
  FROM 결제
 WHERE 주문일자 = '20180316'

 

위 SQL은 두 집합 사이에 인스턴스 중복 가능성이 있으므로 Union All을 사용하면 중복 데이터가 발생할 수 있다.

소트 연산이 일어나지 않도록 아래 SQL로 수정하자.

 

SELECT 결제번호, 주문번호, 결제금액, 주문일자 ...
  FROM 결제
 WHERE 결제일자 = '20180316'
UNION ALL
SELECT 결제번호, 주문번호, 결제금액, 주문일자 ...
  FROM 결제
 WHERE 주문일자 = '20180316'
   AND 결제일자 <> '20180316'

 

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

WHERE LNNVL(결제일자 = '20180316')

 

 

5.2.2 Exists 활용

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

 

* DISTINCT 연산자 사용
SELECT DISTINCT P.상품번호, P.상품명, P.상품가격, ...
  FROM 상품 P, 계약 C
 WHERE P.상품유형코드 = :pclscd
   AND C.상품번호 = P.상품번호
   AND C.계약일자 BETWEEN :DT1 AND :DT2
   AND C.계약구분코드 = :ctpcd
   
   
 --------------------------------------------------------------------
 
 * EXISTS 서브쿼리 사용
SELECT DISTINCT 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 서브쿼리는 데이터 존재 여부만 확인하면 되기 때문에 조건절을 만족하는 데이터를 모두 읽지 않는다.

또한 DISTINCT 연산자를 사용하지 않았으므로 상품 테이블에 대한 부분범위 처리도 가능하다.

 

DISTINCT 뿐만 아니라 MINUS 연산자도 EXISTS 서브쿼리로 변환 가능하다.

 

 

5.2.3 조인 방식 변경

 

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

 

두 테이블을 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

 

위 SQL의 경우 인덱스를 사용해도 Hash Join이기 때문에 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

 

하지만 위 SQL의 경우 힌트를 통해 NL 조인을 유도해서 인덱스를 사용해 Sort Order By 연산을 생략할 수 있다.