PART-004

마감 재집계에 정정 요청 주문을 함께 넣자 한 달 집계가 36개월 칸을 다 연다

기초SELECT한빛유통379 / 700

상황

정산팀의 「월 마감 재집계」는 마감할 달을 고르면 그 달 주문의 상태별 건수와 금액을 다시 계산한다. 월초 마감 주간에는 하루 수백 번 호출된다. 지난주부터 고객센터가 정정을 요청한 주문은 주문한 달과 상관없이 이번 마감에 함께 넣으라는 요구가 붙었다. 조민우가 조건에 OR ADJ_YN = 'Y' 한 줄을 더한 뒤로 0.01초에 끝나던 조회가 0.12초가 되었다. 화면은 :v_ym = '202406'을 넘긴다.

테이블과 인덱스

T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): T_ORD_BIG 1,000,000행을 PARTITION BY RANGE (ORD_DT) 월별 36칸(P202201 ~ P202412)과 빈 PMAX 1칸, 모두 37칸으로 나눴다. P202406은 30번째 칸이다. 칸 하나를 FULL로 읽으면 약 161블록, 37칸을 다 읽으면 5,868블록이다. 빈 PMAX는 세그먼트가 없다.
이 문제에서는 정정 요청 표시 ADJ_YN VARCHAR2(1)이 더해져 있다(문제 설정). 정정 요청이 걸린 주문만 'Y'이고 나머지는 NULL이라 블록 수는 그대로다. 'Y'는 352건이고 36칸에 고루 흩어져 있으며, 그중 2024년 6월 주문은 12건이다.
ORD_DT는 NOT NULL이고 전부 자정이다. 2024년 6월은 27,390행이다.
LOCAL 인덱스 IX_P14_ADJ (ADJ_YN): NULL은 인덱스에 들어가지 않으므로 칸마다 블록 1개다. ORD_DT에는 인덱스가 없다. 통계는 최신이다.

원본 SQL

sql

SELECT ORD_ST, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD_BIG_P14
 WHERE (ORD_DT >= TO_DATE(:v_ym, 'YYYYMM')
        AND ORD_DT < ADD_MONTHS(TO_DATE(:v_ym, 'YYYYMM'), 1))
    OR ADJ_YN = 'Y'
 GROUP BY ORD_ST

실행계획 학습용 예시 · 교재 14화 실측 규모

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |      1 |        |      3 | 00:00:00.12 |    5868 |        |       |
|   1 |  HASH GROUP BY        |               |      1 |      3 |      3 | 00:00:00.12 |    5868 |        |       |
|   2 |   PARTITION RANGE ALL |               |      1 |  27740 |  27730 | 00:00:00.11 |    5868 |      1 |    37 |
|*  3 |    TABLE ACCESS FULL  | T_ORD_BIG_P14 |     36 |  27740 |  27730 | 00:00:00.10 |    5868 |      1 |    37 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter((("ORD_DT">=TO_DATE(:V_YM,'YYYYMM') AND "ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1))
              OR "ADJ_YN"='Y'))

문제

실행계획의 어느 항목에서 칸을 전부 열었다는 것을 알 수 있는지 쓰고, 조건에 그 달의 범위가 그대로 있는데도 칸을 고르지 못하는 까닭을 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 그 달의 주문은 그 달 칸에서만 읽게 하시오. 두 조건에 모두 맞는 주문을 한 번만 세는 방법과 읽는 블록 수가 어떻게 바뀌는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 갈래 ① 그 달 주문 : 파티션 키 범위만 있으므로 칸 하나
-- 갈래 ② 정정 요청 주문 : 칸은 못 고르지만 IX_P14_ADJ 로 찾아가고, 갈래 ①이 이미 센 그 달 주문은 뺀다
SELECT ORD_ST, COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM (SELECT ORD_ST, ORD_AMT
          FROM T_ORD_BIG_P14
         WHERE ORD_DT >= TO_DATE(:v_ym, 'YYYYMM')
           AND ORD_DT <  ADD_MONTHS(TO_DATE(:v_ym, 'YYYYMM'), 1)
        UNION ALL
        SELECT ORD_ST, ORD_AMT
          FROM T_ORD_BIG_P14
         WHERE ADJ_YN = 'Y'
           AND (LNNVL(ORD_DT >= TO_DATE(:v_ym, 'YYYYMM'))
                OR LNNVL(ORD_DT < ADD_MONTHS(TO_DATE(:v_ym, 'YYYYMM'), 1))))
 GROUP BY ORD_ST;

-- 옵티마이저에게 나누게 해도 같다 (LNNVL 은 옵티마이저가 붙인다)
--   SELECT /*+ OR_EXPAND */ ORD_ST, COUNT(*), SUM(ORD_AMT) FROM T_ORD_BIG_P14 WHERE (…) OR ADJ_YN = 'Y' GROUP BY ORD_ST;

개선 후 실행계획 학습용 예시 · 교재 14화 실측 규모

xplan

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |               |      1 |        |      3 | 00:00:00.01 |     549 |        |       |
|   1 |  HASH GROUP BY                                 |               |      1 |      3 |      3 | 00:00:00.01 |     549 |        |       |
|   2 |   VIEW                                         |               |      1 |  27742 |  27730 | 00:00:00.01 |     549 |        |       |
|   3 |    UNION-ALL                                   |               |      1 |        |  27730 | 00:00:00.01 |     549 |        |       |
|*  4 |     FILTER                                     |               |      1 |        |  27390 | 00:00:00.01 |     161 |        |       |
|   5 |      PARTITION RANGE ITERATOR                  |               |      1 |  27390 |  27390 | 00:00:00.01 |     161 |    KEY |   KEY |
|*  6 |       TABLE ACCESS FULL                        | T_ORD_BIG_P14 |      1 |  27390 |  27390 | 00:00:00.01 |     161 |    KEY |   KEY |
|   7 |     PARTITION RANGE ALL                        |               |      1 |    352 |    340 | 00:00:00.01 |     388 |      1 |    37 |
|*  8 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14 |     36 |    352 |    340 | 00:00:00.01 |     388 |      1 |    37 |
|*  9 |       INDEX RANGE SCAN                         | IX_P14_ADJ    |     36 |    352 |    352 | 00:00:00.01 |      36 |      1 |    37 |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(TO_DATE(:V_YM,'YYYYMM')<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1))
   6 - filter(("ORD_DT">=TO_DATE(:V_YM,'YYYYMM') AND "ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1)))
   8 - filter((LNNVL("ORD_DT">=TO_DATE(:V_YM,'YYYYMM')) OR LNNVL("ORD_DT"<ADD_MONTHS(TO_DATE(:V_YM,'YYYYMM'),1))))
   9 - access("ADJ_YN"='Y')

  (실행 때 고른 칸 — 갈래 ①은 30번 P202406 하나 · 갈래 ②는 정정 요청 352건 중 6월 12건을 뺀 340건)

모범 답안 서술

2번 줄의 PARTITION RANGE ALL과 Pstart 1, Pstop 37, 3번 줄의 Starts 36을 보면 데이터가 있는 36칸을 다 열었다는 것을 알 수 있다(빈 PMAX는 세그먼트가 없어 시작하지 않았다). 6월 칸 하나면 161블록인데 5,868블록을 읽었다. 조건에 6월 범위가 그대로 있는데도 칸을 고르지 못하는 까닭은 OR에 있다. OR는 두 조건 중 하나만 맞아도 결과에 넣는다. ADJ_YN = 'Y'인 주문은 주문한 달과 상관없이 36칸 어디에나 있을 수 있으므로, 옵티마이저는 어느 칸도 건너뛸 수 없다. 파티션 키를 가공하지 않았어도 OR의 다른 쪽이 칸과 상관없는 컬럼에 걸려 있으면 칸 고르기는 막힌다. 그래서 37칸을 다 열고 행마다 두 조건을 따져 거른다. 고치려면 OR를 두 갈래로 나눠 갈래마다 자기 길로 읽게 한다. 갈래 ①은 파티션 키 범위만 남으므로 KEY로 30번 칸 하나만 연다(Starts 1, 161블록). 갈래 ②는 칸을 고를 수 없지만 IX_P14_ADJ로 정정 요청 주문만 찾아간다(칸마다 인덱스 1블록씩 36블록과 테이블 352블록). 두 갈래를 그냥 UNION ALL로 이어 붙이면 6월 주문이면서 정정 요청이 걸린 12건을 두 번 센다. 그래서 갈래 ②에 LNNVL(ORD_DT >= 첫날) OR LNNVL(ORD_DT < 다음 달 첫날)을 붙여 갈래 ①이 이미 센 행을 뺀다. 결과는 27,390 + 340 = 27,730행으로 원래와 같고, Buffers는 5,868 → 549로 준다. /*+ OR_EXPAND */ 힌트를 주면 옵티마이저가 같은 모양으로 나누고 LNNVL도 스스로 붙인다.

해설

나눈 보람은 갈래마다 자기 길이 있을 때 생긴다. 갈래 ①의 길은 칸 고르기, 갈래 ②의 길은 IX_P14_ADJ다. ADJ_YN에 인덱스가 없다면 갈래 ②가 다시 36칸을 FULL로 읽어(5,868블록) 나눈 쪽이 오히려 더 비싸다.
옵티마이저가 스스로 나누기도 한다(OR-Expansion). 나누면 계획에 VW_ORE_ 뷰와 UNION-ALL, LNNVL이 보인다(교재 08화 §4). 이 계획처럼 나누지 않았다면 OR_EXPAND 힌트(예전 버전은 USE_CONCAT)나 직접 쓴 UNION ALL로 나눈다.
LNNVL을 쓰는 까닭: LNNVL(조건)은 조건이 거짓이거나 NULL일 때 참이다. 이 문제는 ORD_DT가 NOT NULL이라 ORD_DT < 첫날 OR ORD_DT >= 다음 달 첫날로 써도 같다. NULL을 허용하는 컬럼이면 NOT (…)만으로는 그 컬럼이 NULL인 행이 두 갈래에서 모두 빠진다.
UNION으로 이어 붙이면 중복은 사라지지만 소트가 더해지고, 고른 컬럼(ORD_ST, ORD_AMT)이 같은 서로 다른 주문까지 하나로 합쳐 건수와 금액이 줄어든다. 행을 구분하는 키 없이 UNION으로 중복을 없애면 안 된다.
OR의 모든 갈래에 파티션 키 조건이 있으면(예: "6월 주문, 또는 5월 주문 중 정정 요청분") OR를 그대로 두어도 갈래별 범위를 합쳐 칸을 고를 수 있다(PARTITION RANGE OR). 칸을 막는 것은 OR 자체가 아니라 칸과 상관없는 갈래다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 실측 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 실측 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황