추출 조건에 「전부」를 더한 뒤로 고객별 최근 주문 3건 추출이 TEMP를 37MB 쓴다

모범 답안 SQL ​

-- 매주 (:n > 0) : 순위 조건을 홀로 둔다 → 정렬 단계로 내려가 고객마다 n 건만 남긴다 (WINDOW SORT PUSHED RANK)
SELECT CUS_ID, ORD_SEQ, ORD_DT, ORD_AMT, rn
  FROM (SELECT o.CUS_ID, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT,
               ROW_NUMBER() OVER (PARTITION BY o.CUS_ID
                                  ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC) AS rn
          FROM T_ORD_BIG o)
 WHERE rn <= :n;

-- 분기에 한두 번 (:n = 0, 전부) : 따로 보낸다 · 100만 행 정렬은 피할 수 없으니 정산 배치와 겹치지 않는 시간에
SELECT o.CUS_ID, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT,
       ROW_NUMBER() OVER (PARTITION BY o.CUS_ID
                          ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC) AS rn
  FROM T_ORD_BIG o;

개선 후 실행계획 학습용 예시 · 메모리 크기는 교재 09화 규모 ​

[매주 추출 · :n = 3 · 월요일 06:00]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1 |        |   6000 |00:00:00.38 |    5826 |       |          |         |
|*  1 |  VIEW                   |           |      1 |   1000K|   6000 |00:00:00.38 |    5826 |       |          |         |
|*  2 |   WINDOW SORT PUSHED RANK|          |      1 |   1000K|   6000 |00:00:00.37 |    5826 |   462K|  410K (0)|         |
|   3 |    TABLE ACCESS FULL    | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.06 |    5826 |       |          |         |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"<=:N)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "O"."CUS_ID" ORDER BY INTERNAL_FUNCTION("O"."ORD_DT") DESC ,
              INTERNAL_FUNCTION("O"."ORD_SEQ") DESC )<=:N)

모범 답안 서술 ​

2번 줄을 비교하면 원인이 보인다. 지난달까지는 WHERE rn <= :n이 정렬 단계로 내려가 WINDOW SORT PUSHED RANK가 되었다. 고객마다 지금까지 본 주문 가운데 위의 3건만 작업 공간에 두고 나머지는 보자마자 버리므로, 작업 공간에는 6,000행(고객 2,000명 × 3건)만 남아 410K로 끝났다. 지금은 조건이 (:n = 0 OR rn <= :n)처럼 OR로 묶였다. OR로 묶인 순위 조건은 정렬 단계로 밀어 넣을 수 없다. :n = 0이면 모든 행이 필요하므로 '고객마다 n건만 남겨도 된다'는 약속이 성립하지 않기 때문이다. 그래서 2번 줄이 PUSHED RANK 없는 WINDOW SORT로 100만 행을 전부 정렬하고, 1번 VIEW 줄이 그 뒤에 6,000행을 거른다. 필요한 메모리(OMem)는 44M인데 06시에는 work area 상한이 약 20MB라 20M (1), 곧 one-pass로 돌았고 넘친 37M를 TEMP에 썼다가 다시 읽었다. 2.6초의 대부분이 여기서 나온다. 고친 SQL은 두 경우를 나눈다. 매주 도는 3건 추출은 WHERE rn <= :n 하나만 두어 PUSHED RANK를 되찾는다(Used-Mem 410K, TEMP 0, 0.38초). 분기에 한두 번 도는 '전부' 추출은 따로 보낸다. 전부에 순번을 매기려면 100만 행 정렬은 피할 수 없으므로 정산 배치와 겹치지 않는 시간으로 옮긴다. Buffers는 고친 뒤에도 5,826 그대로다. PUSHED RANK는 정렬 메모리를 줄일 뿐이고, 고객마다 최근 3건을 알려면 테이블을 한 번은 다 읽어야 한다. IX_ORD_BIG_CUS_DT로 고객마다 최근 3건을 찾아가면 고객 한 명에 인덱스 3블록과 테이블 3블록, 2,000명이면 약 12,000블록이라 FULL 한 번(5,826)보다 비싸다. 고객 전부를 뽑는 추출에는 테이블을 한 번 읽고 고객마다 위의 3건만 남기는 지금 방식이 맞다. 정렬 키 끝의 ORD_SEQ DESC는 같은 날 주문 사이의 순서를 정해 매주 같은 데이터에서 같은 3건이 나오게 한다.

해설 ​

• 한 문장으로 두고 싶다면: … WHERE rn <= :n AND :n > 0 UNION ALL SELECT … WHERE :n = 0처럼 서로 겹치지 않는 두 가지로 나눈다. 앞 가지는 순위 조건이 AND로만 묶여 PUSHED RANK가 되고, 실행할 때는 바인드 값에 따라 한쪽만 실제로 일한다. 계획이 길어지는 대신 애플리케이션은 SQL 하나만 안다.
• :n = 0 대신 :n = 999999처럼 큰 값을 넣게 하는 꼼수도 있다. 조건은 단순해지지만 고객마다 남길 행이 사실상 전부라서, 정렬은 결국 100만 행을 다 담는다. '전부'는 따로 다루는 편이 낫다.
• SORT-008에서 본 대로 Top-N은 일량이 아니라 작업 공간을 줄인다. 이 문제도 Buffers는 그대로이고 TEMP와 시간만 준다.
• PGA를 늘리면 one-pass가 optimal로 바뀔 뿐 100만 행 정렬은 그대로다. 조건 하나를 고쳐 정렬에 남는 행을 6,000행으로 줄이는 쪽이 근본 처방이다(교재 09화 §6).

관련 교재 ​

• 09화 · Top-N 정렬과 STOPKEY
• 09화 · "PGA부터 올리자"를 판정하는 법