인덱스를 활용하는 조건식: 컬럼 가공·묵시적 형변환·Sargability

핵심 요약

인덱스가 정렬된 Key 공간에서 필요한 구간만 읽으려면 조건식으로 검색 시작점과 종료점(Start·Stop Key)을 만들 수 있어야 합니다.

Sargable 조건
  → 저장된 Index Key를 기준으로 탐색 경계 계산
  → 필요한 Leaf 범위 중심으로 Scan

Non-Sargable 가능 조건
  → Index Column에 함수·산술·결합·형변환
  → 저장된 Key와 Query 표현식이 다름
  → 넓은 Index Scan 또는 Table Scan 후 Filter 가능

Oracle 공식 문서는 다음과 같은 경우 일반 Index 사용이 제한될 수 있다고 설명합니다.

• Predicate가 Indexed Column에 함수를 적용함
• Query의 데이터 타입 비교 때문에 Index Column에 묵시적 변환이 발생함
• Function-Based Index가 없는 상태에서 저장된 값이 아닌 가공 결과를 검색함

그러나 “컬럼에 함수가 있으면 Index를 절대 못 쓴다”는 규칙은 아닙니다.

가능한 예외
  → Optimizer Query Transformation
  → Function-Based Index
  → Virtual Column + Index
  → 다른 선두 Column으로 넓은 Index Scan 후 Filter

따라서 SQL Text만 보고 결론 내리지 않고 실제 Child Plan에서 다음을 확인합니다.

Predicate Information
  access : 탐색 범위를 만드는 조건
  filter : 읽은 Row·Entry를 사후 평가하는 조건

Runtime Statistics
  Starts·E-Rows·A-Rows·Buffers·Reads

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 기본 원리 범위에서 인덱스를 활용할 수 있는 조건식과 묵시적 형변환을 다룹니다. Index Column 순서·선택성·Clustering과 Scan 효율화는 후속 이론에서 더 깊게 다룹니다.

학습 목표

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

• Sargable 조건과 Start·Stop Key의 관계를 설명한다.
• access Predicate와 filter Predicate의 차이를 설명한다.
• Indexed Column 가공과 상수·Bind 가공을 구분한다.
• 날짜·Timestamp 범위를 반개구간으로 작성한다.
• 묵시적 형변환의 방향이 Index 사용·오류·NLS 의존성에 미치는 영향을 설명한다.
• Bind 데이터 타입·길이가 Cursor 공유와 Plan에 영향을 줄 수 있음을 설명한다.
• Prefix LIKE, Leading Wildcard와 Bind Pattern을 구분한다.
• INLIST ITERATOR, OR Expansion과 수동 UNION ALL의 의미 보존 조건을 설명한다.
• NVL·COALESCE 제거 시 NULL의 3값 논리를 검증한다.
• Function-Based Index의 표현식·통계·DETERMINISTIC·NLS·DML 조건을 설명한다.
• 실제 실행계획과 Runtime Statistics로 Scan 효율을 검증한다.

1. Sargable 조건

Sargable은 “Search Argument Able”에서 나온 실무 용어로, DBMS가 Predicate를 이용해 검색 범위를 효율적으로 정할 수 있는 형태를 뜻합니다.

다음 Index를 가정합니다.

CREATE INDEX orders_date_ix
ON orders(order_date);

탐색 경계를 만들기 쉬운 조건

WHERE order_date >= DATE '2026-07-22'
AND   order_date <  DATE '2026-07-23'

Index에는 order_date 값 자체가 정렬돼 있으므로 시작 Key와 종료 Key를 계산할 수 있습니다.

컬럼 가공 조건

WHERE TO_CHAR(order_date, 'YYYYMMDD') = '20260722'

일반 order_date Index에는 TO_CHAR(order_date,...)의 결과가 저장돼 있지 않습니다. Function-Based Index나 Query Transformation이 없다면 각 Row·Entry의 표현식을 계산한 뒤 조건을 평가해야 할 수 있습니다.

일반 Index 저장값
  → order_date

Query 검색값
  → TO_CHAR(order_date,'YYYYMMDD')

표현식 불일치
  → 일반 Index의 좁은 Start·Stop Key 생성이 어려울 수 있음

2. access와 filter Predicate

실행계획의 Predicate Information은 조건의 실제 역할을 보여 줍니다.

access
  → Index·Join·Partition의 탐색 범위를 만드는 조건

filter
  → Row Source가 읽은 Row를 사후 평가하는 조건

예를 들어 다음과 같은 Plan이 있을 수 있습니다.

INDEX RANGE SCAN ORDERS_DATE_IX
  access("ORDER_DATE">=:B1 AND "ORDER_DATE"<:B2)

이 조건은 Index Range의 시작·종료를 만듭니다.

반면 다음처럼 보일 수 있습니다.

INDEX FULL SCAN ORDERS_DATE_IX
  filter(TO_CHAR("ORDER_DATE",'YYYYMMDD')=:B1)

Index를 읽기는 하지만 원하는 하루 범위로 바로 진입하지 못하고 많은 Entry를 읽은 뒤 Filter할 수 있습니다.

access에 나타난다는 사실만으로 효율이 보장되는 것은 아닙니다. 범위가 넓으면 A-Rows·Buffers가 여전히 클 수 있습니다.

3. 컬럼은 원형으로 두고 값 쪽을 변환한다

가능하면 Indexed Column은 저장된 형태 그대로 비교합니다.

날짜 예제

-- 컬럼 가공·NLS 의존 가능
WHERE TO_CHAR(order_date, 'YYYYMMDD') = :yyyymmdd

-- 컬럼 원형 유지
WHERE order_date >= TO_DATE(:yyyymmdd, 'YYYYMMDD')
AND   order_date <  TO_DATE(:yyyymmdd, 'YYYYMMDD') + 1

더 좋은 Application 계약은 문자열을 SQL 안에서 매번 날짜로 변환하는 것이 아니라 처음부터 DATE 또는 TIMESTAMP Bind를 전달하는 것입니다.

WHERE order_date >= :day_start
AND   order_date <  :next_day_start

숫자 연산 예제

WHERE salary + commission >= :target_amount

재작성 가능성을 검토합니다.

WHERE salary >= :target_amount - commission

그러나 오른쪽에도 Column이 있으므로 단일 salary Index로 고정 경계를 만들 수 있는지는 별도 판단이 필요합니다.

또한 Oracle의 NULL 연산 규칙 때문에 다음을 검증해야 합니다.

commission IS NULL인 경우
salary + commission
  → NULL

target_amount - commission
  → NULL

단순 대수 변환뿐 아니라 NULL·부호·Overflow·데이터 타입 의미가 동일해야 합니다.

문자열 결합

WHERE last_name || first_name = :full_name

일반 (last_name, first_name) Index와 저장 표현식이 다릅니다.

대안은 다음과 같습니다.

• 성·이름을 별도 조건으로 검색
• 정규화된 full_name Virtual Column
• 동일 표현식의 Function-Based Index
• Application 입력 구조 변경

4. 날짜와 Timestamp는 반개구간으로 작성한다

하루 범위는 [시작, 다음 시작)으로 표현합니다.

WHERE order_date >= :day_start
AND   order_date <  :next_day_start

DATE Bind라면 다음처럼 작성할 수 있습니다.

WHERE order_date >= :day_start
AND   order_date <  :day_start + 1

포함
  2026-07-22 00:00:00 이상

제외
  2026-07-23 00:00:00 이상

다음 방식은 Type 정밀도에 의존합니다.

WHERE order_date BETWEEN DATE '2026-07-22'
                     AND DATE '2026-07-22' + 1 - 1/86400

• DATE는 초 단위입니다.
• TIMESTAMP는 Fractional Second를 가질 수 있습니다.
• “마지막 순간”을 임의로 계산하면 더 정밀한 값이 누락될 수 있습니다.

반개구간은 월·연도·Timestamp 범위에도 같은 원칙으로 사용할 수 있습니다.

5. TRUNC(date_column) 재작성과 Function-Based Index

WHERE TRUNC(order_date) = DATE '2026-07-22'

대부분의 하루 검색은 다음으로 재작성할 수 있습니다.

WHERE order_date >= DATE '2026-07-22'
AND   order_date <  DATE '2026-07-23'

Function-Based Index가 후보가 되는 경우는 다음과 같습니다.

• SQL 변경이 어렵습니다.
• 동일한 표현식 검색이 매우 자주 실행됩니다.
• 일 단위 Grouping·Join·Filter가 핵심입니다.
• 별도 Index의 공간·DML 비용이 허용됩니다.

CREATE INDEX orders_trunc_date_ix
ON orders(TRUNC(order_date));

Function-Based Index 조건

Oracle의 Function-Based Index에는 다음 기준이 중요합니다.

1. Query의 표현식과 Index 표현식이 호환돼야 합니다.
2. 사용자 정의 함수는 DETERMINISTIC으로 선언해야 합니다.
3. 함수는 반복 호출에서 같은 입력에 같은 값을 반환해야 합니다.
4. 생성 후 Base Table과 Index의 통계를 수집해야 Optimizer가 사용 여부를 판단하기 쉽습니다.
5. 문자 변환이 포함된 표현식은 NLS 설정 변화에 주의합니다.
6. TO_NUMBER·TO_DATE 같은 변환이 실패하는 데이터가 Insert·Update되면 DML이 실패할 수 있습니다.
7. 함수가 Invalid 또는 Drop되면 Index가 DISABLED 상태가 될 수 있습니다.

Function-Based Index
  → 읽기 성능 후보
  + 표현식 계산·Segment·Redo·Undo·통계 관리 비용

6. 묵시적 형변환

Oracle은 서로 다른 데이터 타입을 비교할 때 한쪽 값을 자동 변환할 수 있습니다. Oracle 공식 문서는 Index 표현식에 묵시적 변환이 발생하면 변환 전 Type으로 정의된 Index를 사용하지 못할 수 있고 성능에 부정적인 영향을 줄 수 있다고 설명합니다.

6.1 문자 Column과 NUMBER Bind

-- account_no VARCHAR2
WHERE account_no = :account_no_number

실제 Predicate가 다음처럼 바뀔 수 있습니다.

filter(TO_NUMBER("ACCOUNT_NO")=:B1)

영향은 다음과 같습니다.

• 일반 문자 Index의 좁은 탐색 경계가 제한될 수 있음
• 숫자가 아닌 데이터에서 ORA-01722
• Row별 변환 CPU
• 다른 Child Plan·선택도 추정
• 데이터 품질 문제 노출

가장 안전한 방법은 문자열 Column에는 문자열 Bind를 전달하는 것입니다.

WHERE account_no = :account_no_varchar

6.2 NUMBER Column과 문자 Bind

-- customer_id NUMBER
WHERE customer_id = :customer_id_text

Oracle이 문자 Bind를 NUMBER로 변환할 수 있지만 잘못된 문자열이면 오류가 발생합니다. 처음부터 NUMBER Bind를 전달해야 변환 방향과 NLS 의존성을 줄일 수 있습니다.

6.3 날짜 Column과 문자열

WHERE order_date = '2026-07-22'

문자열→DATE 변환은 Session NLS 설정에 의존할 수 있습니다.

WHERE order_date >= DATE '2026-07-22'
AND   order_date <  DATE '2026-07-23'

또는 Date Bind를 사용합니다.

DATE Column에 TO_DATE(date_column,...)를 적용하지 않습니다. TO_DATE는 문자 값을 DATE로 명시 변환하는 함수입니다.

7. Bind 타입과 Child Cursor

같은 SQL Text라도 Bind의 데이터 타입·길이·Character Set이 달라지면 Cursor 공유 호환 조건이 달라질 수 있습니다.

호출 A
  customer_id NUMBER Bind

호출 B
  customer_id VARCHAR2 Bind

가능한 영향은 다음과 같습니다.

• 묵시적 형변환 방향 변화
• access와 filter Predicate 변화
• Child Cursor 증가
• 선택도·Cardinality 추정 차이
• Plan 차이
• 오류 가능성

Application의 Prepared Statement에서는 다음을 일관되게 관리합니다.

• Bind 데이터 타입
• 길이·정밀도·Scale
• Character·National Character Type
• DATE·TIMESTAMP 구분
• NULL Bind Type

8. LIKE Predicate

8.1 고정 Prefix

WHERE customer_name LIKE 'KIM%'

시작 문자가 정해져 있으므로 'KIM'으로 시작하는 연속 Key 범위를 만들 수 있습니다.

Start Key
  KIM

Stop Key
  KIM Prefix 범위 종료

8.2 Leading Wildcard

WHERE customer_name LIKE '%KIM'

시작 부분을 알 수 없어 일반 B-tree의 좁은 시작점을 만들기 어렵습니다.

8.3 양쪽 Wildcard

WHERE customer_name LIKE '%KIM%'

일반 B-tree가 Index Full Scan 등으로 일부 역할을 할 가능성은 있어도 Prefix Range Scan의 장점은 얻기 어렵습니다. 대량 부분 문자열 검색은 Oracle Text 같은 별도 검색 구조를 검토합니다.

8.4 Bind Pattern

WHERE customer_name LIKE :name_pattern

Pattern이 'KIM%'인지 '%KIM%'인지에 따라 선택도와 Access Path가 크게 달라질 수 있습니다.

• 대표 Bind별 Child·Plan 확인
• Bind Capture·Trace 확인
• P95·P99 실행 분포 확인

9. IN Predicate와 INLIST ITERATOR

WHERE status IN ('PAID', 'READY')

IN은 여러 등치 조건으로 처리될 수 있습니다.

INLIST ITERATOR
  → IN List의 각 값에 대해
  → 다음 Index Operation을 반복

따라서 IN이 있다는 이유만으로 Index 사용이 불가능하다고 판단하지 않습니다.

그러나 다음 조건에서는 비용이 커질 수 있습니다.

• IN 값 수가 매우 많음
• 각 값의 선택성이 낮음
• 값별 Range Scan에서 같은 Table Block을 반복 방문
• 결과 대부분을 반환

실행계획의 Starts, A-Rows·Buffers와 최종 Row 수를 확인합니다.

10. OR Predicate와 Query Transformation

WHERE customer_id = :customer_id
OR    phone_no    = :phone_no

Oracle Optimizer는 다음 경로를 고려할 수 있습니다.

• OR Expansion
• Concatenation
• 여러 Index Access Path
• Bitmap Conversion
• Full Scan

수동 UNION ALL 재작성은 다음을 보존해야 합니다.

• 중복 Row
• NULL의 3값 논리
• Outer Join·Subquery 의미
• Predicate 평가 순서에 의존하지 않는 결과

예를 들면 다음과 같습니다.

SELECT ...
FROM   customer
WHERE  customer_id = :customer_id
UNION ALL
SELECT ...
FROM   customer
WHERE  phone_no = :phone_no
AND    LNNVL(customer_id = :customer_id);

LNNVL은 첫 Branch와 중복되는 Row를 제외하면서 NULL의 UNKNOWN 의미를 처리하는 데 사용할 수 있습니다.

단순히 다음처럼 작성하면 원래 OR과 결과가 달라질 수 있습니다.

AND customer_id <> :customer_id

customer_id IS NULL이면 비교 결과가 UNKNOWN이기 때문입니다.

11. NVL·COALESCE와 NULL 의미

WHERE NVL(quantity, 0) >= 100

quantity IS NULL이면 0으로 평가되어 조건을 만족하지 않습니다. 다음 재작성과 결과가 같습니다.

WHERE quantity >= 100

반면 다음은 NULL Row를 포함합니다.

WHERE NVL(quantity, 0) < 100

단순 재작성은 결과를 바꿉니다.

-- NULL Row 제외
WHERE quantity < 100

동일 의미는 다음처럼 명시합니다.

WHERE quantity < 100
OR    quantity IS NULL

함수를 제거하기 전에 다음을 검증합니다.

• NULL 포함 여부
• 대체값의 업무 의미
• Column의 음수·범위
• Data Type Precedence
• COALESCE·CASE의 반환 Type
• 다른 Predicate와의 중복

12. 문자형 날짜·번호 저장의 문제

날짜를 다음과 같이 저장했다고 가정합니다.

order_ymd VARCHAR2(8)
'YYYYMMDD'

WHERE order_ymd BETWEEN '20260701' AND '20260731'

고정 형식이 완전히 보장되면 문자 정렬과 날짜 순서가 일치할 수 있습니다. 그러나 다음 위험이 남습니다.

• '20260230' 같은 잘못된 날짜
• 날짜 계산마다 변환 필요
• 시간·Time Zone 표현 한계
• 입력 Format 불일치
• Function-Based Index나 변환 DML 오류

새로운 모델은 업무 타입에 맞는 DATE·TIMESTAMP·NUMBER를 사용합니다.

기존 문자 Column을 변환해 Indexing할 때는 잘못된 데이터 때문에 Function-Based Index 생성 또는 DML이 실패할 수 있으므로 데이터 정제가 먼저 필요합니다.

13. Function-Based Index 선택 절차

다음 질문에 답합니다.

1. 같은 표현식 검색이 중요하고 자주 실행되는가?
2. 일반 Column Range로 재작성할 수 없는가?
3. Virtual Column이 가독성과 통계 관리에 더 적합한가?
4. Query 표현식과 Index 표현식이 호환되는가?
5. 사용자 정의 함수가 DETERMINISTIC이고 반복 가능한가?
6. NLS·Collation·Session Parameter 영향이 통제되는가?
7. 변환 불가능 데이터가 DML을 실패시키지 않는가?
8. Base Table·Index 통계를 수집·유지할 수 있는가?
9. DML CPU·Redo·Undo·Segment 비용이 허용되는가?
10. 함수 Invalid·Drop 시 운영 절차가 준비됐는가?

SQL 수정이 가능한 경우에는 여러 Query가 함께 활용할 수 있는 원본 Column Range Predicate를 먼저 검토합니다.

14. 실행계획과 Runtime Statistics로 검증한다

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       order_date
FROM   orders
WHERE  order_date >= :day_start
AND    order_date <  :next_day_start;

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

확인 항목은 다음과 같습니다.

14.1 비교 예시

변경 전:

INDEX FULL SCAN ORDERS_DATE_IX
  filter(TO_CHAR("ORDER_DATE",'YYYYMMDD')=:B1)

A-Rows  2,000,000
Buffers    18,000
최종 Row      500

변경 후:

INDEX RANGE SCAN ORDERS_DATE_IX
  access("ORDER_DATE">=:B1 AND "ORDER_DATE"<:B2)

A-Rows      500
Buffers       12
최종 Row      500

같은 결과와 같은 Bind·Fetch 조건에서 작업량 감소를 검증합니다.

15. 진단 순서

1. Column 정의·Index Column 순서를 확인한다.
2. Literal·Bind의 실제 데이터 타입을 확인한다.
3. DISPLAY_CURSOR에서 실제 Child·Predicate를 확인한다.
4. Index Column의 함수·산술·결합·묵시 변환을 찾는다.
5. access·filter와 Start·Stop 범위를 구분한다.
6. 결과 의미를 보존하는 원형 Predicate로 재작성한다.
7. 날짜는 반개구간, LIKE는 Prefix 여부를 확인한다.
8. IN 값 수·Starts, OR 변환의 중복·NULL 의미를 확인한다.
9. 재작성이 어렵다면 FBI·Virtual Column을 검토한다.
10. 동일 Bind·Fetch로 A-Rows·Buffers·Elapsed·오류를 재측정한다.
11. DML·다른 Bind·다른 SQL Regression을 확인한다.

16. 자주 혼동하는 판단