Function-Based Index: 표현식 검색·NULL·정렬 최적화

핵심 요약

Function-Based Index(FBI)는 컬럼 원본이 아니라 함수 또는 표현식의 계산 결과를 Index Key로 저장합니다.

일반 B-tree Index
  column_value → ROWID

Function-Based Index
  expression(column_value) → ROWID

Oracle의 FBI는 B-tree 또는 Bitmap 형태가 될 수 있지만, SQLP의 일반적인 온라인 튜닝 문맥에서는 B-tree FBI를 중심으로 이해합니다.

대표 활용은 다음과 같습니다.

• 대소문자 무시 검색: UPPER(name)
• 반복되는 계산식: 12 * salary * commission_pct
• 변경하기 어려운 날짜 가공 SQL: TRUNC(order_date)
• 특정 Row만 저장하는 CASE 표현식
• 조건부 Unique Index
• 표현식 기준 검색·정렬
• 긴 값을 줄인 SUBSTR·STANDARD_HASH 기반 Access 보조

FBI 설계 순서는 다음과 같습니다.

업무 표현식 확인
  → 원본 컬럼 조건으로 SQL 재작성 가능성 검토
  → Virtual Column과 FBI 비교
  → 표현식 결과 타입·NULL·NLS·결정성 확정
  → Index 생성·통계 수집
  → 실제 Child Plan과 작업량 검증
  → DML·함수 변경·회귀·Rollback 검증

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 기본 원리·인덱스 설계 범위에서 Function-Based Index의 표현식 검색, NULL, 조건부 유일성, 정렬과 운영 조건을 다룹니다.

학습 목표

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

• FBI가 저장하는 값과 일반 B-tree Index의 차이를 설명한다.
• SQL 재작성·Virtual Column·FBI 중 적절한 방법을 판단한다.
• Oracle의 표현식 트리 매칭 원리를 설명한다.
• 사용자 정의 함수의 DETERMINISTIC 요구사항과 위험을 설명한다.
• 함수 변경·Invalid·Drop이 FBI에 미치는 영향을 설명한다.
• FBI 생성 후 Table·Index 통계가 필요한 이유를 설명한다.
• 모든 표현식 결과가 NULL인 Row의 저장 여부를 설명한다.
• 조건부 Index와 조건부 Unique Index의 원리를 설명한다.
• NULL 검색의 Sentinel·조건부 상수·복합 일반 Index 패턴을 비교한다.
• FBI를 이용한 검색·ORDER BY·Top-N의 조건을 설명한다.
• NLS·Collation·묵시적 변환·변환 실패가 정확성과 DML에 미치는 영향을 설명한다.
• 실제 Plan·A-Rows·Buffers·DML 통계로 FBI 효과를 검증한다.

1. Function-Based Index의 동작 원리

다음 SQL이 자주 실행된다고 가정합니다.

SELECT customer_id,
       customer_name
FROM   customer
WHERE  UPPER(customer_name) = UPPER(:name);

일반 Index는 원본 값을 저장합니다.

CREATE INDEX customer_name_ix
ON customer(customer_name);

'Kim' → 'Kim'
'KIM' → 'KIM'
'kim' → 'kim'

다음 FBI는 대문자 표현식 결과를 저장합니다.

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

'Kim' → 'KIM'
'KIM' → 'KIM'
'kim' → 'KIM'

SQL이 호환되는 표현식을 사용하면 Optimizer는 FBI의 정렬된 Key에서 Range Scan을 고려할 수 있습니다.

2. SQL 재작성·Virtual Column·FBI 선택

2.1 원본 컬럼 재작성 우선 사례

-- 기존
WHERE TRUNC(order_date) = :day

-- 권장 후보
WHERE order_date >= :day
AND   order_date <  :day + 1

장점입니다.

• 일반 order_date Index를 여러 날짜 SQL이 공용
• 별도 FBI Segment와 DML 유지비 없음
• 날짜 범위와 정렬을 원본 Key로 활용
• 표현식 통계·함수 변경 운영 부담 감소

2.2 FBI가 자연스러운 사례

WHERE UPPER(customer_name) = UPPER(:name)

대소문자 무시 검색이 업무 Key이고 원본 값 범위로 단순 재작성하기 어렵다면 UPPER(customer_name) FBI가 적합할 수 있습니다.

2.3 Virtual Column 대안

표현식을 Schema에 이름으로 노출하고 여러 SQL·Constraint·통계에서 재사용하려면 Virtual Column을 검토합니다.

ALTER TABLE customer ADD (
  normalized_name VARCHAR2(200)
    GENERATED ALWAYS AS (UPPER(TRIM(customer_name))) VIRTUAL
);

CREATE INDEX customer_normal_name_ix
ON customer(normalized_name);

FBI
  → Query 표현식과 직접 연결
  → Schema에 별도 Column 이름 없음

Virtual Column + 일반 Index
  → 표현식에 이름 부여
  → SQL 가독성·재사용·통계·Constraint 관리에 유리할 수 있음

3. 표현식 호환성과 Expression Tree Matching

Oracle은 SQL 표현식과 FBI 정의를 파싱한 뒤 표현식 트리를 비교합니다. 비교는 대소문자를 구분하지 않고 공백을 무시할 수 있지만, 의미가 다른 함수·연산이 추가되면 다른 표현식입니다.

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

호환 가능성이 높은 Query입니다.

WHERE UPPER(customer_name) = :upper_name

다른 표현식입니다.

WHERE UPPER(TRIM(customer_name)) = :upper_trimmed_name

다음도 자동으로 같은 표현식이라고 단정하지 않습니다.

• NVL(col,0)과 COALESCE(col,0)
• col + 1과 1 + col
• TRUNC(date_col)과 CAST(TRUNC(date_col) AS DATE)
• 서로 다른 Schema의 사용자 함수
• Literal·Bind의 다른 데이터 타입
• NLS 의존 변환

사람에게 비슷해 보임
  ≠ Optimizer가 같은 Expression Tree로 판단

실제 Predicate Information과 선택된 Index를 확인합니다.

4. 사용자 함수와 DETERMINISTIC

사용자 정의 PL/SQL 함수를 FBI 표현식에 사용하려면 DETERMINISTIC으로 선언해야 합니다.

CREATE OR REPLACE FUNCTION normalize_code(p_code VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
  RETURN UPPER(TRIM(p_code));
END;
/

DETERMINISTIC 함수는 다음 조건을 만족해야 합니다.

• 같은 입력이면 같은 결과
• 부작용 없음
• 처리되지 않은 예외 없음
• Session Variable·현재 시각·Random 값에 의존하지 않음
• Package Variable에 의존하지 않음
• 결과에 영향을 주는 Database Object를 조회하지 않음

DETERMINISTIC
  → Oracle이 함수 논리를 검증해 주는 인증이 아님
  → 개발자가 조건을 지킨다고 선언

조건을 위반해도 Compiler나 실행 엔진이 진단하지 않을 수 있으며 잘못된 결과가 조용히 생성될 수 있습니다.

4.1 부적합한 함수 예

SYSDATE·SYSTIMESTAMP 사용
DBMS_RANDOM 사용
Session 사용자·언어 설정에 따라 결과 변경
Package Variable 참조
외부 Table 값을 조회해 결과 결정
DML·Log 기록 같은 부작용 수행

5. 함수 변경과 FBI 상태

사용자 함수를 CREATE OR REPLACE로 변경하면 Oracle은 종속 FBI를 DISABLED로 표시할 수 있습니다.

함수 변경·Invalid·Drop
  → 종속 FBI DISABLED 가능
  → Query·DML 실패 가능성
  → 함수 정합성 확인
  → FBI Rebuild·Enable
  → 통계·Plan 재검증

DETERMINISTIC 함수의 정의를 변경하면 종속 FBI를 수동으로 Rebuild해야 합니다. 함수 변경은 단순 Application 배포가 아니라 Index 운영 변경으로 관리합니다.

6. FBI 생성 권한과 통계

사용자 함수 기반 FBI에는 다음 조건이 필요합니다.

• 함수가 DETERMINISTIC
• Index Owner가 함수에 대한 EXECUTE 권한 보유
• 함수 이름이 Index 생성 Schema 기준으로 정상 해석
• 표현식 결과가 B-tree Index Column 제한을 만족

FBI 생성 후 Base Table과 Index의 Optimizer Statistics를 수집합니다.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => USER,
    tabname    => 'CUSTOMER',
    cascade    => TRUE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

확인 항목입니다.

• 표현식 NDV
• NULL 비율
• 빈도 편중·Histogram
• Index Leaf Block·Clustering 관련 통계
• 대표 Bind별 Cardinality
• FBI 사용 Plan과 대체 Plan의 비용

통계가 없거나 부정확하면 FBI가 있어도 Optimizer가 적절히 선택하지 못할 수 있습니다.

7. FBI와 NULL 저장 규칙

일반 B-tree 규칙과 같이 모든 Index 표현식 결과가 NULL인 Row는 FBI Entry가 저장되지 않습니다.

CREATE INDEX orders_open_customer_ix
ON orders(
  CASE WHEN status = 'OPEN' THEN customer_id END
);

status='OPEN'
  → customer_id 반환
  → Entry 저장

status<>'OPEN'
  → NULL 반환
  → 전체 Key NULL
  → Entry 미저장

이 특성으로 특정 Row 집합만 작은 Index에 포함할 수 있습니다.

단, 대상 조건에서도 customer_id가 NULL이면 표현식 결과가 NULL이므로 Entry가 없을 수 있습니다. 업무 규칙과 실제 NULL 가능성을 함께 확인합니다.

8. NULL 검색 패턴

8.1 Sentinel 값을 저장하는 NVL FBI

CREATE INDEX orders_completed_nvl_ix
ON orders(
  NVL(completed_at, TIMESTAMP '9999-12-31 00:00:00')
);

WHERE NVL(completed_at, TIMESTAMP '9999-12-31 00:00:00')
      = TIMESTAMP '9999-12-31 00:00:00'

주의사항입니다.

• 실제 업무 값과 Sentinel이 충돌하지 않아야 함
• 표현식과 Query의 Data Type·정밀도 일치
• Date·Timestamp 혼용 금지
• Sentinel이 정렬·통계 편중에 미치는 영향
• 업무 범위가 확장돼 미래 값이 실제 데이터가 될 가능성

8.2 조건부 상수 FBI

CREATE INDEX orders_open_flag_ix
ON orders(
  CASE WHEN completed_at IS NULL THEN 1 END
);

미완료 Row만 Index에 저장됩니다. 대상 Row 비율이 작고 해당 조회가 중요할 때 유리할 수 있습니다.

8.3 복합 일반 B-tree

CREATE INDEX orders_completed_id_ix
ON orders(completed_at, order_id);

order_id가 NOT NULL이면 (NULL,order_id)는 전체 Key가 NULL이 아니므로 Entry가 존재합니다. FBI를 추가하기 전에 일반 복합 Index로 해결 가능한지 검토합니다.

9. 조건부 Unique Index

FBI의 All-Key-NULL 미저장 규칙으로 특정 조건의 Row에만 유일성을 적용할 수 있습니다.

요구사항입니다.

ACTIVE 주문
  → customer_id + external_order_no 중복 금지

종료 이력
  → 중복 허용

CREATE UNIQUE INDEX orders_active_uk
ON orders(
  CASE WHEN status = 'ACTIVE' THEN customer_id END,
  CASE WHEN status = 'ACTIVE' THEN external_order_no END
);

ACTIVE
  → 실제 두 Key 저장
  → Unique 검사 적용

비활성 상태
  → 두 표현식 모두 NULL
  → Entry 미저장
  → 중복 허용

주의합니다.

• 대상 Row의 Key Column이 NULL 가능한지
• status 값의 정확한 업무 범위
• Unique Index와 Declarative Constraint의 차이
• Application Error 처리
• 조건 변경 시 기존 데이터 검증
• Index가 성능 객체이면서 무결성 객체라는 점

10. 표현식 정렬과 ORDER BY

FBI는 표현식 결과 순서로 정렬됩니다.

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

SELECT customer_id,
       customer_name
FROM   customer
WHERE  UPPER(customer_name) >= :from_name
ORDER BY UPPER(customer_name), customer_id;

Sort 생략 가능성은 다음 조건을 모두 검토합니다.

• 실제 Access Path가 FBI 사용
• ORDER BY 표현식과 Index Expression Tree 호환
• Key Column 순서와 ASC·DESC 방향 호환
• 선행 Key 조건
• NULLS FIRST·LAST 의미
• IN-List·OR·Partition 결과의 전역 순서
• Table Access 후에도 결과 순서 유지
• 다른 Access Path보다 총비용이 낮음

FBI에 정렬된 Key 존재
  ≠ Optimizer가 항상 Sort 없는 Index Plan 선택

Table Random Access가 비싸면 Full Scan + Sort가 더 저렴할 수 있습니다.

11. NLS·Collation·문자 변환

UPPER, LOWER, NLSSORT, TO_CHAR, TO_DATE 같은 표현식은 언어·문자·변환 환경과 연결됩니다.

11.1 명시적인 언어 정렬

CREATE INDEX customer_name_ling_ix
ON customer(
  NLSSORT(customer_name, 'NLS_SORT=KOREAN')
);

Query도 동일한 정렬 의미를 사용해야 합니다.

Session 설정에 암묵적으로 의존하기보다 Expression에 업무 정렬 규칙을 명시합니다.

11.2 내부 Character Conversion 주의

Oracle 문서 기준으로 FBI 정의가 내부 Character Conversion을 만들면 일부 NLS Parameter를 Session에서 변경했을 때 잘못된 결과가 발생할 수 있습니다. NLS_SORT와 NLS_COMP는 Oracle이 별도로 처리하는 예외가 있지만, 전체 NLS 의존성을 안전하다고 일반화하지 않습니다.

11.3 변환 실패와 DML 오류

CREATE INDEX account_no_num_ix
ON account(TO_NUMBER(account_no));

이후 다음 데이터가 들어오면 Index 표현식 계산이 실패할 수 있습니다.

account_no = '105 lbs'

INSERT·UPDATE가 TO_NUMBER 오류로 실패할 수 있으므로 변환 FBI를 만들기 전에 기존 데이터와 입력 규칙을 정제합니다.

12. 표현식 결과 타입과 길이

FBI의 반환값은 일반 B-tree Index Column과 같은 제한을 받습니다.

확인합니다.

• 결과 Data Type
• Character 길이·Byte 길이
• Collation
• LOB·LONG·사용 불가 타입
• Maximum Index Key Length
• 다중 표현식의 합산 길이
• SUBSTR·STANDARD_HASH 대안
• Hash 충돌 검증

긴 Extended Data Type을 검색하기 위한 후보입니다.

CREATE INDEX long_text_prefix_ix
ON t(SUBSTR(long_text_col,1,100));

Prefix Index는 Equality·IN·Range 후보를 줄일 수 있지만 동일 Prefix를 가진 Row의 추가 Filter가 필요할 수 있습니다.

13. DML·공간·운영 비용

Table DML 시 FBI 표현식을 계산하고 Index Entry를 유지합니다.

비용이 커질 수 있는 조건입니다.

• 표현식 계산이 복잡함
• 사용자 함수 호출 비용이 큼
• 대상 Column이 자주 Update됨
• 조건부 Index 대상 Row가 증가
• Key가 길어 Leaf Block이 많음
• 여러 FBI가 같은 DML Row에 존재
• Redo·Undo·Cache·Backup 증가
• Function 배포·Edition 변경과 종속성
• 통계 수집·Rebuild 시간

비교 지표입니다.

조회
  → Elapsed·CPU·Buffers·Reads·Sort

DML
  → INSERT·UPDATE·DELETE TPS
  → Redo·Undo
  → Function CPU·Error

운영
  → Segment Size·Leaf Blocks
  → Rebuild·통계 시간
  → 다른 SQL Plan 회귀

14. 실행계획과 실제 통계 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       customer_id,
       customer_name
FROM   customer
WHERE  UPPER(customer_name) = UPPER(:name);

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

확인합니다.

1. 실제 Child Cursor가 FBI를 사용했는가
2. 표현식이 Index access Predicate인가
3. Table filter로 남은 조건은 무엇인가
4. Starts·A-Rows·Buffers·Reads는 얼마인가
5. Index 후보 대비 최종 Row는 얼마인가
6. Sort·TEMP가 제거됐는가
7. Index-only인지 Table Access가 남는가
8. 일반 Index·Virtual Column·Full Scan보다 총비용이 낮은가
9. 대표·극단 Bind와 NLS 환경에서 결과가 같은가
10. DML·Redo·함수 오류와 다른 SQL 회귀가 허용 범위인가

15. 안전한 적용 절차

1. 반복되는 표현식 SQL·SLA·빈도를 수집한다.
2. 원본 컬럼 Range로 재작성 가능한지 검토한다.
3. Virtual Column과 FBI를 비교한다.
4. 표현식 트리·타입·NULL·NLS·결정성을 확정한다.
5. 기존 데이터의 변환 오류 가능성을 검사한다.
6. Test·Invisible 후보를 생성하고 통계를 수집한다.
7. 동일 Bind·Fetch로 Plan·Buffers·Elapsed를 비교한다.
8. DML·Redo·공간·함수 변경·다른 SQL 회귀를 검증한다.
9. Rebuild·Disable·Rollback 절차를 문서화한다.
10. Canary 배포 후 Plan·오류·DML을 Monitoring한다.

16. 자주 혼동하는 판단