결제 대기 목록 한 쪽 50건을 여는 데 주문상세를 150번 찾아간다

중급SELECT한빛유통244 / 700

상황

정산팀의 「결제 대기 주문」 화면은 결제가 안 된 주문(PENDING)을 최근 것부터 한 쪽에 50건씩 보여 주고, 주문마다 상세 줄 수와 수량, 상세 금액 합계를 붙여 주문 금액과 대조하게 한다. 정산 담당자 10명이 하루 약 3,000번 연다. 신입 개발자는 "상세 칸이 세 개라 스칼라 서브쿼리를 세 개 붙였다. 페이징 뒤에 두었으니 50번씩만 돈다"라고 했다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PENDING 8,000건 · IX_T_ORD_ST_DT (ORD_ST, ORD_DT)가 있다(이 화면용)
T_ORD_DET: 200,000행 · 896블록 · 주문당 1 ~ 3줄(같은 주문의 줄은 같은 블록에 있다) · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO)
통계 최신 · 아래는 첫 쪽(최근 50건)

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.CUS_ID, o.ORD_AMT,
       (SELECT COUNT(*)                   FROM T_ORD_DET d WHERE d.ORD_SEQ = o.ORD_SEQ) AS det_cnt,
       (SELECT SUM(d.ORD_QTY)             FROM T_ORD_DET d WHERE d.ORD_SEQ = o.ORD_SEQ) AS det_qty,
       (SELECT SUM(d.ORD_QTY * d.UNT_PRC) FROM T_ORD_DET d WHERE d.ORD_SEQ = o.ORD_SEQ) AS det_amt
  FROM (SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT
          FROM T_ORD
         WHERE ORD_ST = 'PENDING'
         ORDER BY ORD_DT DESC
         FETCH FIRST 50 ROWS ONLY) o
 ORDER BY o.ORD_DT DESC

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |     50 |00:00:00.01 |     453 |
|   1 |  SORT AGGREGATE                        |                |     50 |      1 |     50 |00:00:00.01 |     100 |
|*  2 |   INDEX RANGE SCAN                     | PK_T_ORD_DET   |     50 |      2 |     99 |00:00:00.01 |     100 |
|   3 |  SORT AGGREGATE                        |                |     50 |      1 |     50 |00:00:00.01 |     150 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T_ORD_DET      |     50 |      2 |     99 |00:00:00.01 |     150 |
|*  5 |    INDEX RANGE SCAN                    | PK_T_ORD_DET   |     50 |      2 |     99 |00:00:00.01 |     100 |
|   6 |  SORT AGGREGATE                        |                |     50 |      1 |     50 |00:00:00.01 |     150 |
|   7 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T_ORD_DET      |     50 |      2 |     99 |00:00:00.01 |     150 |
|*  8 |    INDEX RANGE SCAN                    | PK_T_ORD_DET   |     50 |      2 |     99 |00:00:00.01 |     100 |
|   9 |  SORT ORDER BY                         |                |      1 |     50 |     50 |00:00:00.01 |     453 |
|  10 |   VIEW                                 |                |      1 |     50 |     50 |00:00:00.01 |      53 |
|* 11 |    WINDOW NOSORT STOPKEY               |                |      1 |   8000 |     50 |00:00:00.01 |      53 |
|  12 |     TABLE ACCESS BY INDEX ROWID        | T_ORD          |      1 |   8000 |     51 |00:00:00.01 |      53 |
|* 13 |      INDEX RANGE SCAN DESCENDING       | IX_T_ORD_ST_DT |      1 |   8000 |     51 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."ORD_SEQ"=:B1)
   5 - access("D"."ORD_SEQ"=:B1)
   8 - access("D"."ORD_SEQ"=:B1)
  11 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC )<=50)
  13 - access("ORD_ST"='PENDING')

문제

1번, 3번, 6번 줄이 각각 무엇을 하는지 쓰고, 한 쪽을 열 때 주문상세를 몇 번 찾아가는지 Starts로 계산하시오.
주문마다 주문상세를 한 번만 읽도록 SQL을 고치시오. 페이징을 지금 자리에 그대로 두어야 하는 이유도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 세 값을 자리 수를 맞춰 하나로 이어 붙여 한 번에 가져오고, 바깥에서 잘라 숫자로 되돌린다
--   det = 줄 수(2자리) || 수량 합계(4자리) || 금액 합계(나머지)
SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT,
       TO_NUMBER(SUBSTR(det, 1, 2)) AS det_cnt,
       TO_NUMBER(SUBSTR(det, 3, 4)) AS det_qty,
       TO_NUMBER(SUBSTR(det, 7))    AS det_amt
  FROM (SELECT o.ORD_SEQ, o.ORD_DT, o.CUS_ID, o.ORD_AMT,
               (SELECT LPAD(COUNT(*), 2, '0') || LPAD(SUM(d.ORD_QTY), 4, '0')
                       || SUM(d.ORD_QTY * d.UNT_PRC)
                  FROM T_ORD_DET d
                 WHERE d.ORD_SEQ = o.ORD_SEQ) AS det
          FROM (SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT
                  FROM T_ORD
                 WHERE ORD_ST = 'PENDING'
                 ORDER BY ORD_DT DESC
                 FETCH FIRST 50 ROWS ONLY) o)
 ORDER BY ORD_DT DESC;

-- 12c 이상이면 : ... FROM (페이징 50 건) o
--                 OUTER APPLY (SELECT COUNT(*) AS det_cnt, SUM(ORD_QTY) AS det_qty,
--                                     SUM(ORD_QTY * UNT_PRC) AS det_amt
--                                FROM T_ORD_DET x WHERE x.ORD_SEQ = o.ORD_SEQ) d

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

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |     50 |00:00:00.01 |     203 |
|   1 |  SORT AGGREGATE                        |                |     50 |      1 |     50 |00:00:00.01 |     150 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T_ORD_DET      |     50 |      2 |     99 |00:00:00.01 |     150 |
|*  3 |    INDEX RANGE SCAN                    | PK_T_ORD_DET   |     50 |      2 |     99 |00:00:00.01 |     100 |
|   4 |  SORT ORDER BY                         |                |      1 |     50 |     50 |00:00:00.01 |     203 |
|   5 |   VIEW                                 |                |      1 |     50 |     50 |00:00:00.01 |      53 |
|*  6 |    WINDOW NOSORT STOPKEY               |                |      1 |   8000 |     50 |00:00:00.01 |      53 |
|   7 |     TABLE ACCESS BY INDEX ROWID        | T_ORD          |      1 |   8000 |     51 |00:00:00.01 |      53 |
|*  8 |      INDEX RANGE SCAN DESCENDING       | IX_T_ORD_ST_DT |      1 |   8000 |     51 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."ORD_SEQ"=:B1)
   6 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC )<=50)
   8 - access("ORD_ST"='PENDING')

모범 답안 서술

1번, 3번, 6번 줄은 줄 수(COUNT), 수량 합계, 금액 합계를 구하는 스칼라 서브쿼리 세 개다. 셋 다 페이징이 끝난 50건에서만 돌아 Starts가 50이지만, 같은 주문번호로 같은 상세 줄을 세 번 찾는다. 한 쪽을 열 때 주문상세를 50 × 3 = 150번 찾아가고(인덱스 300블록, 테이블 100블록), 주문 50건을 읽는 데는 53블록이 든다. Buffers 453 가운데 400이 주문상세다. 주문번호가 행마다 다르므로 캐시는 줄여 줄 것이 없다. 스칼라 서브쿼리는 값을 하나만 돌려주므로, 세 값을 자리 수를 맞춰(LPAD) 하나의 문자열로 이어 붙여 한 번에 가져오고, 바깥에서 SUBSTR로 잘라 TO_NUMBER로 되돌린다. 주문상세는 주문마다 한 번만 읽혀 150 → 50번이 되고, Buffers는 453 → 203이 된다. 하루 3,000번이면 약 136만 → 61만 블록이다. 12c 이상이면 OUTER APPLY로 세 값을 한 번에 받아도 된다. 페이징은 지금 자리에 그대로 둔다. 주문상세와 먼저 조인해 GROUP BY한 뒤 페이징하면, 화면에는 50건만 나가는데 결제 대기 8,000건의 상세 약 16,000줄을 다 묶어야 하고 WINDOW NOSORT STOPKEY로 51건에서 멈추던 부분범위 처리가 깨진다. 조인을 쓰려면 페이징한 50건에 붙이고 그 뒤에 묶는다.

해설

이어 붙이는 방법의 대가: 자리 수를 넉넉히 잡지 않으면 값이 커지는 날 자르는 위치가 틀어진다. 앞의 값은 LPAD로 고정 길이를 주고, 마지막 값만 가변 길이로 둔다. 읽기 어려운 SQL이 되므로 주석에 자리 배치를 적어 둔다.
OUTER APPLY: 값 세 개를 그대로 받을 수 있어 읽기 쉽다. 다만 옵티마이저가 래터럴 뷰를 풀어 조인으로 바꾸면 8,000건을 다 묶는 계획이 나올 수 있으니 계획에서 NESTED LOOPS와 Starts 50을 확인한다.
페이징 뒤에 조인 + GROUP BY: 페이징한 50건을 인라인 뷰로 두고 주문상세와 조인해 묶어도 일량은 같다. 바깥 GROUP BY에 화면 컬럼을 모두 넣어야 한다.
교재 09화의 원칙과 같다. 화면에 나가는 행만큼만 일한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황