SQL튜닝이론 - 불필요한 소트연산이 발생하지 않도록 SQL을 작성하는 방법

SQL튜닝이론 - 불필요한 소트연산이 발생하지 않도록 SQL을 작성하는 방법

 

 

SQL을 작성할 때 불필요한 소트가 발생하지 않도록 주의해야 합니다. UNION, MINUS, DISTINCT 연산자는 중복 레코드를 제거하기 위한 소트 연산(SORT UNIQUE)을 발생시키므로 꼭 필요한 경우에만 사용하고, 성능이 느리다면 소트 연산을 피할 방법이 있는지 찾아봐야 합니다. 조인 방식도 소트를 최소화하도록 고려해야 합니다.

 

UNION ALL 활용

 

UNION을 사용하면 옵티마이저는 상단과 하단 두 집합 간 중복을 제거하려고 소트 작업을 수행합니다. 반면, UNION ALL은 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 작업을 수행하지 않습니다. 따라서 될 수 있으면 UNION ALL을 사용해야 합니다. 그런데 UNION을 UNION ALL로 변경하면 결과 집합이 달라질 수 있으므로 주의해야 합니다. 아래 SQL은 UNION 상단과 하단 집합 사이에 인스턴스 중복 가능성이 없습니다. 결제수단코드 조건절에 다른 값을 입력했기 때문이다. 그럼에도 UNION을 사용함으로 인해 소트연산을 발생시키고 있습니다. UNION ALL로 변경이 필요합니다.

 

select 결제번호, 주문번호, 결제금액, 주문일자...
from 결제
where 결제수단코드 = 'M' and 결제이자 = '20180316'
union
select 결제번호, 주문번호, 결제금액, 주문일자...
from 결제
where 결제수단코드 = 'C' and 결제이자 = '20180316'

-- execution plan

-- SELECT STATEMENT
--   SORT (UNIQUE)
--     UNION-ALL
--       FILTER
--        TABLE ACCESS BY INDEX ROWID OF '결제'
--           INDEX RANGE SCAN OF 결제_N1
--      FILTER
--        TABLE ACCESS BY INDEX ROWID OF '결제'
--          INDEX RANGE SCAN OF 결제_N1

 

위아래 두 집합이 상호배타적이므로 UNION ALL을 사용해도 됩니다. 하지만 아래 SQL은 상단과 하단 집합 사이에 인스턴스 중복 가능성이 있습니다.

 

select 결제번호, 주문번호, 결제금액, 주문일자...
from 결제
where 결제이자 = '20180316'
union
select 결제번호, 주문번호, 결제금액, 주문일자...
from 결제
where 주문이자 = '20180316'

-- execution plan

-- SELECT STATEMENT
-- SORT (UNIQUE)
--  UNION-ALL
--    FILTER
--      TABLE ACCESS BY INDEX ROWID OF '결제'
--        INDEX RANGE SCAN OF 결제_N2
--    FILTER
--      TABLE ACCESS BY INDEX ROWID OF '결제'
--        INDEX RANGE SCAN OF 결제_N3

 

결제일자와 주문일자 조건은 상호배타적 조건이 아니기 때문입니다. 만약 UNION을 UNION ALL로 변경하면, 결제일자와 주문일자가 같은 결제 데이터가 중복해서 출력됩니다. 소트연산이 일어나지 않도록 UNION ALL을 사용하면서도 데이터 중복을 피하려면 아래와 같이 쿼리를 구성해야 합니다.

 

select 결제번호, 주문번호, 결제금액, 주문일자...
from 결제
where 결제이자 = '20180316'
union all
select 결제번호, 주문번호, 결제금액, 주문일자...
from 결제
where 주문이자 = '20180316'
and 결제일자 <> '20180316'

-- execution plan
-- SELECT STATEMENT
--     UNION-ALL
--       FILTER
--         TABLE ACCESS BY INDEX ROWID OF '결제'
--           INDEX RANGE SCAN OF 결제_N2
--       FILTER
--         TABLE ACCESS BY INDEX ROWID OF '결제'
--           INDEX RANGE SCAN OF 결제_N3

 

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

 

and (결제이자 <> '20180316' or 결제일자 is null)
-- LNNVL 함수를 이용할 수 있습니다.
-- NULL일 경우 조회되고 20180316 일 경우 조회되지않습니다.
and LNNVL(결제일자 = '20180316')

 

EXISTS 활용

 

중복 레코드를 제거할 목적으로 DISTINCT 연산자를 사용합니다. 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

-- EXECUTION PLAN
-- SELECT STATEMENT
--   HASH (UNIQUE)
--     FILTER
--       NESTED LOOPS
--         TABLE ACESS BY INDEX ROWID OF 상품
--           INDEX RANGE SCAN OF '상품_X1'
--         TABLE ACCESS BY INDEX ROWID OF '계약'
--           INDEX RANGE SCAN OF '계약_X2'

 

DISTINCT를 사용하지 않고 아래와 같이 개선할 수 있습니다.

 

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
)


-- EXECUTION PLAN
-- SELECT STATEMENT
--     FILTER
--       NESTED LOOPS (SEMI)
--         TABLE ACESS BY INDEX ROWID OF 상품
--           INDEX RANGE SCAN OF '상품_X1'
--         TABLE ACCESS BY INDEX ROWID OF '계약'
--           INDEX RANGE SCAN OF '계약_X2'

 

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

 

위 쿼리로는 상품유형코드 조건절 (P. 상품유형코드 = :pclscd)에 해당하는 상품 (C. 상품번호 = P. 상품번호)에 대해 계약일자 조건 기간 (C. 계약일자 between :dt1 and :dt2)에 발생한 계약 중 계약구분코드 조건절을 만족하는 (C. 계약구분코드 = :ctpcd) 데이터가 한건이라도 존재하는지 확인합니다 DISTINCT를 사용하지 않았으므로 상품 테이블에 대한 부분범위 처리도 가능합니다.

 

DISTINCT, MINUS 연산자를 사용한 쿼리는 대부분 EXISTS 서브쿼리로 변환이 가능합니다. 아래는 MINUS 연산자를 NOT EXISTS 서브쿼리로 변환해 튜닝한 사례입니다.

 

-- MINUS -> NOT EXISTS

-- 튜닝 전

SELECT ST.상황접수번호, ST.관제일련번호, ST.상황코드, ST.관제일시
FROM 관제진행상황 ST
WHERE  상황코드 = '0001' -- 신고접수
AND 관제일시 BETWEEN :V_TIMEFROM || '000000' AND :V_TIMETO || '235959'
MINUS
SELECT ST.상황접수번호, ST.관제일련번호, ST.상황코드, ST.관제일시
FROM 관제진행상황 ST, 구조활동 RPT
WHERE 상황코드 = '0001'
AND 관제일시 BETWEEN :V_TIMEFROM || '000000' AND :V_TIMETO || '235959'
AND RPT.출동샌터ID = :V_CNTR_ID
AND ST.상황접수번호 = RPT.상황접수번호
ORDER BY 상황접수번호, 관제일시

-- 튜닝 후
SELECT ST.상황접수번호, ST.관제일련번호, ST.상황코드, ST.관제일시
FROM 관제진행상황 ST
WHERE  상황코드 = '0001' -- 신고접수
AND 관제일시 BETWEEN :V_TIMEFROM || '000000' AND :V_TIMETO || '235959'
AND NOT EXISTS (
  SELECT 'X' FROM 구조활도
  WHERE 출동센터ID = :V_CNTR_ID
  AND 상황접수번호 = ST.상황접수번호
)
ORDER BY 상황접수번호, 관제일시

 

조인방식 변경

 

조인문일 때는 소팅이 발생하지 않도록 조인 방식도 잘 선택해야 합니다. 아래 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

-- EXECUTION PLAN
-- SELECT STATEMENT
--  SORT(ORDER BY)
--    HASH JOIN
--     TABLE ACCESS FULL OF 상품
--     TABLE ACCESS BY INDEX ROWID OF '계약'
--      INDEX RANGE SCAN OF '계약_X01'

 

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

 

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

-- EXECUTION PLAN
-- SELECT STATEMENT
--   NESTED LOOPS
--     NESTED LOOPS
--       TABLE ACCESS BY INDEX ROWID OF '계약'
--         INDEX RANGE SCAN DESENDING OF 계약_x01
--       INDEX UNIQUE SCAN OF 상품_PK
--     TABLCE ACCESS BY INDEX ROWID OF '상품'

 

정렬 기준이 조인 키 칼럼이면 소트머지 조인도 SORT ORDER BY 연산을 생략할 수 있습니다.