상담 많은 고객 200명의 분기 주문 실적을 보는데 주문 인덱스를 통째로 읽는다

기초SELECT한빛유통616 / 700

상황

고객관리팀의 「전담 상담 고객 실적」 리포트는 A와 B 등급 고객 200명의 상담 건수와 분기 주문 실적을 고객마다 한 줄씩 보여 준다. 팀장 회의 전에 하루 수십 번 돌린다. 분기는 화면이 리터럴로 넣는다(아래는 2024년 2분기). 정하은이 계획을 보고 물었다. "고객 200명의 주문만 보면 되는데 왜 주문 인덱스를 통째로 읽죠?" 이 리포트 SQL은 다른 팀도 같이 쓰고 있어서 이번에는 고치지 않기로 했다.

테이블과 인덱스

T_CS_CALL 전담 상담 이력(이 문제용): 36,000행 · 260블록 · CALL_SEQ · CALL_DT · CUS_ID · AGT_ID(전담 상담원) · RGN_ID(고객 지역) · A와 B 등급 고객 200명의 3년치 상담만 쌓인다
고객마다 전담 상담원은 한 명이고 지역도 하나다(상담원 12명 · 지역 8곳). 그래서 (CUS_ID, AGT_ID, RGN_ID) 조합은 고객 수와 같은 200개다.
T_CS_CALL 통계: 최신 · NUM_DISTINCT는 CUS_ID 200 · AGT_ID 12 · RGN_ID 8 · 히스토그램 없음 · 확장 통계 없음
T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객 2,000명 × 500건 · ORD_DT NDV 1,095(2022-01-01 ~ 2024-12-30) · 2024년 4 ~ 6월 주문 83,083건(고객 200명의 몫은 8,308건)
인덱스 IX_ORD_BIG_CUS_DT_AMT (CUS_ID, ORD_DT, ORD_AMT): BLEVEL 2 · 리프 3,950 · 이 SQL이 T_ORD_BIG에서 쓰는 열을 모두 담는다

원본 SQL

sql

SELECT v.CUS_ID, v.AGT_ID, v.RGN_ID, v.call_cnt,
       COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM (SELECT CUS_ID, AGT_ID, RGN_ID, COUNT(*) AS call_cnt
          FROM T_CS_CALL
         GROUP BY CUS_ID, AGT_ID, RGN_ID) v,
       T_ORD_BIG o
 WHERE o.CUS_ID = v.CUS_ID
   AND o.ORD_DT >= DATE '2024-04-01'
   AND o.ORD_DT <  DATE '2024-07-01'
 GROUP BY v.CUS_ID, v.AGT_ID, v.RGN_ID, v.call_cnt

실행계획 학습용 예시

xplan

Plan hash value: 3318260471

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                       |      1 |        |    200 |00:00:00.09 |    4231 |          |
|   1 |  HASH GROUP BY          |                       |      1 |  19200 |    200 |00:00:00.09 |    4231 | 1394K (0)|
|*  2 |   HASH JOIN             |                       |      1 |    807K|   8308 |00:00:00.08 |    4231 | 2210K (0)|
|   3 |    VIEW                 |                       |      1 |  19200 |    200 |00:00:00.01 |     260 |          |
|   4 |     HASH GROUP BY       |                       |      1 |  19200 |    200 |00:00:00.01 |     260 | 1188K (0)|
|   5 |      TABLE ACCESS FULL  | T_CS_CALL             |      1 |  36000 |  36000 |00:00:00.01 |     260 |          |
|*  6 |    INDEX FAST FULL SCAN | IX_ORD_BIG_CUS_DT_AMT |      1 |  84094 |  83083 |00:00:00.05 |    3971 |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUS_ID"="V"."CUS_ID")
   6 - filter(("O"."ORD_DT">=TO_DATE(' 2024-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

문제

3번과 4번 줄의 E-Rows 19,200이 어디서 나온 숫자인지 계산하고, 그 숫자 때문에 2번 줄에서 어떤 조인 방법을 골랐는지 설명하시오.
SQL은 그대로 두고 통계로 추정을 바로잡으시오. 바로잡은 뒤의 계획과 읽는 양도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 세 열을 한 묶음(컬럼 그룹)으로 : 조합 200개 = 고객 수 (숨은 가상 열 하나 · 인덱스가 아니다)
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'T_CS_CALL', '(CUS_ID, AGT_ID, RGN_ID)') AS ext FROM dual;

-- 만든 뒤 반드시 다시 모은다 (그룹의 NDV 200 · 이 SQL 의 커서도 바로 다시 파싱)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_CS_CALL', no_invalidate => FALSE);
END;
/

-- SQL 은 그대로
SELECT v.CUS_ID, v.AGT_ID, v.RGN_ID, v.call_cnt,
       COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM (SELECT CUS_ID, AGT_ID, RGN_ID, COUNT(*) AS call_cnt
          FROM T_CS_CALL
         GROUP BY CUS_ID, AGT_ID, RGN_ID) v,
       T_ORD_BIG o
 WHERE o.CUS_ID = v.CUS_ID
   AND o.ORD_DT >= DATE '2024-04-01'
   AND o.ORD_DT <  DATE '2024-07-01'
 GROUP BY v.CUS_ID, v.AGT_ID, v.RGN_ID, v.call_cnt;

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

xplan

Plan hash value: 1460927735

------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                       |      1 |        |    200 |00:00:00.01 |     862 |          |
|   1 |  HASH GROUP BY         |                       |      1 |    200 |    200 |00:00:00.01 |     862 | 1394K (0)|
|   2 |   NESTED LOOPS         |                       |      1 |   8409 |   8308 |00:00:00.01 |     862 |          |
|   3 |    VIEW                |                       |      1 |    200 |    200 |00:00:00.01 |     260 |          |
|   4 |     HASH GROUP BY      |                       |      1 |    200 |    200 |00:00:00.01 |     260 | 1188K (0)|
|   5 |      TABLE ACCESS FULL | T_CS_CALL             |      1 |  36000 |  36000 |00:00:00.01 |     260 |          |
|*  6 |    INDEX RANGE SCAN    | IX_ORD_BIG_CUS_DT_AMT |    200 |     42 |   8308 |00:00:00.01 |     602 |          |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("O"."CUS_ID"="V"."CUS_ID" AND "O"."ORD_DT">=TO_DATE(' 2024-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "O"."ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

[USER_STAT_EXTENSIONS]  T_CS_CALL · SYS_STU… · ("CUS_ID","AGT_ID","RGN_ID") · NUM_DISTINCT 200

모범 답안 서술

19,200의 출처: 4번 줄은 T_CS_CALL 36,000행을 고객, 상담원, 지역 세 열로 묶는다. 옵티마이저는 세 열이 서로 상관없다고(독립) 보고 각 열의 NDV를 곱해 그룹 수를 200 × 12 × 8 = 19,200으로 셈했다. 입력 36,000행보다 작으니 그대로 쓴다. 그러나 고객마다 전담 상담원과 지역이 하나뿐이라 실제 조합은 고객 수와 같은 200개다. 96배 크게 본 것이다. 그 숫자로 고른 것: 그룹이 19,200개라고 믿으니 조인 추정도 19,200 × 84,094 / 2,000 = 807K로 커졌다. 고객마다 IX_ORD_BIG_CUS_DT_AMT를 찾아가는 NL은 19,200번 × 약 3블록, 곧 5만 블록이 넘는 일로 계산되므로, 인덱스를 한 번 통째로 읽는 INDEX FAST FULL SCAN(3,971블록)과 해시 조인을 골랐다. 실제 고객은 200명이라 NL이면 200번 × 3블록, 600블록 남짓이면 된다. 고침: (CUS_ID, AGT_ID, RGN_ID) 컬럼 그룹을 만들고 다시 모은다. 컬럼 그룹은 WHERE 절의 등치 조건뿐 아니라 GROUP BY의 그룹 수를 셀 때도 쓰인다. 그룹의 NDV 200으로 4번 줄 E-Rows가 200이 되고, 조인 추정도 8,409로 실제(8,308)에 가까워져 NL을 고른다. Buffers는 4,231 → 862다. 대가: 확장 통계는 테이블에 숨은 가상 열을 하나 만드는 DDL이고, 만든 뒤 다시 모아야 쓰인다. 이 세 열로 거르거나 묶는 다른 SQL의 추정도 함께 바뀌므로 반영 전후로 그 SQL들의 계획을 확인한다. 되돌릴 때는 DROP_EXTENDED_STATS로 지운다.

해설

SQL을 고칠 수 있다면 고객 번호 하나로 묶고 상담원과 지역은 MAX로 가져와도 된다. GROUP BY CUS_ID만이면 NDV 200을 그대로 써서 통계 없이도 맞는다. 다만 같은 세 열로 묶는 리포트가 여럿이면 하나하나 고쳐야 하고, 다음에 누가 열을 더하면 다시 곱해진다.
조건절의 곱셈과 같은 가정이다. OPT-003과 OPT-024는 WHERE의 등치 조건 둘을 독립으로 보고 곱해서 틀렸다. 이 문제는 GROUP BY의 열 셋을 곱해서 틀렸다. 컬럼 그룹 하나가 두 곳을 모두 고친다(교재 10화 §8).
히스토그램으로는 고칠 수 없다. 히스토그램은 열 하나의 분포라, 고객이 상담원과 지역을 정한다는 열 사이의 관계를 모른다.
조합이 바뀌면 컬럼 그룹은 모을 때의 실제 조합 수를 센다. 전담 상담원을 바꾸는 고객이 늘어 조합이 200을 넘으면 다시 모을 때 그 수를 따라간다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황