JOIN-028

시간대별 주문 집계에 USE_HASH를 줬는데 계획에는 MERGE JOIN이 나오고 TEMP를 쓴다

중급SELECT버거178 / 700

상황

본사 영업기획팀의 「매장 · 시간대별 주문 수」는 2013년 주문을 매장과 시간대(아침, 점심, 오후, 저녁, 야간, 심야)로 나눠 센다. 시간대 구간은 마케팅팀이 관리하는 구간표 TM_BAND에 있다. 개발자는 큰 테이블 조인이니 해시 조인이 맞다며 USE_HASH를 넣었다. 그런데 실행계획에는 MERGE JOIN이 나오고, 돌 때마다 TEMP를 쓰며 3초가 걸린다. 구간표는 지금까지 늘 정시에서 바뀌었다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_HMS VARCHAR2(6) 'HHMISS' (0을 채운 6자리) · 2013년 500,218행 · SHOP_NO 200곳
TM_BAND 시간대 구간표 (이 문제에서 쓰는 파생 테이블): 6행 · 4블록 · BAND_CD · BAND_NM · STA_HMS · END_HMS (예: 점심 '110000' ~ '135959') · 구간은 겹치지 않고 하루 24시간을 빈틈없이 덮는다 · 모든 경계가 정시다
통계 최신 · pga_aggregate_target 512M · AUTO

원본 SQL

sql

SELECT /*+ USE_HASH(o b) */
       o.SHOP_NO, b.BAND_NM, COUNT(*) AS ord_cnt
  FROM ORD o, TM_BAND b
 WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
   AND o.ORD_HMS BETWEEN b.STA_HMS AND b.END_HMS
 GROUP BY o.SHOP_NO, b.BAND_NM

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |      1 |        |   1200 |00:00:03.12 |   22426 |  2187 |   2187 |       |       |          |         |
|   1 |  HASH GROUP BY        |         |      1 |   1200 |   1200 |00:00:03.12 |   22426 |  2187 |   2187 |  1345K|  1345K| 1402K (0)|         |
|   2 |   MERGE JOIN          |         |      1 |   7503 |    500K|00:00:02.96 |   22426 |  2187 |   2187 |       |       |          |         |
|   3 |    SORT JOIN          |         |      1 |      6 |      6 |00:00:00.01 |       4 |     0 |      0 |  2048 |  2048 | 2048  (0)|         |
|   4 |     TABLE ACCESS FULL | TM_BAND |      1 |      6 |      6 |00:00:00.01 |       4 |     0 |      0 |       |       |          |         |
|*  5 |    FILTER             |         |      6 |        |    500K|00:00:02.88 |   22422 |  2187 |   2187 |       |       |          |         |
|*  6 |     SORT JOIN         |         |      6 |    500K|   1708K|00:00:02.61 |   22422 |  2187 |   2187 |    15M|  1489K|   13M (1)|      17M|
|*  7 |      TABLE ACCESS FULL| ORD     |      1 |    500K|    500K|00:00:00.31 |   22422 |     0 |      0 |       |       |          |         |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("O"."ORD_HMS"<="B"."END_HMS")
   6 - access(INTERNAL_FUNCTION("O"."ORD_HMS")>=INTERNAL_FUNCTION("B"."STA_HMS"))
       filter(INTERNAL_FUNCTION("O"."ORD_HMS")>=INTERNAL_FUNCTION("B"."STA_HMS"))
   7 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

문제

USE_HASH 힌트가 왜 무시됐는지 조인 조건으로 설명하고, 6번 줄 SORT JOIN이 무엇을 정렬하다 TEMP를 썼는지 설명하시오.
구간표를 기준으로 그대로 쓰면서 큰 테이블 쪽 조인이 등치 조건이 되도록 SQL을 고치시오. 고친 SQL이 맞으려면 구간표에 어떤 전제가 필요한지 쓰시오.
시간대를 CASE 식으로 SQL에 박아 넣는 안, ORD에 시간대 컬럼을 더하는 안과 비교해 대가를 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 구간표 6행을 24시간으로 펼친다(작은 쪽에서만 부등호). 큰 쪽 ORD 는 시(앞 두 자리)의 '=' 로 해시 조인
SELECT o.SHOP_NO, h.BAND_NM, COUNT(*) AS ord_cnt
  FROM ORD o,
       (SELECT b.BAND_NM, x.HH
          FROM TM_BAND b,
               (SELECT LPAD(LEVEL - 1, 2, '0') AS HH FROM DUAL CONNECT BY LEVEL <= 24) x
         WHERE x.HH || '0000' BETWEEN b.STA_HMS AND b.END_HMS) h
 WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
   AND h.HH = SUBSTR(o.ORD_HMS, 1, 2)
 GROUP BY o.SHOP_NO, h.BAND_NM;

-- 전제 점검: 펼친 결과가 24행이고 시(HH)가 겹치지 않아야 한다 (24 · 24 가 나와야 한다)
SELECT COUNT(*), COUNT(DISTINCT x.HH)
  FROM TM_BAND b,
       (SELECT LPAD(LEVEL - 1, 2, '0') AS HH FROM DUAL CONNECT BY LEVEL <= 24) x
 WHERE x.HH || '0000' BETWEEN b.STA_HMS AND b.END_HMS;

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

xplan

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |      1 |        |   1200 |00:00:00.52 |   22426 |       |       |          |
|   1 |  HASH GROUP BY                    |         |      1 |   1200 |   1200 |00:00:00.52 |   22426 |  1345K|  1345K| 1402K (0)|
|*  2 |   HASH JOIN                       |         |      1 |   5002 |    500K|00:00:00.41 |   22426 |  1236K|  1236K| 1190K (0)|
|   3 |    VIEW                           |         |      1 |      1 |     24 |00:00:00.01 |       4 |       |       |          |
|   4 |     NESTED LOOPS                  |         |      1 |      1 |     24 |00:00:00.01 |       4 |       |       |          |
|   5 |      TABLE ACCESS FULL            | TM_BAND |      1 |      6 |      6 |00:00:00.01 |       4 |       |       |          |
|*  6 |      VIEW                         |         |      6 |      1 |     24 |00:00:00.01 |       0 |       |       |          |
|   7 |       CONNECT BY WITHOUT FILTERING|         |      6 |        |    144 |00:00:00.01 |       0 |       |       |          |
|   8 |        FAST DUAL                  |         |      6 |      1 |      6 |00:00:00.01 |       0 |       |       |          |
|*  9 |    TABLE ACCESS FULL              | ORD     |      1 |    500K|    500K|00:00:00.29 |   22422 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("H"."HH"=SUBSTR("O"."ORD_HMS",1,2))
   6 - filter(("X"."HH"||'0000'>="B"."STA_HMS" AND "X"."HH"||'0000'<="B"."END_HMS"))
   9 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

모범 답안 서술

조인 조건이 o.ORD_HMS BETWEEN b.STA_HMS AND b.END_HMS 하나뿐이고, 등치 조건이 없다. 해시 조인은 해시 함수로 같은 값을 같은 버킷에 모아 짝을 찾는 방식이라 등치 조건에서만 쓸 수 있다. 그래서 USE_HASH는 쓸 수 없는 힌트가 되어 무시됐고, 옵티마이저는 부등호 조인을 처리할 수 있는 소트 머지 조인을 골랐다. 소트 머지는 양쪽을 조인 키로 줄 세운 뒤 맞춰 본다. 6번 줄 SORT JOIN이 2013년 주문 50만 건을 ORD_HMS 순서로 정렬했다. OMem 15M가 work area에 다 들어가지 않아 13M (1), 곧 onepass로 돌았고 TEMP에 17M를 썼다(Reads/Writes 2,187). 구간 6개마다 정렬된 주문을 시작 시각부터 훑어 내려가며 끝 시각으로 걸렀기 때문에 6번 줄 A-Rows가 1,708K, 5번 줄 FILTER가 그중 500K를 남겼다. 3.12초의 대부분이 이 정렬과 훑기에 들었다. 큰 쪽 ORD의 조인을 등치 조건으로 바꾼다. 구간표의 경계가 모두 정시이므로 주문 시각의 앞 두 자리(시)만 알면 시간대가 하나로 정해진다. 구간표 6행을 0 ~ 23시의 24행으로 펼치고(부등호는 이 작은 쪽에서만 쓴다), ORD와는 h.HH = SUBSTR(o.ORD_HMS, 1, 2)로 붙인다. 그러면 24행이 build인 해시 조인이 되어 50만 건을 정렬하지 않는다. Used-Mem은 1,190K (0)이고 TEMP는 0이며, A-Time은 3.12 → 0.52초로 준다. Buffers는 22,426 그대로다. 이 SQL이 맞으려면 구간표에 전제가 있다. 구간 경계가 모두 정시여야 하고, 구간이 겹치지 않으며 24시간을 빈틈없이 덮어야 한다. 펼친 결과가 24행이고 시가 겹치지 않는지 확인하는 점검 SQL을 함께 둔다. 마케팅팀이 '11시 30분부터 점심' 같은 구간을 만들면 시 단위로는 맞지 않으므로, 그때는 분 단위(1,440행)로 펼친다. CASE WHEN o.ORD_HMS < '060000' THEN ...처럼 SQL에 시간대를 박아 넣으면 조인이 없어져 가장 빠르지만, 마케팅팀이 구간표를 바꿀 때마다 SQL을 고쳐 배포해야 하고 구간표와 SQL이 어긋나도 아무도 모른다. ORD에 시간대 컬럼을 더하면 조회는 등치 조인이 되지만, 100만 행을 한 번 UPDATE해야 하고 적재할 때마다 채워야 하며, 구간표가 바뀌면 지난 주문까지 정정해야 한다. 구간표를 기준으로 그대로 쓰는 펼치기 안이 대가가 가장 작다.

해설

부등호 조인에서 고를 수 있는 것: 해시 조인은 등치 조건이 하나도 없으면 쓸 수 없다. 이때는 NL이나 소트 머지뿐이다. 이 SQL에서 NL을 고르면 구간 6개마다 ORD를 FULL로 읽어 Buffers가 6배(약 13만 4천 블록)가 된다 (교재 06화 §1).
등치 조건이 하나라도 있으면: 부등호 조건이 섞여 있어도 등치 조건이 있으면 해시 조인이 된다. 부등호는 해시 조인의 filter로 쓰인다 (JOIN-022).
다른 정답: 주문을 먼저 SHOP_NO와 시(앞 두 자리)로 묶어 4,800행(200곳 × 24시간)으로 줄인 뒤 구간표와 붙여도 된다. 부등호 조인은 4,800행과 6행 사이에서만 일어나고, 50만 건은 HASH GROUP BY로 한 번 묶이기만 한다.
PGA를 늘리면: 정렬이 optimal로 끝나 TEMP는 사라지지만, 50만 건 정렬과 구간마다 훑는 일은 그대로다. 정렬 자체를 없애는 쪽이 먼저다 (교재 09화 §6).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황