조건이 여섯 칸인 정산 검색 프로시저가 어떤 조건으로 찾든 정산 10만 건을 다 읽는다

실전PLSQL한빛유통62 / 700

상황

정산팀 「정산 검색」 화면은 저장 프로시저 P_STLM_SEARCH가 돌려주는 REF CURSOR를 보여 준다. 조건은 정산일 시작과 끝, 고객번호, 주문번호, 정산번호, 금액 하한의 여섯 칸이고 모두 옵션이다. 하루 약 8천 번 호출된다. 2년 전 조민우가 "SQL은 하나로"라는 원칙에 따라 짰다. 한 번에 640블록이라 하루 500만 블록이 넘는다. 박팀장은 ① 지금처럼 정적 SQL 하나 ② 입력 조합마다 정적 SQL을 나누는 분기 ③ 들어온 조건만 붙이는 동적 SQL을 비교해 오라고 했다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · 정산일 순으로 쌓였다 · STLM_SEQ · ORD_SEQ · CUS_ID(고객 2,000명, 고객당 약 50건) · STLM_DT DATE(시분초 있음, 하루 약 137건) · STLM_AMT
인덱스 IX_STLM_SEQ (STLM_SEQ) 유니크 · IX_STLM_ORD (ORD_SEQ) · IX_STLM_DT (STLM_DT) · IX_STLM_CUS_DT (CUS_ID, STLM_DT) · 통계 최신
한 달 호출 로그: 고객번호 55% · 주문번호 25% · 정산번호 10% · 정산일 범위만 8% · 금액 하한을 함께 넣은 것 2%
화면은 정산일의 기본값으로 최근 7일을 넣지만 상담원이 지울 수 있다

원본 SQL

sql

PROCEDURE P_STLM_SEARCH (
  p_from    IN VARCHAR2,          -- 'YYYYMMDD'
  p_to      IN VARCHAR2,
  p_cus     IN VARCHAR2,
  p_ord     IN NUMBER,
  p_stlm    IN NUMBER,
  p_min_amt IN NUMBER,
  p_rc      OUT SYS_REFCURSOR) IS
BEGIN
  OPEN p_rc FOR
    SELECT STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT
      FROM T_ORD_STLM
     WHERE (p_from    IS NULL OR STLM_DT >= TO_DATE(p_from, 'YYYYMMDD'))
       AND (p_to      IS NULL OR STLM_DT <  TO_DATE(p_to, 'YYYYMMDD') + 1)
       AND (p_cus     IS NULL OR CUS_ID   = p_cus)
       AND (p_ord     IS NULL OR ORD_SEQ  = p_ord)
       AND (p_stlm    IS NULL OR STLM_SEQ = p_stlm)
       AND (p_min_amt IS NULL OR STLM_AMT >= p_min_amt)
     ORDER BY STLM_DT DESC;
END;

실행계획 학습용 예시

xplan

[고객번호 CUS_000123만 넣었을 때(정산일은 지움) · 다른 조합도 계획은 같다]
---------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts | E-Rows | A-Rows | A-Time      | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |      1 |        |     50 | 00:00:00.01 |     640 |
|   1 |  SORT ORDER BY      |            |      1 |      1 |     50 | 00:00:00.01 |     640 |
|*  2 |   TABLE ACCESS FULL | T_ORD_STLM |      1 |      1 |     50 | 00:00:00.01 |     640 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(((:B6 IS NULL OR "STLM_DT">=TO_DATE(:B6,'YYYYMMDD')) AND (:B5 IS NULL OR
              "STLM_DT"<TO_DATE(:B5,'YYYYMMDD')+1) AND (:B4 IS NULL OR "CUS_ID"=:B4) AND (:B3 IS NULL OR
              "ORD_SEQ"=:B3) AND (:B2 IS NULL OR "STLM_SEQ"=:B2) AND (:B1 IS NULL OR "STLM_AMT">=:B1)))

문제

어떤 조건을 넣어도 계획이 같고 640블록을 읽는 이유를 설명하시오.
① 정적 SQL 하나 ② 입력 조합마다 정적 SQL로 분기 ③ 들어온 조건만 붙이는 동적 SQL을 읽는 양, 커서 수, 유지보수, 안전성(SQL 주입, 컴파일 검사)의 관점에서 비교하시오.
채택한 방법으로 프로시저를 고치시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_STLM_SEARCH (
  p_from    IN VARCHAR2,
  p_to      IN VARCHAR2,
  p_cus     IN VARCHAR2,
  p_ord     IN NUMBER,
  p_stlm    IN NUMBER,
  p_min_amt IN NUMBER,
  p_rc      OUT SYS_REFCURSOR) IS
  v_sql VARCHAR2(1000) :=
    'SELECT STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT FROM T_ORD_STLM WHERE 1 = 1';
BEGIN
  -- 들어온 조건은 컬럼 비교로, 안 들어온 조건은 바인드 자리만 남기는 항등식으로 붙인다
  --   → USING 목록은 언제나 같고, 문장은 조합마다 하나 (값은 하나도 이어 붙이지 않는다)
  v_sql := v_sql || CASE WHEN p_from    IS NOT NULL THEN ' AND STLM_DT >= TO_DATE(:b1, ''YYYYMMDD'')'
                                                    ELSE ' AND (1 = 1 OR :b1 IS NULL)' END;
  v_sql := v_sql || CASE WHEN p_to      IS NOT NULL THEN ' AND STLM_DT < TO_DATE(:b2, ''YYYYMMDD'') + 1'
                                                    ELSE ' AND (1 = 1 OR :b2 IS NULL)' END;
  v_sql := v_sql || CASE WHEN p_cus     IS NOT NULL THEN ' AND CUS_ID = :b3'
                                                    ELSE ' AND (1 = 1 OR :b3 IS NULL)' END;
  v_sql := v_sql || CASE WHEN p_ord     IS NOT NULL THEN ' AND ORD_SEQ = :b4'
                                                    ELSE ' AND (1 = 1 OR :b4 IS NULL)' END;
  v_sql := v_sql || CASE WHEN p_stlm    IS NOT NULL THEN ' AND STLM_SEQ = :b5'
                                                    ELSE ' AND (1 = 1 OR :b5 IS NULL)' END;
  v_sql := v_sql || CASE WHEN p_min_amt IS NOT NULL THEN ' AND STLM_AMT >= :b6'
                                                    ELSE ' AND (1 = 1 OR :b6 IS NULL)' END;
  v_sql := v_sql || ' ORDER BY STLM_DT DESC';

  OPEN p_rc FOR v_sql USING p_from, p_to, p_cus, p_ord, p_stlm, p_min_amt;
END;

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

xplan

[고객번호만 넣었을 때 만들어지는 문장]
SELECT ... FROM T_ORD_STLM WHERE 1 = 1 AND (1 = 1 OR :b1 IS NULL) AND (1 = 1 OR :b2 IS NULL)
   AND CUS_ID = :b3 AND (1 = 1 OR :b4 IS NULL) AND (1 = 1 OR :b5 IS NULL) AND (1 = 1 OR :b6 IS NULL)
 ORDER BY STLM_DT DESC
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows | A-Rows | A-Time      | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |     50 | 00:00:00.01 |      53 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T_ORD_STLM     |      1 |     50 |     50 | 00:00:00.01 |      53 |
|*  2 |   INDEX RANGE SCAN DESCENDING | IX_STLM_CUS_DT |      1 |     50 |     50 | 00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUS_ID"=:B3)

[다른 조합] 주문번호만 IX_STLM_ORD · 정산번호만 IX_STLM_SEQ · 둘 다 Buffers 4
            정산일 7일만 IX_STLM_DT · 959건 · Buffers 약 12

모범 답안 서술

PL/SQL 변수는 SQL 안에서 바인드 변수(:B1 ~ :B6)가 되고, 여섯 조건이 모두 (:B IS NULL OR ...) 모양이다. 어느 조건이든 바인드가 NULL이면 모든 행이 맞으므로, 파싱할 때 하나로 정해 두는 계획은 어떤 조건으로도 범위를 정할 수 없어 access가 없다. 옵션 조건이 여섯이라 OR-Expansion도 일어나지 않는다. 그래서 커서 하나, 계획 하나가 모든 조합에 FULL을 쓰고, 고객 한 명의 50건을 찾을 때도 640블록을 다 읽는다(하루 8천 번이면 512만 블록). ① 지금의 정적 SQL 하나는 커서가 하나이고 코드가 짧지만, 위와 같이 어떤 조합도 인덱스를 쓰지 못한다. ② 입력 조합마다 정적 SQL로 분기하면(IF p_ord IS NOT NULL THEN OPEN p_rc FOR SELECT ... WHERE ORD_SEQ = p_ord ...) 조합마다 맞는 인덱스를 쓰고, SQL이 컴파일 때 검사되며 테이블과의 의존 관계도 남는다. 다만 조건이 여섯 칸이면 의미 있는 조합만 골라도 분기가 대여섯 개로 늘고, 같은 SELECT 목록과 ORDER BY가 분기마다 중복된다. 조건이 하나 늘 때마다 분기를 다시 짜야 한다. ③ 들어온 조건만 붙이는 동적 SQL은 조합마다 그 조합에 맞는 문장이 하나씩 생기고, 문장마다 옵티마이저가 맞는 인덱스를 고른다. 안 들어온 조건은 AND (1 = 1 OR :b3 IS NULL)처럼 바인드 자리만 남기는 항등식으로 붙인다. 옵티마이저는 이 항등식을 참으로 지워 버리므로 계획에는 영향이 없고, USING 목록은 언제나 같은 여섯 개라 코드가 단순하다. 문장 수는 실제로 쓰이는 조합만큼으로 정해져 있다(로그상 대여섯 개). 값을 문자열로 이어 붙이지 않으므로 값마다 문장이 생기는 하드 파싱도, SQL 주입도 없다. 대가는 SQL이 실행할 때 만들어지므로 컴파일 검사를 받지 못하고, 문장이 틀리면 실행해 봐야 안다는 점이다. 조합별 문장을 테스트 목록으로 두고 한 번씩 돌려 확인한다. 그래서 ③을 고른다. 고객번호만 넣으면 IX_STLM_CUS_DT를 역순으로 읽어 정렬 없이 50건을 찾고(Buffers 640 → 53), 주문번호나 정산번호만 넣으면 4블록, 정산일 7일만 넣으면 IX_STLM_DT로 12블록 안팎이다.

해설

함정: 동적 SQL을 ' AND CUS_ID = ''' || p_cus || ''''처럼 값을 이어 붙여 만들면 인덱스는 타지만 고객마다 다른 문장이 생겨 하드 파싱이 쏟아지고(교재 11화), 따옴표가 든 입력으로 SQL 주입이 가능해진다.
정산일까지 지우고 아무 조건 없이 검색하면 ③도 10만 건을 FULL로 읽는다. 프로시저에서 '조건이 하나도 없으면 정산일 7일을 강제한다' 같은 규칙을 둔다.
금액 하한처럼 넓은 범위 조건에는 인덱스를 만들지 않는다. 다른 조건과 함께 들어올 때 filter로 쓰이면 충분하다(로그상 2%).
바인드 자리를 맞추는 다른 방법은 DBMS_SQL로 들어온 바인드만 하나씩 묶는 것이다. 조건이 많거나 개수가 정해지지 않은 IN 목록이 섞이면 이쪽이 낫다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황