고객 두 명을 찾는 「고객 360」 한 화면이 콜센터 하루 논리 읽기의 절반을 차지한다

실전SELECT한빛유통292 / 700

상황

콜센터의 「고객 360」은 상담원이 고객명 앞부분을 넣으면 고객 정보와 주문, 정산, 평가 요약을 한 줄씩 보여 준다. 동명이인이 있어 보통 두 명쯤 나온다. 상담원 40명이 통화마다 열어 하루 6,000번 돌고, 한 번은 0.04초지만 하루 논리 읽기는 약 490만 블록으로 콜센터 전체의 절반이다. 박팀장은 "인덱스는 꼭 필요한 것만 만든다. 정산 테이블은 매일 밤 비우고 다시 싣는 테이블이니 그 비용까지 적어 와라"라고 했다.

테이블과 인덱스

M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_NM에는 인덱스가 없다
T_ORD 주문: 100,000행 · 896블록 · IX_ORD_CUS (CUS_ID) (다른 콜센터 화면도 쓴다) · 고객당 약 50건 · 한 고객의 주문은 흩어져 있다
T_ORD_STLM 정산: 100,000행 · 640블록 · 인덱스 없음 · 고객당 약 50건 · 매일 밤 TRUNCATE 뒤 INSERT /*+ APPEND */로 10만 건을 다시 싣는다(지금 약 0.1초)
T_ITM_EVL 상품평가: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO)만 있다 · 고객당 2 ~ 3건 · 리뷰가 올라올 때마다 한 건씩 들어온다
주문, 정산, 평가가 없는 고객도 화면에는 나와야 한다 · 통계 최신

원본 SQL

sql

SELECT c.CUS_ID, c.CUS_NM, c.CUS_GD,
       o.ORD_CNT, o.ORD_AMT, o.LAST_ORD_DT,
       s.STLM_AMT,
       e.EVL_CNT, e.AVG_PT
  FROM M_CUS c,
       (SELECT CUS_ID, COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT, MAX(ORD_DT) AS LAST_ORD_DT
          FROM T_ORD GROUP BY CUS_ID) o,
       (SELECT CUS_ID, SUM(STLM_AMT) AS STLM_AMT
          FROM T_ORD_STLM GROUP BY CUS_ID) s,
       (SELECT CUS_ID, COUNT(*) AS EVL_CNT, ROUND(AVG(EVL_PT), 2) AS AVG_PT
          FROM T_ITM_EVL GROUP BY CUS_ID) e
 WHERE c.CUS_NM LIKE :nm || '%'
   AND o.CUS_ID(+) = c.CUS_ID
   AND s.CUS_ID(+) = c.CUS_ID
   AND e.CUS_ID(+) = c.CUS_ID

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |            |      1 |        |      2 |00:00:00.04 |     822 |          |
|*  1 |  HASH JOIN OUTER                        |            |      1 |      2 |      2 |00:00:00.04 |     822 | 1123K (0)|
|*  2 |   HASH JOIN OUTER                       |            |      1 |      2 |      2 |00:00:00.03 |     774 | 1036K (0)|
|   3 |    NESTED LOOPS OUTER                   |            |      1 |      2 |      2 |00:00:00.01 |     134 |          |
|*  4 |     TABLE ACCESS FULL                   | M_CUS      |      1 |      2 |      2 |00:00:00.01 |      30 |          |
|   5 |     VIEW PUSHED PREDICATE               |            |      2 |      1 |      2 |00:00:00.01 |     104 |          |
|   6 |      SORT GROUP BY                      |            |      2 |      1 |      2 |00:00:00.01 |     104 |          |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD     |      2 |     50 |    100 |00:00:00.01 |     104 |          |
|*  8 |        INDEX RANGE SCAN                 | IX_ORD_CUS |      2 |     50 |    100 |00:00:00.01 |       4 |          |
|   9 |    VIEW                                 |            |      1 |   2000 |   2000 |00:00:00.03 |     640 |          |
|  10 |     HASH GROUP BY                       |            |      1 |   2000 |   2000 |00:00:00.03 |     640 | 1405K (0)|
|  11 |      TABLE ACCESS FULL                  | T_ORD_STLM |      1 |   100K |   100K |00:00:00.01 |     640 |          |
|  12 |   VIEW                                  |            |      1 |   1990 |   1990 |00:00:00.01 |      48 |          |
|  13 |    HASH GROUP BY                        |            |      1 |   1990 |   1990 |00:00:00.01 |      48 | 1289K (0)|
|  14 |     TABLE ACCESS FULL                   | T_ITM_EVL  |      1 |   5000 |   5000 |00:00:00.01 |      48 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."CUS_ID"(+)="C"."CUS_ID")
   2 - access("S"."CUS_ID"(+)="C"."CUS_ID")
   4 - filter("C"."CUS_NM" LIKE :NM||'%')
   8 - access("CUS_ID"="C"."CUS_ID")

문제

세 집계 뷰가 각각 어떻게 처리되었는지(조인 조건이 뷰 안으로 들어갔는지, 뷰를 통째로 만들었는지) 줄 번호와 Buffers로 설명하시오. 고객 조건이 이름이라는 점이 왜 중요한지도 쓰시오.
세 뷰가 모두 고객 두 명의 행만 읽도록 인덱스를 설계하고, 고친 뒤의 Buffers와 하루 읽기량을 쓰시오.
만드는 인덱스마다 DML 쪽 대가(정산 야간 적재, 리뷰 입력, 주문 입력)를 쓰고 우선순위를 정하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 정산: 고객번호 + 집계 컬럼 (가장 큰 몫 640블록을 없앤다)
CREATE INDEX IX_ORD_STLM_CUS ON T_ORD_STLM (CUS_ID, STLM_AMT);

-- ② 주문: (CUS_ID)를 집계 컬럼까지 담은 인덱스로 바꾼다 (선두가 같아 다른 콜센터 화면도 그대로 쓴다)
CREATE INDEX IX_ORD_CUS_DT_AMT ON T_ORD (CUS_ID, ORD_DT, ORD_AMT);
DROP INDEX IX_ORD_CUS;

-- ③ 평가: 고객번호 + 평점
CREATE INDEX IX_ITM_EVL_CUS ON T_ITM_EVL (CUS_ID, EVL_PT);

-- ④ (선택) 고객명 앞부분 검색
CREATE INDEX IX_CUS_NM ON M_CUS (CUS_NM);

-- SQL은 그대로 둔다. 옵티마이저가 세 뷰 모두 JPPD로 연다
-- 고정하려면 /*+ LEADING(c) USE_NL(o s e) PUSH_PRED(o) PUSH_PRED(s) PUSH_PRED(e) */

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

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |      2 |00:00:00.01 |      20 |
|   1 |  NESTED LOOPS OUTER                  |                   |      1 |      2 |      2 |00:00:00.01 |      20 |
|   2 |   NESTED LOOPS OUTER                 |                   |      1 |      2 |      2 |00:00:00.01 |      16 |
|   3 |    NESTED LOOPS OUTER                |                   |      1 |      2 |      2 |00:00:00.01 |      10 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| M_CUS           |      1 |      2 |      2 |00:00:00.01 |       4 |
|*  5 |      INDEX RANGE SCAN                | IX_CUS_NM         |      1 |      2 |      2 |00:00:00.01 |       2 |
|   6 |     VIEW PUSHED PREDICATE            |                   |      2 |      1 |      2 |00:00:00.01 |       6 |
|   7 |      SORT GROUP BY                   |                   |      2 |      1 |      2 |00:00:00.01 |       6 |
|*  8 |       INDEX RANGE SCAN               | IX_ORD_CUS_DT_AMT |      2 |     50 |    100 |00:00:00.01 |       6 |
|   9 |    VIEW PUSHED PREDICATE             |                   |      2 |      1 |      2 |00:00:00.01 |       6 |
|  10 |     SORT GROUP BY                    |                   |      2 |      1 |      2 |00:00:00.01 |       6 |
|* 11 |      INDEX RANGE SCAN                | IX_ORD_STLM_CUS   |      2 |     50 |    100 |00:00:00.01 |       6 |
|  12 |   VIEW PUSHED PREDICATE              |                   |      2 |      1 |      2 |00:00:00.01 |       4 |
|  13 |    SORT GROUP BY                     |                   |      2 |      1 |      2 |00:00:00.01 |       4 |
|* 14 |     INDEX RANGE SCAN                 | IX_ITM_EVL_CUS    |      2 |      3 |      5 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("C"."CUS_NM" LIKE :NM||'%')
       filter("C"."CUS_NM" LIKE :NM||'%')
   8 - access("CUS_ID"="C"."CUS_ID")
  11 - access("CUS_ID"="C"."CUS_ID")
  14 - access("CUS_ID"="C"."CUS_ID")

모범 답안 서술

주문 뷰는 5번 줄 VIEW PUSHED PREDICATE다. IX_ORD_CUS가 있어서 옵티마이저가 조인 조건 CUS_ID = c.CUS_ID를 뷰 안으로 밀어 넣고(JPPD), 고객 두 명마다 그 고객의 주문 50건만 찾아 묶었다(104블록). 테이블 블록이 100개인 것은 주문이 흩어져 있어 한 건에 한 블록씩 읽었기 때문이다. 정산 뷰(9번 줄)와 평가 뷰(12번 줄)는 그냥 VIEW다. 두 테이블에는 CUS_ID 인덱스가 없어 조인 조건을 밀어 넣어도 고객의 행을 찾아갈 길이 없으므로, 옵티마이저는 정산 10만 건(640블록)과 평가 5,000건(48블록)을 통째로 묶어 2,000줄씩 만든 뒤 고객 두 명과 해시 조인했다. 822블록 가운데 640블록이 정산 몫이다. 고객을 이름으로 찾는다는 점이 중요하다. 고객번호를 상수로 받았다면 c.CUS_ID = :cus에서 각 뷰의 CUS_ID = :cus를 끌어내(조건절 이행) 뷰 안에 바로 넣을 수 있다. 이름 검색에서는 고객번호가 실행 중에야 정해지므로, 뷰 안으로 들어갈 수 있는 길은 조인 조건을 행마다 밀어 넣는 JPPD뿐이고, JPPD는 뷰 안에 그 조건을 받을 인덱스가 있어야 이득이다. 인덱스는 세 뷰 모두 CUS_ID를 선두에 두고 집계 컬럼까지 담는다. 정산 (CUS_ID, STLM_AMT), 주문 (CUS_ID, ORD_DT, ORD_AMT), 평가 (CUS_ID, EVL_PT)이다. 세 뷰 모두 인덱스만으로 고객의 행을 묶고(6 + 6 + 4블록), 고객명 인덱스까지 더하면 고객 찾기도 4블록이다. Buffers는 822 → 20이고, 하루 읽기량은 822 × 6,000 = 약 493만 → 20 × 6,000 = 12만 블록이다. 대가와 우선순위는 이렇다. ① 정산 인덱스가 가장 크게 줄이지만, 매일 밤 TRUNCATE 뒤 직접 경로로 10만 건을 실을 때 인덱스도 함께 만들어져(적재가 끝날 때 정렬해 한 번에 만든다) 0.1초이던 적재가 수십 ms 늘어나고 인덱스 redo가 생긴다. 적재 전에 UNUSABLE로 두고 적재 뒤 REBUILD해도 된다. 1순위로 만든다. ② 주문은 이미 (CUS_ID) 인덱스가 있으므로 같은 선두의 큰 인덱스로 바꾸면 주문 입력마다 고치는 인덱스 수는 그대로다. 인덱스가 커지는 것만 대가다. 2순위. ③ 평가는 하루 48 × 6,000 = 약 29만 블록을 줄이는 대신 리뷰 한 건마다 인덱스 한 건을 더 고친다. 리뷰 입력이 적어 대가도 작으므로 3순위로 만든다. ④ 고객명 인덱스는 고객 마스터가 거의 바뀌지 않아 대가가 작지만 줄이는 양도 30 → 4블록뿐이다.

해설

JPPD의 조건: 조인 조건이 뷰 안에 들어가도 그 조건으로 행을 찾을 인덱스가 없으면 행마다 테이블을 통째로 읽게 되므로, 옵티마이저는 차라리 뷰를 한 번 통째로 만든다. 계획에서 VIEW와 VIEW PUSHED PREDICATE가 섞여 있으면 뷰 안의 인덱스부터 확인한다.
다른 정답: 세 요약을 스칼라 서브쿼리로 바꿔도 고객 두 명마다 한 번씩 실행되어 같은 효과가 나지만, 같은 인덱스가 필요하다는 점은 같다. 인덱스 없이 스칼라 서브쿼리로만 바꾸면 고객마다 정산 640블록을 읽어 오히려 늘어날 수 있다.
요약 테이블이나 구체화 뷰: 고객별 요약을 미리 만들어 두면 한 번 읽기로 끝나지만, 정산이 매일 다시 실리고 주문과 리뷰가 수시로 들어오므로 갱신 시점과 비용을 따로 설계해야 한다. 이 화면에는 인덱스 셋이 더 단순하다.
LIKE 검색의 주의: 상담원이 이름 앞에 %를 넣으면 고객명 인덱스를 쓰지 못한다. 고객 마스터는 30블록뿐이라 그때도 큰 손해는 없다(교재 02화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황