상담원 PC 40대가 10초마다 숫자 하나를 센다

중급SELECT한빛유통05 / 700

상황

고객센터 화면 맨 위의 「미완료 주문」 배지는 완료되지 않은 주문 수를 보여 준다. 상담원 PC 40대가 10초마다 이 숫자를 새로 센다. 처음 판은 NOT IN ('COMPLETE')였다. 느리다는 말이 나오자 조민우는 어디선가 들은 말("부정형은 인덱스를 못 탄다")을 따라 긍정형으로 바꿨다. 그러면서 "혹시 상태가 빈 주문이 있으면 빠질까 봐" 하며 OR ORD_ST IS NULL도 붙였다. 그랬더니 더 느려졌다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST COMPLETE 900,000 · PENDING 80,000 · CANCEL 20,000 · NULL 0건(NOT NULL 제약은 없다) · 상태값은 지금 이 셋뿐이다
인덱스 IX_ORD_BIG_ST (ORD_ST): BLEVEL 2 · 리프 2,775블록 · 통계 최신 · ORD_ST에 히스토그램(FREQUENCY)

원본 SQL

sql

-- [처음 판]
SELECT COUNT(*) FROM T_ORD_BIG WHERE ORD_ST NOT IN ('COMPLETE');

-- [조민우의 수정안]
SELECT COUNT(*) FROM T_ORD_BIG
 WHERE ORD_ST = 'PENDING' OR ORD_ST = 'CANCEL' OR ORD_ST IS NULL;

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[처음 판 — NOT IN]
-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |      1 |        |      1 |00:00:00.04 |    2791 |
|   1 |  SORT AGGREGATE       |               |      1 |      1 |      1 |00:00:00.04 |    2791 |
|*  2 |   INDEX FAST FULL SCAN| IX_ORD_BIG_ST |      1 |    100K|    100K|00:00:00.04 |    2791 |
-----------------------------------------------------------------------------------------------
   2 - filter("ORD_ST"<>'COMPLETE')

[조민우의 수정안 — 긍정형 + OR IS NULL]
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.06 |    5826 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.06 |    5826 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |    100K|    100K|00:00:00.06 |    5826 |
-------------------------------------------------------------------------------------------
   2 - filter(("ORD_ST"='PENDING' OR "ORD_ST"='CANCEL' OR "ORD_ST" IS NULL))

문제

처음 판이 인덱스 전체(2,791블록)를 읽는 이유와, 수정안이 더 나빠져 테이블 전체(5,826블록)를 읽는 이유를 설명하시오.
같은 숫자를 내면서 필요한 범위만 읽도록 SQL을 고치시오. 고친 SQL이 처음 판과 언제나 같은 답을 내는지 NULL, 새 상태값, 중복(UNION ALL로 나눴다면)의 관점에서 따져 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 부정형(<> · NOT IN) 대신 남는 값 둘을 긍정형으로 — 두 값의 범위만 찾아간다
SELECT COUNT(*)
  FROM T_ORD_BIG
 WHERE ORD_ST IN ('PENDING', 'CANCEL');

-- (권고) 상태값이 이 셋뿐이고 NULL 이 없음을 데이터베이스가 보증하게 한다 — 지금 데이터로 위반 0 건
-- ALTER TABLE T_ORD_BIG MODIFY ORD_ST NOT NULL;
-- ALTER TABLE T_ORD_BIG ADD CONSTRAINT CK_ORD_BIG_ST CHECK (ORD_ST IN ('COMPLETE', 'PENDING', 'CANCEL'));

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |      1 |        |      1 |00:00:00.01 |     268 |
|   1 |  SORT AGGREGATE    |               |      1 |      1 |      1 |00:00:00.01 |     268 |
|   2 |   INLIST ITERATOR  |               |      1 |        |    100K|00:00:00.01 |     268 |
|*  3 |    INDEX RANGE SCAN| IX_ORD_BIG_ST |      2 |    100K|    100K|00:00:00.01 |     268 |
--------------------------------------------------------------------------------------------
   3 - access(("ORD_ST"='CANCEL' OR "ORD_ST"='PENDING'))

모범 답안 서술

처음 판의 2번 줄은 INDEX FAST FULL SCAN이고 조건은 filter("ORD_ST"<>'COMPLETE')이다. 인덱스는 ORD_ST 순으로 정렬되어 있지만, '<> COMPLETE'는 정렬 위에서 '여기부터 여기까지'로 지목할 수 없는 부정형이다. 그래서 인덱스 100만 항목을 전부 훑으며 COMPLETE 90만 건을 버린다(Buffers 2,791). 수정안은 OR의 한 갈래가 ORD_ST IS NULL이다. 단일 컬럼 인덱스에는 NULL인 행이 저장되지 않으므로 그 갈래는 인덱스로 답할 수 없고, 갈래 하나 때문에 OR 전체가 테이블 FULL이 됐다(2,791 → 5,826). 게다가 뜻도 바뀌었다. NOT IN ('COMPLETE')는 ORD_ST가 NULL인 행을 세지 않는다(NULL과 비교한 결과는 참이 아니다). 지금은 NULL이 0건이라 숫자만 같을 뿐이다. 남는 값 둘을 긍정형 ORD_ST IN ('PENDING', 'CANCEL')로 주면 INLIST ITERATOR가 두 값의 범위만 찾아간다(3번 줄 Starts 2). Buffers는 2,791 → 268로 줄고, NULL을 세지 않는 것도 처음 판과 같다. 대가는 남는 값을 사람이 알고 있어야 한다는 점이다. 새 상태값(예: RETURN)이 생기면 부정형은 저절로 세지만 긍정형은 조용히 빠뜨린다. 그래서 NOT NULL과 CHECK 제약(또는 코드 테이블)으로 상태값이 셋뿐임을 보증하고, 상태를 추가할 때 이 SQL을 점검 목록에 올린다. OR를 UNION ALL로 나눠 따로 센 뒤 더하는 것도 답이다. 그때는 갈래가 겹치지 않는지 본다. 상태값은 서로 배타적이라 중복이 없지만, 겹칠 수 있는 조건이라면 뒤 갈래에 LNNVL(앞 갈래 조건)을 붙여 두 번 세지 않게 한다.

해설

이 버전에서는 OR 자체가 나쁜 것이 아니다. 갈래마다 인덱스가 있으면 옵티마이저가 OR-Expansion(VW_ORE_)이나 BITMAP OR로 스스로 나눈다(교재 08화 OR 표). 막히는 것은 갈래 하나라도 인덱스로 못 찾을 때다.
히스토그램이 없으면 옵티마이저는 ORD_ST의 세 값이 1/3씩 있다고 보고 IN 판도 66만 건으로 추정해, 다시 FAST FULL SCAN을 고를 수 있다. SQL을 바꾼 뒤에는 실행계획과 E-Rows를 함께 확인한다.
근본 원인은 요구 사항일 수 있다. 10초마다 100만 행에 걸친 숫자를 다시 세야 하는지부터 따져 본다. 1분짜리 캐시나 상태가 바뀔 때 갱신하는 집계 행을 쓰면 268블록도 읽을 필요가 없다.
NOT IN (서브쿼리)는 더 위험하다. 서브쿼리 결과에 NULL이 하나라도 있으면 답이 0건이 되므로 NOT EXISTS와 답이 다르다(교재 08화 §7).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황