상담원 주문 검색이 고객번호를 넣어도 석 달 치 주문을 다 읽는다

중급SELECT한빛유통54 / 700

상황

고객센터 「주문 검색」은 기간을 반드시 넣고, 주문번호와 고객번호 가운데 아는 것을 더 넣어 찾는다. 상담원 40명이 하루 약 1만 번 쓰고, 열에 아홉은 고객번호와 석 달 기간을 넣는다. 둘 다 비우면 화면이 기간을 하루로 묶는다. SQL은 한 문장이고, 넣지 않은 칸을 건너뛰려고 조건마다 (:v IS NULL OR ...)를 달았다. 오과장은 "고객번호로 시작하는 인덱스까지 만들어 줬는데 왜 안 쓰느냐"고 묻는다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_SEQ 유일 · CUS_ID NOT NULL(2,000명, 고객당 500건, 테이블 전체에 흩어져 있다) · ORD_DT DATE(전부 자정, 하루 913건, 석 달 약 8만 3천 건)
인덱스 IX_ORD_BIG_SEQ (ORD_SEQ) 유니크 · IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT) · IX_ORD_BIG_DT (ORD_DT) · 통계 최신
고객 CUS_000123의 2024-04-01 ~ 2024-06-30 주문 41건

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND (:v_ord IS NULL OR ORD_SEQ = :v_ord)
   AND (:v_cus IS NULL OR CUS_ID = :v_cus)
 ORDER BY ORD_DT DESC, ORD_SEQ DESC

실행계획 학습용 예시

xplan

[고객번호 CUS_000123 · 2024-04-01 ~ 2024-06-30]
---------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows | A-Time      | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |     41 | 00:00:00.09 |    5826 |
|   1 |  SORT ORDER BY       |           |      1 |    205 |     41 | 00:00:00.09 |    5826 |
|*  2 |   FILTER             |           |      1 |        |     41 | 00:00:00.09 |    5826 |
|*  3 |    TABLE ACCESS FULL | T_ORD_BIG |      1 |    205 |     41 | 00:00:00.09 |    5826 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - filter(("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1 AND
              (:V_ORD IS NULL OR "ORD_SEQ"=:V_ORD) AND (:V_CUS IS NULL OR "CUS_ID"=:V_CUS)))

문제

고객번호를 넣었는데도 IX_ORD_BIG_CUS_DT를 쓰지 못하고 테이블 전체를 읽는 이유를 Predicate Information으로 설명하시오.
입력 조합(주문번호가 있을 때, 고객번호만 있을 때, 기간만 있을 때)마다 맞는 인덱스로 찾아가도록 SQL을 고치시오.
고친 방법의 대가와, 옵션 조건이 더 늘어날 때의 대안을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 갈래 ① 주문번호가 있다 → 유니크 인덱스 한 번
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE :v_ord IS NOT NULL
   AND ORD_SEQ = :v_ord
   AND CUS_ID  = NVL(:v_cus, CUS_ID)          -- 두 칸을 다 넣었으면 둘 다 맞아야 한다 (CUS_ID는 NOT NULL)
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
UNION ALL
-- 갈래 ② 고객번호만 있다 → (CUS_ID, ORD_DT)로 그 고객의 기간만
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE :v_ord IS NULL
   AND :v_cus IS NOT NULL
   AND CUS_ID  = :v_cus
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
UNION ALL
-- 갈래 ③ 기간만 있다 (화면이 하루로 묶는다) → (ORD_DT)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE :v_ord IS NULL
   AND :v_cus IS NULL
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY ORD_DT DESC, ORD_SEQ DESC

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

xplan

[고객번호 CUS_000123 · 2024-04-01 ~ 2024-06-30]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | Starts | E-Rows | A-Rows | A-Time      | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   |      1 |        |     41 | 00:00:00.01 |      45 |
|   1 |  SORT ORDER BY                          |                   |      1 |    956 |     41 | 00:00:00.01 |      45 |
|   2 |   UNION-ALL                             |                   |      1 |        |     41 | 00:00:00.01 |      45 |
|*  3 |    FILTER                               |                   |      1 |        |      0 | 00:00:00.01 |       0 |
|*  4 |     TABLE ACCESS BY INDEX ROWID         | T_ORD_BIG         |      0 |      1 |      0 | 00:00:00.01 |       0 |
|*  5 |      INDEX UNIQUE SCAN                  | IX_ORD_BIG_SEQ    |      0 |      1 |      0 | 00:00:00.01 |       0 |
|*  6 |    FILTER                               |                   |      1 |        |     41 | 00:00:00.01 |      45 |
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG         |      1 |     42 |     41 | 00:00:00.01 |      45 |
|*  8 |      INDEX RANGE SCAN                   | IX_ORD_BIG_CUS_DT |      1 |     42 |     41 | 00:00:00.01 |       4 |
|*  9 |    FILTER                               |                   |      1 |        |      0 | 00:00:00.01 |       0 |
|  10 |     TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG         |      0 |    913 |      0 | 00:00:00.01 |       0 |
|* 11 |      INDEX RANGE SCAN                   | IX_ORD_BIG_DT     |      0 |    913 |      0 | 00:00:00.01 |       0 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter((:V_ORD IS NOT NULL AND TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD')))
   4 - filter(("CUS_ID"=NVL(:V_CUS,"CUS_ID") AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND
              "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))
   5 - access("ORD_SEQ"=:V_ORD)
   6 - filter((:V_ORD IS NULL AND :V_CUS IS NOT NULL AND TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD')))
   8 - access("CUS_ID"=:V_CUS AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)
   9 - filter((:V_ORD IS NULL AND :V_CUS IS NULL AND TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD')))
  11 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

모범 답안 서술

3번 줄을 보면 고객번호 조건은 (:V_CUS IS NULL OR "CUS_ID"=:V_CUS) 모양으로 filter에만 있고 access가 없다. 이 조건은 :v_cus가 NULL이면 모든 행이 맞는 조건이라서, 파싱할 때 하나로 정해 두는 계획은 이것으로 범위를 정할 수 없다. 옵션 조건이 하나라면 옵티마이저가 OR-Expansion으로 갈래를 나눠 주기도 하지만, 이 SQL처럼 옵션 조건이 둘이면 펼치지 않았다(계획에 VW_ORE_가 없다). 남은 access 후보는 기간뿐인데 석 달이면 약 8만 3천 건이라, 옵티마이저는 IX_ORD_BIG_DT로 8만 번 테이블을 찾아가느니 FULL을 골랐다. 그래서 고객 한 명의 41건을 찾으려고 100만 행을 다 읽는다(Buffers 5,826). 입력 조합마다 갈래를 따로 쓰고 UNION ALL로 잇는다. 갈래마다 :v_ord IS NOT NULL, :v_ord IS NULL AND :v_cus IS NOT NULL, :v_ord IS NULL AND :v_cus IS NULL처럼 바인드 조건을 붙여 서로 겹치지 않게 하면, 실행할 때 한 갈래만 돌고 나머지는 FILTER에서 멈춘다(Starts 0). 주문번호 갈래는 ORD_SEQ = :v_ord로 유니크 인덱스를 한 번 찾고, 고객 갈래는 CUS_ID = :v_cus와 기간으로 IX_ORD_BIG_CUS_DT에서 그 고객의 석 달 치만 읽고, 기간 갈래는 하루치를 IX_ORD_BIG_DT로 읽는다. 고객번호와 석 달을 넣은 검색은 Buffers 5,826 → 45가 된다. 주문번호 갈래에는 CUS_ID = NVL(:v_cus, CUS_ID)를 남겨, 두 칸을 다 넣었을 때 둘 다 맞아야 한다는 원래 뜻을 지킨다. CUS_ID가 NOT NULL이라 이 NVL은 안전하다. 화면에서 조합에 따라 SQL 셋을 따로 불러도 결과는 같다. 다만 화면 코드에 분기가 생기고 SQL을 세 벌 관리해야 한다. 대가는 SQL이 길어진다는 점이다. 조건이 늘면 조합도 곱으로 는다. 옵션 조건이 대여섯 개로 늘면 갈래를 손으로 나누기보다 들어온 조건만 붙이는 동적 SQL(값은 바인드 변수로)로 넘어간다. 어느 쪽이든 결과가 넓은 조합(기간만 넣는 검색)은 화면 규칙으로 범위를 묶어 두어야 한다.

해설

함정: 두 조건을 CUS_ID = NVL(:v_cus, CUS_ID) AND ORD_SEQ = NVL(:v_ord, ORD_SEQ)로 바꿔도 옵션 조건이 둘이라 옵티마이저가 하나만 펼치거나 펼치지 않는다. 교재 08화 OR 표의 NVL 펼치기는 옵션 조건이 하나일 때의 이야기다.
갈래 조건은 서로 겹치지 않아야 한다. 겹치면 같은 주문이 두 갈래에서 나와 두 번 보인다. 여기서는 주문번호가 있는지, 없으면 고객번호가 있는지로 순서를 정해 겹침을 없앴다.
바인드 조건만 보는 FILTER는 계획 하나에 길을 여럿 두는 장치다(교재 11화 OR-Expansion의 좋은 얼굴과 같은 원리). 값이 바뀌어도 커서는 하나다.
ORDER BY는 UNION ALL 전체에 한 번 건다. 주문번호 갈래는 1건, 고객 갈래는 수십 건이라 정렬 부담은 작다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황