Oracle 인덱스 스캔 방식1: Index Range Scan, Unique Scan

Oracle 데이터베이스는 다양한 인덱스 스캔 방법을 활용하여 데이터를 효율적으로 검색합니다. 이 글은 첫 번째로 Index Range Scan, Index Range Scan Descending 및 Index Unique Scan에 대해 알아보려고 합니다. 

 

 

Index Range Scan

이 인덱스 스캔 방식은 Unique Index(Primary Key, Artificial Key 등) 및 Non Unique로 생성된 인덱스에서 발생되는 액세스 방식입니다. Unique Index의 경우는 인덱스 구성 칼럼의 일부에만 조건이 주어진 경우나 Like, Between 또는 equal 조건이 아닌 범위 조건 ( >, =>, <, <= )에서 주로 발생합니다. 

 

Non Unique Index의 경우는 인덱스를 사용할 수 있는 모든 조건에 대해 발생하게 됩니다. 인덱스 트리 구조에 대해 수직적인 탐색 후에 수평적인 오름차순 탐색을 조건이 만족할 때까지 양방향 Linked List를 따라 수행합니다.

 

결과적으로 인덱스 Range scan은 Single Block I/O가 발생하게 됩니다. 인덱스 Leaf Node의 Entry들의 정렬된 결과를 얻게 되는 원리입니다. 

 

Index Range Scan

1. 작동 방식

• 데이터베이스는 범위의 시작점을 찾기 위해 인덱스의 B-tree 구조를 탐색합니다.
• 다음 지정된 범위의 끝에 도달할 때까지 인덱스 항목을 순차적으로 검색합니다.
• 이 방법은 BETWEEN, <, <=, > 및 >=와 같은 범위 조건을 사용하는 쿼리에 이상적입니다.

 

2. 예시 쿼리와 실행계획

SELECT * 
FROM 직원
WHERE 지역 = :VAL
AND 월급 BETWEEN 50000 AND 100000
ORDER BY 입사일자;
 
인덱스 02: 지역 + 입사일자
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS
   1    0   TABLE ACCESS (BY INDEX ROWID) OF '직원' (TABLE)
   2    1     INDEX (RANGE SCAN) OF '직원_X02' (INDEX)
 

 

 

Index Range Scan Descending 

이 스캔 방식은 Index Range Scan과 유사하게 작동하지만 인덱스 항목을 내림차순으로 스캔합니다. 이는 추가 정렬 작업을 수행하지 않고 쿼리에서 데이터를 내림차순으로 정렬해야 하는 경우에 유용합니다.

 

인덱스의 Branch Block에서 Where절을 만족하는 다음 엔트리의 DBA(Data Block Address) 즉 rowid를 이용하여 해당 Leaf Node를 수직적인 탐색을 합니다. 인덱스 트리 구조에 대해 수직적인 탐색 후에 수평적인 내림차순 탐색을 조건이 만족할 때까지 양방향 Linked List를 따라 수행합니다. 과정을 보면 Index Range Scan과 모두 동일합니다. 

 

1. 작동 방식

• 데이터베이스는 지정된 범위의 가장 높은 값에서 시작됩니다.
• 그런 다음 범위의 하한에 도달할 때까지 인덱스 항목을 내림차순으로 순차적으로 검색합니다.
• 이 방법은 데이터를 내림차순으로 정렬해야 하는 쿼리에 효율적입니다.

 

2. 예시 쿼리와 실행계획

SELECT /*+ index_desc(a 직원_X02) */ * 
FROM 직원a
WHERE 지역 = :VAL
AND 월급 BETWEEN 50000 AND 100000
ORDER BY 입사일자;
 
인덱스 02: 지역 + 입사일자
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS
   1    0   TABLE ACCESS (BY INDEX ROWID) OF '직원' (TABLE) 
   2    1     INDEX (RANGE SCAN DESCENDING) OF '직원_X02' (INDEX)
 
 

 

Index Unique Scan

인덱스 유니크 스캔은 쿼리가 Unique Index에서 하나의 유일한 행을 검색해야 할 때 사용됩니다. 이 스캔은 Key 또는 Unique Index로 생성된 인덱스에서 발생되는 액세스 방식입니다. 

인덱스 구성 칼럼에 대한 Where절이 ‘=‘로 모두 주어졌을 경우인데요. 인덱스 트리 구조에 대해 수직적인 탐색을 수행하여  Single Block I/O 발생하게 됩니다. 

 

Index Unique Scan

 

1. 작동 방식

• 데이터베이스는 Uniqe 인덱스를 사용하여 지정된 키 값과 일치하는 정확한 행을 찾습니다.
• Unique 인덱스는 인덱스 된 값이 고유하도록 보장하므로 일치하는 행을 찾는 즉시 스캔이 중지됩니다.
• 이 방법은 Primary Key 및 Unique 제약 조건 조회에 적합합니다.

 

2. 예시 쿼리와 실행계획

SELECT * 
FROM 직원
WHERE 직원ID = :VAL
;
 
인덱스 PK: 직원ID
 
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF '직원' (TABLE)
   2    1     INDEX (UNIQUE SCAN) OF '직원_PK' (INDEX (UNIQUE))