연말 감사 선물 대상 스무 명을 뽑는 화면이 주문 테이블을 3,340번 찾아간다

기초SELECT한빛유통308 / 700

상황

마케팅팀의 「연말 감사 선물 대상」 확인 화면은 "A 등급 고객 중 올해(2024년) 구매 완료 주문이 한 번이라도 있는 고객"을 뽑는다. 대상을 확정하기 전까지 하루에 수십 번 돌린다. 개발자는 '한 번이라도 있는'을 HAVING COUNT(*) >= 1로 옮겼다.

테이블과 인덱스

M_CUS: 2,000행 · 30블록 · CUS_GD A 1%(20명)
T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객당 약 500건 · 2024년이 33%(고객당 약 167건) · ORD_ST COMPLETE 90%
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT)가 있다(CS 화면용). 한 고객의 주문은 테이블 곳곳의 블록에 흩어져 있다. 통계는 최신이다.

원본 SQL

sql

SELECT c.CUS_ID, c.CUS_NM, c.CUS_GD
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.CUS_GD = 'A'
   AND o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= DATE '2024-01-01'
   AND o.ORD_DT <  DATE '2025-01-01'
   AND o.ORD_ST = 'COMPLETE'
 GROUP BY c.CUS_ID, c.CUS_NM, c.CUS_GD
HAVING COUNT(*) >= 1
 ORDER BY c.CUS_ID

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |     20 | 00:00:00.03 |    3443 |
|*  1 |  SORT GROUP BY                 |                   |      1 |     20 |     20 | 00:00:00.03 |    3443 |
|   2 |   NESTED LOOPS                 |                   |      1 |   3006 |   3006 | 00:00:00.03 |    3443 |
|   3 |    NESTED LOOPS                |                   |      1 |   3340 |   3340 | 00:00:00.01 |     103 |
|*  4 |     TABLE ACCESS FULL          | M_CUS             |      1 |     20 |     20 | 00:00:00.01 |      30 |
|*  5 |     INDEX RANGE SCAN           | IX_ORD_BIG_CUS_DT |     20 |    167 |   3340 | 00:00:00.01 |      73 |
|*  6 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |   3340 |      1 |   3006 | 00:00:00.02 |    3340 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(COUNT(*)>=1)
   4 - filter("C"."CUS_GD"='A')
   5 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - filter("O"."ORD_ST"='COMPLETE')

문제

결과는 20명인데 실행계획이 주문을 몇 건 찾고 몇 블록을 읽었는지 쓰고, 그 일이 왜 필요 없는지 설명하시오.
결과가 바뀌지 않게 SQL을 고치고, 고친 SQL이 고객마다 '어디서 멈추는지'를 설명하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

SELECT c.CUS_ID, c.CUS_NM, c.CUS_GD
  FROM M_CUS c
 WHERE c.CUS_GD = 'A'
   AND EXISTS (SELECT 1
                 FROM T_ORD_BIG o
                WHERE o.CUS_ID = c.CUS_ID
                  AND o.ORD_DT >= DATE '2024-01-01'
                  AND o.ORD_DT <  DATE '2025-01-01'
                  AND o.ORD_ST = 'COMPLETE')
 ORDER BY c.CUS_ID

개선 후 실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |     20 | 00:00:00.01 |     112 |
|   1 |  SORT ORDER BY                 |                   |      1 |     20 |     20 | 00:00:00.01 |     112 |
|   2 |   NESTED LOOPS SEMI            |                   |      1 |     20 |     20 | 00:00:00.01 |     112 |
|*  3 |    TABLE ACCESS FULL           | M_CUS             |      1 |     20 |     20 | 00:00:00.01 |      30 |
|*  4 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |     20 |      1 |     20 | 00:00:00.01 |      82 |
|*  5 |     INDEX RANGE SCAN           | IX_ORD_BIG_CUS_DT |     20 |    167 |     22 | 00:00:00.01 |      60 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("C"."CUS_GD"='A')
   4 - filter("O"."ORD_ST"='COMPLETE')
   5 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

모범 답안 서술

결과는 20명인데, 6번 줄이 테이블을 3,340번 찾아가(Starts 3,340) 2024년 구매 완료 주문 3,006건을 다 확인하고, 1번 줄이 그 3,006건을 고객별로 묶어 20줄로 줄인다. Buffers 3,443의 대부분(3,340)은 '이미 주문이 있다고 확인된 고객'의 나머지 주문을 확인하는 데 쓰였다. HAVING COUNT(*) >= 1은 내부 조인 뒤에는 언제나 참이다. 그래서 조건 노릇은 하지 못하고 '조건에 맞는 주문을 다 세라'는 지시가 된다. 옵티마이저도 중간에 멈출 수 없다. EXISTS로 '있는가'만 물으면 세미 조인(NESTED LOOPS SEMI)이 된다. 고객 한 명마다 인덱스로 2024년 첫 주문을 찾아 COMPLETE이면 그 자리에서 멈추고 다음 고객으로 넘어간다(20명 × 약 1.1건). Buffers는 3,443 → 112로 준다. 조인 행과 GROUP BY가 사라져 묶을 것도 없다.

해설

DISTINCT로 썼다면: SELECT DISTINCT c.…도 '짝을 다 만들고 지워라'라는 뜻이다. 12c 이상의 옵티마이저는 SELECT 목록이 한쪽 테이블 컬럼뿐인 DISTINCT 조인을 스스로 세미 조인으로 바꾸기도 한다(Partial Join, 교재 08화에서 SUM(DISTINCT)가 세미 조인으로 바뀐 변환이다). 하지만 COUNT(*)처럼 중복 수에 따라 답이 바뀌는 집계가 끼면 바꾸지 못한다. 변환을 믿기보다 뜻을 SQL에 그대로 적는다.
IN (SELECT o.CUS_ID FROM T_ORD_BIG o WHERE …)도 같은 세미 조인으로 펼쳐진다.
세미 조인이 언제나 NL인 것은 아니다. 바깥 집합이 크면(C 등급 1,800명) 고객마다 인덱스를 찾는 것보다 T_ORD_BIG를 한 번 읽는 해시 세미 조인(약 5,860)이 싸다. 어느 쪽을 쓸지는 옵티마이저가 비용으로 고른다.
첫 건이 늦게 나오는 조건(예: CANCEL 2%)이면 고객마다 수십 건을 지나서야 멈춘다. 인덱스를 (CUS_ID, ORD_DT, ORD_ST)로 넓히면 확인이 인덱스 안에서 끝나지만, 그만큼 적재 비용이 든다(교재 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황