JOIN-037

SORT JOIN이 2,000번 돈다며 NL로 바꾼 월 청구서 추출이 읽는 블록이 아홉 배가 됐다

기초SELECT한빛유통187 / 700

상황

「월 청구서 추출」은 매달 1일 새벽, 한 달 치 주문을 고객 순으로 뽑아 청구서 출력 프로그램에 넘긴다. 프로그램은 고객 번호가 바뀔 때마다 새 장을 시작하므로 고객 순서가 꼭 맞아야 한다. 지난달 조민우가 계획의 SORT JOIN 줄 Starts가 2,000인 것을 보고 "고객마다 주문을 다시 정렬하느라 2,000번 돈다"라며 힌트를 USE_MERGE에서 USE_NL로 바꿨다. 이번 달 추출은 읽는 블록이 932에서 8,249로 늘었다. 아래는 2024년 6월로 돌린 지금의 계획과 바꾸기 전의 계획이다.

테이블과 인덱스

M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · 고객 번호 순으로 적재되어 있다
T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_CUS_DT (CUS_ID, ORD_DT) · 한 고객의 주문은 테이블 여기저기에 흩어져 있다
2024년 6월 주문 4,172건 · 그달 주문한 고객 1,752명 · 통계 최신

원본 SQL

sql

SELECT /*+ LEADING(c o) USE_NL(o) INDEX(o IX_T_ORD_CUS_DT) */
       c.CUS_ID, c.CUS_NM, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM M_CUS c, T_ORD o
 WHERE o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to
 ORDER BY c.CUS_ID
-- 바꾸기 전 힌트 : /*+ LEADING(c o) USE_MERGE(o) */

실행계획 학습용 예시 · 머지 조인 모양은 교재 06화 실측과 같다

xplan

[지금 · USE_NL]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |   4172 |00:00:00.11 |    8249 |
|   1 |  NESTED LOOPS                 |                 |      1 |   4170 |   4172 |00:00:00.11 |    8249 |
|   2 |   NESTED LOOPS                |                 |      1 |   4170 |   4172 |00:00:00.04 |    4077 |
|   3 |    TABLE ACCESS BY INDEX ROWID| M_CUS           |      1 |   2000 |   2000 |00:00:00.01 |      36 |
|   4 |     INDEX FULL SCAN           | PK_M_CUS        |      1 |   2000 |   2000 |00:00:00.01 |       6 |
|*  5 |    INDEX RANGE SCAN           | IX_T_ORD_CUS_DT |   2000 |      2 |   4172 |00:00:00.02 |    4041 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T_ORD           |   4172 |      2 |   4172 |00:00:00.06 |    4172 |
-----------------------------------------------------------------------------------------------------------
   5 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO)

[바꾸기 전 · USE_MERGE]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   4172 |00:00:00.02 |     932 |          |
|   1 |  MERGE JOIN                   |          |      1 |   4170 |   4172 |00:00:00.02 |     932 |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | M_CUS    |      1 |   2000 |   2000 |00:00:00.01 |      36 |          |
|   3 |    INDEX FULL SCAN            | PK_M_CUS |      1 |   2000 |   2000 |00:00:00.01 |       6 |          |
|*  4 |   SORT JOIN                   |          |   2000 |   4170 |   4172 |00:00:00.01 |     896 |  267K (0)|
|*  5 |    TABLE ACCESS FULL          | T_ORD    |      1 |   4170 |   4172 |00:00:00.01 |     896 |          |
----------------------------------------------------------------------------------------------------------
   4 - access("O"."CUS_ID"="C"."CUS_ID")
       filter("O"."CUS_ID"="C"."CUS_ID")
   5 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO))

문제

바꾸기 전 계획의 4번 줄 SORT JOIN Starts 2,000이 무엇을 센 것인지 설명하고, 조민우의 해석이 왜 틀렸는지 쓰시오.
지금 계획에서 8,249블록이 어디서 나오는지 줄 번호와 Starts로 설명하시오.
이 추출에 맞는 조인 방식을 정해 SQL을 고치고, 해시 조인과 비교해 그 방식이 나은 이유를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 첫 번째 입력 M_CUS 는 PK 로 고객 순서대로 읽고(정렬 없음), 주문 4,172 건만 한 번 정렬해 맞댄다
SELECT /*+ LEADING(c o) USE_MERGE(o) */
       c.CUS_ID, c.CUS_NM, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM M_CUS c, T_ORD o
 WHERE o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to
 ORDER BY c.CUS_ID
-- 해시로 써도 된다 : LEADING(c o) USE_HASH(o) 는 926 블록 + 4,172 행 SORT ORDER BY

개선 후 실행계획 학습용 예시 · 머지 조인 모양은 교재 06화 실측과 같다

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   4172 |00:00:00.02 |     932 |          |
|   1 |  MERGE JOIN                   |          |      1 |   4170 |   4172 |00:00:00.02 |     932 |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | M_CUS    |      1 |   2000 |   2000 |00:00:00.01 |      36 |          |
|   3 |    INDEX FULL SCAN            | PK_M_CUS |      1 |   2000 |   2000 |00:00:00.01 |       6 |          |
|*  4 |   SORT JOIN                   |          |   2000 |   4170 |   4172 |00:00:00.01 |     896 |  267K (0)|
|*  5 |    TABLE ACCESS FULL          | T_ORD    |      1 |   4170 |   4172 |00:00:00.01 |     896 |          |
----------------------------------------------------------------------------------------------------------
   4 - access("O"."CUS_ID"="C"."CUS_ID")
       filter("O"."CUS_ID"="C"."CUS_ID")
   5 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO))

[해시 조인이라면] HASH JOIN 926 블록 + SORT ORDER BY 4,172 행 (결과가 고객 순서가 아니다)

모범 답안 서술

바꾸기 전 계획에서 T_ORD는 5번 줄에서 한 번만 FULL로 읽혔다(Starts 1, 896블록). 4번 줄 SORT JOIN은 그 결과 4,172건을 고객 번호로 한 번 정렬해 PGA에 두었다(267K, optimal). SORT JOIN의 Starts 2,000은 테이블을 2,000번 다시 읽었다는 뜻이 아니다. 위쪽 고객 2,000명이 한 명씩 내려올 때마다, 이미 정렬된 주문 목록에서 그 고객의 자리를 찾아 읽던 위치를 되감은 횟수다. 메모리 안에서 손가락만 옮기는 일이라 블록을 더 읽지 않는다(4번 줄 Buffers 896 = 5번 줄 896). 지금의 NL은 다르다. 고객 2,000명마다 IX_T_ORD_CUS_DT를 찾아 들어가고(5번 줄 Starts 2,000, 4,041블록), 찾은 주문 4,172건마다 T_ORD 블록을 하나씩 찾아간다(6번 줄 Starts 4,172). 한 고객의 주문이 테이블에 흩어져 있어서 테이블 랜덤 액세스가 한 건에 한 블록씩 든다. 그달 주문이 없는 고객 248명에게도 인덱스를 한 번씩 찾아갔다. 그래서 932 → 8,249블록이 됐다. 바꾸기 전 계획이 맞다. 첫 번째 입력인 M_CUS는 PK_M_CUS INDEX FULL SCAN으로 이미 고객 번호 순서대로 읽히므로 따로 정렬하지 않는다. 머지 조인의 결과도 그 순서대로 나오므로 ORDER BY c.CUS_ID를 위한 SORT ORDER BY가 계획에 없다. 힌트를 USE_MERGE(o)로 되돌리면 Buffers는 8,249 → 932가 된다. 해시 조인(USE_HASH(o))도 두 테이블을 한 번씩 읽어 926블록이지만, 결과가 고객 순서로 나오지 않아 4,172행을 SORT ORDER BY로 한 번 더 정렬해야 한다. 이 추출처럼 조인 키의 순서가 곧 결과의 순서라면 소트 머지가 정렬 한 번을 아낀다.

해설

Starts는 줄마다 뜻이 다르다: NL inner의 Starts는 왕복 횟수지만, 소트 머지 아래쪽 SORT JOIN의 Starts는 첫 번째 입력의 행 수만큼 되감은 횟수다. 비용은 Buffers와 Used-Mem으로 판단한다 (교재 06화 §2).
소트 머지가 나은 경우: 첫 번째 입력을 인덱스로 조인 키 순서대로 읽어 정렬을 생략할 수 있고, 결과도 조인 키 순서로 내야 할 때다. 등치 조건이 아닌 조인처럼 해시 조인을 쓸 수 없을 때도 소트 머지가 후보가 된다.
두 번째 입력은 언제나 정렬한다: 오라클은 머지 조인의 두 번째 입력에 늘 SORT JOIN을 둔다. 그래서 작은 쪽, 또는 인덱스로 순서를 얻을 수 없는 쪽을 두 번째에 둔다. 여기서는 4,172건이라 267K로 끝났다.
힌트 대장: USE_MERGE로 되돌릴 때 "결과 순서가 조인 키 순서와 같고, 한 달 주문이 수천 건"이라는 근거를 힌트 유효기간 대장에 함께 적는다. 한 달 주문이 수십만 건으로 늘어 정렬이 work area를 넘으면 다시 잰다 (교재 06화 §8).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 머지 조인 모양은 교재 06화 실측과 같다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 머지 조인 모양은 교재 06화 실측과 같다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황