스캔 범위와 필터 조건

#핵심 요약

결합 인덱스는 선두 컬럼부터 연속된 등치(=) 조건이 있을 때 스캔 범위를 가장 잘 줄임
실습 요약: "선두 컬럼이 =이면 모이고, BETWEEN이면 흩어진다"

범위 조건이 맨 뒤에 있으면 조건을 만족하는 레코드가 한 구간에 모여 불필요한 스캔이 거의 없음
선두 컬럼이 범위 조건이면 뒤 컬럼이 =라도 사이사이의 불필요한 레코드를 함께 읽음

중간 컬럼 조건이 빠지면 그 뒤 컬럼 조건은 스캔 범위를 줄이지 못하고 필터로만 쓰임

선두 컬럼의 BETWEEN을 IN-List로 바꾸면 "넓게 한 번 읽기"가 "짧게 여러 번 찾기"가 됨
값이 너무 많으면 수직 탐색이 반복되어 오히려 불리함

결과 건수(Rows)에 비해 읽은 블록(cr)이 지나치게 많으면 인덱스를 탔어도 넓게 읽고 대부분 버리는 비효율임

#개념과 동작 원리

실습 예(인덱스 c1 + c2, 값 A1A3, B1B4, C1~C3, D1):

실습의 BETWEEN 비효율 설명: BETWEEN '1' AND '3'에서 시작값 1과 끝값 3 구간은 조건을 만족하는 지점부터 읽거나 벗어나기 직전에 멈출 수 있지만, 중간값 2 구간은 거의 전체를 읽어야 함

실행계획의 Predicate Information에서 스캔 범위를 정하는 조건은 access, 읽은 뒤 거르는 조건은 filter로 표시됨(실행계획 읽기)
퀴즈 해설은 범위 조건 뒤의 컬럼 조건이 "대개 filter가 된다"고 적었음

#주요 용어와 구성 요소

#필수 명령어 또는 SQL

#실습 1: 등치 조건과 범위 조건

CREATE TABLE idx_demo (c1 VARCHAR2(1), c2 NUMBER, txt VARCHAR2(10));
-- A1~A3, B1~B4, C1~C3, D1 11행 입력 (INSERT 11개 생략)
CREATE INDEX idx_demo_c1_c2 ON idx_demo (c1, c2);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'IDX_DEMO', CASCADE => TRUE);

SELECT * FROM idx_demo WHERE c1 = 'B' AND c2 = 3;
SELECT * FROM idx_demo WHERE c1 BETWEEN 'A' AND 'C' AND c2 BETWEEN 2 AND 3;

결과 해석:

실습 결과: 두 SQL 모두 인덱스 Buffers 2였고 테이블 포함 합계는 3과 4였음
데이터가 11건뿐이라 블록 수 차이로는 드러나지 않음

#실습 2: BETWEEN을 IN-List로

CREATE TABLE mcustsum AS
SELECT ROWNUM custno,
       '2025' || LPAD(CEIL(ROWNUM / 100000), 2, '0') salemm,
       DECODE(MOD(ROWNUM, 12), 1, 'A', 'B') salegb,
       ROUND(DBMS_RANDOM.VALUE(1000, 100000), -2) saleamt
  FROM dual
CONNECT BY LEVEL <= 1200000;

CREATE INDEX mcustsum_idx01 ON mcustsum (salemm, salegb);

SELECT COUNT(*) FROM mcustsum t
 WHERE salegb = 'A' AND salemm BETWEEN '202501' AND '202512';

결과 해석: 인덱스를 바꿀 수 있으면 등치 조건 컬럼 salegb를 앞에 두는 것이 가장 좋고, 인덱스를 바꿀 수 없으면 선두 컬럼의 BETWEEN을 IN-List로 바꿔 12번의 짧은 범위 스캔으로 만듦
실습에서는 IN-List 값을 LISTAGG(DISTINCT...)로 만들어 붙였음

#실습 3: 중간 컬럼이 빠진 결합 인덱스

CREATE INDEX idx_term_multi ON term_multi (c1, c2, c3, c4);

SELECT * FROM term_multi WHERE c1 = '성' AND c2 = '능' AND c4 = '선';

결과 해석:

실습 답은 빠진 c3를 건너뛴 인덱스 (c1, c2, c4)를 새로 만드는 것이었음
16건 실습이라 실습 기록상 두 인덱스 모두 Buffers 4로 차이가 보이지 않음

#실습 4: 중간 글자 검색과 Oracle Text

term LIKE '성능_선'은 앞 두 글자로 범위를 잡은 뒤 나머지를 거름
실습에서는 이를 Oracle Text 인덱스로 바꾸는 답을 적었음

CREATE INDEX idx_term_text ON term_dict (term) INDEXTYPE IS ctxsys.context;

SELECT term FROM term_dict WHERE CONTAINS(term, '성능_선') > 0;

결과 해석:

실습 결과: DOMAIN INDEX로 바뀌었지만 Buffers는 2에서 10으로 오히려 늘었음(16건 실습)
실습 환경의 Oracle Text 설치 여부와 동기화 설정은 기재 없음
인덱스 생성 권한과 동기화 방식은 아래 조건으로 구분함

#IN 조건은 언제 액세스 조건이 되는가

실행계획의 카디널리티는 각 연산이 내보내는 행 수임
컬럼의 서로 다른 값 개수인 NDV와 구분함
값이 다양한 컬럼의 = 조건은 한 번에 좁은 범위로 줄여 주므로 보통 인덱스 앞에 둠
DEPTNO, JOB처럼 값 종류가 적은 컬럼도 IN 값 개수가 적으면 INLIST ITERATOR로 인덱스를 여러 번 짧게 찾아 읽는 블록을 줄일 수 있음

실습 기록(emp_big 14만 행, deptno = 20 AND job IN ('CLERK','SALESMAN','ANALYST')): 인덱스 (deptno, job)과 (job, deptno) 모두 INLIST ITERATOR 아래 INDEX RANGE SCAN Starts 3, Buffers 84였고, 두 인덱스를 함께 두면 옵티마이저는 (deptno, job)을 골랐음

인덱스 선두 컬럼에 IN이 오면 그 값마다 수직 탐색을 반복하는 INLIST ITERATOR만이 액세스 조건으로 쓰는 방법이고, 보통 그것이 효율적임
후행 컬럼의 IN은 다름: 선행 컬럼 조건만으로 이미 리프 블록이 충분히 좁혀지면, 후행 IN을 액세스 조건으로 만들어 여러 번 수직 탐색하는 것보다 필터로 한 번 훑는 편이 블록을 덜 읽음

-- 인덱스: 고객별가입상품_상품X (상품ID, 고객번호), 고객별가입상품_고객X (고객번호, 상품ID)
SELECT /*+ index(a 고객별가입상품_상품X) */ *
  FROM 고객별가입상품 a
 WHERE 고객번호 = 1234
   AND 상품ID IN ('NH00037', 'NH00041', 'NH00050');      -- 두 컬럼 모두 액세스 조건

SELECT /*+ num_index_keys(a 고객별가입상품_상품X 1) */ *
  FROM 고객별가입상품 a
 WHERE 고객번호 = 1234
   AND 상품ID IN ('NH00037', 'NH00041', 'NH00050');      -- 첫 컬럼만 액세스, 둘째는 필터

SELECT *
  FROM 고객별가입상품 a
 WHERE 고객번호 = 1234
   AND 상품ID || '' IN ('NH00037', 'NH00041', 'NH00050'); -- 컬럼 가공으로 같은 효과

결과 해석:

NUM_INDEX_KEYS(테이블 인덱스 n)은 인덱스 앞 n개 컬럼까지만 액세스 조건으로 쓰게 하는 힌트임

선행 컬럼이 유일하거나 매우 좁게 걸러 주면 후행 IN은 필터로 둬도 됨
선행 컬럼이 많은 행을 남기면 후행 IN을 INLIST ITERATOR로 액세스 조건화하는 것이 도움이 됨

"IN 조건은 항상 액세스 조건이 유리하다"는 일반화는 인덱스 컬럼 순서와 데이터 분포에 따라 틀릴 수 있음

#BETWEEN과 LIKE의 스캔 범위

CREATE INDEX emp_big_idx1 ON emp_big (hire_ym, grade);   -- hire_ym '201812'~'201912', grade A 10% / B 90%

SELECT COUNT(*) FROM emp_big
 WHERE hire_ym LIKE '2019%' AND grade = 'B';

SELECT /*+ index_ss(emp_big emp_big_idx1) */ COUNT(*) FROM emp_big
 WHERE hire_ym BETWEEN '201901' AND '201912' AND grade = 'B';

결과 해석:

실습 기록: 튜닝 전 1826, 튜닝 뒤 1522(주석에 적힌 값으로, 무엇의 값인지는 기재 없음)
LIKE '2019%'는 '2019'로 시작하는 모든 값을 포함하는 범위라 BETWEEN '201901' AND '201912'보다 넓게 읽을 수 있음

#범위 조건을 남용할 때의 비효율

인덱스가 (회사코드, 지역코드, 상품코드) 순일 때, 항상 들어오는 지역코드를 선택 조건처럼 LIKE :reg || '%'로 쓰면, 값이 정확히 '05'로 바인딩되어도 LIKE는 범위 조건이라 그 뒤의 상품코드 = 조건이 액세스 조건이 되지 못하고 필터로 처리됨
실습은 (seller_code, region_code, product_name) 인덱스를 둔 50만 건 coupang_product 테이블로 같은 상황을 만들었음
선택 조건이 아닌 값은 =로 쓰고, 정말 선택적인 조건이면 경우마다 SQL을 나누는 방법을 검토함

연습 문제: 인덱스 emp_big(deptno, job)에서 deptno BETWEEN 10 AND 30 AND job = 'CLERK'가 INDEX FAST FULL SCAN(실습 Buffers 437)으로 처리될 때, 인덱스를 바꾸지 않고 SQL만 고쳐 읽는 블록을 줄여 봄(예: deptno IN (10, 20, 30))

#구문과 옵션 설명

CONTEXT 인덱스 생성에는 해당 텍스트 컬럼에 인덱스를 생성할 수 있는 권한이 필요하며 CTXAPP 역할 자체는 생성의 필수 조건이 아님
기본 SYNC (MANUAL)은 DML 뒤 수동 동기화가 필요한 방식임
SYNC (ON COMMIT)과 SYNC (EVERY ...)는 반영 시점이 다르며 주기 방식은 CREATE JOB 권한도 확인함

CONTAINS의 _는 검색어의 한 문자 자리를 확장하지만 문서의 토큰화, 불용어 설정이 개입함
전체 문자열을 비교하는 LIKE와 결과가 다를 수 있음

#사용 조건과 주의사항

NUM_INDEX_KEYS의 지원 구문은 확인 불가이므로 기존 실습의 힌트와 관찰을 지원되는 일반 설정으로 확대하지 않음
SQL 변경 전후에는 결과 집합의 동등성과 실제 계획, 행 수, Buffers를 함께 비교함
기존 1826, 1522는 측정 항목이 확인 불가이므로 실행 시간이나 I/O 단위를 새로 붙이지 않음