JOIN-066

「월간 주문 채널 분석」 추출에 칸을 하나 더할 때마다 읽는 블록이 2천 개씩 늘어난다

중급SELECT한빛유통216 / 700

상황

마케팅팀의 「월간 주문 채널 분석」은 한 달 주문마다 유입 채널, 쿠폰 번호, 선물 포장 여부, 할부 개월을 붙여 내려받는 추출이다. 처음에는 유입 채널 한 칸이었는데 요청이 올 때마다 칸을 하나씩 더해 지금은 네 칸이 됐고, 칸이 늘 때마다 읽는 블록도 2천여 개씩 늘었다. 마케팅팀은 다음 달에 배송 메모와 영수증 종류도 더해 달라고 한다. 부가 속성은 2021년 주문 화면 개편 때 T_ORD에 컬럼을 늘리지 않고 행으로 넣기로 한 T_ORD_ATTR에 있다. 아래는 2024년 6월로 조회한 실행계획이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 2024년 6월 4,172건(테이블 전체에 흩어져 있다)
T_ORD_ATTR 주문 부가 속성(이 문제에서 쓰는 테이블): 400,000행 · 2,220블록 · ORD_SEQ · ATTR_CD · ATTR_VAL · PK_T_ORD_ATTR (ORD_SEQ, ATTR_CD) BLEVEL 2
ATTR_CD 6종의 행 수: CHNL 98,800 · RCPT_TP 120,000 · DLV_MSG 81,200 · PAY_INST 60,000 · CPN 30,000 · GIFT_YN 10,000 · ATTR_CD에 히스토그램 있음
6월 주문의 속성 행: CHNL 4,135 · PAY_INST 2,497 · CPN 1,254 · GIFT_YN 418 · 네 속성이 모두 없는 주문 37건(콜센터 전화 주문) · 통계 최신

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_AMT,
       a1.ATTR_VAL AS chnl, a2.ATTR_VAL AS cpn_no,
       a3.ATTR_VAL AS gift_yn, a4.ATTR_VAL AS pay_inst
  FROM T_ORD o
  LEFT JOIN T_ORD_ATTR a1 ON a1.ORD_SEQ = o.ORD_SEQ AND a1.ATTR_CD = 'CHNL'
  LEFT JOIN T_ORD_ATTR a2 ON a2.ORD_SEQ = o.ORD_SEQ AND a2.ATTR_CD = 'CPN'
  LEFT JOIN T_ORD_ATTR a3 ON a3.ORD_SEQ = o.ORD_SEQ AND a3.ATTR_CD = 'GIFT_YN'
  LEFT JOIN T_ORD_ATTR a4 ON a4.ORD_SEQ = o.ORD_SEQ AND a4.ATTR_CD = 'PAY_INST'
 WHERE o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |      1 |        |   4172 |00:00:00.21 |    9776 |           |
|*  1 |  HASH JOIN OUTER        |            |      1 |   4172 |   4172 |00:00:00.21 |    9776 | 1586K (0) |
|*  2 |   HASH JOIN OUTER       |            |      1 |   4172 |   4172 |00:00:00.16 |    7556 | 1586K (0) |
|*  3 |    HASH JOIN OUTER      |            |      1 |   4172 |   4172 |00:00:00.11 |    5336 | 1586K (0) |
|*  4 |     HASH JOIN OUTER     |            |      1 |   4172 |   4172 |00:00:00.06 |    3116 | 1452K (0) |
|*  5 |      TABLE ACCESS FULL  | T_ORD      |      1 |   4172 |   4172 |00:00:00.01 |     896 |           |
|*  6 |      TABLE ACCESS FULL  | T_ORD_ATTR |      1 |  98800 |  98800 |00:00:00.03 |    2220 |           |
|*  7 |     TABLE ACCESS FULL   | T_ORD_ATTR |      1 |  30000 |  30000 |00:00:00.03 |    2220 |           |
|*  8 |    TABLE ACCESS FULL    | T_ORD_ATTR |      1 |  10000 |  10000 |00:00:00.03 |    2220 |           |
|*  9 |   TABLE ACCESS FULL     | T_ORD_ATTR |      1 |  60000 |  60000 |00:00:00.03 |    2220 |           |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A4"."ORD_SEQ"="O"."ORD_SEQ")
   2 - access("A3"."ORD_SEQ"="O"."ORD_SEQ")
   3 - access("A2"."ORD_SEQ"="O"."ORD_SEQ")
   4 - access("A1"."ORD_SEQ"="O"."ORD_SEQ")
   5 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO))
   6 - filter("A1"."ATTR_CD"='CHNL')
   7 - filter("A2"."ATTR_CD"='CPN')
   8 - filter("A3"."ATTR_CD"='GIFT_YN')
   9 - filter("A4"."ATTR_CD"='PAY_INST')

문제

칸을 하나 더할 때마다 읽는 블록이 왜 2천 개 남짓씩 느는지 실행계획의 줄 번호로 설명하시오.
T_ORD_ATTR를 한 번만 읽도록 SQL을 고치시오. 결과 행 수와 각 칸의 값이 원래 SQL과 같아야 하며, 그렇게 되는 근거를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 속성 테이블은 한 번만 읽는다 : 필요한 속성 넷을 ON 절에서 IN 으로 고르고(아우터 조인 유지)
-- 주문별로 묶으면서 속성마다 칸으로 편다
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_AMT,
       MAX(CASE WHEN a.ATTR_CD = 'CHNL'     THEN a.ATTR_VAL END) AS chnl,
       MAX(CASE WHEN a.ATTR_CD = 'CPN'      THEN a.ATTR_VAL END) AS cpn_no,
       MAX(CASE WHEN a.ATTR_CD = 'GIFT_YN'  THEN a.ATTR_VAL END) AS gift_yn,
       MAX(CASE WHEN a.ATTR_CD = 'PAY_INST' THEN a.ATTR_VAL END) AS pay_inst
  FROM T_ORD o
  LEFT JOIN T_ORD_ATTR a
    ON a.ORD_SEQ = o.ORD_SEQ
   AND a.ATTR_CD IN ('CHNL', 'CPN', 'GIFT_YN', 'PAY_INST')
 WHERE o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to
 GROUP BY o.ORD_SEQ, o.ORD_DT, o.ORD_AMT

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |   4172 |00:00:00.08 |    3116 |           |
|   1 |  HASH GROUP BY        |            |      1 |   4172 |   4172 |00:00:00.08 |    3116 | 2370K (0) |
|*  2 |   HASH JOIN OUTER     |            |      1 |   8344 |   8341 |00:00:00.06 |    3116 | 1452K (0) |
|*  3 |    TABLE ACCESS FULL  | T_ORD      |      1 |   4172 |   4172 |00:00:00.01 |     896 |           |
|*  4 |    TABLE ACCESS FULL  | T_ORD_ATTR |      1 |   198K |   198K |00:00:00.03 |    2220 |           |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ORD_SEQ"="O"."ORD_SEQ")
   3 - filter(("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO))
   4 - filter(("A"."ATTR_CD"='CHNL' OR "A"."ATTR_CD"='CPN' OR "A"."ATTR_CD"='GIFT_YN' OR "A"."ATTR_CD"='PAY_INST'))

모범 답안 서술

속성 하나를 칸 하나로 붙일 때마다 T_ORD_ATTR를 한 번씩 조인했다. 조인마다 40만 행짜리 속성 테이블을 FULL로 읽고(6번부터 9번 줄, 2,220블록씩) 그 속성의 행만 남겨 해시 아우터 조인을 한다. 그래서 칸이 넷이면 2,220 × 4 = 8,880블록을 읽고, 칸을 더할 때마다 2,220블록이 는다. 6월 주문은 4,172건뿐인데 속성 행은 매번 테이블 전체에서 고른다. 같은 테이블을 속성 수만큼 조인하는 대신 한 번만 조인하고 묶어서 편다. ON 절에서 필요한 속성 넷을 IN으로 고르면 속성 테이블을 한 번 읽어 6월 주문의 속성 행 8,304줄을 붙이고, 주문별로 묶으면서 MAX(CASE ...)로 속성마다 칸을 만든다. PK_T_ORD_ATTR (ORD_SEQ, ATTR_CD) 때문에 한 주문에 같은 속성은 하나뿐이므로 MAX는 그 값 하나를 꺼낼 뿐이고, 원래 SQL의 칸 값과 같다. Buffers는 9,776 → 3,116, 시간은 0.21 → 0.08초다. 배송 메모와 영수증 종류를 더해도 IN 목록과 CASE 두 줄만 늘고 읽는 블록은 그대로다. 함정은 아우터 조인이다. 속성이 하나도 없는 콜센터 전화 주문 37건도 결과에 나와야 한다. ATTR_CD IN (...)을 ON 절이 아니라 WHERE 절에 쓰면, 짝이 없어 NULL로 채워진 행이 그 조건에 걸러져 37건이 사라진다. 원래 SQL이 속성마다 ON 절에 ATTR_CD를 둔 것과 같은 뜻을 지키려면 ON 절에 둔다. 주문별로 묶을 때는 결과로 내는 주문 컬럼을 GROUP BY에 모두 넣는다. ORD_SEQ가 PK라서 주문이 합쳐지거나 쪼개지지 않으므로 결과는 4,172행 그대로다.

해설

인라인 뷰로 먼저 펴는 모양: 속성 테이블을 주문별로 먼저 펴서(GROUP BY ORD_SEQ) 주문에 아우터 조인해도 한 번 읽는다. 다만 옵티마이저가 뷰를 풀지 않으면 6월이 아닌 전체 10만 주문의 속성 20만 행을 다 묶은 뒤 맞대므로, 조인한 뒤 묶는 모양이 대개 싸다.
NL로 한 번만 붙이면: 6월 주문 4,172건마다 PK_T_ORD_ATTR를 ORD_SEQ 범위로 찾아 속성을 한꺼번에 가져올 수도 있다. 약 1만 6천 블록이라 이 규모에서는 해시가 싸다. 주문 수십 건짜리 화면이라면 NL이 맞다.
EAV 설계의 대가: 컬럼을 늘리지 않으려고 속성을 행으로 넣으면, 속성을 칸으로 볼 때마다 이런 펼치기가 필요하고 속성별 통계와 제약도 걸기 어렵다. 늘 함께 쓰는 속성이 정해졌다면 컬럼으로 옮기는 것도 검토한다.
같은 테이블을 여러 번 읽는 다른 모양: 같은 원리가 UNION ALL이나 스칼라 서브쿼리로 같은 테이블을 여러 번 읽는 SQL에도 통한다. 한 번 읽고 CASE로 나누는 것이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황