Index Unique·Range·Descending Scan: 탐색 범위와 정렬 방향

핵심 요약

Oracle B-tree Index의 Scan 이름은 Constraint 이름이나 “인덱스를 사용했다”는 사실만으로 결정되지 않습니다. 실제 Index의 고유성, SQL Predicate가 고정하는 Key, 읽는 방향과 범위가 Operation을 결정합니다.

정확한 판단 순서

1. USER_INDEXES의 UNIQUENESS와 Index Type 확인
2. USER_IND_COLUMNS의 Key 순서·ASC/DESC 확인
3. Predicate가 고정한 선행 Key 확인
4. 논리적 Start·Stop 경계와 Scan 방향 표시
5. Index Access·Filter와 Table Access 구분
6. Starts·A-Rows·Buffers·Reads·Sort·Fetch 범위 검증

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 스캔 방식 범위에서 Unique·Range·Descending·MIN/MAX Scan을 다룹니다. Index Full·Fast Full·Skip Scan은 인접 이론과 연결합니다.

학습 목표

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

• INDEX UNIQUE SCAN의 정확한 선택 조건을 설명한다.
• PK·UK Constraint와 실제 Index UNIQUENESS의 차이를 설명한다.
• 복합 Unique Index의 일부 Key가 Range Scan이 되는 이유를 설명한다.
• Range Scan의 양쪽 제한·하한 제한·상한 제한을 구분한다.
• Prefix LIKE, Leading Wildcard와 INLIST 반복 Range를 구분한다.
• Nonunique 중복 Key의 ROWID 정렬을 설명한다.
• INDEX RANGE SCAN DESCENDING의 방향과 정렬 활용을 설명한다.
• Ascending Index의 전체 역방향 Scan과 혼합 ASC·DESC Index를 구분한다.
• ORDER BY가 결과 순서 보장의 출발점인 이유를 설명한다.
• Top-N·MIN/MAX의 조기 종료와 실제 Plan 검증 항목을 설명한다.
• Access·Filter Predicate와 Table Access 비용을 구분한다.
• INDEX·INDEX_DESC Hint의 역할과 한계를 설명한다.

1. 공통 원리: 수직 탐색과 Leaf Range

다음 Index를 가정합니다.

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

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

customer_id
  → 같은 customer_id 안에서 order_date
    → 같은 customer_id·order_date 안에서 order_id
      → Nonunique 중복 Key 안에서 ROWID

Range 계열 Scan은 다음 순서로 동작합니다.

Root·Branch 수직 탐색
  → Start Key가 있을 Leaf 탐색

Leaf 수평 탐색
  → 순방향 또는 역방향으로 Entry 읽기

Stop 경계 도달
  → 더 이상 조건을 만족할 Key가 없으면 종료

필요한 Column이 Index에 없음
  → ROWID Table Access

성능은 Operation 이름보다 다음 작업량에 좌우됩니다.

• 시작 Leaf를 얼마나 정확히 찾는가
• 몇 Leaf Block·Entry를 읽는가
• 몇 ROWID를 Table로 전달하는가
• 몇 Table Block을 방문하는가
• Sort·Top-N에서 얼마나 일찍 중단하는가

2. INDEX UNIQUE SCAN

2.1 정확한 선택 조건

Oracle은 다음 조건을 모두 만족할 때 Unique Scan을 고려합니다.

1. 실제 Access에 사용하는 Index가 UNIQUE
2. Predicate가 Unique Index의 모든 Key Column을 등치로 지정
3. 하나의 Key에 가능한 ROWID가 0개 또는 1개

CREATE UNIQUE INDEX orders_pk
ON orders(order_id);

SELECT *
FROM   orders
WHERE  order_id = :order_id;

TABLE ACCESS BY INDEX ROWID ORDERS
  INDEX UNIQUE SCAN ORDERS_PK

SELECT *에 Index에 없는 Column이 있으므로 Unique Scan 뒤 Table Access가 나타날 수 있습니다.

2.2 복합 Unique Index

CREATE UNIQUE INDEX order_item_uk
ON order_item(order_id, line_no);

전체 Key 등치입니다.

WHERE order_id = :order_id
AND   line_no  = :line_no

INDEX UNIQUE SCAN 후보

일부 Key만 지정합니다.

WHERE order_id = :order_id

여러 line_no가 존재할 수 있으므로 다음이 후보입니다.

INDEX RANGE SCAN ORDER_ITEM_UK

Unique Index
  ≠ 모든 Query가 Unique Scan

Unique Scan
  = 실제 Unique Index
  + 전체 Key Equality

2.3 PK·UK Constraint인데 Range Scan이 가능한 이유

Constraint가 결과의 유일성을 보장하더라도 실제 Index가 Nonunique이면 Operation은 Range Scan이 될 수 있습니다.

CREATE TABLE t_table(numcol NUMBER);

CREATE INDEX t_table_ix
ON t_table(numcol);  -- NONUNIQUE

ALTER TABLE t_table
ADD CONSTRAINT t_table_pk
PRIMARY KEY(numcol)
USING INDEX t_table_ix;

SELECT *
FROM   t_table
WHERE  numcol = :numcol;

INDEX RANGE SCAN T_TABLE_IX 가능

Oracle 공식 문서도 Unique·Primary Key Constraint만으로 Unique Scan이 보장되지 않으며, 기존 Nonunique Index를 사용할 수 있음을 설명합니다.

2.4 NULL 주의

Unique Constraint는 NULL을 업무 값처럼 하나로 고정하지 않습니다. 일반 B-tree 단일 Index에는 모든 Key가 NULL인 Row가 저장되지 않고, 복합 Unique Index에서도 일부 NULL 조합은 여러 Row에 존재할 수 있습니다.

IS NULL 조건
  ≠ 전체 Unique Key Equality
  ≠ Unique Scan 보장

실제 Index Entry 존재 여부와 Predicate를 확인합니다.

3. INDEX RANGE SCAN

3.1 정의

INDEX RANGE SCAN은 정렬된 Index에서 Start·Stop 경계 사이를 읽는 Ordered Scan입니다.

가능한 결과
  → 0개
  → 1개
  → 여러 Entry·ROWID

대표 조건입니다.

• Nonunique Index의 등치
• Unique Index의 일부 Key
• >, >=, <, <=
• BETWEEN
• 고정 Prefix LIKE 'ABC%'
• INLIST Iterator 아래 값별 Equality·Range
• 정렬 또는 Top-N을 위한 Ordered Scan

3.2 양쪽 제한 Range

WHERE order_date >= :from_date
AND   order_date <  :to_date

Start
  → from_date 이상 첫 Key

Stop
  → to_date 이상 첫 Key에 도달하기 전

3.3 하한만 제한

WHERE order_date >= :from_date

Start는 있지만 상한은 Index 또는 선행 Prefix Group의 끝입니다. 범위가 매우 넓어질 수 있습니다.

3.4 상한만 제한

WHERE order_date < :to_date

Index 또는 선행 Prefix Group의 시작 쪽부터 상한 전까지 읽을 수 있습니다.

3.5 선행 Equality와 Range

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

customer_id Prefix 고정
  → 해당 고객의 from_date에서 시작
  → 해당 고객의 to_date 전 종료

다른 고객 Group은 읽지 않아도 됩니다.

4. 중복 Key와 ROWID 순서

Nonunique Index에서 같은 Key를 가진 여러 Row는 ROWID로 구분됩니다.

(20, ROWID-A)
(20, ROWID-B)
(20, ROWID-C)

다음 Query는 Range Scan으로 같은 Key의 모든 Entry를 읽을 수 있습니다.

WHERE department_id = 20

Equality Predicate
  + Nonunique Index
  → 하나의 Key Group에 여러 ROWID 가능
  → INDEX RANGE SCAN

Range Scan의 Stop 경계는 “다음 정수”로 외우지 않고 같은 Key의 모든 ROWID Entry가 끝나는 논리적 경계로 이해합니다.

5. LIKE·IN과 Range Scan

5.1 Prefix LIKE

WHERE customer_name LIKE 'KIM%'

고정된 시작 Prefix가 있으므로 연속 Key 범위를 만들 수 있습니다.

Start
  → KIM Prefix의 첫 Entry

Stop
  → KIM Prefix 범위를 벗어난 첫 Key

5.2 Leading Wildcard

WHERE customer_name LIKE '%KIM'

시작 Key를 알 수 없어 일반적인 좁은 Range Scan에 불리합니다. Index Full Scan·Table Full Scan 또는 별도 검색 구조가 후보가 될 수 있습니다.

5.3 INLIST Iterator

WHERE status IN ('PAID','READY')

값별로 여러 불연속 Range가 생길 수 있습니다.

INLIST ITERATOR
  INDEX RANGE SCAN  -- PAID
  INDEX RANGE SCAN  -- READY

실행계획에서는 하나의 하위 Operation이 여러 번 시작될 수 있으므로 Starts를 확인합니다.

IN 값 20개
하위 Index Starts 20
1회 Buffers 5
→ 총 Index Buffers 약 100

6. Access Predicate와 Filter Predicate

다음 Index를 봅니다.

(customer_id, order_date, order_id)

다음 조건은 중간 order_date가 빠져 있습니다.

WHERE customer_id = :customer_id
AND   order_id    = :order_id

개념적 처리입니다.

customer_id
  → Access Prefix

order_id
  → 여러 날짜 Group 안에서 추가 Filter 가능

Plan에는 order_id가 Index Operation의 Filter로 나타나거나 일부 Access 표현에 포함될 수 있지만, 고객 Group의 많은 Leaf Entry를 읽을 수 있습니다.

Predicate Information
  access : Start·Stop 탐색 경계
  filter : 읽은 Entry·Row를 반환 전에 평가

실제 Leaf Scan량은 Index Buffers·Reads와 A-Rows를 함께 확인합니다.

7. INDEX RANGE SCAN DESCENDING

7.1 동작

기본 Ascending Range Scan은 낮은 Key에서 높은 Key 방향으로 읽습니다. Descending Range Scan은 높은 Key에서 낮은 Key 방향으로 Leaf Link를 따라갑니다.

INDEX RANGE SCAN DESCENDING

Oracle은 내림차순 ORDER BY를 만족하거나 상한보다 작은 값에서 큰 값부터 찾을 때 Descending Scan을 고려할 수 있습니다.

7.2 Ascending Index의 역방향 Scan

다음 일반 Index가 있습니다.

CREATE INDEX orders_customer_date_ix
ON orders(customer_id, order_date, order_id);

customer_id가 등치로 고정된 Query입니다.

ORDER BY order_date DESC, order_id DESC

Index 전체 방향을 반대로 읽어 요구 정렬을 지원할 수 있습니다.

Ascending 정의
  (order_date ASC, order_id ASC)

역방향 Scan
  (order_date DESC, order_id DESC)

7.3 혼합 방향

ORDER BY order_date DESC, order_id ASC

일반 Ascending Index를 전체 역방향으로 읽으면 두 Column 모두 DESC가 되므로 요구와 다릅니다.

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

혼합 방향 정렬이 중요하고 자주 실행된다면 해당 방향 Index를 검토합니다.

7.4 Reverse Key Index와 혼동 금지

INDEX RANGE SCAN DESCENDING은 B-tree Leaf를 역방향으로 읽는 Access Path입니다. Reverse Key Index는 Key Byte를 물리적으로 뒤집어 순차 증가 Key의 Hot Block을 분산하는 별도 Index 유형이며 일반 Range·정렬 지원이 제한됩니다.

8. ORDER BY와 순서 보장

Index Scan이 현재 실행에서 정렬된 Row를 생산하더라도 ORDER BY가 없으면 Application이 그 순서를 계약으로 사용하면 안 됩니다.

SELECT order_id
FROM   orders
WHERE  customer_id = :customer_id;

다음 요소로 Access Path와 Row 생산 순서는 바뀔 수 있습니다.

• Statistics
• Bind 값
• Parallel
• 다른 Index
• Optimizer 환경
• Partitioning

업무상 순서가 필요하면 명시합니다.

ORDER BY order_date DESC, order_id DESC

Optimizer가 Index 순서를 이용하면 Sort를 생략할 수 있지만, 순서 보장은 SQL 의미에서 시작합니다.

9. Top-N과 조기 종료

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

적합한 Index와 Plan입니다.

WINDOW NOSORT STOPKEY
  TABLE ACCESS BY INDEX ROWID
    INDEX RANGE SCAN DESCENDING

가능한 이점입니다.

• 최신 Key부터 읽음
• 별도 Sort 제거
• 20행을 얻은 뒤 조기 중단
• 전체 고객 이력을 읽지 않음

확인 항목입니다.

• SORT ORDER BY가 남는가
• NOSORT·STOPKEY가 나타나는가
• Index A-Rows가 N 부근에서 멈추는가
• Table A-Rows·Buffers는 얼마인가
• 첫 페이지와 전체 Fetch 조건이 동일한가
• 편중 고객에서도 조기 종료하는가

10. INDEX RANGE SCAN (MIN/MAX)

다음 Query는 Index Key 범위의 한쪽 끝 Entry만 읽어 최솟값·최댓값을 구할 수 있습니다.

SELECT MAX(order_date)
FROM   orders
WHERE  customer_id = :customer_id;

SORT AGGREGATE
  FIRST ROW
    INDEX RANGE SCAN (MIN/MAX)

적용 가능성을 높이는 조건입니다.

• 선행 Key Predicate가 Index와 호환
• MIN·MAX 대상이 다음 Key로 정렬
• 단순 Aggregate 구조
• 불필요한 함수·추가 Filter가 적음
• 데이터 타입과 표현식이 Index Key와 호환

복잡한 Group By·여러 Aggregate·추가 Row 검사가 있으면 같은 최적화가 적용되지 않을 수 있으므로 실제 Plan을 확인합니다.

11. Index Scan과 Table Access

11.1 Unique Scan 뒤 Table Access

TABLE ACCESS BY INDEX ROWID ORDERS
  INDEX UNIQUE SCAN ORDERS_PK

Unique Scan은 ROWID를 최대 하나 찾지만 SELECT *이면 Table Block 방문은 필요합니다.

11.2 Range Scan 뒤 후보 과다

INDEX A-Rows 100,000
TABLE A-Rows      20
TABLE Buffers 80,000

Scan 방향과 정렬이 맞아도 후보 ROWID가 너무 많고 Table Filter에서 대부분 제거되면 비효율적입니다.

11.3 Covering

조건과 반환 Column이 모두 Index에 있으면 Table Access가 사라질 수 있습니다.

INDEX RANGE SCAN
  → 결과 Column까지 Index에서 반환

다만 넓은 Index의 Leaf Block·DML·Redo·공간 비용을 Workload 전체로 비교합니다.

11.4 BATCHED

TABLE ACCESS BY INDEX ROWID BATCHED는 ROWID를 모아 Table Block 접근 순서를 개선하려는 방식이며 후보 수를 줄이거나 Table Access를 제거하지 않습니다.

12. Hint의 역할과 한계

/*+ INDEX(o orders_x1) */
/*+ INDEX_DESC(o orders_x1) */

• INDEX Hint는 특정 Index 사용을 유도
• INDEX_DESC Hint는 해당 Index의 Descending Scan을 유도

그러나 INDEX Hint로 UNIQUE SCAN과 RANGE SCAN 중 특정 Operation 이름까지 직접 지정하는 것은 아닙니다. 실제 Index 고유성과 Predicate 구조가 Scan Type을 결정합니다.

Hint는 후보 경로 비교에 사용할 수 있지만 운영 고정 전 다음을 검증합니다.

• 대표·편중 Bind
• Statistics 변경
• Top-N·전체 Fetch
• 다른 SQL 회귀
• DML·공간 비용

13. 실제 실행계획 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       order_date
FROM   orders
WHERE  customer_id = :customer_id
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'
  )
);

확인 순서입니다.

1. USER_INDEXES.UNIQUENESS를 확인한다.
2. USER_IND_COLUMNS의 Key 순서·DESC를 확인한다.
3. Unique Scan이면 모든 Key Equality인지 확인한다.
4. Range Scan이면 bounded·unbounded·INLIST를 표시한다.
5. Start·Stop Key와 Asc·Desc 방향을 그린다.
6. Access·Filter Predicate를 구분한다.
7. Starts·E-Rows·A-Rows·Buffers·Reads를 확인한다.
8. Table Access 후보와 최종 Row를 비교한다.
9. Sort·NOSORT·STOPKEY·MIN/MAX를 확인한다.
10. 동일 Bind·Fetch에서 후보 Plan과 회귀를 비교한다.

13.1 A-Rows와 내부 검사량

Index Operation의 A-Rows는 상위로 반환한 Entry·ROWID 수입니다. Index Filter에서 제거한 내부 Entry는 모두 표시되지 않을 수 있습니다.

Index A-Rows 20
Index Buffers 15,000

→ 20개만 읽었다고 단정 금지
→ 넓은 Leaf Scan·Index Filter 확인

13.2 Starts

INLIST·Nested Loops에서는 Index Operation이 반복될 수 있습니다.

Starts 10,000
A-Rows 20,000
Buffers 80,000

Rows / Start    = 2
Buffers / Start = 8

1회 비용이 작아도 총비용은 큽니다.

14. 자주 혼동하는 판단