서브쿼리

서브쿼리가 일으키는 폐해

서브쿼리의 문제점

서브쿼리의 성능적 문제는 결과적으로 서브쿼리가 실제 데이터를 저장하지 않는다는 점이 원인
1. 연산 비용 추가
- 서브쿼리에 접근할 때마다 SELECT 구문을 실행하여 데이터를 생성
- 서브쿼리가 복잡할수록 이러한 실행 비용은 더 높아짐
  1. 데이터 I/O 비용 발생
- 서브쿼리의 데이터 양이 클 경우 TEMP 탈락 현상이 발생할 수 있음 3. 최적화 불가
- 명시적인 제약 또는 인덱스가 작성되어 있는 테이블과 달리, 서브쿼리에는 메타 정보가 하나도 존재하지 않음
- 따라서, 옵티마이저가 쿼리를 해석하기 위한 정보를 얻을 수 없음
  서브쿼리 의존증

문제. 고객별 최소 순번 레코드 구하기

서브쿼리를 사용한 방법
```
SELECT R1.cust_id, R1.seq, R1.price
FROM Receipts R1
    INNER JOIN
      (SELECT cust_id, MIN(seq) AS min_seq
        FROM Receipts
        GROUP BY cust_id) R2
ON R1.cust_id = R2.cust_id
 AND R1.seq = R2.min_seq
```
- 코드가 복잡해서 읽기 어려움
- 성능이 좋지 않음
  - 서브쿼리는 대부분 일시적인 영역 (메모리 또는 디스크)에 확보되므로 오버헤드 발생
  - 서브쿼리는 인덱스 또는 제약 정보를 가지지 않기 때문에 최적화 불가능
  - 위의 쿼리는 결합을 필요로 하기 때문에 비용이 높고 실행 계획 변동 리스크 발생
  - Receipts 테이블에 스캔이 두 번 필요

상관 서브쿼리를 사용한 방법

SELECT cust_id, seq, price
FROM Receipts R1
 WHERE seq = (SELECT MIN(seq)
 								FROM Receipts R2
     			 WHERE R1.cust_id = R2.cust_id);

서브쿼리를 사용할 때와 마찬가지로 테이블 스캔 2번 필요, 성능적인 장점이 없음

윈도우 함수로 결합을 제거

SELECT cust_id, seq, price
FROM (SELECT cust_id, seq, price,
                          ROW_NUMBER()
     					OVER (PARTITION BY cust_id
                        ORDER BY seq) AS row_seq
      FROM Reciepts) WORK
WHERE WORK.row_seq = 1;

- 윈도우 함수 ROW_NUMBER를 사용하여 각 사용자의 구매 이력에 번호 부여 ### 장기적 관점에서의 리스크 관리

서브쿼리 또는 상관 서브쿼리를 사용한 방법에 비해 윈도우 함수를 사용한 쿼리가 얼마나 성능이 좋은지는 사용하는 DBMS 또는 데이터베이스 서버의 성능, 매개 변수나 인덱스 같은 환경 요인에 의해 크게 바뀔 수 있음
그러나 저장소의 I/O 양을 감소시키는 것이 SQL 튜닝의 기본 원칙
또한, 결합을 사용한 쿼리에는 두 개의 불안정 요소가 있음
- 결합 알고리즘의 변동 리스크
  - 테이블의 레코드 개수가 늘어나다보면 옵티마이저가 선택하는 결합 알고리즘이 변하고 실행 계획이 변함
  - Sort Merge나 Hash에 필요한 메모리가 부족해지면 일시적으로 저장소를 사용하면서 성능 하락 (TEMP 탈락)
- 환경 요인에 의한 지연 리스크(인덱스, 메모리, 매개변수 등)
  - 내부 테이블의 결합 키에 인덱스가 존재하면 성능이 개선되지만 항상 결합 키에 인덱스가 존재하지는 않음
  - TEMP 탈락이 발생하는 경우 작업 메모리를 늘려 성능을 개선할 수 있으나, 메모리 튜닝은 한정된 리소스 내부에서의 트레이드-오프를 발생시킴
    서브쿼리 의존증 - 응용편

문제. 고객별 최솟값 - 최대값의 차이 구하기

서브쿼리를 사용한 방법

SELECT TMP_MIN.cust_id,
   TMP_MIN.price - TMP_MAX.price AS diff
 FROM  (SELECT R1.cust_id, R1.seq, R1.price
 					FROM Receipts R1
            INNER JOIN
            	(SELECT cust_id, MIN(seq) AS min_seq
             		 FROM Receipts
                GROUP BY cust_id) R2
        ON R1.cust_id = R2.cust_id
       AND R1.seq = R2.min_seq) TMP_MIN
   INNER JOIN
   	(SELECT R3.cust_id, R3.seq, R3.price
 					FROM Receipts R3
            INNER JOIN
            	(SELECT cust_id, MAX(seq) AS max_seq
             		 FROM Receipts
                GROUP BY cust_id) R4
        ON R3.cust_id = R4.cust_id
       AND R3.seq = R4.max_seq) TMP_MAX
 ON TMP_MIN.cust_id = TMP_MAX.cust_id);

쿼리가 굉장히 길고 가독성도 좋지 않음
테이블에 4번 접근하므로 성능도 좋지 않음

레코드 간 비교에서도 결합은 불필요

SELECT cust_id,
           SUM(CASE WHEN min_seq = 1 THEN price ELSE 0 END)
  		- SUM(CASE WHEN max_seq = 1 THEN price ELSE 0 END) AS diff
  FROM (SELECT cust_id, price, 
 								ROW_NUMBER() OVER (PARTITION BY cust_id
       															ORDER BY seq) AS min_seq,
              ROW_NUMBER() OVER (PARTITION BY cust_id
              											ORDER BY seq DESC) AS max_seq
        FROM Receipts ) WORK
 WHERE WORK.min_seq = 1
 		OR WORK.max_seq = 1
 GROUP BY cust_id;

최솟값, 최대값은 각각 다른 레코드에 있으므로 이를 계산하기 위해서 CASE 식과 SUM 함수로 두 값을 하나의 레코드에 집약
테이블 스캔 횟수 1회, 윈도우 함수에 의한 정렬 2회

서브쿼리는 정말 나쁠까?

서브쿼리를 사용하면 문제를 분할하여 생각하기가 쉬워짐
그러나 bottom-up 방식의 사고방식은 비절차 지향형 언어인 SQL과 서로 지향하는 방향과 성질이 맞지 않음
서브쿼리 사용이 더 나은 경우
결합할 때는 최대한 결합 대상 레코드 수를 줄이는 것이 중요
옵티마이저가 이러한 것을 잘 판별하지 못할 때 연산 순서를 직접 명시해주면 좋은 성능을 얻을 수 있음
결합과 집약 순서

문제. 회사마다 주요 사업소(main_flg 필드가 Y인 사업소)의 직원 수 구하기

결합 후 집약

SELECT C.co_cd, MAX(C.district),
    SUM(emp_nbr) AS sum_exp
FROM Companies C
      INNER JOIN
        Shops S
  ON C.co_cd = S.co_cd
 WHERE main_flg = 'Y'
 GROUP BY C.co_cd;

집약 후 결합

SELECT C.co_cd, C.district, sum_emp
  FROM Companies C
      INNER JOIN
        (SELECT co_cd, 
                SUM(emp_nbr) AS sum_emp
          FROM Shops
         WHERE main_flg = 'Y'
         GROUP BY co_cd) CSUM
   ON C.co_cd = CSUM.co_cd   

결합 대상 레코드 수
- 결합 후 집약
  - 회사 테이블 : 레코드 4개
  - 사업소 테이블 : 레코드 10개
- 집약 후 결합
  - 회사 테이블 : 레코드 4개
  - 사업소 테이블(CSUM) : 레코드 10개
- 회사 테이블의 규모에 비해 사업 소 테이블의 규모가 매우 크다면, 일단 결합 대상 레코드 수를 집약하는 편이 I/O 비용을 더 줄일 수 있음

서브쿼리

서브쿼리가 일으키는 폐해

서브쿼리의 문제점

서브쿼리 의존증

서브쿼리 의존증 - 응용편

서브쿼리는 정말 나쁠까?

서브쿼리 사용이 더 나은 경우

결합과 집약 순서