주문상세 조인을 걷어냈는데도 「연간 주문 목록」 내려받기는 그대로 무겁다

중급SELECT한빛유통346 / 700

상황

정산팀의 「연간 주문 목록」 내려받기는 2024년 주문에 고객 이름과 등급을 붙여 엑셀로 내린다(순서는 엑셀에서 정한다). 2년 전에는 '특정 상품군이 들어간 주문만' 고르려고 주문상세(T_ORD_DET)를 함께 조인했는데, 주문 하나가 상세 줄 수만큼 여러 번 나와서 DISTINCT를 붙였다. 지난달 상품군 조건이 없어지면서 개발자가 주문상세 조인을 걷어냈다. 그런데 내려받기는 여전히 2.8초가 걸리고, 월초에 여러 명이 동시에 받으면 TEMP를 쓴다. 개발자는 "조인을 하나 뺐는데 왜 그대로인지 모르겠다"고 한다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_SEQ NOT NULL(시퀀스로 채운다, 유일 제약은 없다) · 2024년 주문 333,968행
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · T_ORD_BIG.CUS_ID는 NOT NULL이고 모든 값이 M_CUS에 있다
내려받기 도구는 한 번에 5,000행씩 가져온다. 월초에는 동시 작업이 많아 work area 상한이 약 12MB다. 통계는 최신이다.

원본 SQL

sql

SELECT DISTINCT o.ORD_SEQ, o.ORD_DT, o.CUS_ID, c.CUS_NM, c.CUS_GD, o.ORD_ST, o.ORD_AMT
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID
   AND o.ORD_DT >= DATE '2024-01-01'
   AND o.ORD_DT <  DATE '2025-01-01'

실행계획 학습용 예시

xplan

[월초 · 동시 내려받기]
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |    333K|00:00:02.84 |    5863 |       |       |          |         |
|   1 |  HASH UNIQUE        |           |      1 |    334K|    333K|00:00:02.84 |    5863 |    24M|  3511K|   12M (1)|      21M|
|*  2 |   HASH JOIN         |           |      1 |    334K|    333K|00:00:00.33 |    5856 |  1335K|  1335K| 1572K (0)|         |
|   3 |    TABLE ACCESS FULL | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |       |       |          |         |
|*  4 |    TABLE ACCESS FULL | T_ORD_BIG |      1 |    334K|    333K|00:00:00.12 |    5826 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")
   4 - filter(("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')))

문제

주문상세 조인을 걷어낸 지금 이 SQL에서 DISTINCT가 지우는 줄이 있는지 테이블 관계로 판단하고, 1번 줄이 하는 일과 그 비용을 실행계획으로 설명하시오.
결과가 바뀌지 않게 SQL을 고치고, 고치기 전에 무엇을 확인해야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 중복을 만들던 주문상세 조인은 이미 없다 · 고객은 PK 로 한 명씩 붙고 ORD_SEQ 는 주문마다 하나 → DISTINCT 를 뗀다
-- 떼기 전 확인 (0 건이어야 한다) :
--   SELECT ORD_SEQ FROM T_ORD_BIG GROUP BY ORD_SEQ HAVING COUNT(*) > 1;
SELECT o.ORD_SEQ, o.ORD_DT, o.CUS_ID, c.CUS_NM, c.CUS_GD, o.ORD_ST, o.ORD_AMT
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID
   AND o.ORD_DT >= DATE '2024-01-01'
   AND o.ORD_DT <  DATE '2025-01-01'

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

xplan

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |    333K|00:00:00.41 |    5923 |       |       |          |         |
|*  1 |  HASH JOIN         |           |      1 |    334K|    333K|00:00:00.41 |    5923 |  1335K|  1335K| 1572K (0)|         |
|   2 |   TABLE ACCESS FULL| M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |       |       |          |         |
|*  3 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |    334K|    333K|00:00:00.18 |    5893 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."CUS_ID"="O"."CUS_ID")
   3 - filter(("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')))

모범 답안 서술

지금 이 SQL에서 DISTINCT가 지울 줄은 없다. 중복을 만들던 주문상세 조인(1:M)은 이미 걷어냈고, 남은 M_CUS는 PK_M_CUS로 주문마다 고객 한 명씩 붙는 M:1 조인이라 주문 수를 늘리지 않는다. SELECT 절에 ORD_SEQ가 있고 주문번호는 주문마다 하나이므로 모든 줄이 이미 서로 다르다. 그래도 1번 줄 HASH UNIQUE는 2024년 주문 333,968줄을 모두 해시 표에 올려 같은 줄이 있는지 확인한다. 지울 것이 없다는 것도 다 올려 봐야 알기 때문이다. 줄마다 고객 이름까지 실려 필요한 메모리가 24MB(OMem)인데, 월초의 상한 약 12MB에 걸려 12M (1), 곧 one-pass로 21MB를 TEMP에 썼다가 다시 읽었다. 2.8초의 대부분이 이 중복 제거다. 조인을 하나 뺐어도 가장 비싼 일은 남아 있었던 것이다. DISTINCT를 떼면 1번 줄이 사라져 해시 조인 결과를 바로 내보낸다. TEMP는 쓰지 않고 시간은 2.8초 → 0.4초다. Buffers는 5,863 → 5,923으로 조금 는다. 중복 제거가 있을 때는 테이블을 한 번에 다 읽었지만, 이제는 Fetch Call(5,000행씩 약 67번)마다 테이블 블록을 다시 찾기 때문이다. 떼기 전에 결과가 바뀌지 않는다는 것을 확인한다. ORD_SEQ에는 유일 제약이 없으므로 '시퀀스로 채운다'는 말 대신 GROUP BY ORD_SEQ HAVING COUNT(*) > 1 확인 쿼리로 중복 0건을 확인한다. 앞으로도 유일해야 한다면 유일 제약을 거는 것이 약속을 남기는 방법이다.

해설

'혹시 몰라서' 남긴 DISTINCT: 중복이 생기는 까닭(1:M 조인)이 사라지면 DISTINCT도 함께 떼야 한다. 남겨 두면 결과는 같으면서 행 수만큼의 중복 제거가 매번 돈다. 반대로 1:M 조인이 남아 있다면 DISTINCT 대신 EXISTS로 고친다(SORT-003, SORT-038).
순서가 필요해 ORDER BY를 붙이면 HASH UNIQUE 대신 같은 크기의 SORT ORDER BY가 생긴다. 이 내려받기는 엑셀에서 정렬하므로 DB에서 줄 세우지 않는다.
유일 제약을 걸면 유일 인덱스가 하나 생겨 주문을 넣을 때마다 고칠 인덱스가 는다(교재 04화, 14화). 제약 없이 확인 쿼리를 정합성 점검에 넣어 두는 것도 방법이다.
유일 제약이 있으면 옵티마이저가 필요 없는 중복 제거를 스스로 빼는 변환을 쓰는 버전도 있다. 이 테이블에는 제약이 없어서 그럴 근거가 없었다. 옵티마이저는 데이터가 아니라 제약을 보고 판단한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황