매장 인덱스를 두고도 매장별 누적 주문 순위가 주문 100만 건을 다 읽는다

중급DDL버거43 / 700

상황

버거원 본사 대시보드의 「매장 순위」는 오픈 이후 누적 주문 수로 매장 순위를 매기고, 맨 아래에 '매장 미확인' 줄을 따로 보여 준다. 대시보드는 10분마다 새로 고친다. 조민우는 "매장 인덱스가 있는데 왜 테이블을 읽냐"며 WHERE SHOP_NO IS NOT NULL을 붙였다. 인덱스만 읽게 되어 빨라졌지만 '매장 미확인' 줄이 사라졌다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록(전체 스캔 22,239) · ORD_NO NUMBER(PK)
ORD_DT VARCHAR2(8) · ORD_HMS VARCHAR2(6) · SHOP_NO VARCHAR2(10): 세 컬럼 모두 NOT NULL 제약이 없다(적재 스크립트의 DDL 그대로)
ORD_DT와 ORD_HMS의 실제 NULL은 0건이다. SHOP_NO는 2013-03-02 ~ 03-05 키오스크 장애 때 매장 번호 없이 들어온 주문 1,204건이 NULL이다 (문제 설정: 교재의 ORD에는 SHOP_NO가 빈 행이 없다)
인덱스 ORD_PK (ORD_NO): 리프 약 2,090 · ORD_X01 (ORD_DT, ORD_HMS): 리프 3,774 · IX_ORD_SHOP_DT (SHOP_NO, ORD_DT): 리프 3,310 · 통계 최신

원본 SQL

sql

-- [원본]
SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD
 GROUP BY SHOP_NO
 ORDER BY ord_cnt DESC;

-- [조민우의 수정안]
SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD
 WHERE SHOP_NO IS NOT NULL
 GROUP BY SHOP_NO
 ORDER BY ord_cnt DESC;

실행계획 학습용 예시

xplan

[원본]
--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |      1 |        |    201 |00:00:00.21 |   22239 |
|   1 |  SORT ORDER BY         |                |      1 |    200 |    201 |00:00:00.21 |   22239 |
|   2 |   HASH GROUP BY        |                |      1 |    200 |    201 |00:00:00.21 |   22239 |
|   3 |    TABLE ACCESS FULL   | ORD            |      1 |   1000K|   1000K|00:00:00.12 |   22239 |
--------------------------------------------------------------------------------------------------

[조민우의 수정안]
--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |      1 |        |    200 |00:00:00.09 |    3318 |
|   1 |  SORT ORDER BY         |                |      1 |    200 |    200 |00:00:00.09 |    3318 |
|   2 |   HASH GROUP BY        |                |      1 |    200 |    200 |00:00:00.09 |    3318 |
|*  3 |    INDEX FAST FULL SCAN| IX_ORD_SHOP_DT |      1 |    999K|    999K|00:00:00.05 |    3318 |
--------------------------------------------------------------------------------------------------
   3 - filter("SHOP_NO" IS NOT NULL)

문제

IX_ORD_SHOP_DT가 있는데도 원본이 테이블 전체를 읽는 이유를 설명하시오. ORD_DT의 실제 NULL이 0건인데도 그렇게 되는 이유도 설명하시오.
조민우의 수정안이 틀린 이유를 설명하시오.
'매장 미확인' 줄을 지키면서 인덱스만 읽도록 고치시오. 인덱스를 새로 만들지 않는 방법을 먼저 찾으시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 정말 0건인가 (ORD_DT 는 업무상 비어 있을 수 없는 컬럼이다)
SELECT COUNT(*) FROM ORD WHERE ORD_DT IS NULL;          -- 0

-- ② 제약으로 약속한다 → IX_ORD_SHOP_DT (SHOP_NO, ORD_DT) 의 키 하나는 늘 NULL 이 아니다
ALTER TABLE ORD MODIFY (ORD_DT NOT NULL);

-- ③ SQL 은 원본 그대로 (매장 미확인 줄이 남는다)
SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD
 GROUP BY SHOP_NO
 ORDER BY ord_cnt DESC;

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

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |      1 |        |    201 |00:00:00.09 |    3318 |
|   1 |  SORT ORDER BY         |                |      1 |    200 |    201 |00:00:00.09 |    3318 |
|   2 |   HASH GROUP BY        |                |      1 |    200 |    201 |00:00:00.09 |    3318 |
|   3 |    INDEX FAST FULL SCAN| IX_ORD_SHOP_DT |      1 |   1000K|   1000K|00:00:00.05 |    3318 |
--------------------------------------------------------------------------------------------------

모범 답안 서술

결합 인덱스는 키 컬럼 중 하나라도 NULL이 아니면 그 행을 담지만, 키가 모두 NULL인 행은 담지 않는다. IX_ORD_SHOP_DT의 두 컬럼 SHOP_NO와 ORD_DT는 둘 다 NOT NULL 제약이 없다. 그래서 옵티마이저 입장에서는 'SHOP_NO도 ORD_DT도 NULL인 주문'이 인덱스에서 빠져 있을 수 있고, 인덱스만 세면 COUNT(*)가 틀릴 수 있다. ORD_DT의 실제 NULL이 0건이어도 마찬가지다. 옵티마이저는 지금의 데이터가 아니라 제약을 보고 계획을 세운다. 그래서 원본은 테이블 전체를 읽는다(Buffers 22,239). 조민우의 수정안은 SHOP_NO IS NOT NULL로 'SHOP_NO에 값이 있는 행'만 세게 해서 인덱스를 쓸 수 있게 했다. 대신 질문이 바뀌었다. 매장 미확인 1,204건이 집계에서 빠져 '매장 미확인' 줄이 사라졌다. 제약은 ORD_DT에 건다. ORD_DT는 실제 NULL이 0건이라 NOT NULL을 걸 수 있고, SHOP_NO는 키오스크 장애 주문 때문에 진짜 NULL이 있어 걸 수 없다. ALTER TABLE ORD MODIFY (ORD_DT NOT NULL)을 걸면 IX_ORD_SHOP_DT의 키 하나는 늘 NULL이 아니므로 모든 행이 인덱스에 있다는 것이 보장된다. SQL은 원본 그대로 두어도 옵티마이저가 테이블보다 작은 인덱스를 INDEX FAST FULL SCAN으로 읽고, SHOP_NO가 NULL인 행도 한 그룹으로 센다(Buffers 22,239 → 3,318). 인덱스는 하나도 늘지 않는다. 제약을 걸기 전에 ORD_DT IS NULL이 0건인지 세고, POS와 키오스크 적재 프로그램이 ORD_DT를 늘 채우는지 확인한다. NULL이 한 건이라도 있으면 제약은 ORA-02296으로 실패하고, 제약을 건 뒤 NULL을 넣는 적재는 ORA-01400으로 막힌다. MODIFY는 100만 행을 읽어 검증하는 동안 DML을 잠깐 막으므로 영업 시간을 피해서 건다.

해설

다른 정답: (SHOP_NO, 0)이나 (SHOP_NO, ORD_NO) 인덱스를 새로 만들어도 된다. 대가는 100만 행짜리 인덱스가 하나 더 느는 것이다(주문을 넣을 때마다 고친다). 제약 하나로 기존 인덱스를 살리는 쪽이 싸다.
ORD_DT에 건 NOT NULL은 ORD_X01도 살린다. (ORD_DT, ORD_HMS)가 모든 행을 담게 되어 전체 건수나 날짜순 정렬도 인덱스로 할 수 있다(교재 09화 §7의 조건 ②).
비슷한 문제인 ACC-006은 세는 컬럼 자체에 NOT NULL을 걸었다. 이 문제는 세는 컬럼(SHOP_NO)에 진짜 NULL이 있어서, 같은 인덱스의 다른 컬럼에 제약을 거는 것이 답이다.
근본 대책: 10분마다 2년 치를 다시 셀 일인지 따져 본다. 일 마감 때 매장별 누계를 집계 테이블에 더해 가면 200행만 읽으면 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황