복합 인덱스 설계 원칙: 조건·정렬·수행 빈도·DML 비용

핵심 요약

Oracle 공식 문서는 복합 인덱스를 여러 컬럼으로 구성된 Concatenated Index로 설명하며, 컬럼은 해당 인덱스를 실제로 사용하는 Query에 가장 적합한 순서로 배치해야 한다고 안내합니다. 복합 인덱스는 일반적으로 전체 Key 또는 선행 부분(Leading Portion)을 사용하는 Query에 유리하며, 선두 컬럼이 빠진 경우에도 조건에 따라 Skip Scan이 선택될 수 있습니다.

좋은 복합 인덱스
  = Critical SQL의 Leaf Scan·ROWID·Sort 감소
  + 여러 핵심 SQL의 공용성
  + 필요한 Top-N·정렬·Covering 지원
  - DML·Redo·Undo·공간·경합·유지보수 비용

복합 인덱스 설계는 다음과 같은 단일 암기 규칙으로 해결되지 않습니다.

잘못된 단일 규칙
  → NDV가 가장 큰 컬럼을 무조건 선두
  → Equality 컬럼을 무조건 모두 Range 앞에 배치
  → SELECT 컬럼을 전부 포함해 Covering
  → ORDER BY 컬럼을 넣으면 Sort가 항상 제거

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

1. Critical Access Path와 목표 지표 선정
2. SQL별 필수·선택 Predicate, Operator, Bind 분포 정리
3. 선행 Equality Prefix와 첫 Range 후보 결정
4. ORDER BY·Top-N·Paging·IN-List 요구 반영
5. Table Access·Clustering·Covering 비용 평가
6. 기존 Index와 중복·Constraint·Partition 속성 확인
7. 동일 조건에서 후보별 실제 Plan·Runtime 통계 비교
8. DML·공간·동시성·다른 SQL 회귀 검증
9. Invisible Index·Canary·Rollback을 포함해 배포

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 설계 범위에 해당합니다. B-tree 구조, Access·Filter Predicate와 Start·Stop Key는 앞선 이론을 전제로 하며, Clustering Factor·Table Access 최소화는 후속 이론과 연결합니다.

학습 목표

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

• Critical Access Path를 Workload·SLA·업무 중요도로 선정한다.
• 복합 인덱스의 Leading Portion과 선행 Equality Prefix를 설명한다.
• 필수 조건과 선택 조건을 구분해 컬럼 순서를 결정한다.
• 첫 Range 뒤 후행 컬럼이 Leaf Scan과 ROWID 후보에 미치는 영향을 설명한다.
• 선택도·카디널리티·NDV·데이터 편중을 구분한다.
• ORDER BY·Top-N·Paging과 ASC·DESC 컬럼 방향을 함께 설계한다.
• IN-List가 여러 Range와 Sort를 만들 수 있음을 설명한다.
• Covering Index의 조회 이득과 폭·DML 비용을 비교한다.
• Clustering Factor가 큰 Range Scan의 Table I/O에 미치는 방향을 설명한다.
• 기존 인덱스의 중복·포함 관계와 Constraint 용도를 확인한다.
• Invisible Index로 제거·신규 후보를 제한적으로 검증한다.
• Starts·A-Rows·Buffers·Reads·Sort·Elapsed와 DML 지표로 후보를 검증한다.

1. Workload가 설계의 출발점이다

모든 조건 조합을 지원하는 인덱스를 만들 수는 없습니다. 먼저 성능을 반드시 보장해야 하는 SQL과 업무 흐름을 선정합니다.

실행 빈도가 낮아도 전체 업무를 장시간 중단시키는 Batch는 Critical Access Path일 수 있습니다. 반대로 자주 실행되지만 이미 실행당 1~2 Block만 읽는 SQL은 인덱스 변경 우선순위가 낮을 수 있습니다.

2. 복합 인덱스의 Leading Portion

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

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

Key는 다음 순서로 정렬됩니다.

customer_id
  → 같은 customer_id에서 status
    → 같은 customer_id·status에서 order_date
      → 같은 값에서 order_id와 ROWID

Oracle은 일반적으로 전체 Key 또는 선행 부분을 조건에 사용하는 Query에서 이 인덱스를 효율적으로 사용할 수 있습니다.

활용하기 쉬운 조건
  customer_id
  customer_id + status
  customer_id + status + order_date

선두가 빠진 조건
  status
  order_date
  status + order_date
  → 일반 Range Scan의 연속 Prefix가 아님
  → Skip Scan·Full/Index Full Scan 등 다른 경로 검토

2.1 Skip Scan은 예외이지 기본 설계 목표가 아니다

선두 컬럼의 NDV가 작고 비선두 조건이 선택적이면 Optimizer는 Logical Subindex를 반복 탐색하는 INDEX SKIP SCAN을 선택할 수 있습니다.

Index: (gender, email)
Predicate: email = :email

gender='F' Group 탐색
gender='M' Group 탐색

Skip Scan이 가능하다는 이유로 핵심 SQL의 선행 컬럼을 의도적으로 생략하지 않습니다. 선두 NDV·반복 Starts·Buffers를 실제로 검증합니다.

3. 필수 조건과 선택 조건을 구분한다

3.1 항상 존재하는 조건

Multi-Tenant 환경에서 모든 핵심 SQL에 다음 조건이 포함된다고 가정합니다.

WHERE tenant_id = :tenant_id
AND   customer_id = :customer_id

tenant_id는 단순 NDV 외에도 다음 이유로 선행 후보가 될 수 있습니다.

• 모든 핵심 Query에 필수
• 업무·보안 격리 Prefix
• Tenant별 Partition·Clustering과 연계
• 동일 customer_id가 Tenant마다 중복될 수 있음

3.2 자주 생략되는 조건

주요 SQL이 다음 두 유형이라면 status의 위치를 신중하게 결정합니다.

-- SQL A
WHERE customer_id = :customer_id
AND   status = :status
AND   order_date >= :from_date

-- SQL B
WHERE customer_id = :customer_id
AND   order_date >= :from_date

(customer_id, status, order_date)
  → A는 좁은 Prefix + 날짜 Range
  → B는 상태 Group들을 반복·광범위하게 읽을 수 있음

(customer_id, order_date, status)
  → B 공용성이 높음
  → A에서 status는 첫 Range 뒤 Filter 성격이 커질 수 있음

어느 후보가 좋은지는 SQL A·B의 빈도·날짜 범위·상태별 분포·최종 Row 수로 결정합니다.

4. Equality와 첫 Range

복합 인덱스에서는 선행 Equality가 연속될수록 다음 Range를 좁게 사용할 수 있습니다.

후보 A: (customer_id, status, order_date)

customer_id = :customer
status      = :status
order_date >= :from_date

(customer_id,status) Prefix 고정
  → 해당 Group의 order_date 시작점
  → Group 종료까지 연속 Scan

첫 Range 뒤의 컬럼은 인덱스에 존재하더라도 넓은 Leaf Scan을 완전히 줄이지 못할 수 있습니다.

후보 B: (customer_id, order_date, status)

customer_id = :customer
order_date  >= :from_date   ← 첫 Range
status       = :status      ← 후행 Filter 가능

후보 B의 status는 Table에 전달할 ROWID를 줄일 수 있지만, 넓은 날짜 Range의 Index Buffers가 남을 수 있습니다.

5. 선택도·카디널리티·NDV·편중

5.1 선택도

선택도 = 조건 결과 행 수 ÷ 전체 행 수

선택도 값이 낮을수록 적은 Row를 선택합니다.

5.2 카디널리티

SQL 튜닝 문맥의 카디널리티는 Row Source가 반환할 것으로 예상하거나 실제 반환한 Row 수입니다.

예상 카디널리티
  ≈ 전체 Row × 예상 선택도

5.3 NDV

NDV는 서로 다른 값의 개수입니다. 균등 분포라면 Equality 평균 선택도를 1/NDV로 근사할 수 있지만 실제 분포는 다를 수 있습니다.

STATUS NDV = 3

PAID      80%
CANCELLED 15%
ERROR      5%

같은 Column도 Bind 값에 따라 후보 Row 수가 16배 차이날 수 있습니다. NDV만 보고 컬럼 순서를 정하지 않고 다음을 확인합니다.

• 값별 빈도·Histogram
• 조건 필수 여부
• Operator(=, Range, IN)
• 다른 컬럼과의 상관관계
• SQL 수행 빈도
• 반환 Row·Table Access
• 대표·극단 Bind

5.4 “변별력 높은 컬럼”이라는 모호한 표현

다음처럼 수치로 표현합니다.

customer_id=:id
  → 평균 1~5행
  → 모든 핵심 SQL의 필수 Equality

status='PAID'
  → 전체의 80%
  → 일부 SQL에서 생략

order_date 최근 90일
  → 고객별 평균 2,000행

이렇게 해야 선두 컬럼 순서를 객관적으로 비교할 수 있습니다.

6. ORDER BY·Top-N·Paging

다음 Query를 가정합니다.

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
AND    status = :status
ORDER BY order_date DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

후보입니다.

CREATE INDEX orders_x2
ON orders(
  customer_id,
  status,
  order_date DESC,
  order_id DESC
);

선행 Equality가 고정되고 나머지 Index 방향이 ORDER BY와 호환되면 Sort 없이 앞쪽 20건에서 멈출 가능성이 있습니다.

다음을 실제 Plan에서 확인합니다.

• INDEX RANGE SCAN 또는 DESCENDING
• SORT ORDER BY, SORT ORDER BY STOPKEY 존재 여부
• Index가 반환한 후보 Row와 최종 20행
• Fetch가 첫 페이지인지 전체 Fetch인지
• NULLS FIRST/LAST 요구와 Index 순서
• Partition별 정렬과 전체 정렬
• 다른 Bind에서도 Stopkey가 일찍 작동하는지

6.1 ASC·DESC 전체 역방향과 혼합 방향

(A ASC,B ASC) 인덱스는 정방향으로 A ASC,B ASC, 전체 역방향으로 A DESC,B DESC를 지원할 수 있습니다. A ASC,B DESC와 같은 혼합 방향에는 해당 방향의 Index가 필요할 수 있습니다.

CREATE INDEX orders_mix_ix
ON orders(customer_id ASC, order_date DESC);

결과 순서가 필요하면 현재 Plan의 Index 순서에 의존하지 않고 ORDER BY를 명시합니다.

6.2 Descending Index 통계

Oracle은 Descending Index를 Function-Based Index처럼 취급하며, Table과 Index 통계가 수집돼야 Optimizer가 사용 여부를 판단하기 쉽습니다.

7. IN-List와 전역 정렬

WHERE region_code = 'SEOUL'
AND   blood_type IN ('A','O')
ORDER BY age

후보 (region_code,blood_type,age)는 값별로 다음 두 Range를 읽을 수 있습니다.

('SEOUL','A',age...)
('SEOUL','O',age...)

각 Range 내부에서는 age 순서지만 두 Range를 합친 결과가 전체 age 순서를 자동 보장하지 않아 Sort가 남을 수 있습니다.

후보 (region_code,age,blood_type)는 서울 고객을 age 순서로 읽으면서 blood_type을 Filter할 수 있지만 서울 구간이 크면 Leaf Scan량이 증가할 수 있습니다.

조건 축소 우선
  → (region_code,blood_type,age)

정렬·Top-N 우선
  → (region_code,age,blood_type)

비교 항목입니다.

• IN 값 수와 Index Starts
• 각 Range의 A-Rows·Buffers
• 전체 서울 Row 수
• Filter 통과율
• Sort·TEMP
• Top-N 조기 종료 여부
• 실행 빈도

8. Covering Index와 Table Access

다음 후보를 봅니다.

(customer_id, status, order_date DESC, order_id DESC, amount)

조건과 반환 컬럼이 모두 Index에 있으면 Table Access를 생략할 수 있습니다.

Index-only Access
  → Table ROWID Access·Table Buffers 감소 가능

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

• Entry 폭·Leaf Block·Tree 크기 증가
• Buffer Cache 점유
• INSERT·DELETE·Key UPDATE 비용
• amount 변경 시 Index UPDATE
• Redo·Undo·Storage·Backup 증가
• Block Split·Hot Leaf 가능성
• 다른 SQL의 Plan 변화

Covering 여부는 “Table Access가 0”이라는 사실만으로 결정하지 않습니다. Index Buffers와 전체 Workload 비용을 비교합니다.

9. Clustering Factor와 Table Access

Index Range Scan으로 많은 ROWID를 반환할 때 물리적으로 가까운 Key가 같은 Table Block에 모여 있으면 Table Access가 상대적으로 효율적입니다.

낮은 Clustering Factor 방향
  → 인접 Index Entry가 같은·인접 Table Block을 가리킴
  → 큰 Range Scan의 Table I/O가 상대적으로 적을 수 있음

높은 Clustering Factor 방향
  → ROWID가 넓은 Table Block에 분산
  → Block을 읽고 다시 방문하는 비용 증가 가능

따라서 같은 후보 Row 수라도 Table Buffers가 크게 다를 수 있습니다. Column 순서·Table 물리 배치·BATCHED ROWID Access를 함께 봅니다.

10. 기존 인덱스와 중복 관계

새 인덱스를 만들기 전에 다음을 조사합니다.

• 동일한 선행 Column을 가진 Index
• 새 후보가 기존 Index를 포함하는지
• Unique·Primary Key·Foreign Key Constraint 용도
• Visible·Invisible·Unusable 상태
• Local·Global, Prefixed·Nonprefixed 속성
• Partitioning Key 포함 여부
• ASC·DESC·Function-Based 표현식
• Compression·Storage·Tablespace
• 실제 사용 SQL과 Monitoring 자료

기존: (customer_id, order_date)
신규: (customer_id, order_date, status)

신규가 기존 SQL을 모두 대체할 가능성
  → 있음

그러나 자동 대체 확정 불가
  → Index 폭·Clustering·정렬·Constraint·Partition 속성 확인

11. Invisible Index를 이용한 검증

Oracle의 Invisible Index는 DML에 의해 계속 유지되지만 기본적으로 Optimizer가 Query Plan에 사용하지 않습니다.

11.1 신규 후보 제한 테스트

CREATE INDEX orders_candidate_ix
ON orders(customer_id, status, order_date DESC)
INVISIBLE;

검증 Session에서만 다음 Parameter를 사용할 수 있습니다.

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

운영 전체 Plan에 즉시 노출하지 않고 후보를 비교할 수 있습니다.

11.2 제거 전 영향 테스트

기존 Index를 Invisible로 바꿔 기본 Optimizer 선택에서 제외한 뒤 회귀를 관찰할 수 있습니다.

ALTER INDEX old_orders_ix INVISIBLE;

Invisible Index도 DML로 유지되므로 DML 비용 제거 효과를 측정하는 방법은 아닙니다. Query Plan 의존성 확인에 사용합니다.

12. DML·공간·동시성 비용

Oracle은 Table의 DML이 발생하면 관련 Index를 자동 유지합니다. Index가 많고 넓을수록 쓰기 비용이 증가합니다.

INSERT

• 모든 관련 Index에 Entry 삽입
• Buffer·Undo·Redo 증가
• Leaf 공간 부족 시 Split 가능
• 순차 증가 Key는 Right-Hand Hot Leaf 경합 가능

DELETE

• 관련 Index Entry를 삭제 상태로 처리
• 공간은 재사용될 수 있지만 Segment가 즉시 축소되지는 않음
• 많은 Index에 삭제 작업이 반복

UPDATE

• Index Key 컬럼 변경 시 기존 Entry 삭제 + 새 Entry 삽입
• Covering 후행 컬럼도 변경되면 Index 유지 작업 발생
• 여러 Index에 포함된 Column 변경은 비용이 누적

운영 비용

• Segment·Backup·Recovery·통계 수집 시간
• Cache 점유
• Online Build·배포 시간
• Index Rebuild·Coalesce 판단 비용
• RAC Hot Block 가능성
• Optimizer 후보 증가

13. 후보 비교 예제

주요 SQL입니다.

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
AND    status = :status
AND    order_date >= :from_date
ORDER BY order_date DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

후보 A

(customer_id,status,order_date DESC,order_id DESC)

• Equality Prefix와 날짜 Range·정렬을 지원
• 상태 조건이 없는 SQL에서는 공용성 저하 가능
• amount는 Table에서 읽음

후보 B

(customer_id,order_date DESC,order_id DESC,status)

• 상태 조건이 없는 고객별 최신 주문에도 활용 가능
• status는 첫 Range 뒤 Filter
• 날짜 범위가 넓고 PAID 비율이 낮으면 Index Buffers 증가 가능

후보 C

(customer_id,status,order_date DESC,order_id DESC,amount)

• Covering으로 Table Access 제거 가능
• Index 폭·DML·Redo·Cache 비용 증가

후보 D

Full Table Scan 또는 Partition Scan

• 결과 비율이 크거나 고객·기간 조건이 넓으면 대량 Scan이 유리할 수 있음
• Partition Pruning·Parallel·Direct Path 조건 확인

14. 실제 실행계획과 통계로 검증한다

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
AND    status = :status
AND    order_date >= :from_date
ORDER BY order_date DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

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

14.1 대표 Bind만 테스트하지 않는다

• 결과 0~1건의 선택적 Bind
• 평균 Bind
• 대량·편중 Bind
• 상태 조건 존재·생략
• 짧은 기간·긴 기간
• Top-N 첫 페이지·전체 Fetch
• Peak 동시성

을 분리해 측정합니다.

15. 설계 절차

1. Critical SQL·SLA·수행 빈도·동시성을 수집한다.
2. WHERE·JOIN·ORDER BY·SELECT 컬럼을 분리한다.
3. 필수 Equality와 선택 조건을 구분한다.
4. 첫 Range와 예상 Leaf Scan을 표시한다.
5. ORDER BY·Top-N·IN-List·Paging을 반영한다.
6. Covering·Clustering·Table Access 비용을 계산한다.
7. 기존 Index·Constraint·Partition·정렬 방향과 중복을 점검한다.
8. 후보를 Invisible 또는 검증 환경에서 생성한다.
9. 동일 Bind·Fetch·동시성으로 Runtime 통계를 비교한다.
10. DML·공간·다른 SQL 회귀와 Rollback을 확인한다.
11. Canary 배포 후 Monitoring 기준으로 채택·원복한다.

16. 자주 혼동하는 판단