Index Join과 Bitmap Conversion: 여러 인덱스의 ROWID 결합

핵심 요약

Oracle은 한 Table에 여러 Predicate가 있다고 해서 반드시 하나의 Index만 선택하지 않습니다. 같은 Table의 여러 Index Row Source를 결합하는 대표 방식은 Index Join과 Bitmap Conversion입니다.

Index Join
  여러 B-tree Index Scan
  → ROWID 기준 Hash Join
  → Query에 필요한 모든 Column을 Index들에서 획득
  → Table Access 제거

Bitmap Conversion
  B-tree Index가 생성한 ROWID
  또는 영구 Bitmap Index의 Bitmap
  → 임시 Bitmap 집합으로 변환
  → BITMAP AND·OR·MINUS
  → 필요 시 BITMAP CONVERSION TO ROWIDS
  → Table Access

두 방식의 목적은 다릅니다.

Index Join의 핵심
  → 여러 Index가 함께 Query를 Cover
  → Table Access를 항상 피함

Bitmap Conversion의 핵심
  → 여러 후보 Row 집합을 Boolean 연산으로 결합
  → 후보를 줄인 뒤 Table Access가 이어질 수 있음

Oracle 공식 문서에서 Index Join은 여러 Index가 Query의 모든 요청 Column을 반환하고 Table 접근 비용보다 여러 Index Scan·Hash Join 비용이 낮을 때 고려합니다. Index Join은 자주 비싼 후보이며, 가장 선택적인 Index 하나를 사용한 뒤 Table을 읽는 경로가 더 저렴할 수 있습니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 스캔 방식·인덱스 설계 연결 범위에서 Index Join과 여러 Index의 Bitmap 결합을 다룹니다.

학습 목표

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

• Index Join의 ROWID Hash Join 구조를 설명한다.
• VIEW index$_join$_...와 access(ROWID=ROWID)를 해석한다.
• Index Join에서 Table Access가 항상 제거되는 이유를 설명한다.
• Index Join이 성립하지 않는 Projection·Predicate 조건을 판단한다.
• BITMAP CONVERSION FROM ROWIDS와 TO ROWIDS의 방향을 구분한다.
• BITMAP AND·OR·MINUS의 집합 의미를 설명한다.
• 영구 Bitmap Index와 B-tree ROWID의 임시 Bitmap Conversion을 구분한다.
• INDEX_JOIN과 INDEX_COMBINE Hint의 목적과 한계를 설명한다.
• Index Join·Bitmap Conversion·복합 Index·단일 Index+Table Access를 비교한다.
• Starts·A-Rows·Buffers·Reads·Workarea·TEMP·Table Access로 실제 비용을 검증한다.

1. 하나의 Table에서 여러 Index를 사용하는 후보

다음 Table과 Index를 가정합니다.

CREATE TABLE customer (
    customer_id   NUMBER       NOT NULL,
    region_code   VARCHAR2(20) NOT NULL,
    grade_code    VARCHAR2(20) NOT NULL,
    customer_name VARCHAR2(100),
    email         VARCHAR2(200)
);

CREATE INDEX customer_region_ix
ON customer(region_code, customer_id);

CREATE INDEX customer_grade_ix
ON customer(grade_code, customer_id);

Query입니다.

SELECT customer_id
FROM   customer
WHERE  region_code = 'SEOUL'
AND    grade_code  = 'VIP';

Optimizer 후보입니다.

A. region Index
   → ROWID Table Access
   → grade Filter

B. grade Index
   → ROWID Table Access
   → region Filter

C. 두 Index를 ROWID Hash Join
   → customer_id를 Index에서 반환
   → Table Access 제거

D. (region_code,grade_code,customer_id) 복합 Index

E. Table Full Scan

최적 경로는 각 후보 Row 수, Index 크기, Table Row 폭, Clustering, Hash·Bitmap 작업량과 실행 빈도로 결정합니다.

2. Index Join의 공식 정의

Oracle의 Index Join Scan은 같은 Table의 여러 Index를 읽고, 얻은 ROWID를 Hash Join하여 Query가 요청한 모든 Column을 반환하는 Access Path입니다.

VIEW index$_join$_001
  HASH JOIN
    INDEX RANGE SCAN CUSTOMER_REGION_IX
    INDEX RANGE SCAN CUSTOMER_GRADE_IX

핵심 결합 Key입니다.

ROWID = ROWID

업무 Column customer_id가 두 Index에 존재하더라도, 같은 물리 Table Row를 식별하는 내부 Join 기준은 ROWID입니다.

2.1 처리 순서

1. CUSTOMER_REGION_IX에서 SEOUL Entry·ROWID 읽기
2. CUSTOMER_GRADE_IX에서 VIP Entry·ROWID 읽기
3. 두 ROWID 집합을 Hash Join
4. 동일 ROWID만 남김
5. customer_id를 Index Entry에서 반환

2.2 index$_join$ View

index$_join$_001은 사용자가 만든 Schema View가 아니라 Optimizer가 여러 Index를 하나의 Row Source처럼 표현한 내부 View 이름입니다.

VIEW index$_join$_...
  → 여러 Index Row Source의 결합 결과

Plan에서 VIEW, HASH JOIN, 각 Index Scan과 access(ROWID=ROWID)를 함께 확인합니다.

3. Index Join에서 Table Access가 항상 제거되는 이유

Oracle 공식 정의에서 Index Join은 Query에 필요한 모든 데이터를 Index들에서 얻습니다.

WHERE 평가 Column
+ JOIN·GROUP·ORDER BY에 필요한 Column
+ SELECT 반환 Column
= 참여 Index들이 모두 제공

따라서 Table Access가 필요하지 않습니다.

SELECT customer_id
FROM   customer
WHERE  region_code='SEOUL'
AND    grade_code='VIP';

두 Index가 제공하는 정보입니다.

CUSTOMER_REGION_IX
  region_code, customer_id, ROWID

CUSTOMER_GRADE_IX
  grade_code, customer_id, ROWID

Query에 필요한 모든 정보가 있습니다.

3.1 Table Access가 나타나는 경우

SELECT customer_id, customer_name
FROM   customer
WHERE  region_code='SEOUL'
AND    grade_code='VIP';

customer_name이 어느 Index에도 없으면 Table을 읽어야 합니다.

TABLE ACCESS BY INDEX ROWID
  여러 Index 결합 또는 하나의 Index Scan

Table Access가 뒤따르는 여러 Index 경로를 Oracle 공식 의미의 완전한 Index Join과 동일하게 부르지 않고 실제 Plan Operation을 구분합니다.

4. Index Join을 고려할 수 있는 조건

유리할 가능성이 있는 조건입니다.

• 두세 개의 작은 Index가 Query의 모든 Column을 제공
• Table Row가 매우 넓고 Table Block Access가 비쌈
• 각 Index Scan 후보가 지나치게 크지 않음
• ROWID Hash Join 후 후보가 크게 감소
• 기존 Index 재사용이 새 Wide Covering Index보다 저렴
• Workarea Memory 안에서 Hash Join 처리 가능
• 실행 빈도와 CPU 비용이 허용 범위

불리할 가능성이 있는 조건입니다.

• 한 Index가 수백만 Entry를 Full·Fast Full Scan
• 두 후보 집합이 모두 매우 큼
• Hash Workarea가 부족해 TEMP Spill
• 가장 선택적인 Index 하나 + Table Access가 훨씬 작음
• 핵심 OLTP SQL에서 단순·안정적인 Plan이 필요
• 고빈도 SQL이라 작은 CPU 차이가 누적
• 복합 Index가 한 번의 좁은 Range Scan으로 처리 가능

5. Index Join의 비용 구조

Index Join 총비용
  = 첫 번째 Index Scan
  + 두 번째 Index Scan
  + 추가 Index Scan
  + ROWID Hash Build·Probe
  + Workarea Memory
  + TEMP Spill 가능성

5.1 Index Scan량

Oracle 공식 예시처럼 한쪽 Index는 선택적 Range Scan, 다른 Index는 Fast Full Scan이 될 수 있습니다.

INDEX RANGE SCAN EMP_NAME_IX
INDEX FAST FULL SCAN EMP_EMAIL_UK

두 번째 Index 전체를 읽어 필요한 Projection Column을 얻는 비용이 Table Access보다 낮을 때만 의미가 있습니다.

5.2 Hash Join 전후 후보

Region Index A-Rows  2,000,000
Grade Index A-Rows   1,000,000
Hash Join A-Rows        10,000

결합 후 크게 감소하지만 두 Index Scan·Hash 비용은 모두 발생합니다.

5.3 Workarea·TEMP

Hash Join이 PGA Workarea 안에서 처리되지 못하면 TEMP I/O가 발생할 수 있습니다.

확인합니다.

• Hash Join A-Rows
• Memory·One-Pass·Multi-Pass 여부
• TEMP 사용량
• CPU Time
• Elapsed
• 동시 Session의 PGA 압력

6. Bitmap Conversion의 방향

Bitmap Conversion은 Bitmap Entry와 Table Row 위치 사이를 변환합니다.

6.1 BITMAP CONVERSION FROM ROWIDS

B-tree INDEX RANGE SCAN
  → ROWID 목록

BITMAP CONVERSION FROM ROWIDS
  → 실행 중 임시 Bitmap 집합

여러 B-tree가 만든 ROWID 집합을 Boolean 연산하기 위한 방향입니다.

6.2 BITMAP CONVERSION TO ROWIDS

BITMAP AND·OR·MINUS 결과
  → 설정된 Bit

BITMAP CONVERSION TO ROWIDS
  → Table Access에 사용할 ROWID

영구 Bitmap Index를 읽었거나 임시 Bitmap을 만들었든, Table Column이 필요하면 최종 Bitmap을 ROWID로 바꿀 수 있습니다.

7. Bitmap 집합 연산

7.1 AND

Region=SEOUL Bitmap
AND
Grade=VIP Bitmap
→ 두 조건을 모두 만족하는 Row

7.2 OR

Region=SEOUL Bitmap
OR
Grade=VIP Bitmap
→ 둘 중 하나 이상을 만족하는 Row

7.3 MINUS

전체 후보 Bitmap
MINUS
제외 조건 Bitmap
→ 제외 조건을 뺀 Row

NOT, <>, NULL 의미를 보존하기 위해 여러 MINUS Source가 나타날 수 있습니다.

7.4 후보 감소 후 Table Access

TABLE ACCESS BY INDEX ROWID
  BITMAP CONVERSION TO ROWIDS
    BITMAP AND
      BITMAP CONVERSION FROM ROWIDS
        INDEX RANGE SCAN CUSTOMER_REGION_IX
      BITMAP CONVERSION FROM ROWIDS
        INDEX RANGE SCAN CUSTOMER_GRADE_IX

Bitmap 결합은 후보를 줄이는 목적이며, Query에 필요한 Column이 Index들에 모두 없으면 Table Access가 정상적으로 이어집니다.

8. 영구 Bitmap Index와 임시 Bitmap Conversion 구분

8.1 영구 Bitmap Index

BITMAP INDEX SINGLE VALUE CUSTOMER_GRADE_BIX
BITMAP INDEX RANGE SCAN CUSTOMER_AGE_BIX
BITMAP MERGE
BITMAP AND
BITMAP CONVERSION TO ROWIDS

Data Dictionary에서도 확인합니다.

SELECT index_name, index_type
FROM   user_indexes
WHERE  table_name='CUSTOMER';

INDEX_TYPE = BITMAP

8.2 B-tree의 임시 Bitmap

BITMAP CONVERSION FROM ROWIDS
  INDEX RANGE SCAN CUSTOMER_REGION_IX

하위 Operation이 B-tree INDEX RANGE SCAN이면 영구 Bitmap Object가 없어도 실행 중 Bitmap으로 변환한 것입니다.

영구 Bitmap 여부
  = Plan 하위 Object·Operation
  + USER_INDEXES.INDEX_TYPE
  + 실제 DDL

9. Index Join과 Bitmap Conversion 비교

Index Join은 여러 Index Column을 조합해 Projection까지 완성하고, Bitmap Conversion은 집합 조건을 조합해 후보를 줄이는 데 초점을 둡니다.

10. AND·OR 조건과 여러 Index

10.1 AND

WHERE region_code='SEOUL'
AND   grade_code='VIP'

후보입니다.

• 선택적인 Index 하나 + Table Filter
• Index Join
• B-tree ROWID의 Bitmap AND
• 복합 Index
• Table Full Scan

10.2 OR

WHERE region_code='SEOUL'
OR    grade_code='VIP'

Bitmap OR나 OR Expansion·UNION ALL이 후보가 될 수 있습니다. Index Join은 두 조건을 모두 만족하는 동일 ROWID를 Hash Join하는 목적이므로 OR 합집합과는 다릅니다.

10.3 중복 Row

OR를 UNION ALL Branch로 나누면 두 조건을 모두 만족하는 Row의 중복 처리 의미를 확인해야 합니다. Bitmap OR는 Row 집합 Bit를 합쳐 중복 Row 위치를 하나로 표현합니다.

11. 하나의 복합 Index와 비교

다음 복합 Index를 검토합니다.

CREATE INDEX customer_region_grade_ix
ON customer(region_code, grade_code, customer_id);

복합 Index가 유리할 가능성

• Region·Grade 조합이 고정적이고 고빈도
• 선행 Equality로 한 번의 좁은 Range Scan
• Sort·Top-N까지 Key 순서 지원
• 두 단일 Index 후보가 매우 큼
• Hash·Bitmap 결합이 CPU·TEMP를 많이 사용
• OLTP에서 단순·안정적인 Plan이 중요

여러 Index 결합이 유리할 가능성

• 조건 조합이 매우 다양하고 고정 복합 Index 수가 과도해짐
• 기존 작은 Index들이 Query를 Cover
• Table Row가 매우 넓음
• 각 개별 Predicate가 선택적
• 조회 빈도가 낮아 추가 Wide Index의 DML 비용이 불리
• OR 조건의 집합 결합이 필요

Wide Index 비용

복합·Covering Index는 조회를 단순화하지만 다음 비용이 있습니다.

• Segment·Leaf Block 증가
• Buffer Cache 점유
• INSERT·DELETE
• Key Column UPDATE
• Redo·Undo
• Statistics·Backup
• 다른 SQL Plan 변화

12. Hint를 이용한 제한적 실험

12.1 INDEX_JOIN

SELECT /*+ INDEX_JOIN(c customer_region_ix customer_grade_ix) */
       customer_id
FROM   customer c
WHERE  region_code='SEOUL'
AND    grade_code='VIP';

INDEX_JOIN Hint는 Index Join Access Path를 유도합니다. Query를 해결하는 모든 Column을 제공하는 충분히 적은 수의 Index가 있어야 긍정적 효과를 기대할 수 있습니다.

12.2 INDEX_COMBINE

SELECT /*+ INDEX_COMBINE(c customer_region_ix customer_grade_ix) */
       *
FROM   customer c
WHERE  region_code='SEOUL'
OR     grade_code='VIP';

INDEX_COMBINE은 Bitmap·B-tree·Domain Index를 결합하는 후보를 유도할 수 있습니다. 지정한 합법적 Index를 Cost와 무관하게 사용할 수 있으므로 테스트 결과를 운영 정답으로 즉시 고정하지 않습니다.

12.3 Hint 검증

확인합니다.

• 실제 Plan에 적용됐는가
• Hint Report의 Used·Unused·Error
• Outline Data
• Index Join에서 Table Access가 제거됐는가
• Bitmap Conversion의 하위 Object Type
• 대표·극단 Bind의 회귀

13. 실행계획과 Runtime 통계 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       customer_id
FROM   customer
WHERE  region_code=:region
AND    grade_code=:grade;

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

13.1 Index Join

확인 순서입니다.

1. VIEW index$_join$_...가 있는가
2. HASH JOIN access가 ROWID=ROWID인가
3. 각 Index Operation의 Starts·A-Rows·Buffers·Reads
4. 한 Index가 Fast Full Scan인지
5. Hash Join 전후 A-Rows
6. Table Access가 정말 없는가
7. Memory·TEMP·CPU·Elapsed

13.2 Bitmap Conversion

1. FROM ROWIDS 아래 B-tree Index Scan 확인
2. 영구 BITMAP INDEX Operation 확인
3. AND·OR·MINUS Tree 확인
4. 결합 전후 후보 규모 확인
5. TO ROWIDS 뒤 Table Access 확인
6. Bitmap CPU·Memory와 Table Buffers 확인

13.3 Starts·A-Rows 주의

• Starts: Row Source 시작 횟수
• A-Rows: 상위로 반환한 실제 Row 수
• 내부 Filter·Bitmap 처리 전체를 직접 나타내지 않음
• 상위 Operation Buffers는 하위 작업을 포함할 수 있음

모든 Plan Line의 Buffers를 무조건 합산하지 않고 주요 Branch와 Statement 총량을 구분합니다.

14. 적용 판단 절차

1. WHERE·JOIN·SELECT·ORDER BY Column을 분류한다.
2. 기존 Index별 제공 Column·ROWID·크기를 확인한다.
3. 가장 선택적인 단일 Index + Table Access를 계산한다.
4. Index Join이 Query 전체를 Cover하는지 확인한다.
5. Bitmap AND·OR 후 후보 감소율을 예상한다.
6. 복합 Index·Full Scan·OR Expansion을 비교한다.
7. ALLSTATS LAST로 Index·Hash·Bitmap·Table 비용을 측정한다.
8. Workarea·TEMP·동시 Session의 PGA를 확인한다.
9. 신규 Index의 DML·Redo·공간과 다른 SQL 회귀를 측정한다.
10. 동일 Bind·Fetch·동시성에서 최저 전체 Workload 비용을 선택한다.

15. 자주 혼동하는 판단