9장. 인덱스 구조와 활용

1억 건에서 한 건을 3~4번의 블록 읽기로 찾아내는 B*Tree. 그 구조를 이해하면 "왜 인덱스가 있는데 안 타지?"라는 질문에 스스로 답할 수 있게 됩니다.

9.1 B*Tree — 루트, 브랜치, 리프

Oracle의 기본 인덱스는 B*Tree(Balanced Tree)입니다. 이름 그대로 균형 잡힌 트리라서, 어떤 키를 찾든 루트에서 리프까지 내려가는 깊이(height)가 항상 같습니다. 구성 요소는 세 층입니다.

• 루트(root) 블록 — 트리의 꼭대기. 어느 브랜치로 내려갈지 알려주는 이정표.
• 브랜치(branch) 블록 — 중간 안내판. "이 범위의 키는 이쪽 아래로"라는 정보만 담습니다.
• 리프(leaf) 블록 — 실제 인덱스 키 값 + ROWID가 정렬된 채로 저장되는 곳. 그리고 리프끼리는 양방향 링크(doubly linked list)로 연결되어 있어, 범위 검색 시 트리를 다시 오르지 않고 옆으로 쭉 읽어나갈 수 있습니다.

ROWID는 "이 행이 어느 데이터파일, 어느 블록, 몇 번째 슬롯에 있는가"를 담은 물리 주소입니다. 인덱스에서 키를 찾으면 ROWID를 얻고, 그 주소로 테이블 블록을 단 한 번에 찍어서 갑니다. 전체 과정을 애니메이션으로 봅시다.

9.2 인덱스 access path 5종 — 실행계획에서 만나는 이름들

같은 인덱스라도 옵티마이저가 사용하는 방식은 다양합니다. 실행계획에 등장하는 대표적인 5가지를 정리합니다.

-- FAST FULL SCAN이 뜨는 전형적 상황: 인덱스만으로 답이 되는 쿼리
CREATE INDEX emp_dept_sal_idx ON emp(deptno, sal);

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;
-- 필요한 컬럼(deptno, sal)이 모두 인덱스 안에 있으므로
-- 테이블은 아예 안 읽고 INDEX FAST FULL SCAN 가능

9.3 Clustering Factor — 인덱스 성능의 숨은 지배자

인덱스 탐색 자체는 늘 빠릅니다. 문제는 리프에서 얻은 ROWID로 테이블 블록을 방문하는 횟수입니다. Oracle은 이를 Clustering Factor(CF)라는 통계로 측정합니다. 계산 방법은 단순합니다 — 리프의 키를 순서대로 따라가며 ROWID가 가리키는 테이블 블록이 직전과 달라질 때마다 카운트를 1 올립니다.

• CF ≈ 테이블 블록 수 → 이웃한 키들이 같은 블록에 모여 있음. 좋음.
• CF ≈ 테이블 행 수 → 키마다 매번 다른 블록으로 점프. 나쁨.

9.4 Bitmap Index — 저(低)카디널리티의 무기

성별, 지역, 상태 코드처럼 값의 종류(카디널리티)가 적은 컬럼은 B*Tree로 만들면 효율이 떨어집니다. 이때 Oracle(Enterprise Edition)의 비트맵 인덱스가 등장합니다. 값 하나마다 "각 행이 이 값인가?"를 0/1 비트열로 저장하고, 여러 조건은 비트 연산(AND/OR)으로 순식간에 결합합니다.

9.5 특수 인덱스 3종 — 함수기반, 리버스 키, IOT

함수기반 인덱스 (Function-Based Index)

WHERE 절에서 컬럼을 가공하면(UPPER(name) 등) 일반 인덱스는 사용할 수 없습니다. 가공을 없애는 것이 최선이지만, 불가피하다면 가공된 결과 자체를 인덱싱할 수 있습니다.

-- UPPER(ename) 조건은 ename 인덱스를 못 쓴다 → 함수 결과를 인덱싱
CREATE INDEX emp_upper_ename_idx ON emp (UPPER(ename));

SELECT * FROM emp WHERE UPPER(ename) = 'SCOTT';  -- 이제 인덱스 사용 가능

리버스 키 인덱스 (Reverse Key Index)

시퀀스로 증가하는 PK는 항상 가장 오른쪽 리프 블록에만 INSERT가 몰립니다. 동시 INSERT가 많으면(특히 RAC 환경) 그 블록을 두고 경합이 발생하죠. 리버스 키 인덱스는 키의 바이트를 뒤집어 저장해(1234→4321) INSERT를 여러 리프로 분산시킵니다. 단, 키 순서가 파괴되므로 범위 스캔(BETWEEN, <, >)은 불가능해집니다. 등호 검색만 하는 컬럼에만 고려하세요.

IOT (Index-Organized Table)

보통은 "테이블 따로, 인덱스 따로"지만, IOT는 테이블 자체를 PK 순서의 B*Tree로 저장합니다. 리프 블록에 ROWID 대신 행 데이터 전체가 들어 있어, PK로 조회하면 테이블 접근 단계가 아예 없습니다.

CREATE TABLE code_master (
  code_id   NUMBER PRIMARY KEY,
  code_name VARCHAR2(100)
) ORGANIZATION INDEX;   -- 테이블이 곧 인덱스

PK 중심으로만 접근하는 코드성 테이블, 좁은 테이블에 적합합니다. 반대로 PK 외 컬럼 검색이 많거나 행이 큰 테이블에는 부적합합니다(보조 인덱스가 물리 ROWID를 쓸 수 없어 접근이 한 단계 더 복잡해집니다).

✍️ 이해도 체크