JOIN-011

지역 고객 250명의 6월 매출을 내는데 12만 블록을 읽는다

중급SELECT한빛유통161 / 700

상황

영업팀의 「지역별 고객 월 매출」은 지역 하나를 고르면 그 지역 고객마다 한 달 주문 건수와 금액을 낸다. 지역 담당자 8명이 달을 바꿔 가며 하루 400번쯤 돌린다. 작년에 T_ORD_BIG에 고객 번호 인덱스를 만들고 NL 조인으로 바꾼 뒤 "빨라졌다"는 보고가 있었지만, 지금은 한 번에 2초 가까이 걸린다. 아래는 RGN03 지역(고객 250명)의 2024년 6월 실행계획이다.

테이블과 인덱스

M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · 지역 8곳에 250명씩
T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객마다 500건 · 고객 번호를 돌려 가며 적재해서 한 고객의 주문은 테이블 전체에 흩어져 있다 · 2024년 6월 27,390건
인덱스 IX_ORD_BIG_CUS (CUS_ID): 높이 3 · 리프 약 2,700블록 · 통계 최신
RGN03 고객 250명의 6월 주문은 3,423건 (고객마다 12 ~ 15건)

원본 SQL

sql

SELECT /*+ LEADING(c o) USE_NL(o) INDEX(o IX_ORD_BIG_CUS) */
       c.CUS_ID, c.CUS_NM, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.RGN_ID = :v_rgn
   AND o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= TO_DATE(:v_ym || '01', 'YYYYMMDD')
   AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:v_ym || '01', 'YYYYMMDD'), 1)
 GROUP BY c.CUS_ID, c.CUS_NM

실행계획 학습용 예시 · 행 수는 실데이터

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |    250 |00:00:01.84 |    126K |
|   1 |  HASH GROUP BY                 |                |      1 |    250 |    250 |00:00:01.84 |    126K |
|   2 |   NESTED LOOPS                 |                |      1 |   3425 |   3423 |00:00:01.83 |    126K |
|   3 |    NESTED LOOPS                |                |      1 |   125K |   125K |00:00:00.06 |    1037 |
|*  4 |     TABLE ACCESS FULL          | M_CUS          |      1 |    250 |    250 |00:00:00.01 |      30 |
|*  5 |     INDEX RANGE SCAN           | IX_ORD_BIG_CUS |    250 |    500 |   125K |00:00:00.04 |    1007 |
|*  6 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG      |   125K |     14 |   3423 |00:00:01.74 |    125K |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("C"."RGN_ID"=:V_RGN)
   5 - access("O"."CUS_ID"="C"."CUS_ID")
   6 - filter(("O"."ORD_DT">=TO_DATE(:V_YM||'01','YYYYMMDD') AND
              "O"."ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM||'01','YYYYMMDD'),1)))

문제

6번 줄의 Starts와 A-Rows, Predicate Information 6번을 보고 12만 블록이 어디서 나오는지 설명하시오.
inner 쪽 인덱스를 고쳐 개선안을 작성하고, 해시 조인으로 바꾸는 안과 Buffers를 비교하시오. 인덱스 컬럼 순서를 어떻게 정했는지도 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객('=')을 앞에, 기간(범위)을 뒤에. 합계를 낼 ORD_AMT 까지 담으면 테이블을 읽지 않는다
CREATE INDEX IX_ORD_BIG_CUS_DT ON T_ORD_BIG (CUS_ID, ORD_DT, ORD_AMT);
-- 기존 IX_ORD_BIG_CUS (CUS_ID) 는 새 인덱스의 앞부분과 겹친다. 쓰는 화면을 확인한 뒤 지운다

SELECT /*+ LEADING(c o) USE_NL(o) INDEX(o IX_ORD_BIG_CUS_DT) */
       c.CUS_ID, c.CUS_NM, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.RGN_ID = :v_rgn
   AND o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= TO_DATE(:v_ym || '01', 'YYYYMMDD')
   AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:v_ym || '01', 'YYYYMMDD'), 1)
 GROUP BY c.CUS_ID, c.CUS_NM

개선 후 실행계획 학습용 예시 · 행 수는 실데이터

xplan

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |      1 |        |    250 |00:00:00.01 |     791 |
|   1 |  HASH GROUP BY       |                   |      1 |    250 |    250 |00:00:00.01 |     791 |
|   2 |   NESTED LOOPS       |                   |      1 |   3425 |   3423 |00:00:00.01 |     791 |
|*  3 |    TABLE ACCESS FULL | M_CUS             |      1 |    250 |    250 |00:00:00.01 |      30 |
|*  4 |    INDEX RANGE SCAN  | IX_ORD_BIG_CUS_DT |    250 |     14 |   3423 |00:00:00.01 |     761 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("C"."RGN_ID"=:V_RGN)
   4 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_DT">=TO_DATE(:V_YM||'01','YYYYMMDD') AND
              "O"."ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM||'01','YYYYMMDD'),1))

모범 답안 서술

6번 줄 TABLE ACCESS BY INDEX ROWID T_ORD_BIG의 Starts는 125K이고 A-Rows는 3,423이다. 고객 250명마다 IX_ORD_BIG_CUS로 그 고객의 주문 500건을 모두 찾고(5번 줄 A-Rows 125K) 테이블을 125,000번 찾아간 뒤, Predicate 6번의 기간 조건으로 97%를 버렸다. 인덱스에 ORD_DT가 없어서 기간 조건은 테이블을 읽은 다음에야 걸러진다. 한 고객의 주문은 테이블 전체에 흩어져 있어 찾아갈 때마다 다른 블록을 읽으므로, 테이블 액세스에서만 125,000블록이 나왔다. NL 조인 자체는 맞다. 고객 250명은 적고, 고객 한 명의 한 달 주문도 14건 안팎이다. 문제는 inner 쪽 인덱스가 조인 키만 담고 있어서 inner 쪽 조건(기간)을 인덱스 안에서 걸러 내지 못한다는 점이다. inner 인덱스는 조인 키 CUS_ID('=' 조건)를 앞에, ORD_DT(범위 조건)를 뒤에 둔다. (ORD_DT, CUS_ID) 순서로 만들면 고객마다 6월 한 달 2만 7천 건의 구간을 매번 훑어야 해서 안 된다. (CUS_ID, ORD_DT) 인덱스만 만들어도 테이블은 6월 주문 3,423번만 찾아가므로 Buffers는 약 4,200이다. 합계를 낼 ORD_AMT까지 담은 (CUS_ID, ORD_DT, ORD_AMT) 인덱스는 테이블을 읽지 않고 인덱스 761블록으로 끝나서, Buffers는 126K → 791이 된다. 해시 조인으로 바꾸면 T_ORD_BIG을 FULL로 한 번 읽으므로 5,826 + 30 = 5,856블록이다. 원본보다 21배 낫지만, 지역 고객 250명처럼 바깥 집합이 작고 inner 인덱스가 조건을 다 담으면 NL이 더 싸다.

해설

겹치는 인덱스: 새 인덱스의 앞부분이 IX_ORD_BIG_CUS (CUS_ID)와 같다. 고객 번호만으로 조회하는 화면도 새 인덱스로 처리할 수 있으므로, 쓰는 화면을 확인한 뒤 기존 인덱스는 지운다(교재 04화 §6 접두 중복). 100만 행 테이블에 인덱스가 늘면 적재할 때마다 갱신 비용이 붙는다.
커버링의 대가: ORD_AMT를 넣으면 인덱스가 커진다. 이 화면이 금액 말고 다른 컬럼(ORD_ST 등)을 더 보여 주게 되면 다시 테이블 액세스가 생긴다. 커버링은 그 SQL에 맞춘 설계라는 점을 리포트에 적는다(교재 04화 §7).
함정: '인덱스가 있으니 NL이 맞다'와 'NL이 느리니 해시로 바꾼다'는 둘 다 반만 맞다. NL에서는 inner 인덱스가 조인 키와 inner 쪽 조건을 함께 담고 있는지를 본다.
범위가 넓어지면: 지역 대신 '전체 고객'을 고르면 바깥 집합이 2,000명이 된다. 새 인덱스로 약 6,000블록, 해시는 5,856블록이라 둘이 비슷해진다. 바깥 집합이 더 커지면 해시가 낫다(교재 06화 §5 손익분기).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 실데이터 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 실데이터 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황