SQL튜닝 - 고급 SQL 기법을 활용한 성능개선

 

데이터베이스 CALL을 반복적으로 일으키는 프로그램을 ONE-SQL로 통합하면 성능이 개선됩니다. ONE SQL을 작성하기 위해 다양한 패턴을 사용할 수 있습니다.

 

CASE문 활용

 

-- 월별납입방법별집계 테이블을 읽어 월요금납부실적과 같은 형태로 가공하고자 한다.

-- 월별납입방법별집계       월별요금납부실적
-- # 고객번호             # 고객번호
-- # 납입월              # 납입월
-- # 납입방법코드          # 지로
-- # 납입금액             # 자동이체
--                      # 신용카드
--                      # 핸드폰
--                      # 인터넷


INSERT INTO 월별요금납부실적
(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)
SELECT
K.고객번호, '200903' 납입월
, A.납입금액 지로
, B.납입금액 자동이체
, C.납입금액 신용카드
, D.납입금액 핸드폰
, E.납입금액 인터넷
FROM 고객 K
,(
  SELECT 고객번호, 납입금액
  FROM 월별납입방법별집계
  WHERE 납입월 = '200903'
  AND 납입방법코드 = 'A'
) A
,(
  SELECT 고객번호, 납입금액
  FROM 월별납입방법별집계
  WHERE 납입월 = '200903'
  AND 납입방법코드 = 'B'
) B
,(
  SELECT 고객번호, 납입금액
  FROM 월별납입방법별집계
  WHERE 납입월 = '200903'
  AND 납입방법코드 = 'C'
) C
,(
  SELECT 고객번호, 납입금액
  FROM 월별납입방법별집계
  WHERE 납입월 = '200903'
  AND 납입방법코드 = 'D'
) D
,(
  SELECT 고객번호, 납입금액
  FROM 월별납입방법별집계
  WHERE 납입월 = '200903'
  AND 납입방법코드 = 'E'
) E
WHERE A.고객번호(+) = K.고객번호
AND  B.고객번호(+) = K.고객번호
AND  C.고객번호(+) = K.고객번호
AND  D.고객번호(+) = K.고객번호
AND  E.고객번호(+) = K.고객번호
AND NVL(A.납입금액, 0) + NVL(B.납입금액, 0) + NVL(C.납입금액, 0) + NVL(D.납입금액, 0) + NVL(E.납입금액, 0) > 0

 

위 쿼리는 효율을 고려하지 않은 ONE-SQL입니다. 중요한 것은 어떻게 I/O를 효율화 할지 입니다. I/O 효율화는 같은 레코드를 반복해서 엑세스하지않고 얼마만큼 블록 엑세스양을 최소화 할 수 있느냐에 달려있습니다. I/O 효율화를 고려한 SQL은 다음과 같습니다.

 

INSERT INTO 월별요금납부실적
(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)
SELECT
고객번호, 납입월
, NVL(SUM(CASE WHEN 납입방법코드 = 'A' THEN 납입금액), 0) 지로
, NVL(SUM(CASE WHEN 납입방법코드 = 'B' THEN 납입금액), 0) 자동이체
, NVL(SUM(CASE WHEN 납입방법코드 = 'C' THEN 납입금액), 0) 신용카드
, NVL(SUM(CASE WHEN 납입방법코드 = 'D' THEN 납입금액), 0) 핸드폰
, NVL(SUM(CASE WHEN 납입방법코드 = 'E' THEN 납입금액), 0) 인터넷
FROM 월별납입방법별집계
WHERE 납입월 = '2000903'
GROUP BY 고객번호, 납입월;

 

데이터 복제기법

 

복제용 테이블을 미리 만들어두고 이를 데이터 복제에 활용합니다.

 

create table copy_t(no number, no2 varchar(2));

insert into copy_t
select rownum, lpad(rownum, 2, '0') from big_table where rownum <= 31;

alter table copy_t add constraint copy_t_pk primary key(no);
create unique index copy_t_no2_idx on copy_t(no2);

 

생성한 테이블을 조인절 없이 조인하면 카티션 곱이 발생해 데이터가 2배로 복제됩니다.

 

select * from emp a, copy_t b
where b.no <= 2

 

ORACLE 9I 부터는 DUAL 테이블을 활용할 수 있습니다. DUAL 테이블에 START WITH 절이 없는 CONNECT BY 구문을 사용하면 두 레코드를 가진 집합이 자동으로 만들어집니다.

 

SELECT * FROM EMP A,
(SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2) B

 

 

데이터 복제 기법을 실제 업무에 적용한 예시입니다.

 

BREAK ON 카드상품분류

SELECT A.카드상품분류
, (CASE WHEN B.NO = 1 THEN A.고객등급 ELSE '소계' END) AS 고객등급
, (SUM A.거래금액) as 거래금액
FROM
(
    SELECT 카드.카드상품분류 AS 카드상품분류
    , 고객.고객등급 AS 고객등급
    , SUM(거래금액) AS 거래금액
    FROM 카드월실적, 카드, 고객
    WHERE 실적년월 = '201008'
    AND 카드.카드번호 = 카드월실적.카드번호
    AND 고객.고객번호 = 카드.고객번호
    GROUP BY 카드.카드상품분류, 고객.고객등급
) A
, COPY_T B
WHERE B.NO <= 2
GROUP BY A.카드상품분류, B.NO, (CASE WHEN B.NO = 1 THEN A.고객등급 ESEL '소계' END)

SELECT A.DEPTNO, (CASE WHEN B.NO = 1 THEN A.JOB ELSE '소계' END)
FROM
(
  SELECT DEPTNO, JOB, SUM(SAL) AS SAL
  FROM EMP
  GROUP BY DEPTNO, JOB
) A
, (
    SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2
) B
WHERE B.NO <= 2
GROUP BY A.DEPTNO, (CASE WHEN B.NO = 1 THEN A.JOB ELSE '소계' END)
ORDER BY A.DEPTNO

 

UNION ALL을 활용한 M:M 관계 해결

 

M:M 관계의 조인을 해결하거나 FULL OUTER JOIN을 대체하기 위해 UNION ALL을 활용할 수 있습니다. M:M 관계의 두 테이블을 조인하면 카티시안 곱이 발생합니다. 이때 두 테이블을 동일한 기준으로 GROUP BY 한 후 FULL OUTER JOIN 하면 1:1 관계로 조인할 수 있습니다.

 

이때 DBMS에 따라 FULL OUTER JOIN이 비효율적으로 동작할 수 있습니다. 아래의 실행계획처럼 하나의 테이블을 두 번씩 엑세스합니다.

 

--- EXECUTION PLAN
SELECT STATEMENT OPTIMIZER=CHOOSE
  VIEW
    UNION-ALL
      HASH JOIN(OUTER)
        VIEW
          SORT
            TABLE ACCESS (FULL) OF '부서별판매계획'
        VIEW
          SORT
            TABLE ACCESS (FULL) OF '채널별판매실적'
        SORT(GROUP BY)
          FILTER
            TABLE ACCESS (FULL) OF '채널별판매실적'
            SORT(GROUP BY NOSORT)
              FILTER
                TABLE ACCESS (FULL) OF '부서별판매계획'

 

성능을 높이기위해 FULL OUTER JOIN 대신 UNION ALL 한 후 GROUP BY 하면 동일한 결과를 얻을 수 있습니다.

 

페이징 처리

 

일반적인 페이징 처리용 SQL

 

SELECT *
FROM (
    SELECT ROWNUM NO, COUNT(*) OVER () CNT
    FROM ()
    WHERE ROWNUM <= :PAGE * :PG_SIZE + 1 -- 6  11 16
)
WHERE NO BETWEEN (:PAGE - 1) * PG_SIZE + 1 AND :PAGE * PG_SIZE -- 1 ~ 5 / 6 ~ 10 / 11 15

 

위 쿼리의 실행계획은 다음과 같습니다.

 

-- SELECT STATEMENT OPTIMIZER = ALL_ROWS
--    FILTER
--        VIEW
--            WINDOW(BUFFER)
--                COUNT(STOPKEY)
--                    VIEW
--                        TABLE ACCESS BY INDEX ROWID OF A
--                            INDEX RANGE SCAN ON IDX_A            

 

:PG_ZISE 는 사용자가 한 번에 FETCH 해올 데이터 건수이고 :PAGE는 출력하고자하는 페이지 번호입니다.

 

CNT는 다음 페이지에 읽을 남은 데이트가 있는지 확인하는 용도입니다. CNT가 :PG_SIZE * :PAGE 보다 크면 출력할 데이터가 남아있는 것이고 같거나 적다면 데이터를 이미 전부 읽어왔음을 의미합니다. 전체 건수를 세지않고도 다음 버튼의 활성화 여부를 판단할 수 있습니다. TOP-N SORT 알고리즘이 작동하므로

SORT 가 발생하더라도 부하를 최소화할 수 있습니다.

 

뒷페이지까지 조회하는 경우

 

뒷 번 페이지까지 읽을 수록 한 번에 읽어오는 레코드가 많아지므로 비효율이 증가합니다. 앞에 읽었던 레코드를 다시 읽는 반복 엑세스가 발생하기 때문입니다. 이 경우 해당 페이지의 마지막 레코드 다음으로 바로 찾아가도록 조건이 추가돼야합니다.

 

UNION ALL 활용

 

UNION ALL 을 활용해 ONE SQL로 처리할 수 있습니다.

 

SELECT 
  FROM(
    SELECT 
    FROM A
    WHERE :페이지이동 = 'NEXT'
    AND 거래일시 >= :TRD_TIME -- 사용자가 입력한 거래일자 또는 거래 일시
    ORDER BY 거래일시 --- 2
  )
  WHERE ROWNUM <= 11
  UNION ALL
  SELECT 
  FROM(
    SELECT 
    FROM A
    WHERE :페이지이동 = 'PREV'
    AND 거래일시 <= : TRD_TIME -- 사용자가 입력한 거래일자 또는 거래 일시
    ORDER BY 거래일시  DESC
  )
  WHERE ROWNUM <= 11
  ORDER BY 거래일시

 

윈도우 함수 활용

 

윈도우 함수를 통해 단순화된 쿼리를 작성할 수 있습니다.

 

SELECT 일련번호, 측정값
-- 일련번호 순으로 첫 레코드부터 현재 레코드까지 값들 중 일련번호기준으로 가장 최신 레코드의 상태코드
,last_value(상태코드 ignore nulls) over(order by 일련번호 rows between unbounded preceding and current row) 상태코드
FROM 장비측정 O
ORDER BY 일련번호

 

WITH 구문 활용

 

WITH 절을 처리하는 DBMS 내부 실행 방식에는 2가지가 있습니다.

 

- MATERIALIZE 방식 : 내부적으로 임시 테이블을 생성하여 재사용

- INLINE 방식 : 별도의 임시 테이블을 만들지 않고 참조된 횟수만큼 런타임시 반복 수행합니다. SQL 문에서 반복적으로 참조되는 집합을 미리 선언하여 코딩을 단순화

 

ORACLE의 경우, 두 가지 모두를 지원합니다. 옵티마이저가 상황에 따라 실행방식을 결정하고 필요하다면 사용자가 힌트를 통해 지정할 수 있습니다. MATERIALIZE 방식을 통해 생성된 임시 테이블은 SQL이 실행되는 동안만 유지됩니다. WITH 절은 2개 이상 선언할 수 있으며 WITH 절 내에서 다른 WITH 절을 참조할 수 있습니다.