Index Skip Scan: 선두 컬럼 없이 논리 Subindex 탐색

핵심 요약

INDEX SKIP SCAN은 복합 B-tree Index의 선두 컬럼 또는 선두 Prefix가 Query Predicate에 없을 때, Oracle이 Index를 여러 논리 Subindex로 나누어 후행 Key를 반복 탐색하는 Access Path입니다.

CREATE INDEX customer_gender_email_ix
ON customer(gender, email);

SELECT customer_id
FROM   customer
WHERE  email = :email;

gender 값이 F, M처럼 적다면 개념적으로 다음 두 Subindex를 탐색합니다.

Subindex F
  (gender='F', email=:email)

Subindex M
  (gender='M', email=:email)

TABLE ACCESS BY INDEX ROWID BATCHED CUSTOMER
  INDEX SKIP SCAN CUSTOMER_GENDER_EMAIL_IX

Oracle 공식 기준에서 Skip Scan이 유리해질 가능성이 큰 조건은 다음과 같습니다.

선두 컬럼 조건 없음
+ 선두 Key의 Distinct Value가 상대적으로 적음
+ 비선두 Key의 Distinct Value가 많고 Predicate가 선택적
+ 각 Subindex의 Leaf Range와 Table Access가 작음

Skip Scan의 비용은 단순히 선두 NDV 하나로 결정되지 않습니다.

Skip Scan 총비용
  ≈ 논리 Subindex 수직 탐색
   + 각 Subindex Leaf Scan
   + Index Filter
   + 후보 ROWID
   + Table Block Access
   + 상위 Iterator·Nested Loops의 반복 Starts

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 스캔 방식 범위에서 Index Skip Scan의 구조·통계·대안 비교를 다룹니다.

학습 목표

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

• Skip Scan과 일반 Range Scan의 차이를 설명한다.
• 논리 Subindex와 선두 NDV의 관계를 설명한다.
• 후행 Equality·Range Predicate가 Skip Scan 비용에 미치는 영향을 설명한다.
• 여러 선두 컬럼이 생략됐을 때 실제 Prefix 조합 수를 판단한다.
• 선두값 편중·컬럼 상관관계·Column Group Statistics의 역할을 설명한다.
• Skip Scan의 access와 filter가 동시에 나타날 수 있는 이유를 설명한다.
• Starts와 내부 논리 Subindex Probe 수를 구분한다.
• Covering·Clustering Factor·Table Access 비용을 포함해 판단한다.
• Skip Scan·전용 Index·Full Scan 계열을 비교한다.
• INDEX_SS·INDEX_SS_ASC·INDEX_SS_DESC Hint를 실험에 사용한다.

1. 선두 컬럼이 없으면 왜 일반 Range Scan이 어려운가

Index를 다음처럼 정의합니다.

CREATE INDEX customer_gender_email_ix
ON customer(gender, email);

B-tree Key는 사전식으로 정렬됩니다.

F, a@example.com
F, b@example.com
F, z@example.com
M, a@example.com
M, b@example.com
M, z@example.com

선두 Key가 있는 조건입니다.

WHERE gender = 'F'
AND   email  = :email

('F',:email) Prefix를 직접 탐색
→ 일반 INDEX RANGE SCAN

선두 Key가 없는 조건입니다.

WHERE email = :email

같은 Email Key가 F Group과 M Group에 각각 존재할 수 있어 Index 전체에서 하나의 연속 구간으로 모이지 않습니다.

F Group의 email 위치
M Group의 email 위치

따라서 하나의 일반 Range Scan 경계만으로 모든 후보를 찾기 어렵습니다.

2. 논리 Subindex의 원리

Oracle은 Index를 물리적으로 분할하지 않고 선두 Key의 각 값별로 작은 Index가 있는 것처럼 해석합니다.

Physical Index
  (gender,email)

Logical Subindex F
  F,a...
  F,b...
  F,z...

Logical Subindex M
  M,a...
  M,b...
  M,z...

개념적으로 다음 SQL의 합집합과 비슷합니다.

SELECT *
FROM customer
WHERE gender='F'
AND   email=:email

UNION ALL

SELECT *
FROM customer
WHERE gender='M'
AND   email=:email;

각 Subindex에서는 후행 email이 정렬돼 있으므로 Equality 또는 Range 탐색이 가능합니다.

F Prefix 수직 탐색 → email Range
M Prefix 수직 탐색 → email Range

3. Oracle이 Skip Scan을 고려하는 대표 조건

Oracle 공식 Access Path 설명은 다음 두 조건을 핵심으로 제시합니다.

1. 복합 Index의 선두 컬럼이 Query Predicate에 없음
2. 선두 Key의 Distinct Value는 비교적 적고 비선두 Key에는 많은 Distinct Value가 있음

예시입니다.

gender NDV = 2
email NDV  = 10,000,000

WHERE email = :email

Email이 선택적이면 각 성별 Prefix에서 짧은 Range만 확인하면 됩니다.

반대 예입니다.

customer_id NDV = 5,000,000
signup_date Predicate = 최근 2년

많은 논리 Prefix
× 각 Prefix의 넓은 날짜 Range
→ Skip Scan 비용 증가

NDV는 후보 판단의 시작이지 최종 결론이 아닙니다.

4. 후행 Predicate의 형태

4.1 Equality Predicate

WHERE email = :email

Email이 거의 고유하다면 각 Subindex에서 0~소수 Entry만 반환할 수 있습니다.

낮은 선두 NDV
× 선택적인 후행 Equality
→ Skip Scan에 유리할 가능성

4.2 Range Predicate

WHERE signup_date >= :from_date
AND   signup_date <  :to_date

각 Subindex마다 날짜 Range를 반복 탐색합니다.

Prefix F 날짜 Range
Prefix M 날짜 Range
...

날짜 범위가 넓으면 선두 NDV가 낮아도 많은 Leaf Block을 읽을 수 있습니다.

4.3 Prefix LIKE

WHERE email LIKE 'kim%'

고정 Prefix가 있는 LIKE는 각 논리 Subindex에서 Range를 만들 수 있습니다.

F Group의 kim Prefix
M Group의 kim Prefix

Leading Wildcard LIKE '%kim'은 각 Subindex에서도 좁은 Start Key를 만들기 어려워 Skip Scan 이점이 제한됩니다.

5. 여러 선두 컬럼이 생략된 경우

다음 Index를 가정합니다.

CREATE INDEX sales_x1
ON sales(channel_code, region_code, customer_id, sale_date);

다음 SQL은 앞의 두 Key를 지정하지 않습니다.

WHERE customer_id = :customer_id
AND   sale_date   >= :from_date

개념적인 논리 Prefix입니다.

(ONLINE,SEOUL)
(ONLINE,BUSAN)
(STORE,SEOUL)
(STORE,BUSAN)
...

비용은 각 컬럼 NDV를 단순히 곱한 값과 항상 같지 않습니다.

단순 독립 가정
  channel NDV × region NDV

실제 판단
  존재하는 (channel,region) 조합 수
  + 각 조합의 Row 분포
  + customer_id·sale_date와의 상관관계

예를 들어 ONLINE은 서울만, STORE는 부산만 존재한다면 실제 조합 수는 단순 곱보다 작습니다.

6. Column Group Statistics와 상관관계

개별 Column Statistics만 있으면 Optimizer는 여러 선두 컬럼의 관계를 독립적으로 추정할 수 있습니다.

channel_code와 region_code가 강하게 상관됨
→ 단일 Column NDV만으로 조합 수를 잘못 추정 가능

Column Group Statistics는 여러 컬럼을 하나의 단위로 취급해 결합 Cardinality 추정을 개선할 수 있습니다.

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

Column Group Statistics는 실제 Skip Scan 내부 Probe 수를 직접 표시하지는 않지만, 관련 Predicate·Prefix의 Cardinality와 Cost 추정에 도움을 줄 수 있습니다.

7. 데이터 편중과 Bind별 차이

선두 NDV가 2여도 분포는 다를 수 있습니다.

후행 Email Equality가 선택적이면 두 Prefix 모두 짧은 Scan으로 끝날 수 있습니다.

후행 날짜 Range가 넓으면 다음처럼 비용이 편중됩니다.

F Prefix
  → Index 대부분을 Scan

M Prefix
  → 매우 작은 Range

다음을 함께 확인합니다.

• 선두 NDV
• Prefix별 Row 수
• 후행 값의 Histogram·편중
• 선두·후행 컬럼의 상관관계
• 대표 Bind·극단 Bind의 실제 A-Rows·Buffers

평균 Selectivity 하나로 모든 Bind 비용을 일반화하지 않습니다.

8. NULL 선두 Key 주의

일반 B-tree는 모든 Index Key Column이 NULL인 Row만 저장하지 않습니다.

(gender,email)

gender=NULL
email='a@example.com'
→ 전체 Key가 NULL이 아니므로 Entry 존재 가능

따라서 선두 컬럼이 Nullable이면 논리적으로 NULL Prefix Group도 존재할 수 있습니다.

gender=NULL Subindex
gender='F' Subindex
gender='M' Subindex

실제 Data·Statistics·Predicate를 기준으로 판단하고 F·M 두 번만 Probe한다처럼 고정하지 않습니다.

9. 실행계획의 Access·Filter

가능한 Plan입니다.

TABLE ACCESS BY INDEX ROWID BATCHED CUSTOMER
  INDEX SKIP SCAN CUSTOMER_GENDER_EMAIL_IX

Predicate Information 예시입니다.

access(email=:email)
filter(email=:email)

같은 후행 Predicate가 access와 filter 양쪽에 나타날 수 있습니다.

access
  → 각 논리 Subindex의 탐색 경계에 사용

filter
  → 읽은 Entry가 실제 조건을 만족하는지 추가 확인

Operation 이름이나 Predicate Text만으로 Leaf Scan량을 확정하지 않습니다.

Index A-Rows 10
Index Buffers 5,000
→ 내부적으로 넓은 Scan·Filter 가능성

10. Starts와 내부 Probe 수를 구분한다

V$SQL_PLAN_STATISTICS_ALL.LAST_STARTS는 마지막 실행에서 해당 Row Source가 시작된 횟수입니다.

INDEX SKIP SCAN Starts = 1

이는 상위 Operation이 Skip Scan Row Source를 한 번 시작했다는 의미입니다.

Starts = 1
  ≠ 논리 Subindex가 1개

Skip Scan 내부에서 F·M·NULL 등 여러 Subindex를 탐색해도 하나의 Row Source 내부 작업으로 처리될 수 있습니다.

반대로 다음처럼 Starts가 클 수 있습니다.

NESTED LOOPS
  INDEX SKIP SCAN
    Starts = 50,000

상위 Driving Row마다 Skip Scan 전체가 반복된 것입니다.

총비용
  = Starts
  × Skip Scan 1회 내부 논리 Subindex 작업

11. Covering·Table Access·Clustering

11.1 Covering

CREATE INDEX customer_gender_email_ix
ON customer(gender, email, customer_id);

SELECT customer_id
FROM   customer
WHERE  email=:email;

필요한 Column이 Index에 모두 있으면 Table Access를 생략할 수 있습니다.

INDEX SKIP SCAN
  → Index-only 결과 가능

11.2 Table Access

SELECT customer_name
FROM   customer
WHERE  email=:email;

customer_name이 Index에 없으면 ROWID Table Access가 필요합니다.

11.3 Clustering Factor

Skip Scan이 많은 ROWID를 반환할 때 Table Row가 넓은 Block에 분산돼 있으면 Table Buffers가 커질 수 있습니다.

같은 후보 10,000 ROWID

Clustering 양호
  → 500 Table Blocks

Clustering 불량
  → 9,000 Table Blocks

후행 Predicate 선택도만이 아니라 Table Access 비용을 포함합니다.

12. 대안 Access Path 비교

대안 A: INDEX SKIP SCAN

장점:

• 기존 복합 Index 재사용
• 신규 Index DML·공간 비용 없음
• 낮은 선두 NDV와 선택적인 후행 조건에서 유리 가능

단점:

• 논리 Subindex 반복
• Cardinality·Statistics 변화에 따라 Plan 변동 가능
• 핵심 고빈도 SQL에서는 누적 비용이 큼

대안 B: 후행 컬럼 전용 Index

CREATE INDEX customer_email_ix
ON customer(email);

장점:

• Email Key로 직접 한 번 Range Scan
• Plan 단순·안정 가능

단점:

• Segment·Cache·DML·Redo·Undo 증가
• 기존 Index와 중복 가능

대안 C: 다른 복합 Index

CREATE INDEX customer_email_gender_ix
ON customer(email, gender);

Email 중심 SQL을 직접 지원하지만 Gender 중심 SQL 공용성과 DML을 함께 평가합니다.

대안 D: INDEX FULL·FAST FULL SCAN

Skip Scan의 논리 Prefix 수와 각 Range가 커지면 Index 전체 Scan이 더 저렴할 수 있습니다.

대안 E: TABLE ACCESS FULL

Table이 작거나 결과 비율이 크고 ROWID Table Access가 많으면 Full Scan이 더 유리할 수 있습니다.

13. Skip Scan과 Index Join·Bitmap Conversion 차이

INDEX SKIP SCAN은 하나의 Index 내부 Access Path입니다.

14. Hint로 후보 비교

Oracle은 다음 Hint를 제공합니다.

/*+ INDEX_SS(c customer_gender_email_ix) */
/*+ INDEX_SS_ASC(c customer_gender_email_ix) */
/*+ INDEX_SS_DESC(c customer_gender_email_ix) */

• INDEX_SS: Skip Scan 유도
• INDEX_SS_ASC: Ascending 방향 Skip Scan 유도
• INDEX_SS_DESC: Descending 방향 Skip Scan 유도

Hint는 테스트 환경에서 후보 Access Path의 실제 비용을 비교하는 수단으로 사용합니다.

Hint 사용
  → 반드시 적용됐다고 가정 금지
  → 실제 Plan·Hint Report·Outline 확인

운영 SQL에 고정하기 전에 Statistics·Index 설계·Bind 편중을 먼저 해결합니다.

15. 실제 검증 절차

SELECT /*+ GATHER_PLAN_STATISTICS */
       customer_id
FROM   customer
WHERE  email=:email;

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

확인 순서입니다.

1. Index Key 순서와 생략된 선두 Prefix를 표시한다.
2. 선두 NDV·NULL·실제 조합 수를 확인한다.
3. 후행 Predicate의 형태와 선택도를 확인한다.
4. INDEX SKIP SCAN·access·filter를 확인한다.
5. LAST_STARTS·LAST_OUTPUT_ROWS를 확인한다.
6. LAST_CR_BUFFER_GETS·LAST_DISK_READS를 확인한다.
7. Table A-Rows·Buffers·Clustering 비용을 확인한다.
8. 전용 Index·Full·Fast Full·Table Full 후보를 비교한다.
9. 동일 Bind Type·Fetch·동시성에서 반복 측정한다.
10. 신규 Index DML·Redo·공간과 다른 SQL 회귀를 포함한다.

16. 자주 혼동하는 판단