복합 인덱스의 조건 처리: Access Predicate·Index Filter·Table Filter

핵심 요약

Oracle 실행계획은 Predicate를 크게 다음 두 공식 Column으로 기록합니다.

ACCESS_PREDICATES
  → Access Structure에서 Row·Entry를 찾는 데 사용
  → Index Range Scan의 Start·Stop Predicate가 대표적

FILTER_PREDICATES
  → 해당 Operation이 읽은 Row·Entry를
     상위 Operation으로 반환하기 전에 Filter

실무 교육에서는 Filter가 표시된 Operation에 따라 다음처럼 부르기도 합니다.

Index Filter Predicate
  → Index Operation의 FILTER_PREDICATES
  → Leaf Entry를 읽은 뒤 Table Access 전에 후보 ROWID를 줄임

Table Filter Predicate
  → Table Operation의 FILTER_PREDICATES
  → ROWID로 Table Row를 읽은 뒤 최종 Filter

Index Filter Predicate, Table Filter Predicate는 처리 위치를 이해하기 위한 교육 용어입니다. Oracle 실행계획의 공식 저장 항목은 ACCESS_PREDICATES와 FILTER_PREDICATES입니다.

복합 인덱스의 조건 효과는 Predicate 표기만으로 판단하지 않습니다.

Index: (A, B, C, D)

A = :a
B = :b
C BETWEEN :c1 AND :c2
D = :d

A·B
  → 선행 Key 구간 고정

C
  → 첫 범위 조건
  → 연속 Leaf Scan의 주요 Start·Stop 범위 결정

D
  → C 범위 안에서 Entry를 더 거를 수 있음
  → Table Access 후보는 줄일 수 있으나
     넓은 C 범위의 Leaf Scan 비용은 남을 수 있음

진단의 핵심은 다음 세 비용을 분리하는 것입니다.

1. Leaf Scan 비용
   → Index Buffers·Reads

2. ROWID 후보 비용
   → Index가 상위로 반환한 A-Rows

3. Table Random Access·Table Filter 비용
   → Table Buffers·Reads와 최종 A-Rows

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 기본 원리 범위에서 복합 인덱스 Predicate 처리 위치와 작업량을 다룹니다. Start·Stop Key의 세부 경계 계산, Scan 효율화와 Index 설계는 후속 이론에서 더 깊게 다룹니다.

학습 목표

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

• Oracle 공식 ACCESS_PREDICATES와 FILTER_PREDICATES를 구분한다.
• Index Filter·Table Filter라는 교육 용어를 실제 Plan Operation에 연결한다.
• 복합 인덱스의 사전식 정렬을 설명한다.
• 선행 등치 조건과 첫 범위 조건의 역할을 설명한다.
• 첫 범위 뒤 후행 조건이 Leaf Scan과 Table Access에 미치는 영향을 설명한다.
• access에 표시된 조건들의 물리적 Scan 축소 효과가 서로 다를 수 있음을 설명한다.
• 선두 Column 누락 시 Skip Scan 가능성과 일반 Range Scan의 차이를 설명한다.
• Starts·E-Rows·A-Rows·Buffers·Reads로 반복 탐색과 작업량을 계산한다.
• A-Rows가 Index 내부 검사 Entry 전체를 의미하지 않는 이유를 설명한다.
• Index Filter와 Table Filter의 비용 차이를 설명한다.
• 컬럼 순서 변경·Filter Column 추가·Covering의 Trade-off를 비교한다.
• Index 후보를 같은 Bind·Fetch 조건으로 검증한다.

1. 공식 Predicate와 교육 용어

Oracle의 V$SQL_PLAN, PLAN_TABLE, 관련 Plan View는 다음 정보를 제공합니다.

예를 들어 다음 Plan을 봅니다.

TABLE ACCESS BY INDEX ROWID BATCHED ORDERS
  filter("STATUS"='PAID')
  INDEX RANGE SCAN ORDERS_X2
    access("CUSTOMER_ID"=:B1
       AND "ORDER_DATE">=:B2
       AND "ORDER_DATE"<:B3)

Index Access Predicate
  → CUSTOMER_ID·ORDER_DATE로 Index 탐색 범위 설정

Table Filter Predicate
  → Table Row를 읽은 뒤 STATUS 검사

Index Operation에 Filter가 표시되면 이를 Index Filter라고 설명할 수 있습니다.

INDEX RANGE SCAN ORDERS_X1
  access("CUSTOMER_ID"=:B1
     AND "ORDER_DATE">=:B2)
  filter("STATUS"='PAID')

이 경우 status는 Leaf Entry에서 평가되므로 조건에 맞지 않는 ROWID가 Table Operation으로 전달되는 것을 줄일 수 있습니다.

2. 처리 위치가 비용을 바꾼다

다음 SQL을 가정합니다.

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
AND    order_date >= :from_date
AND    order_date <  :to_date
AND    status = 'PAID';

Index 1

CREATE INDEX orders_x1
ON orders(customer_id, order_date, status);

개념적 처리입니다.

1. customer_id·order_date로 시작 Leaf와 날짜 범위 결정
2. 날짜 범위의 Leaf Entry 읽기
3. Index Entry의 status 확인
4. PAID ROWID만 Table에 전달
5. Table에서 order_id·amount 읽기

status는 Table Access를 줄일 수 있지만 날짜 범위 자체가 넓으면 많은 Leaf Entry를 읽을 수 있습니다.

Index 2

CREATE INDEX orders_x2
ON orders(customer_id, order_date);

status가 Index에 없으므로 다음과 같이 처리될 수 있습니다.

1. customer_id·order_date 범위 Scan
2. 모든 후보 ROWID를 Table에 전달
3. Table Row를 읽고 status Filter

Table Filter는 Index Scan과 후보 Table Access가 이미 발생한 뒤 Row를 제거합니다.

3. 복합 인덱스의 사전식 정렬

복합 인덱스 (A,B,C)는 다음과 같은 사전식 순서를 가집니다.

(A=1, B=1, C=1)
(A=1, B=1, C=2)
(A=1, B=2, C=1)
(A=2, B=1, C=1)

선행 Column이 고정되면 뒤 Column의 연속 범위를 좁게 찾을 수 있습니다.

WHERE A = :a
AND   B = :b
AND   C >= :c

A·B
  → 하나의 Prefix 구간 고정

C
  → 그 Prefix 안의 시작 위치 결정

반대로 선행 Column이 여러 값의 범위라면 후행 Column이 모든 중간 Prefix 구간을 완전히 제거하지 못할 수 있습니다.

4. 선행 등치와 첫 범위 조건

다음 인덱스를 가정합니다.

(A, B, C, D)

WHERE A = :a
AND   B = :b
AND   C BETWEEN :c1 AND :c2
AND   D = :d

일반적인 물리적 효과는 다음과 같습니다.

후보 인덱스가 (A,B,D,C)라면 D가 항상 Equality 조건으로 사용될 때 다음처럼 더 좁은 범위를 만들 수 있습니다.

(A=:a, B=:b, D=:d) Prefix 고정
  → C Range Scan

그러나 D 조건이 자주 생략되는 SQL에서는 인덱스 공용성이 떨어질 수 있습니다.

5. 후행 조건도 access에 표시될 수 있다

실행계획의 Predicate 표현은 논리적 Access 역할을 보여 주지만, 복합 Key의 모든 중간 구간을 후행 조건이 완전히 제거한다는 뜻은 아닙니다.

인덱스 (C1,C2)를 가정합니다.

WHERE C1 BETWEEN 'A' AND 'C'
AND   C2 BETWEEN 2 AND 3

사전식 전체 범위를 개념적으로 보면 다음과 같습니다.

시작 경계
  ('A', 2)

종료 경계
  ('C', 3)

이 범위 안에는 다음 Entry가 포함될 수 있습니다.

('A', 2)      통과
('A', 3)      통과
('B', 1)      전체 사전식 범위 안, C2 Filter에서 탈락 가능
('B', 2)      통과
('B', 3)      통과
('B', 9)      전체 사전식 범위 안, C2 Filter에서 탈락 가능
('C', 1)      탈락 가능
('C', 2)      통과
('C', 3)      통과

C2는 양 끝 경계에 기여하면서도 중간 C1 Prefix 구간에서는 추가 평가가 필요할 수 있습니다.

access에 표시됨
  ≠ 조건이 모든 Leaf Group을 완전히 잘라 냄

실제 Leaf Scan량은 Buffers·Reads와 후보 Row를 확인합니다.

6. 선두 Column과 Skip Scan 예외

Oracle 공식 설명에서 Index Range Scan은 일반적으로 한 개 이상의 선행 Column이 조건에 지정될 때 사용됩니다.

그러나 선두 Column이 없다고 항상 Index가 불가능한 것은 아닙니다.

Index: (gender, email)

Predicate:
  email = :email

선두 gender의 Distinct Value가 매우 적으면 Optimizer가 Index를 여러 Logical Subindex로 나눠 INDEX SKIP SCAN을 고려할 수 있습니다.

gender='F' Subindex에서 email 탐색
gender='M' Subindex에서 email 탐색

Skip Scan은 선두 Column이 직접 고정된 일반 Range Scan과 비용 구조가 다릅니다. Starts·Buffers와 선두 Column NDV를 함께 봅니다.

7. Index Filter와 Table Filter의 비용 차이

예를 들어 날짜 범위에서 100,000개의 Entry를 읽고 상태 조건에 맞는 Row가 1,000개라고 가정합니다.

상태가 Index Filter

Leaf Entry 검사     100,000
Table 전달 ROWID      1,000
Table 방문             1,000

상태가 Table Filter

Leaf Entry 검사     100,000
Table 전달 ROWID    100,000
Table 방문          100,000
최종 결과             1,000

두 경우 모두 Leaf Scan은 넓을 수 있지만 Table Random Access 비용은 크게 다릅니다.

8. 실제 실행계획 예제

--------------------------------------------------------------------------------
| Id | Operation                            | Starts | A-Rows | Buffers | Reads |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |      1 |  1,000 |  85,000 | 5,000 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED |      1 |  1,000 |  80,000 | 4,500 |
|  2 |   INDEX RANGE SCAN ORDERS_X2         |      1 |100,000 |   5,000 |   500 |
--------------------------------------------------------------------------------

Predicate Information
  1 - filter("STATUS"='PAID')
  2 - access("CUSTOMER_ID"=:B1
         AND "ORDER_DATE">=:B2
         AND "ORDER_DATE"<:B3)

해석합니다.

1. Index가 100,000개의 후보 ROWID를 상위로 반환했습니다.
2. Table에서 모든 후보 Row를 읽고 상태를 평가했습니다.
3. 최종 결과는 1,000행입니다.
4. Table Operation에 80,000 Buffers가 집중됐습니다.
5. 주요 낭비는 Table Filter 이전의 과도한 후보 ROWID와 Table Access입니다.

status를 Index에 추가한 후 다음 결과가 나왔다고 가정합니다.

INDEX RANGE SCAN A-Rows   1,000
INDEX Buffers             5,200
TABLE A-Rows              1,000
TABLE Buffers             2,000
TOTAL Buffers             7,200

Table Access 후보는 크게 줄었지만 Index Buffers가 여전히 5,200이라면 상태 조건이 Leaf Scan 범위 자체를 줄인 것이 아니라 Index Filter로 처리됐을 가능성을 검토합니다.

컬럼 순서를 (customer_id,status,order_date)로 변경해 Index Buffers까지 줄어드는지 같은 Bind로 비교합니다.

9. A-Rows 하나로 내부 검사량을 확정하지 않는다

A-Rows는 해당 Row Source가 상위 Operation으로 반환한 Row 수입니다.

Index Operation에 Filter가 있으면 다음이 가능합니다.

내부 Leaf Entry 검사    100,000
Index Filter 통과         1,000
Index A-Rows              1,000
Index Buffers              5,000

A-Rows만 보면 1,000개의 Entry만 읽은 것처럼 오해할 수 있습니다.

다음을 함께 봅니다.

• Index Buffers·Reads
• Table로 전달된 ROWID 수
• 최종 Row 수
• SQL Trace Row Source cr·pr·rows
• 다른 Index 후보와 전후 비교
• SQL Monitor·ASH의 Plan Line 활동

Index A-Rows 적음
+ Index Buffers 큼
  → 넓은 Leaf Scan 뒤 Index Filter 가능성

10. Starts와 반복 탐색

Nested Loops Inner Operation이나 INLIST ITERATOR에서는 같은 Index Operation이 여러 번 시작됩니다.

| Operation        | Starts | A-Rows | Buffers |
| INDEX RANGE SCAN | 10,000 | 20,000 | 80,000  |

평균 Row / Start
  = 20,000 ÷ 10,000
  = 2

평균 Buffer / Start
  = 80,000 ÷ 10,000
  = 8

한 번의 탐색이 작아도 10,000회 반복하면 총비용은 큽니다.

총비용
  = 1회 비용
  × Starts

반복 비용이 크면 다음을 함께 검토합니다.

• Join Order와 Driving Row 수
• Index Prefix와 Unique 여부
• IN List 값 수
• Cache Locality
• Batch·Set 기반 처리 가능성

11. TABLE ACCESS BY INDEX ROWID BATCHED

TABLE ACCESS BY INDEX ROWID BATCHED는 여러 ROWID를 모아 Table Block 순서에 가깝게 접근하려는 최적화입니다.

Index ROWID Batch 수집
  → Block 위치 고려
  → Table Row 접근

장점은 같은 Table Block의 반복 방문을 줄일 가능성이 있다는 것입니다.

그러나 다음을 의미하지는 않습니다.

BATCHED
  ≠ Table Filter 제거
  ≠ 후보 ROWID 감소
  ≠ 반드시 한 번의 Multiblock Physical Read

100,000개 후보 ROWID를 BATCHED로 읽더라도 최종 1,000행이라면 후보 자체를 줄일 수 있는 Index·Predicate 설계를 우선 검토할 수 있습니다.

12. 후보 Index 비교

주요 SQL입니다.

WHERE customer_id = :customer_id
AND   order_date >= :from_date
AND   order_date <  :to_date
AND   status = :status

후보 A: (customer_id, order_date, status)

• 고객별 날짜 조회와 날짜 정렬에 공용성이 높습니다.
• status는 첫 범위 뒤의 Index Filter 성격이 커질 수 있습니다.
• 날짜 범위가 넓으면 Leaf Buffers가 클 수 있습니다.

후보 B: (customer_id, status, order_date)

• 고객·상태 Equality Prefix 뒤 날짜 범위를 Scan합니다.
• status가 항상 있는 SQL에서는 Leaf Scan을 더 좁힐 수 있습니다.
• status가 생략되는 SQL의 활용도는 낮아질 수 있습니다.

후보 C: (customer_id, order_date)

• 인덱스가 더 작고 고객별 날짜 SQL에 공용성이 있습니다.
• status는 Table Filter가 되어 많은 Table Access를 만들 수 있습니다.

후보 D: Full Table Scan·Partition Scan

• 결과 비율이 매우 크거나 많은 Table Block을 방문해야 한다면 대량 Scan이 더 유리할 수 있습니다.
• Index 사용 여부가 아니라 총 Buffers·Reads·Elapsed와 병렬·Direct Path 조건으로 비교합니다.

13. Covering과 후행 Column 추가

다음 Index를 가정합니다.

CREATE INDEX orders_x3
ON orders(
  customer_id,
  status,
  order_date,
  order_id,
  amount
);

조건과 SELECT Column이 모두 Index에 있으면 Table Access를 생략할 수 있습니다.

Index-only Access
  → Table Buffers 감소 가능

그러나 Oracle 일반 B-tree에서 추가 Column은 Index Entry의 일부입니다.

• Entry 폭 증가
• Leaf Block 수와 Tree 크기 증가
• Buffer Cache 점유
• INSERT·DELETE·Key UPDATE 비용
• Redo·Undo·Storage 증가
• 다른 SQL의 Plan 선택 변화
• Hot Leaf·Block Split 가능성

Covering은 실행 빈도와 Table Access 절감량이 유지비보다 큰지 측정해 결정합니다.

14. 동일 조건 검증

인덱스 후보 비교는 같은 조건에서 수행합니다.

• 동일 SQL Text·Hint
• 동일 Bind 값·데이터 타입
• 동일 Fetch 범위
• 동일 동시성
• 동일 Cache 조건 또는 차이 기록
• 동일 통계정보·Optimizer 환경
• 동일 PDB·Service·Instance

확인 지표입니다.

15. 진단 절차

1. 인덱스 Column 순서를 왼쪽부터 기록한다.
2. WHERE·JOIN Predicate를 각 Column에 대응한다.
3. 선행 Equality 조건이 어디까지 이어지는지 표시한다.
4. 첫 Range 조건을 찾고 사전식 Start·Stop 범위를 그린다.
5. 후행 조건이 Index Filter인지 Table Filter인지 확인한다.
6. Predicate Information의 access·filter Operation을 확인한다.
7. Starts·A-Rows·Buffers·Reads를 수집한다.
8. Leaf Scan·ROWID 후보·Table Filter 비용을 분리한다.
9. 컬럼 순서 변경·후행 Column 추가·Covering·Full Scan을 비교한다.
10. 같은 Bind·Fetch로 반복 검증하고 DML·다른 SQL Regression을 확인한다.

16. 자주 혼동하는 판단