JOIN-013

하루치 주문 137건에 정산 금액을 붙이는데 정산 테이블을 6,850번 찾아간다

중급SELECT한빛유통163 / 700

상황

정산팀의 「결제일 대사」 화면은 결제일 하나를 고르면 그날 결제된 주문마다 주문 금액과 정산 금액을 나란히 보여 준다. 월말에는 날짜를 바꿔 가며 하루 수백 번 돌린다. 정산 테이블에는 「고객별 정산 내역」 화면을 위해 만든 고객 번호 인덱스가 하나 있다. 개발자는 조인 조건에 고객 번호와 주문 번호를 모두 적었으니 인덱스를 잘 쓸 것이라고 했다. 아래는 결제일 2024-06-14(주문 137건)의 실행계획이다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_PAY (PAY_DT) · PAY_DT는 시분초 없는 날짜 · 하루 137건 · 고객마다 50건
T_ORD_STLM 정산: 100,000행 · 640블록 · 주문 한 건에 정산 한 건 · 야간 배치가 TRUNCATE 뒤 T_ORD 순서대로 적재한다 · 한 고객의 정산 50건은 테이블 전체에 흩어져 있다
인덱스 IX_STLM_CUS (CUS_ID): 「고객별 정산 내역」 화면용 · 리프 약 280블록 · 통계 최신

원본 SQL

sql

SELECT o.ORD_SEQ, o.CUS_ID, o.PAY_TP, o.PAY_AMT, s.STLM_SEQ, s.STLM_AMT,
       CASE WHEN s.STLM_AMT <> o.PAY_AMT THEN 'Y' END AS diff_yn
  FROM T_ORD o, T_ORD_STLM s
 WHERE o.PAY_DT  = :v_pay_dt
   AND s.CUS_ID  = o.CUS_ID
   AND s.ORD_SEQ = o.ORD_SEQ

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

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |      1 |        |    137 |00:00:00.04 |    7263 |
|   1 |  NESTED LOOPS                          |              |      1 |    137 |    137 |00:00:00.04 |    7263 |
|   2 |   NESTED LOOPS                         |              |      1 |   6850 |   6850 |00:00:00.01 |     413 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD        |      1 |    137 |    137 |00:00:00.01 |     139 |
|*  4 |     INDEX RANGE SCAN                   | IX_T_ORD_PAY |      1 |    137 |    137 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN                    | IX_STLM_CUS  |    137 |     50 |   6850 |00:00:00.01 |     274 |
|*  6 |   TABLE ACCESS BY INDEX ROWID          | T_ORD_STLM   |   6850 |      1 |    137 |00:00:00.03 |    6850 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("O"."PAY_DT"=:V_PAY_DT)
   5 - access("S"."CUS_ID"="O"."CUS_ID")
   6 - filter("S"."ORD_SEQ"="O"."ORD_SEQ")

문제

6번 줄의 Starts와 A-Rows, Predicate Information 5번과 6번을 보고, 조인 조건 두 개가 각각 어디서 쓰였는지 설명하시오.
인덱스를 새로 만들지, 기존 인덱스를 고칠지 정하고 개선안(DDL과 SQL)을 작성하시오. 「고객별 정산 내역」 화면과 야간 적재에 주는 영향도 함께 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객별 화면용 (CUS_ID) 인덱스를 (CUS_ID, ORD_SEQ) 로 바꾼다: 두 화면이 인덱스 하나를 같이 쓴다
CREATE INDEX IX_STLM_CUS_ORD ON T_ORD_STLM (CUS_ID, ORD_SEQ);
-- 고객별 화면의 계획이 새 인덱스로 바뀐 것을 확인한 뒤 옛 인덱스를 지운다
DROP INDEX IX_STLM_CUS;

SELECT o.ORD_SEQ, o.CUS_ID, o.PAY_TP, o.PAY_AMT, s.STLM_SEQ, s.STLM_AMT,
       CASE WHEN s.STLM_AMT <> o.PAY_AMT THEN 'Y' END AS diff_yn
  FROM T_ORD o, T_ORD_STLM s
 WHERE o.PAY_DT  = :v_pay_dt
   AND s.CUS_ID  = o.CUS_ID
   AND s.ORD_SEQ = o.ORD_SEQ

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

xplan

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |      1 |        |    137 |00:00:00.01 |     550 |
|   1 |  NESTED LOOPS                          |                 |      1 |    137 |    137 |00:00:00.01 |     550 |
|   2 |   NESTED LOOPS                         |                 |      1 |    137 |    137 |00:00:00.01 |     413 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD           |      1 |    137 |    137 |00:00:00.01 |     139 |
|*  4 |     INDEX RANGE SCAN                   | IX_T_ORD_PAY    |      1 |    137 |    137 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN                    | IX_STLM_CUS_ORD |    137 |      1 |    137 |00:00:00.01 |     274 |
|   6 |   TABLE ACCESS BY INDEX ROWID          | T_ORD_STLM      |    137 |      1 |    137 |00:00:00.01 |     137 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("O"."PAY_DT"=:V_PAY_DT)
   5 - access("S"."CUS_ID"="O"."CUS_ID" AND "S"."ORD_SEQ"="O"."ORD_SEQ")

모범 답안 서술

조인 조건 두 개 중 s.CUS_ID = o.CUS_ID만 5번 줄 IX_STLM_CUS의 access 조건으로 쓰였고, s.ORD_SEQ = o.ORD_SEQ는 6번 줄에서 테이블을 읽은 뒤의 filter로 쓰였다. 인덱스에 ORD_SEQ가 없기 때문이다. 주문 137건마다 그 고객의 정산 50건을 모두 찾아(5번 줄 A-Rows 6,850) 테이블을 6,850번 읽고, 그중 주문 번호가 맞는 137건만 남겼다. 한 고객의 정산은 테이블 전체에 흩어져 있어 찾아갈 때마다 다른 블록을 읽으므로, Buffers 7,263 중 6,850이 6번 줄에서 나온다. 조인 조건을 다 적었다고 인덱스가 그 조건을 다 쓰는 것은 아니다. 주문 번호까지 인덱스에 담으면 그 조건은 테이블에 가기 전에 인덱스 안에서 걸러지고, 테이블은 137번만 읽는다. 인덱스를 하나 더 만들기보다 기존 IX_STLM_CUS (CUS_ID)를 (CUS_ID, ORD_SEQ)로 바꾼다. 앞부분이 CUS_ID이므로 「고객별 정산 내역」 화면도 같은 인덱스로 범위 스캔을 할 수 있고, 정산 테이블의 인덱스 수는 그대로 하나다. 고친 뒤 이 화면은 주문마다 인덱스로 정산 한 건을 바로 찾아 Buffers가 7,263 → 550으로 준다. 정산 테이블은 야간 배치가 TRUNCATE 뒤 다시 적재하는 테이블이다. 인덱스가 늘수록 적재할 때 고칠 인덱스도 늘어 배치가 길어진다. 그래서 (ORD_SEQ) 인덱스를 따로 만드는 안도 이 화면은 550블록으로 같지만, 두 화면이 한 인덱스를 같이 쓰는 안을 고른다.

해설

다른 정답: T_ORD_STLM (ORD_SEQ) 인덱스를 따로 만들어도 이 화면은 550블록이다. 주문 번호가 정산을 하나로 정하므로 조인 조건에서 s.CUS_ID = o.CUS_ID를 빼도 결과는 같다. 다만 인덱스가 둘이 되면 야간 적재가 인덱스 두 개를 고쳐야 한다.
고객별 화면 확인: (CUS_ID, ORD_SEQ)로 바꾸면 인덱스가 조금 커져 고객별 화면이 읽는 리프 블록이 1개 안팎 늘 수 있다. 바꾸기 전과 뒤에 그 화면의 계획과 Buffers를 재서 리포트에 함께 적는다.
적재와 인덱스: 직접 경로 적재(APPEND)도 인덱스는 적재가 끝날 때 한꺼번에 고친다. 적재 전에 인덱스를 UNUSABLE로 두고 적재 뒤 REBUILD하는 방법도 검토할 수 있다(교재 14화).
함정: Predicate Information에서 조건이 access인지 테이블의 filter인지를 확인하지 않으면, '조건은 다 적었다'는 말만 믿게 된다. NL에서 inner 테이블 줄의 filter는 Starts만큼 테이블을 읽은 뒤에 걸러진다는 뜻이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황