[SQL튜닝/고급SQL튜닝] 고급 SQL 활용 (CASE문, Union All을 활용한 M:M 관계 조인, With 구문 활용)

🖥️ 들어가며

📌 CASE문: One SQL을 위한 전통적인 방법(CASE, DECODE), PIVOT 활용 방법
📌 UNION ALL을 활용한 M:M 관계 조인: FULL OUTER JOIN 대체, 성능 향상
📌 WITH 구문 활용: WITH 구문 방식 - Matrialize(임시 테이블 생성), Inline(미생성)

 

---

✏️ 1. CASE문 활용

 

 

 

• ⭐⭐⭐ 레코드(Row)를 → 컬럼으로 변환할 때 Case문을 활용하면 좋습니다.
• 비효율적으로 작성된 SQL
  • 월별납입방법별집계 테이블을 5회 반복 Access 합니다.
    => 1회만 Access 하도록 튜닝이 필요합니다.

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

 

• 튜닝 방법 1: 전통적인 방법 (DECODE, CASE)
  • ⭐⭐⭐ SUM(DECODE 납입방법코드, 'A', 납입금액) 지로
  • ⭐⭐⭐ NVL(SUM(CASE WHEN 납입방법코드 = 'A' THEN 납입금액 END), 0) 지로
    • 문법: CASE WHEN 비교값1 THEN 결과1 END

• 튜닝 방법 2: PIVOT 
  • PIVOT 기능
    • 원래 행 데이터였던 것을 → 새로운 열로 반환합니다. 
    • Oracle 11g부터 제공하는 기능입니다.
  • ⭐⭐⭐ PIVOT(SUM(납입금액) FOR 납입방법코드 IN ('A' AS 지로, 'B' AS 자동이체, ... , 'E' AS 인터넷))
    • 문법:
      PIVOT (
                     집계함수(집계할 열)
                     FOR 피벗할 열
                     IN    (피벗할 값1 [AS 별칭1], ...)) 

행 → 열 변환

INSERT INTO 월별요금납부실적
(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)

-- 전통적인 방법 1. DECODE
SELECT 고객번호, 납입월, 
       SUM(DECODE(납입방법코드, 'A', 납입금액)) 지로,
       SUM(DECODE(납입방법코드, 'B', 납입금액)) 자동이체,
       SUM(DECODE(납입방법코드, 'C', 납입금액)) 신용카드,
       SUM(DECODE(납입방법코드, 'D', 납입금액)) 핸드폰,
       SUM(DECODE(납입방법코드, 'E', 납입금액)) 인터넷
FROM   월별납입방법별집계
WHERE  납입월 = '200903'
GROUP BY 고객번호, 납입월;

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

-- PIVOT 활용 방법
SELECT 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷
FROM   (SELECT 고객번호, 납입월, 납입방법코드, 납입금액
        FROM   월별납입방법별집계
        WHERE  납입월 = '200903'
        )
PIVOT   (SUM(납입금액) FOR 납입방법코드 IN
                          ('A' AS 지로,
                           'B' AS 자동이체,
                           'C' AS 신용카드,
                           'D' AS 핸드폰,
                           'E' AS 인터넷))

 

 

✏️ 2. Union All을 활용한 M:M 관계의 조인

 

 

 

• Full Outer Join을 → Union All로 대체할 수 있습니다.
• 월별 각 상품의 계획대비판매실적을 집계하려고 할 때
  • 상품과 연월을 기준으로 볼 때 두 테이블은 M:M 관계입니다.
  • 따라서 Join 시 카테시안 곱이 발생합니다.
• Union All 활용 SQL
  • ⭐⭐⭐
    SELECT ... , 계획금액, TO_NUMBER(NULL) 실적금액
    UNION ALL
    SELECT ..., TO_NUMBER(NULL) 계획금액, 실적금액
  • 아래 SQL문에서 Union All의 성능상 이점
    • Full Outer Join: 두 테이블의 모든 행을 매칭시킴. 매칭되지 않는 행에 Null 값을 채움.
    • Union All: 단순히 두 쿼리의 결과를 연결함 (중복 제거 작업을 하지 않음)

-- UNION ALL 활용1
SELECT 부서번호, 년월, 계획금액, TO_NUMBER(NULL) 실적금액
FROM   계획
WHERE  부서 = '10'
AND    년월 BETWEEN '202001' AND '202007'
UNION ALL
SELECT 부서번호, 년월, TO_NUMBER(NULL) 계획금액, 실적금액
FROM   계획
WHERE  부서 = '10'
AND    년월 BETWEEN '202001' AND '202007'

-- UNION ALL 활용2
SELECT  부서번호, 년월,
        NVL(MAX(계획금액), 0) 계획금액, NVL(MAX(실적금액), 0) 실적금액 -- MIN으로 바꿔도 동일
FROM    (SELECT 부서번호, 년월, 계획금액, TO_NUMBER(NULL) 실적금액
         FROM   계획
         WHERE  부서 = '10'
         AND    년월 BETWEEN '202001' AND '202007'
         UNION ALL
         SELECT 부서번호, 년월, TO_NUMBER(NULL) 계획금액, 실적금액
         FROM   계획
         WHERE  부서 = '10'
         AND    년월 BETWEEN '202001' AND '202007'
         )
GORUP BY 부서번호, 년월

 

 

✏️ 3. With 구문 활용

• Oracle 9i, SQL Server 2005 버전부터 지원하는 기능입니다.
• With절 처리 방식
  • Materialize 방식
    - 내부적으로 임시 테이블을 생성 → 반복 재사용
    - With절을 선언한 SQL 종료 시 해제됩니다.
    ⭐⭐⭐ WITH alias1 AS (SELECT /*+ materialize*/ ...
  • Inline 방식
    - 물리적으로 임시 테이블을 생성하지 않음
    - 참조된 횟수만큼 런타임 시 반복 수행
    - 반복적으로 참조되는 집합을 미리 선언하여 코딩 단순화
    ⭐⭐⭐ WITH alias1 AS (SELECT /*+ inline*/ ...
  • 방식 선택: 옵티마이저 선택 또는 힌트로 선택할 수 있습니다.
  • Oracle은 2가지 방식 모두 지원하고, SQL Server는 Inline 방식으로만 실행합니다.
• With절 문법
  • With절은 2개 이상 선언 가능합니다.
  • With절 내에서 다른 With절을 참조 가능합니다. 
  • ⭐⭐⭐
    WITH alias1 AS (subquery1), ...
    SELECT ...
    FROM    ...
    WHERE ...
  • T_DEPT
    - WITH절은 DEPT 테이블에서 DEPTNO, DNAME, LOC 열을 선택하여 T_DEPT라는 임시 뷰를 생성합니다.
    - T_DEPT는 메인 쿼리에서 2번 사용됩니다. (재사용: ①JOIN ②서브쿼리에서)
    - 경우에 따라 임시 결과를 캐시해서 반복적인 계산을 줄일 수 있습니다.

WITH T_DEPT AS (SELECT DEPTNO, DNAME, LOC FROM DEPT)

SELECT E.EMPNO, E.NAME, D.DNAME
FROM   EMP E, T_DEPT D
WHERE  E.DEPTNO = D.DEPTNO
AND    DEPTNO IN (SELECT DEPTNO
                  FROM   T_DEPT
                  WHERE  LOC = 'New York');

 

 

 

 

---

📒 정리하면

1. CASE문
   1. 레코드를 → 컬럼으로 변환할 수 있습니다. (⭐⭐⭐ One SQL)
   2. 전통적인 방법: DECODE, CASE
      PIVOT(집계함수(집계할열) FOR 피벗할열 IN (피벗할값1 [AS 별칭1], ...))
2. UNION ALL을 활용한 M:M 관계의 조인
   1. FULL OUTER JOIN 대체 가능, 이는 M:M 관계에서 카테시안 곱을 피하고 성능을 향상시킵니다.
   2. FULL OUTER JOIN: 두 테이블의 모든 행을 매칭 시킴, 매칭되지 않은 행에는 Null 값을 채움
   3. UNION ALL: 단순하 두 쿼리의 결과를 연결함 (중복 제거 작업 XXX)
3. WITH절 구문
   1. Materialize(임시테이블 생성) 또는 Inline(미생성) 방식으로 처리될 수 있습니다. (SQL Server는 Inline만 가능)
   2. 복잡한 쿼리를 단순화해 가독성을 높이고, 임시 뷰를 생성하여 코드 재사용성을 높입니다.

---

• 참고자료
  • https://weblogs.sqlteam.com/jeffs/2007/04/20/60180/ 
  • https://weblogs.sqlteam.com/jeffs/2007/04/19/full-outer-joins/ 
  • https://www.oracletutorial.com/oracle-basics/oracle-pivot/ 
  • http://www.gurubee.net/lecture/2408