IN·BETWEEN·LIKE 조건의 인덱스 탐색: 반복 Probe와 연속 범위

핵심 요약

IN, BETWEEN, LIKE는 모두 검색 조건이지만 B-tree Index에서 만드는 탐색 형태가 다릅니다.

IN (값1, 값2, 값3)
  → 여러 이산 Key·Range
  → 값별 하위 Operation 반복 가능
  → INLIST ITERATOR 후보

BETWEEN 하한 AND 상한
  → 하한·상한을 모두 포함
  → 하나의 연속 Range

LIKE 'ABC%'
  → 첫 Wildcard 전의 고정 Prefix ABC
  → Prefix Range 후보

LIKE '%ABC'
  → 시작 Key를 특정하기 어려움
  → 좁은 B-tree Range에 불리

성능 판단은 연산자 이름이 아니라 다음 구조를 기준으로 합니다.

1. 선행 Equality Prefix가 어디까지 고정되는가
2. 첫 Range Predicate가 어느 Index Column에서 시작되는가
3. IN 값별 Probe 수와 각 Range의 Leaf 작업량은 얼마인가
4. Index가 반환한 후보 ROWID와 Table Access는 얼마인가
5. 결과 의미가 NULL·날짜·문자 비교 규칙까지 동일한가

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 스캔 효율화 범위에서 IN·BETWEEN·LIKE 조건의 탐색 구간, 반복 Probe와 실제 실행 통계를 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음 내용을 설명할 수 있어야 합니다.

• Literal IN-List가 만드는 여러 이산 Range를 설명한다.
• INLIST ITERATOR의 반복 구조와 Starts를 해석한다.
• IN (subquery)가 INLIST가 아니라 Semijoin 등으로 변환될 수 있음을 설명한다.
• Partition Key의 IN 조건과 KEY(INLIST)를 설명한다.
• IN·NOT IN의 NULL 의미를 설명한다.
• BETWEEN의 포함 경계와 날짜·Timestamp 반개구간을 설명한다.
• BETWEEN을 IN으로 바꿀 수 있는 제한적인 조건을 판단한다.
• LIKE의 고정 Prefix, _, %, ESCAPE를 설명한다.
• Leading Wildcard와 표현식 LIKE의 Index 제약을 설명한다.
• Optional Predicate·OR Expansion과 Dynamic SQL 대안을 설명한다.
• Starts·A-Rows·Buffers·Reads로 반복 Probe와 연속 Scan을 비교한다.

1. Index Key 순서 위에 조건을 표시한다

다음 Index를 가정합니다.

CREATE INDEX orders_x1
ON orders(tenant_id, order_status, order_date, order_id);

정렬 순서입니다.

tenant_id
  → order_status
    → order_date
      → order_id·ROWID

다음 조건은 하나의 선행 Prefix와 날짜 Range를 만듭니다.

WHERE tenant_id    = :tenant_id
AND   order_status = 'READY'
AND   order_date  >= :from_date
AND   order_date  <  :to_date

(tenant_id,'READY') Prefix 고정
  → from_date에서 시작
  → to_date 전 종료

order_status IN (...)이면 상태별로 분리된 날짜 Range가 생길 수 있습니다.

(tenant_id,'ERROR',from_date) ~ (tenant_id,'ERROR',to_date)
(tenant_id,'HOLD' ,from_date) ~ (tenant_id,'HOLD' ,to_date)

이때 총비용은 각 Range의 수직 탐색·Leaf Scan·Table Access를 모두 합한 값입니다.

2. Literal IN-List와 INLIST ITERATOR

2.1 SQL 의미

WHERE order_status IN ('READY','ERROR','HOLD')

논리적으로 다음과 같은 Membership 조건입니다.

order_status = 'READY'
OR order_status = 'ERROR'
OR order_status = 'HOLD'

동일 값이 목록에 반복돼도 결과 집합에는 중복 효과가 없습니다. 다만 실행계획의 실제 Probe 수는 Optimizer 처리와 다른 Iterator 구조를 포함하므로 목록 길이와 Starts가 항상 같다고 단정하지 않습니다.

2.2 INLIST ITERATOR

Index로 IN 값을 구현할 때 다음 Plan이 나타날 수 있습니다.

INLIST ITERATOR
  TABLE ACCESS BY INDEX ROWID BATCHED ORDERS
    INDEX RANGE SCAN ORDERS_X1

INLIST ITERATOR는 IN 목록의 값마다 바로 아래 Operation을 반복합니다.

ERROR → Index Range Scan
HOLD  → Index Range Scan
READY → Index Range Scan

확인할 비용입니다.

총 Index 비용
  ≈ 각 Probe의 Root·Branch 탐색
   + Leaf Scan
   + Table ROWID Access

2.3 INLIST가 항상 선택되지는 않는다

다음 조건에 따라 다른 Plan이 더 저렴할 수 있습니다.

• IN 값 수
• 값별 선택도·데이터 편중
• IN Column의 Index 위치
• 선행 조건이 이미 만든 범위 크기
• 반복 Probe와 Table Access 총비용
• Full Scan·다른 복합 Index 비용
• Query Transformation
• Partition Pruning

예를 들어 한 고객의 전체 주문이 20행이라면 고객 Prefix를 한 번 읽고 Status를 Filter하는 편이 상태별 반복 Probe보다 저렴할 수 있습니다.

3. IN Subquery는 Literal INLIST와 다르다

다음 조건은 Literal 목록이 아니라 Subquery Membership입니다.

WHERE customer_id IN (
  SELECT customer_id
  FROM   vip_customer
)

Optimizer는 Subquery Unnesting과 Semijoin 등으로 변환할 수 있습니다.

Literal IN-List
  → INLIST ITERATOR 후보

IN (Subquery)
  → FILTER Subquery
  → Nested Loops Semijoin
  → Hash Semijoin
  → Merge Semijoin
  → 다른 Transformation 후보

따라서 SQL Text에 IN이 있다는 이유만으로 INLIST ITERATOR를 예상하지 않습니다.

4. IN과 Partition Pruning

IN Column이 Partition Key라면 Plan에 다음 표현이 나타날 수 있습니다.

PARTITION RANGE INLIST
  TABLE ACCESS FULL

Index와 Partition Key에 모두 사용되면 다음과 같은 구조가 가능할 수 있습니다.

INLIST ITERATOR
  PARTITION RANGE ITERATOR
    TABLE ACCESS BY LOCAL INDEX ROWID
      INDEX RANGE SCAN

PSTART·PSTOP의 KEY(INLIST)는 IN 값으로 Partition 또는 Index 경계를 결정한다는 의미입니다.

IN 조건 존재
  ≠ 반드시 INLIST ITERATOR

Partition Key만 IN
  → PARTITION ... INLIST
  → 각 Partition Full Scan 가능

Plan Tree 전체에서 Iterator 위치와 Starts를 확인합니다.

5. 복합 인덱스에서 IN의 위치

5.1 첫 Range 앞의 IN

-- Index: (tenant_id, order_status, order_date)
WHERE tenant_id    = :tenant_id
AND   order_status IN ('ERROR','HOLD')
AND   order_date  >= :from_date
AND   order_date  <  :to_date

상태별로 좁은 날짜 Range를 만들 수 있습니다.

ERROR 날짜 Range
HOLD  날짜 Range

IN 목록이 작고 각 날짜 Range가 좁다면 효율적일 수 있습니다.

5.2 첫 Range 뒤의 IN

-- Index: (tenant_id, order_date, order_status)
WHERE tenant_id    = :tenant_id
AND   order_date  >= :from_date
AND   order_date  <  :to_date
AND   order_status IN ('ERROR','HOLD')

날짜가 첫 Range이므로 Status는 해당 날짜 범위 안에서 추가 Filter 성격이 커질 수 있습니다.

tenant_id·날짜 Leaf Range Scan
  → 각 Entry의 Status 확인

order_status가 access에 일부 표시되더라도 중간 날짜 Key 구간의 Leaf Scan이 모두 제거됐다고 단정하지 않습니다. Index Buffers·Reads로 확인합니다.

6. IN·NOT IN과 NULL

6.1 IN 목록의 NULL

WHERE order_status IN ('READY', NULL)

NULL과의 일반 비교는 TRUE가 아니라 UNKNOWN입니다. 이 조건은 NULL Row를 선택하는 IS NULL과 같지 않습니다.

WHERE order_status = 'READY'
OR    order_status IS NULL

NULL을 포함하려면 명시적으로 작성합니다.

6.2 NOT IN의 NULL 함정

WHERE department_id NOT IN (10,20,NULL)

NOT IN은 != ALL 의미이며 목록에 NULL이 있으면 비교가 UNKNOWN을 포함합니다. Oracle SQL Language Reference의 예시처럼 조건 결과가 FALSE 또는 UNKNOWN이 되어 Row가 반환되지 않을 수 있습니다.

Subquery의 NOT IN에서도 반환 집합에 NULL이 존재하는지 반드시 확인합니다.

6.3 Collation

Character IN 조건은 Collation의 영향을 받을 수 있습니다. Literal·Column·Bind의 Data Type과 Collation을 일치시켜 결과와 Cardinality를 검증합니다.

7. 긴 IN-List의 운영 판단

Oracle 26ai는 단일 Expression IN 목록에 많은 값을 허용하지만, 문법상 허용된다는 사실과 효율적인 실행은 다릅니다.

긴 목록의 문제입니다.

• 많은 Range Probe와 Starts
• Parse·Bind 전달 비용
• 값별 편중
• 큰 Table 후보·Table Access
• SQL Text·Cursor 관리
• Application에서 목록 생성 오류
• Partition·Join 대안과의 비용 차이

대안 후보입니다.

• Temporary Table 또는 Staging Table에 값 저장 후 Join
• Collection·Table Function 기반 Join
• 업무 Key Table과 Semijoin
• Batch 단위 분할
• 조건 조합에 맞는 복합 Index
• Full·Partition Scan

선택은 실제 결과·작업량·운영 안정성으로 결정합니다.

8. BETWEEN은 양쪽을 포함하는 연속 Range다

8.1 포함 의미

WHERE grade BETWEEN 1 AND 3

다음과 같습니다.

WHERE grade >= 1
AND   grade <= 3

Start
  → 1 이상 첫 Key

Stop
  → 3의 모든 포함 대상 Entry 뒤 경계

8.2 날짜·Timestamp

WHERE order_date BETWEEN DATE '2026-07-01'
                     AND DATE '2026-07-31'

상한은 2026-07-31 00:00:00이므로 7월 31일 낮 시간은 포함되지 않습니다.

월 전체는 반개구간이 안전합니다.

WHERE order_date >= DATE '2026-07-01'
AND   order_date <  DATE '2026-08-01'

Timestamp Fractional Second에도 안전하며 마지막 순간을 임의로 빼지 않습니다.

8.3 하한이 상한보다 큰 경우

일반적인 x BETWEEN high AND low는 x>=high AND x<=low를 동시에 만족해야 하므로 보통 TRUE가 될 수 없습니다. Application에서 입력 경계를 정규화하거나 유효성을 검증합니다.

9. BETWEEN을 IN으로 바꾸는 제한적 조건

다음은 Domain이 정수 1·2·3으로 제한될 때 결과가 같을 수 있습니다.

grade BETWEEN 1 AND 3

grade IN (1,2,3)

차이입니다.

BETWEEN
  → 하나의 연속 Leaf Range

IN
  → 값별 이산 Probe

재작성 조건입니다.

• Domain이 유한한 이산값
• 경계·NULL·Data Type 결과가 완전히 동일
• IN 목록 유지가 안전
• 반복 Probe 총비용이 연속 Scan보다 작음

재작성하면 안 되는 사례입니다.

• NUMBER에 1.5·2.5 존재
• DATE·TIMESTAMP Range
• 문자 Collation 사이에 다른 값 존재
• 목록이 길거나 업무 Domain 변경 가능
• 기존 연속 Range가 이미 짧음

이는 일반 공식이 아니라 실측 기반의 제한적 후보입니다.

10. LIKE와 고정 Prefix

10.1 Wildcard 의미

%
  → 0개 이상의 문자

_
  → 정확히 1개의 문자

LIKE는 기본적으로 대소문자를 구분하며 Character 비교·Collation 설정의 영향을 받을 수 있습니다.

10.2 Prefix Pattern

WHERE customer_name LIKE 'KIM%'

첫 Wildcard 전의 KIM이 고정됩니다.

KIM Prefix 첫 Key
  → KIM Prefix Range Scan
  → Pattern 최종 확인

WHERE customer_name LIKE 'KIM_'

KIM Prefix Range는 사용할 수 있지만 _가 정확히 한 문자라는 조건은 추가 평가가 필요할 수 있습니다.

10.3 Leading Wildcard

LIKE '%KIM'
LIKE '%KIM%'
LIKE '_KIM%'

첫 문자부터 고정되지 않아 일반 B-tree의 좁은 시작점을 만들기 어렵습니다.

Oracle SQL Analysis Report도 Leading Wildcard Predicate가 Index Range Scan Key 사용을 막을 수 있다고 안내할 수 있습니다.

대안입니다.

• Oracle Text
• 업무에 맞는 검색 전용 구조
• 제한적인 Reverse Expression FBI
• Full Scan·Index Full Scan

10.4 ESCAPE

문자 %나 _ 자체를 찾으려면 Escape 문자를 지정합니다.

WHERE promotion_text LIKE '50\% OFF' ESCAPE '\'

Escape 의미와 Application 문자열 처리를 함께 검증합니다.

11. 표현식 LIKE와 Function-Based Index

WHERE UPPER(customer_name) LIKE 'KIM%'

일반 customer_name Index와 저장 표현식이 다릅니다.

CREATE INDEX customer_upper_name_ix
ON customer(UPPER(customer_name));

대소문자 무시 검색을 일관되게 수행한다면 Query와 동일한 Expression Tree의 FBI를 검토합니다.

확인 항목입니다.

• UPPER·TRIM 등 표현식 일치
• Data Type·Collation·NLS
• NULL
• Index Statistics
• DML 유지비
• 실제 access Predicate

12. Optional Predicate와 OR Expansion

다음 SQL은 조건 조립은 편리하지만 모든 Bind 조합에서 좁은 Range를 보장하지 않습니다.

WHERE (:region IS NULL OR region = :region)
AND   (:status IS NULL OR order_status = :status)

Optimizer는 일부 OR 조건을 여러 Branch로 확장하는 OR Expansion을 고려할 수 있지만, 항상 적용되는 것은 아닙니다.

문제점입니다.

• NULL·Non-NULL Bind별 Selectivity 차이
• OR·NVL 표현식
• Cardinality 추정
• 공용 Plan과 Child Cursor
• Index 조합
• 모든 조건 생략 시 대량 Scan

대안입니다.

• 조건 조합별 Static SQL
• Bind를 유지하는 안전한 Dynamic SQL
• 자주 사용하는 조합용 복합 Index
• 대표·극단 Bind별 Plan 검증

사용자 입력을 SQL Text에 직접 결합하지 않습니다.

13. 실행계획과 실제 통계

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       order_date
FROM   orders
WHERE  tenant_id    = :tenant_id
AND    order_status IN ('ERROR','HOLD')
AND    order_date  >= :from_date
AND    order_date  <  :to_date;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_no,
    'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
  )
);

확인 순서입니다.

1. Literal IN인지 Subquery IN인지 구분
2. INLIST ITERATOR·Partition INLIST 위치 확인
3. 하위 Operation의 Starts 확인
4. access·filter Predicate 확인
5. Index A-Rows·Buffers·Reads 확인
6. Table 후보·최종 A-Rows·Buffers 확인
7. IN 값 수·값별 편중·날짜 범위 변경
8. Full Scan·다른 Index·Join 대안 비교
9. 동일 Fetch 범위·Bind Type·동시성으로 반복 측정

13.1 Starts 해석

Starts 20
Buffers 200

평균 Buffers / Start
  = 10

하지만 Starts=IN 값 수라고 항상 고정하지 않습니다.

• Duplicate Literal 정리
• Partition Iterator
• Nested Loops
• 다른 Transformation
• 실행 중 중단

등이 Plan에 결합될 수 있으므로 전체 Tree를 봅니다.

13.2 A-Rows 주의

Index A-Rows는 상위로 반환한 Row 수입니다. Index Filter에서 제거된 내부 Entry 전체를 뜻하지 않습니다.

Index A-Rows 100
Index Buffers 10,000

→ 넓은 Leaf Scan 뒤 Filter 가능성

14. 적용 판단 절차

1. 조건의 결과 의미·NULL·날짜·Collation을 확정한다.
2. Index Column 순서에 Equality·IN·Range·LIKE를 표시한다.
3. Literal IN·Subquery IN·Partition IN을 구분한다.
4. 예상 Range 수와 첫 Range 위치를 계산한다.
5. BETWEEN·LIKE의 경계와 Escape를 검증한다.
6. Optional Predicate Transformation 가능성을 확인한다.
7. 실제 Starts·A-Rows·Buffers·Reads를 수집한다.
8. 반복 Probe·연속 Range·Join·Full Scan을 비교한다.
9. 동일 결과·Fetch·Bind에서 대표·극단값을 반복 측정한다.
10. SQL 가독성·유지보수·DML·보안을 포함해 적용한다.

자주 혼동하는 판단