상담원 화면의 고객 이름 순서를 바로잡은 다음 날부터 주문 원장 첫 화면이 주문 테이블을 통째로 읽는다

기초SELECT한빛유통338 / 700

상황

고객센터 「고객 찾기」는 고객을 이름순으로 20명씩 보여 준다. 상담원들이 "영문 이름은 대문자와 소문자가 따로 놀고, '(주)'로 시작하는 법인과 한자 이름이 맨 앞에 몰려 있다"고 하자, 개발팀은 지난주 고객센터 WAS의 연결 풀 초기화 SQL에 ALTER SESSION SET NLS_SORT = KOREAN_M 한 줄을 넣었다. 이름 순서는 상담원들이 원하던 대로 바뀌었다. 그다음 날부터 같은 WAS의 「주문 원장」 첫 화면(고객번호순 50건)이 0.01초에서 0.4초로 늘었다. 상담원 200명이 하루 약 1만 번 연다. 개발팀은 "주문 원장 SQL은 한 글자도 바꾸지 않았다"고 한다.

테이블과 인덱스

M_CUS 고객: 2,000행 · 30블록 · CUS_NM VARCHAR2 NOT NULL(대부분 한글 이름이고, 영문 이름 37명, 한자 이름 12명, '(주)'로 시작하는 법인 41곳) · 인덱스 IX_M_CUS_NM (CUS_NM)
T_ORD_BIG: 1,000,000행 · 5,826블록 · CUS_ID VARCHAR2(40) NOT NULL(모두 'CUS_' + 숫자 여섯 자리) · 인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): BLEVEL 2 · 리프 약 3,000블록 · 한 고객의 주문은 테이블 곳곳에 흩어져 있다
NLS_COMP는 BINARY 그대로다. 통계는 최신이다.

원본 SQL

sql

-- 고객센터 WAS 연결 풀 초기화 SQL (지난주 추가)
ALTER SESSION SET NLS_SORT = KOREAN_M;

-- Q1 「고객 찾기」 첫 화면 : 이름순 20명
SELECT CUS_ID, CUS_NM, CUS_GD
  FROM M_CUS
 ORDER BY CUS_NM
 FETCH FIRST 20 ROWS ONLY;

-- Q2 「주문 원장」 첫 화면 : 고객번호순 50건
SELECT CUS_ID, ORD_DT, ORD_SEQ, ORD_AMT
  FROM T_ORD_BIG
 ORDER BY CUS_ID, ORD_DT
 FETCH FIRST 50 ROWS ONLY;

실행계획 학습용 예시 · 정렬 키의 모양(NLSSORT)과 정렬 순서는 교재 환경 23ai에서 확인

xplan

[Q2 · 지난주까지 (세션 NLS_SORT = BINARY)]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |     50 |00:00:00.01 |      53 |          |
|*  1 |  COUNT STOPKEY                 |                   |      1 |        |     50 |00:00:00.01 |      53 |          |
|   2 |   VIEW                         |                   |      1 |     50 |     50 |00:00:00.01 |      53 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |      1 |   1000K|     50 |00:00:00.01 |      53 |          |
|   4 |     INDEX FULL SCAN            | IX_ORD_BIG_CUS_DT |      1 |     50 |     50 |00:00:00.01 |       3 |          |
------------------------------------------------------------------------------------------------------------------------

[Q2 · 지금 (세션 NLS_SORT = KOREAN_M)]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |     50 |00:00:00.41 |    5826 |          |
|*  1 |  COUNT STOPKEY                 |                   |      1 |        |     50 |00:00:00.41 |    5826 |          |
|   2 |   VIEW                         |                   |      1 |   1000K|     50 |00:00:00.41 |    5826 |          |
|*  3 |    SORT ORDER BY STOPKEY       |                   |      1 |   1000K|     50 |00:00:00.41 |    5826 | 15360 (0)|
|   4 |     TABLE ACCESS FULL          | T_ORD_BIG         |      1 |   1000K|   1000K|00:00:00.06 |    5826 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=50)
   3 - filter(ROWNUM<=50)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   3 - (#keys=2) NLSSORT("CUS_ID",'nls_sort=''KOREAN_M'''), "ORD_DT", "CUS_ID", "ORD_SEQ", "ORD_AMT"

[Q1 · 지금]  SORT ORDER BY STOPKEY · TABLE ACCESS FULL M_CUS · Buffers 30   (지난주는 INDEX FULL SCAN IX_M_CUS_NM · Buffers 17)

문제

주문 원장 SQL을 바꾸지 않았는데 실행계획이 인덱스 읽기에서 테이블 전체 읽기와 SORT ORDER BY STOPKEY로 바뀐 까닭을 3번 줄의 정렬 키로 설명하시오.
상담원이 원하는 이름 순서는 지키면서 주문 원장이 다시 50건만 읽고 멈추도록 고치시오. 고객번호와 고객 이름에 각각 어떤 정렬이 맞는지 근거를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 연결 풀 초기화 SQL에서 지운다 : 풀 전체가 아니라 이름 목록에만 언어 정렬을 쓴다
-- ALTER SESSION SET NLS_SORT = KOREAN_M;   (삭제)

-- Q1 「고객 찾기」 : 상담원이 원하는 순서를 쿼리에 적는다 (같은 이름은 고객번호 순)
SELECT CUS_ID, CUS_NM, CUS_GD
  FROM M_CUS
 ORDER BY NLSSORT(CUS_NM, 'NLS_SORT=KOREAN_M'), CUS_ID
 FETCH FIRST 20 ROWS ONLY;
-- (선택) 이 목록까지 인덱스로 멈추게 하려면
--   CREATE INDEX IX_M_CUS_NM_KO ON M_CUS (NLSSORT(CUS_NM, 'NLS_SORT=KOREAN_M'));

-- Q2 「주문 원장」 : 그대로 · 세션이 BINARY 라서 IX_ORD_BIG_CUS_DT 의 순서를 쓴다
SELECT CUS_ID, ORD_DT, ORD_SEQ, ORD_AMT
  FROM T_ORD_BIG
 ORDER BY CUS_ID, ORD_DT
 FETCH FIRST 50 ROWS ONLY;

개선 후 실행계획 학습용 예시 · 정렬 키의 모양(NLSSORT)과 정렬 순서는 교재 환경 23ai에서 확인

xplan

[Q2 · 고친 뒤 (세션 BINARY)]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |     50 |00:00:00.01 |      53 |          |
|*  1 |  COUNT STOPKEY                 |                   |      1 |        |     50 |00:00:00.01 |      53 |          |
|   2 |   VIEW                         |                   |      1 |     50 |     50 |00:00:00.01 |      53 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |      1 |   1000K|     50 |00:00:00.01 |      53 |          |
|   4 |     INDEX FULL SCAN            | IX_ORD_BIG_CUS_DT |      1 |     50 |     50 |00:00:00.01 |       3 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=50)

[Q1 · 고친 뒤]  SORT ORDER BY STOPKEY · TABLE ACCESS FULL M_CUS · Buffers 30 · 작업 공간 약 4KB (2,000행 Top-N 정렬)

모범 답안 서술

3번 줄의 정렬 키는 CUS_ID 값 그대로가 아니라 NLSSORT("CUS_ID", 'nls_sort=''KOREAN_M''')다(Column Projection). 세션의 NLS_SORT가 KOREAN_M이면 ORDER BY는 문자 컬럼을 언어 정렬 값으로 줄 세운다. 인덱스 IX_ORD_BIG_CUS_DT는 값을 바이트 순서(BINARY)로 저장해 두었으므로, 옵티마이저는 인덱스 순서가 요구한 순서와 같다고 볼 수 없다. 그래서 4번 줄이 100만 행(5,826블록)을 다 읽고 3번 줄이 Top-N 정렬로 50건을 고른다. SQL은 그대로여도 세션 설정이 정렬의 뜻을 바꿨다. NLS_COMP는 BINARY라서 WHERE 조건의 비교와 인덱스 범위 스캔은 영향을 받지 않는다. 연결 풀 초기화 SQL에서 이 한 줄을 지우고, 언어 정렬은 이름 목록 쿼리에만 ORDER BY NLSSORT(CUS_NM, 'NLS_SORT=KOREAN_M')로 적는다. 세션이 BINARY로 돌아오면 주문 원장은 다시 인덱스를 처음부터 읽다가 50건에서 멈춘다(Buffers 5,826 → 53, 0.41초 → 0.01초). 하루 1만 번이면 약 5,800만 → 53만 블록이다. 두 컬럼에 맞는 정렬이 다르다. 고객번호는 모두 'CUS_' + 숫자 여섯 자리라서 BINARY와 KOREAN_M의 순서가 같고, 언어 정렬은 비용만 더한다. 이름은 다르다. BINARY에서는 영문 대문자가 소문자보다 앞에 오고, '(' 같은 기호와 한자가 한글보다 앞에 온다. KOREAN_M은 영문을 대소문자 구분 없이 앞에 모으고, 한글 다음에 기호로 시작하는 이름과 한자 이름을 둔다. 상담원이 원한 것이 이 순서이므로 이름 목록에는 KOREAN_M이 맞다. 이름 목록은 2,000행을 Top-N 정렬하므로 비용이 작다(Buffers 30). 이것까지 인덱스로 멈추게 하려면 NLSSORT(CUS_NM, 'NLS_SORT=KOREAN_M') 함수 기반 인덱스를 만들면 되지만, 30블록짜리 테이블에 인덱스 유지 비용을 들일 이유가 적다.

해설

ORDER BY NLSSORT(CUS_ID, 'NLS_SORT=BINARY')로는 풀리지 않는다: 풀 설정을 둔 채 주문 원장 쪽에 BINARY를 적어도, 함수를 씌운 정렬 키는 인덱스 순서로 인정되지 않아 정렬이 남는다(교재 환경 23ai에서도 같았다). 정렬 기준은 세션에서 정하거나, 언어 정렬이 필요한 쿼리에만 적는다.
주문 원장에 언어 정렬 인덱스를 만드는 답: (NLSSORT(CUS_ID, 'NLS_SORT=KOREAN_M'), ORD_DT) 인덱스를 만들면 풀 설정을 둔 채로도 멈추게 할 수 있다. 하지만 BINARY와 순서가 같은 코드에 100만 행짜리 인덱스를 하나 더 두는 셈이고, 같은 풀에서 문자 컬럼으로 정렬하는 SQL마다 같은 일이 되풀이된다.
세션 설정 한 줄은 그 풀을 쓰는 모든 SQL에 퍼진다. 넣기 전에 V$SQL에서 그 풀의 문자 컬럼 ORDER BY를 찾아 실행계획이 바뀌는지 확인한다. 연결 풀 초기화 SQL, 로그온 트리거, 클라이언트의 NLS 설정도 함께 본다.
함수 기반 인덱스로 이름 정렬을 받칠 때도 CUS_NM이 NOT NULL이어야 인덱스에 모든 행이 있어 정렬을 대신할 수 있다(교재 09화 §7).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 정렬 키의 모양(NLSSORT)과 정렬 순서는 교재 환경 23ai에서 확인 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 정렬 키의 모양(NLSSORT)과 정렬 순서는 교재 환경 23ai에서 확인 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황