힌트까지 붙였는데 건수 한 줄이 테이블을 다 읽는다

기초SELECT한빛유통81 / 700

상황

월말 마감 대사 작업이다. 마감 담당 오과장의 검증 스크립트는 단계가 끝날 때마다 T_ORD_BIG의 전체 건수를 원장과 맞춰 본다. 단계마다 돌고, 재실행하고, 사람이 손으로 한 번 더 돌리다 보니 마감 하루에 이 문장이 200번 넘게 실행된다. 조민우는 "ORD_DT 인덱스가 있으니 인덱스만 세면 된다" 하고 INDEX_FFS 힌트를 붙였다. 그런데 실행계획은 그대로다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · PK 없음
컬럼 선언: ORD_SEQ NUMBER NOT NULL · CUS_ID VARCHAR2(40) NOT NULL · ORD_DT DATE (NULL 허용) · ORD_ST · ORD_AMT
인덱스는 IX_ORD_BIG_DT (ORD_DT) 하나뿐이다(LEAF 2,653 · 통계 최신)
ORD_DT의 실제 NULL은 0건이다(NUM_NULLS = 0). 적재 프로그램은 주문일을 늘 채운다

원본 SQL

sql

SELECT /*+ INDEX_FFS(o IX_ORD_BIG_DT) */ COUNT(*)
  FROM T_ORD_BIG o

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.04 |    5826 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.04 |    5826 |
|   2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.04 |    5826 |
----------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 -  SEL$1 / "O"@"SEL$1"
         U -  INDEX_FFS(o IX_ORD_BIG_DT)

문제

인덱스가 있고 힌트까지 붙였는데도 TABLE ACCESS FULL로 처리되는 이유를, B-tree 인덱스에 무엇이 들어 있고 무엇이 빠지는지를 근거로 설명하시오.
결과(전체 건수)의 뜻을 바꾸지 않고 인덱스만 읽어 건수를 세도록 고치시오. 그 조치를 하기 전에 무엇을 확인해야 하는지, 그 조치가 다른 프로그램에 어떤 영향을 주는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 뜻이 정말 같은가 — NULL 이 한 건이라도 있으면 인덱스로는 전체 건수를 셀 수 없다
SELECT COUNT(*) FROM T_ORD_BIG WHERE ORD_DT IS NULL;      -- 0

-- ② '주문일이 없는 주문은 없다' 는 업무 규칙을 제약으로 선언한다 (적재 담당 확인 뒤)
ALTER TABLE T_ORD_BIG MODIFY (ORD_DT NOT NULL);

-- ③ 문장은 그대로 — 힌트 없이 옵티마이저가 INDEX FAST FULL SCAN 을 고른다
SELECT COUNT(*)
  FROM T_ORD_BIG;

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

xplan

--------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |      1 |        |      1 |00:00:00.03 |    2671 |
|   1 |  SORT AGGREGATE       |               |      1 |      1 |      1 |00:00:00.03 |    2671 |
|   2 |   INDEX FAST FULL SCAN| IX_ORD_BIG_DT |      1 |   1000K|   1000K|00:00:00.03 |    2671 |
--------------------------------------------------------------------------------------------

모범 답안 서술

실행계획 2번 줄은 TABLE ACCESS FULL이고, 아래 Hint Report에는 U - INDEX_FFS(o IX_ORD_BIG_DT)로 힌트가 쓰이지 않았다(Unused)고 나온다. B-tree 인덱스는 키 컬럼이 전부 NULL인 행을 담지 않는다. ORD_DT는 NULL 허용으로 선언되어 있으므로, 옵티마이저 입장에서는 IX_ORD_BIG_DT의 엔트리 수가 테이블 행 수와 같다는 보장이 없다. 인덱스만 세면 COUNT()가 틀릴 수 있으므로 옵티마이저는 그 길을 아예 후보에 올리지 않는다. 힌트는 후보 가운데서 고르게 할 뿐(선택지 제한)이고, 답이 틀릴 수 있는 길을 새로 만들지는 못한다(06화 §7). 먼저 ORD_DT IS NULL이 0건인지 세어 확인하고, 업무 규칙대로 NOT NULL 제약을 선언한다. 그러면 인덱스가 모든 행을 담는다는 것이 증명되므로, 옵티마이저가 힌트 없이도 INDEX FAST FULL SCAN을 고른다. 테이블보다 작은 인덱스 세그먼트(LEAF 2,653)를 멀티블록으로 읽는 방식이다(Buffers 5,826 → 2,671). 마감 하루에 200번 실행되면 약 116만 → 53만 블록으로 준다. COUNT()를 COUNT(ORD_DT)나 WHERE ORD_DT IS NOT NULL로 바꾸면 지금은 숫자가 같아도 뜻이 다르다. NULL이 한 건이라도 들어오는 날에는 건수가 조용히 줄어든다. 제약은 데이터 모델에 관한 결정이다. 이미 NULL이 있으면 제약을 거는 순간 ORA-02296으로 실패하고, 제약을 건 뒤에는 NULL을 넣는 적재가 ORA-01400으로 막힌다. 그러므로 적재 담당에게 먼저 알린다.

해설

함정: SELECT COUNT(ORD_DT)나 WHERE ORD_DT IS NOT NULL을 쓰면 오늘은 같은 1,000,000이 나오고 FFS도 탄다. 그러나 그 결과는 '주문일이 있는 주문의 수'이고, 다른 질문에 대한 답이다. 08화에서 본 함정("지금 NULL이 없어서 같다")과 같다.
다른 정답: NOT NULL 컬럼에 인덱스가 있으면 된다. ORD_SEQ로 PK를 만들면(유일하고 NOT NULL이다) 리프가 더 작아서 FFS가 더 싸고, 다른 조회에도 쓰인다. 대가는 인덱스 하나만큼의 적재 비용이다(교재 14화). COUNT(1)과 COUNT(ORD_SEQ)도 COUNT(*)와 같은 실행계획이 나온다. 결과를 가르는 것은 세는 방법이 아니라 인덱스가 모든 행을 담느냐다.
제약의 대가: MODIFY … NOT NULL은 거는 순간 100만 행을 한 번 읽어 검증하고, 그동안 그 테이블의 DML을 막는다. 마감 시간을 피해서 건다.
NULL까지 인덱스에 담는 (ORD_DT, 0) 결합 인덱스 기법(교재 02화 §1)은 IS NULL 검색을 위한 것이다. 건수를 세는 데 쓰려면 실행계획으로 확인해야 한다. 비트맵 인덱스도 NULL을 담지만, 갱신이 잦은 주문 테이블에서는 잠금 단위가 커서 쓰지 않는다.
근본 대책: 단계마다 100만 건을 처음부터 다시 세야 하는지부터 따져 본다. 적재 단계가 넣은 건수(SQL%ROWCOUNT)를 로그로 남겨 더해 가면, 전체를 다시 세는 일은 마감 끝에 한 번이면 된다. 이것은 요구 재협상의 주제다(교재 09화 §13).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황