JOIN-042

영수증 번호 순 연간 판매 상세 파일이 해시 조인 뒤에 55만 줄을 다시 정렬하다가 TEMP를 쓴다

중급SELECT유통192 / 700

상황

본사 회계팀은 분기 마감마다 「연간 판매 상세 파일」을 받는다. 2023년 판매상품 줄마다 판매 금액과 판매 헤더의 판매일, 요일, 매장을 붙여 영수증 번호 순으로 내려보내야 회계 시스템이 영수증 단위로 읽어 들인다(같은 영수증의 줄은 붙어 있기만 하면 된다). 파일 생성에 4초가 걸리고 돌릴 때마다 TEMP 경보가 울린다. DBA는 "조인 다음의 정렬이 TEMP를 쓴다"라고 했다. 아래는 2023년 한 해의 실행계획이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · SLE_DT VARCHAR2(8) · 2023년 250,128건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_NO의 앞 8자리가 판매일자 · SLE_NO 순으로 적재되어 있다
2023년 판매상품 548,397줄(CF11_PK 리프 약 2,140블록, 테이블 약 4,100블록)
정렬 하나가 쓸 수 있는 work area는 약 25M다 · 통계 최신

원본 SQL

sql

SELECT b.SLE_NO, b.ITM_NO, b.SLE_QTY, b.SLE_QTY * b.SLE_UPRC AS SLE_AMT,
       a.SLE_DT, a.SLE_DOW, a.SHP_NO
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN '20230101' AND '20231231'
   AND b.SLE_NO = a.SLE_NO
   AND b.SLE_NO >= '20230101'
   AND b.SLE_NO <  '20240101'
 ORDER BY b.SLE_NO

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |    548K|00:00:04.12 |   12787 |  5248 |   5248 |       |       |          |         |
|   1 |  SORT ORDER BY      |      |      1 |    548K|    548K|00:00:04.12 |   12787 |  5248 |   5248 |    44M|  2365K|   24M (1)|     41M |
|*  2 |   HASH JOIN         |      |      1 |    548K|    548K|00:00:01.31 |   12787 |     0 |      0 |    18M|  3245K|   21M (0)|         |
|*  3 |    TABLE ACCESS FULL| CF10 |      1 |    250K|    250K|00:00:00.07 |    4583 |     0 |      0 |       |       |          |         |
|*  4 |    TABLE ACCESS FULL| CF11 |      1 |    548K|    548K|00:00:00.19 |    8204 |     0 |      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   3 - filter(("A"."SLE_DT">='20230101' AND "A"."SLE_DT"<='20231231'))
   4 - filter(("B"."SLE_NO">='20230101' AND "B"."SLE_NO"<'20240101'))

문제

4.12초와 TEMP가 계획의 어느 줄, 어느 열에서 나오는지 설명하고, 해시 조인 뒤에 정렬이 따로 필요한 이유를 쓰시오.
정렬을 줄이는 조인 방식으로 SQL을 고치시오. 두 테이블 중 어느 쪽을 먼저 두어야 하는지와 그 이유도 쓰시오.
NL 조인으로 바꾸는 안과 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 첫 번째 입력 CF11 은 CF11_PK 로 영수증 번호 순서대로 읽는다(정렬 없음).
-- 두 번째 입력 CF10 2023 년 250K 행만 SORT JOIN 으로 정렬하고, 결과가 영수증 순으로 나오므로 SORT ORDER BY 가 없다
SELECT /*+ LEADING(b a) USE_MERGE(a) INDEX(b CF11_PK) */
       b.SLE_NO, b.ITM_NO, b.SLE_QTY, b.SLE_QTY * b.SLE_UPRC AS SLE_AMT,
       a.SLE_DT, a.SLE_DOW, a.SHP_NO
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN '20230101' AND '20231231'
   AND b.SLE_NO = a.SLE_NO
   AND b.SLE_NO >= '20230101'
   AND b.SLE_NO <  '20240101'
 ORDER BY b.SLE_NO

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

xplan

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |    548K|00:00:01.58 |   10823 |       |       |          |
|   1 |  MERGE JOIN                  |         |      1 |    548K|    548K|00:00:01.58 |   10823 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CF11    |      1 |    548K|    548K|00:00:00.61 |    6240 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | CF11_PK |      1 |    548K|    548K|00:00:00.14 |    2140 |       |       |          |
|*  4 |   SORT JOIN                  |         |    548K|    250K|    548K|00:00:00.69 |    4583 |    16M|  1471K|   14M (0)|
|*  5 |    TABLE ACCESS FULL         | CF10    |      1 |    250K|    250K|00:00:00.07 |    4583 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."SLE_NO">='20230101' AND "B"."SLE_NO"<'20240101')
   4 - access("B"."SLE_NO"="A"."SLE_NO")
       filter("B"."SLE_NO"="A"."SLE_NO")
   5 - filter(("A"."SLE_DT">='20230101' AND "A"."SLE_DT"<='20231231'))

모범 답안 서술

원본은 1번 줄 SORT ORDER BY에서 시간과 TEMP를 쓴다. 2번 줄 해시 조인은 결과를 해시 테이블의 순서대로 내보내므로 영수증 번호 순서가 없다. 그래서 조인이 끝난 548K줄을 다시 정렬해야 했고, 정렬할 양(OMem 44M)이 work area(약 25M)를 넘어 Used-Mem 24M (1), 곧 onepass로 Used-Tmp 41M을 TEMP에 썼다가 다시 읽었다(Reads와 Writes 5,248). 4.12초 중 2.8초가 이 정렬이다. CF11_PK는 (SLE_NO, ITM_NO) 순서이므로 CF11을 이 인덱스로 읽으면 이미 영수증 번호 순서다. 소트 머지 조인의 첫 번째 입력으로 CF11을 두면 첫 번째 입력은 정렬하지 않고, 결과도 그 순서대로 나오므로 SORT ORDER BY가 없어진다. 두 번째 입력인 CF10 2023년 250K행만 SORT JOIN으로 정렬하는데, 조인 키와 세 컬럼만 담아 14M (0)으로 메모리 안에서 끝난다. 순서가 중요하다. 오라클은 머지 조인의 두 번째 입력을 언제나 정렬하므로, CF10을 첫 번째에 두면 CF11 548K줄을 SORT JOIN으로 정렬해야 해서 원본과 비슷해진다. 정렬을 피할 수 있는 큰 쪽(인덱스로 순서대로 읽히는 CF11)을 첫 번째에 둔다. 고친 뒤 Buffers는 12,787 → 10,823, TEMP는 41M → 0, 시간은 4.12초 → 1.58초다. CF11을 FULL 대신 인덱스로 읽지만 테이블이 SLE_NO 순으로 적재되어 있어 블록을 거의 한 번씩만 방문한다. NL 조인(CF11을 인덱스로 읽고 CF10_PK를 548K번 찾아간다)은 정렬이 없지만 약 160만 블록을 읽어 이 파일에는 맞지 않는다.

해설

소트 머지가 나은 경우: 첫 번째 입력을 인덱스로 조인 키 순서대로 읽을 수 있고, 결과도 그 순서로 내야 할 때다. 해시 조인은 순서를 지키지 못해 결과를 한 번 더 정렬해야 한다. 등치가 아닌 조인처럼 해시 조인을 쓸 수 없는 경우도 소트 머지의 자리다.
정렬할 집합부터 줄인다: 원본의 정렬은 SELECT의 일곱 컬럼 548K줄이었고, 머지 조인의 정렬은 CF10의 네 컬럼 250K행이다. PGA를 늘리기 전에 정렬할 집합의 행 수와 폭부터 줄인다 (교재 09화 §4, 06화 §6).
전제를 적어 둔다: 이 계획은 회계 시스템이 영수증 번호 순서만 요구한다는 전제에 기댄다. 같은 영수증 안의 상품 순서(ITM_NO)까지 요구가 바뀌면 ORDER BY b.SLE_NO, b.ITM_NO에서도 정렬이 생략되는지 계획으로 다시 확인한다.
물리 읽기: 버퍼 캐시에 CF11이 없으면 인덱스를 거친 테이블 액세스는 싱글 블록 읽기 4,100번이 된다. 클러스터링 팩터가 좋아도 FULL의 멀티블록 읽기보다 느릴 수 있으므로 운영 시간대의 캐시 상태로 재 본다 (교재 04화 §2).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황