Clustering Factor 이해: 인덱스 순서와 Table Block 배치

핵심 요약

Clustering Factor(CF)는 B-tree Index Key 순서와 Heap Table Row의 물리적 Block 배치 사이의 상관관계를 나타내는 Index 통계입니다.

낮은 CF 방향
  → 인접 Index Entry의 ROWID가 같은·인접 Table Block을 가리킴
  → 큰 Range Scan에서도 Block 재사용 가능성이 큼
  → ROWID Table Access 비용이 낮게 추정될 가능성

높은 CF 방향
  → 인접 Index Entry의 ROWID가 여러 Table Block에 흩어짐
  → 큰 Range Scan에서 같은 Block을 반복해서 읽을 수 있음
  → Full Scan이 더 일찍 유리해질 가능성

Oracle 공식 기준의 대표 해석입니다.

CF가 Table Blocks에 가까움
  → Table Row가 Index Key 순서에 비교적 잘 정렬됨

CF가 Table Rows에 가까움
  → Table Row가 Index Key 순서 기준으로 무작위에 가깝게 분산됨

CF는 다음을 의미하지 않습니다.

CF
  ≠ Index Leaf Block의 정렬 품질
  ≠ 특정 SQL이 실제로 읽을 정확한 Block 수
  ≠ Table 하나에 공통인 값
  ≠ Index Rebuild만으로 근본 개선되는 값

CF는 Index Scan과 Full Table Scan의 비용을 비교할 때 사용되는 거친 Table I/O 추정 지표입니다. 실제 판단은 선택도·후보 ROWID·Covering·Partition Pruning·Table Buffers·Elapsed를 함께 봅니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 테이블 액세스 최소화 범위에서 Clustering Factor의 의미·비용 영향·개선 대안을 다룹니다.

학습 목표

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

• Clustering Factor가 나타내는 물리적 상관관계를 설명한다.
• CF가 Table Blocks와 Table Rows에 가까울 때의 의미를 구분한다.
• CF가 Table이 아니라 개별 Index 통계인 이유를 설명한다.
• 같은 후보 Row 수에서 Table Block 방문 수가 달라지는 이유를 설명한다.
• CF가 큰 Range Scan 비용에 미치는 영향을 설명한다.
• CF의 영향이 큰 SQL과 작은 SQL을 구분한다.
• USER_INDEXES, USER_IND_STATISTICS, USER_IND_PARTITIONS에서 관련 통계를 조회한다.
• AVG_DATA_BLOCKS_PER_KEY와 CF의 차이를 설명한다.
• Index Rebuild와 Table 재배치의 차이를 설명한다.
• Covering·복합 Index·Partitioning·IOT·Full Scan 대안을 비교한다.
• 실제 실행통계로 CF 기반 예상과 Runtime 결과를 검증한다.

1. Clustering Factor가 필요한 이유

다음 두 Table이 모두 1,000,000행이고 같은 Index Range Scan으로 10,000행을 찾는다고 가정합니다.

경우 A: Index 순서와 Table 배치가 비슷함

Index Key 순서  1001  1002  1003  1004  1005
Table Block       B1    B1    B1    B2    B2

인접 ROWID가 같은 Block을 가리키므로 한 Block 방문으로 여러 Row를 처리할 수 있습니다.

경우 B: Index 순서와 Table 배치가 다름

Index Key 순서  1001  1002  1003  1004  1005
Table Block       B1   B80   B13   B47    B2

같은 후보 수라도 Table Block 전환과 Random Access가 많아집니다.

후보 Row 수가 같음
  ≠ Table Block 작업량이 같음

Optimizer는 이런 차이를 Index Cost에 반영하기 위해 Clustering Factor를 사용합니다.

2. 계산 원리를 직관적으로 이해하기

Oracle은 통계 수집 시 B-tree Entry를 Key 순서로 따라가며 ROWID가 가리키는 Table Block의 연속성을 통계화합니다.

단순 예입니다.

Index Entry  Table Block
K1           B10
K2           B10
K3           B10
K4           B11
K5           B11
K6           B20

개념적 Block 흐름입니다.

B10 → B10 → B10 → B11 → B11 → B20

• 같은 Block이 연속되면 CF는 낮은 방향
• Entry마다 Block이 자주 바뀌면 CF는 높은 방향

이 설명은 이해를 위한 단순화입니다. CF는 특정 SQL의 실제 Block 방문 수를 직접 저장한 값이 아니라 전체 Index와 Table 배치 관계를 요약한 Optimizer Statistics입니다.

3. Table Blocks·Table Rows와 비교한다

다음 통계를 가정합니다.

Table Rows   = 1,000,000
Table Blocks =    50,000

INDEX_A CF   =    62,000
INDEX_B CF   =   930,000

INDEX_A

CF가 Table Blocks에 비교적 가까움
→ 인접 Key의 ROWID가 같은 Block에 모일 가능성이 큼
→ 넓은 Range Scan의 Table Block 재사용 가능성

INDEX_B

CF가 Table Rows에 가까움
→ 인접 Key의 ROWID가 여러 Block에 분산
→ 넓은 Range Scan의 Random Table Access 증가 가능

CF를 단순 점수로 보지 않습니다.

CF 해석
  + 실제 선택도
  + 후보 ROWID
  + Table Block 수
  + Covering 여부
  + Partition Pruning
  + 통계 최신성
  + Runtime Buffers·Reads

4. CF는 Table이 아니라 Index별 통계다

한 Table에 다음 Index가 있다고 가정합니다.

CREATE INDEX orders_date_ix
ON orders(order_date);

CREATE INDEX orders_customer_ix
ON orders(customer_id);

Table Row가 Date 순서에 가깝게 적재됐다면 다음과 같은 차이가 가능합니다.

ORDERS_DATE_IX
  → 낮은 CF 방향

ORDERS_CUSTOMER_IX
  → 높은 CF 방향

Table을 customer_id 순서로 재배치하면 Customer Index CF는 좋아질 수 있지만 Date Index CF는 나빠질 수 있습니다.

하나의 Heap Table
  → 여러 Index Key 순서에 동시에 완벽히 정렬하기 어려움

Oracle 공식 문서도 특정 Index의 CF 개선을 위한 Table 재구성이 다른 Index의 CF를 악화시킬 수 있다고 설명합니다.

5. Optimizer 비용과 CF의 연결

일반적인 B-tree Range Scan 비용입니다.

Root·Branch 수직 탐색
+ Leaf Block Scan
+ ROWID 기반 Table Block Access

CF는 주로 마지막 Table Access 비용 추정에 영향을 줍니다.

낮은 CF 방향

후보 ROWID가 같은 Block에 모임
→ 예상 Table Block 방문 수 감소
→ Index Range Scan이 더 넓은 범위까지 경쟁력 유지 가능

높은 CF 방향

후보 ROWID가 여러 Block에 분산
→ 예상 Table Block 방문 수 증가
→ Full Table Scan의 비용이 더 일찍 유리해질 수 있음

Oracle은 CF를 Index Scan과 Full Scan 중 어느 경로가 효율적인지 판단하는 데 사용합니다.

6. CF의 영향이 큰 경우와 작은 경우

영향이 큰 경우

• 많은 ROWID를 반환하는 Index Range Scan
• Index에 없는 Column을 조회해 Table Access가 필요한 SQL
• NL Join Inner에서 Range Scan이 반복되는 SQL
• Table Row가 넓고 후보가 여러 Block에 분산된 경우
• 같은 SQL이 매우 자주 실행돼 Buffer Get이 누적되는 경우

영향이 작은 경우

• INDEX UNIQUE SCAN으로 한 건을 찾는 경우
• 후보가 극소수인 경우
• Covering Index로 Table Access가 제거된 경우
• Table 자체가 매우 작은 경우
• Partition Pruning으로 접근 Block이 이미 작아진 경우
• Index Fast Full Scan처럼 Table Access가 없는 경로

CF는 Table Access가 있어야 직접적인 영향이 커진다.

7. 같은 후보 Row 수라도 실제 비용이 다른 이유

Plan A
  Index A-Rows   10,000
  Table Buffers     400

Plan B
  Index A-Rows   10,000
  Table Buffers   8,900

두 Plan의 후보 Row 수는 같지만 Table Block 분산이 다를 수 있습니다.

추가 확인 항목입니다.

• TABLE ACCESS BY INDEX ROWID BATCHED 여부
• Table Buffers·Physical Reads
• Clustering Factor
• Row Migration·Chaining
• Cache 상태
• 동일 Block 재방문
• Starts 반복 횟수

BATCHED는 ROWID 접근 순서를 개선할 수 있지만 후보 수 자체를 줄이거나 나쁜 Clustering을 완전히 없애지는 않습니다.

8. CF와 AVG_DATA_BLOCKS_PER_KEY

Index Statistics에는 다음과 같은 보조 지표가 있습니다.

CLUSTERING_FACTOR
  → Index 전체 Key 순서와 Table Block 배치의 전반적 관계

AVG_DATA_BLOCKS_PER_KEY
  → 한 Distinct Key가 평균적으로 가리키는 Table Block 수

예를 들어 중복값이 많은 Index에서 AVG_DATA_BLOCKS_PER_KEY가 크면 특정 Equality Key도 여러 Table Block에 분산될 수 있습니다.

두 통계는 서로 대체하지 않습니다.

CF
  → 넓은 Range와 전체 순서 관계 판단

AVG_DATA_BLOCKS_PER_KEY
  → Distinct Key별 평균 Block 분산 이해

실제 SQL의 Bind 편중·Histogram과 Runtime 통계를 추가로 봅니다.

9. Partitioned Index에서는 Partition 통계도 본다

Partitioned Table·Index에서는 전체 Index 통계와 개별 Index Partition 통계가 다를 수 있습니다.

SELECT index_name,
       partition_name,
       num_rows,
       leaf_blocks,
       clustering_factor,
       last_analyzed
FROM   user_ind_partitions
WHERE  index_name = 'ORDERS_DATE_LIX'
ORDER BY partition_position;

월별 Partition의 Data 적재 순서가 다르면 Partition마다 CF가 달라질 수 있습니다.

최근 Partition
  순차 적재
  → 낮은 CF 방향

과거 Partition
  대량 Update·재적재
  → 높은 CF 방향 가능

Query가 Partition Pruning으로 일부 Partition만 읽는다면 전체 Global CF만으로 Runtime 비용을 단정하지 않고 실제 대상 Partition 통계와 실행통계를 봅니다.

10. 통계 조회와 최신성 검증

SELECT i.index_name,
       i.blevel,
       i.leaf_blocks,
       i.distinct_keys,
       i.avg_data_blocks_per_key,
       i.clustering_factor,
       i.num_rows AS index_rows,
       i.last_analyzed,
       t.num_rows AS table_rows,
       t.blocks   AS table_blocks
FROM   user_indexes i
JOIN   user_tables t
  ON   t.table_name = i.table_name
WHERE  i.table_name = 'ORDERS'
ORDER BY i.index_name;

확인합니다.

1. CF가 Table Blocks·Rows 중 어디에 가까운가
2. Index·Table Statistics의 LAST_ANALYZED가 비슷한가
3. 대량 Load·Delete·Move·Partition 작업 이후 통계가 갱신됐는가
4. 통계가 Global·Partition 수준에서 필요한 범위를 반영하는가
5. 실제 E-Rows·A-Rows·Buffers가 예상과 비슷한가

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'ORDERS',
    cascade => TRUE
  );
END;
/

통계 수집은 현재 배치를 반영하지만 물리 Row 배치를 바꾸지는 않습니다.

11. Index Rebuild가 CF를 근본적으로 개선하지 않는 이유

Index Rebuild는 Index Segment를 재구성합니다.

Index Entry 재정렬
Leaf Block 재구성
공간 사용·BLEVEL·Leaf Blocks 변화 가능

그러나 Heap Table Row의 물리적 Block 위치는 유지됩니다.

Index Rebuild
  → Index 구조 변화
  → Table Row 배치 유지
  → Index Key 순서와 Table Block 관계의 근본 원인 유지

따라서 나쁜 CF만을 이유로 Index Rebuild를 수행해도 Table Random Access가 줄지 않을 수 있습니다.

12. Table 재배치와 유지 한계

특정 Key 순서에 가깝게 Table을 재배치하는 후보입니다.

CREATE TABLE orders_new AS
SELECT *
FROM   orders
ORDER BY order_date;

Oracle 공식 예제에서도 Key 순서로 CTAS한 Table의 Index CF가 낮아지는 사례를 제시합니다.

실제 운영에서는 다음 방법을 검토할 수 있습니다.

• CTAS + Object 전환
• DBMS_REDEFINITION 등 Online Redefinition
• Partition 단위 재적재·교환
• IOT 또는 Table Cluster
• Attribute Clustering 등 물리 배치 설계

주의사항입니다.

• 한 Index CF 개선이 다른 Index CF를 악화시킬 수 있음
• 이후 Insert·Update로 배치가 다시 흐트러짐
• 공간·Redo·가용성·복구 계획 필요
• Constraint·Trigger·Grant·Statistics 재구성
• 단순 ALTER TABLE MOVE만으로 원하는 Key 순서가 보장되지는 않음

13. 개선 대안 선택

13.1 후보 ROWID를 줄인다

더 선택적인 복합 Index
Equality Column을 첫 Range 앞에 배치
Table Filter Column을 Index에 추가

13.2 Table Access를 제거한다

Covering Index
필요한 SELECT Column만 반환
Index Join

13.3 접근 Segment를 줄인다

Partitioning
Partition Pruning
조건부 Index

13.4 Random Access를 피한다

Table Full Scan
Parallel Full Scan
Index Fast Full Scan

13.5 저장 구조를 바꾼다

IOT
Table Cluster
특정 Key 기준 Table 재배치

최적 대안은 SQL 빈도·SLA·DML·공간·다른 SQL 회귀를 포함해 결정합니다.

14. 실행계획과 Runtime 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       customer_id,
       amount
FROM   orders
WHERE  order_date >= :from_date
AND    order_date <  :to_date;

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

확인 순서입니다.

1. Index Operation의 Starts·A-Rows·Buffers·Reads
2. TABLE ACCESS Operation의 A-Rows·Buffers·Reads
3. 후보 ROWID와 최종 Row 차이
4. Table Filter 위치
5. BATCHED 여부
6. Partition Start·Stop
7. Sort·TEMP·Parallel
8. CPU·Elapsed·P95

V$SQL_PLAN_STATISTICS_ALL에서 다음 값을 연결할 수 있습니다.

LAST_STARTS
LAST_OUTPUT_ROWS
LAST_CR_BUFFER_GETS
LAST_CU_BUFFER_GETS
LAST_DISK_READS

상위 Row Source의 Buffer 값이 하위 작업을 포함할 수 있으므로 모든 Plan Line을 단순 합산하지 않습니다.

15. 진단 사례

통계입니다.

ORDERS
  NUM_ROWS  = 20,000,000
  BLOCKS    =    800,000

ORDERS_DATE_IX
  LEAF_BLOCKS       = 60,000
  CLUSTERING_FACTOR = 1,100,000

ORDERS_CUSTOMER_IX
  LEAF_BLOCKS       = 70,000
  CLUSTERING_FACTOR = 18,000,000

날짜 범위 Query

Index A-Rows     100,000
Table Buffers     12,000

Date Index CF는 Table Blocks에 상대적으로 가까워 넓은 Range에서도 Block 재사용이 가능합니다.

고객 범위 Query

Index A-Rows     100,000
Table Buffers     92,000

Customer Index CF가 Rows에 가까워 후보가 여러 Block에 흩어질 가능성이 큽니다.

대안을 비교합니다.

• Customer·Date 복합 Index
• Covering
• Partition Pruning
• Full Scan
• Query Pattern이 안정적이면 Table 재배치·IOT

16. 자주 혼동하는 판단