slow index

“인덱스를 사용하도록 쿼리를 짰으니 무조건 빠를 것이다.” 데이터베이스 성능 튜닝과 관련된 흔한 오해입니다. 인덱스가 기대만큼 빠르지 않을 때 트리 구조가 깨졌거나 불균형해졌다고 생각하기 쉽지만, 실제 운영 환경에서 병목의 대부분은 트리 탐색 자체가 아니라 그 이후에 수반되는 추가 작업과 디스크 I/O에서 발생합니다.

이 글에서는 인덱스를 사용해도 쿼리가 느려지는 내부 동작 원리를 살펴보고, 실행 계획 분석과 최적화 방법을 정리합니다.

---

1. 인덱스 조회가 느려지는 2가지 핵심 원인

데이터베이스가 인덱스를 통해 데이터를 조회하는 과정은 크게 다음의 3단계로 이루어집니다.

$$\text{Index Lookup} = \text{Tree Traversal} \rightarrow \text{Following the Leaf Node Chain} \rightarrow \text{Fetching Table Data}$$

1. 트리 탐색 (Tree Traversal): 루트 노드부터 리프 노드까지 찾아 내려가는 과정입니다. 테이블 규모가 커져도 인덱스 깊이(Index Depth)는 보통 몇 단계 안에서 유지되므로 읽어야 하는 블록 수의 증가폭이 작습니다. 즉, 데이터 양이 늘어나더라도 탐색 시간은 대체로 로그 시간($O(\log N)$)에 수렴합니다.
2. 리프 노드 체인 스캔: 일치하는 데이터를 찾기 위해 양방향 연결 리스트인 리프 노드 체인을 따라가며 스캔하는 과정입니다.
3. 실제 테이블 데이터 액세스 (TABLE ACCESS BY INDEX ROWID): 인덱스에서 얻은 물리적 주소인 ROWID를 사용하여 실제 테이블 블록에서 레코드를 가져오는 과정입니다.

이 중 트리 탐색은 매우 빠르지만, 다음 두 요인이 결합되면 성능이 저하됩니다.

① 넓은 범위의 리프 노드 체인 스캔

인덱스의 리프 노드는 양방향 연결 리스트 구조로 서로 연결되어 있습니다. 조건에 부합하는 시작점을 찾은 후, 조건이 끝날 때까지 리프 노드 체인을 따라가며 데이터를 계속 읽어 나갑니다. 조건 범위에 해당하는 행이 많을수록 읽어야 하는 리프 노드의 개수도 늘어납니다.

② 과도한 무작위 테이블 블록 액세스

인덱스를 타더라도 검색하는 컬럼이 인덱스에 전부 포함되어 있지 않다면, 데이터베이스는 인덱스에서 얻은 물리적 주소인 ROWID를 사용하여 실제 테이블 블록을 다시 조회해야 합니다. 인덱스 리프 노드는 정렬되어 있지만, 이에 매칭되는 실제 테이블 블록들은 디스크 상에 무작위로 흩어져 있는 경우가 많습니다. 이로 인해 싱글 블록 I/O(Single-Block Read) 가 대량으로 발생하고, 인덱스 매칭 건수만큼 디스크 무작위 접근이 유발됩니다.

---

2. 깨진 트리 구조에 대한 오해와 인덱스 활용법

인덱스 조회 성능이 떨어질 때 인덱스 트리가 불균형해졌거나 망가졌다고 생각하기 쉽습니다. 하지만 B-Tree 계열 인덱스는 자체적으로 균형을 유지하도록 설계되어 있으므로, 구조 자체가 문제인 경우는 많지 않습니다.

인덱스가 느린 진짜 원인을 파악하기 위해서는 데이터베이스가 해당 인덱스를 어떻게 활용하고 있는지 실행 계획을 통해 직접 확인해 보아야 합니다. 오라클 데이터베이스의 실행 계획에서 자주 나타나는 대표적인 세 가지 기본 동작은 다음과 같습니다.

• INDEX UNIQUE SCAN
  • unique 제약 조건 등으로 인해 매칭되는 건수가 최대 1개로 보장될 때 사용됩니다. 리프 노드 체인을 따라 이동할 필요가 없으므로, 가장 효율적입니다.
• INDEX RANGE SCAN
  • 다수의 레코드가 검색 조건에 매칭될 가능성이 있을 때 수행됩니다. 트리 탐색을 거친 후 리프 노드 체인을 순차적으로 스캔하며 일치하는 대상을 찾습니다.
• TABLE ACCESS BY INDEX ROWID
  • 앞선 인덱스 스캔(주로 INDEX RANGE SCAN)을 통해 얻은 ROWID 목록을 기반으로 실제 테이블 블록에서 레코드를 가져옵니다.

결국 INDEX RANGE SCAN으로 읽는 범위가 넓어지고, 그에 따라 TABLE ACCESS BY INDEX ROWID 작업이 수반되는 것이 슬로우 인덱스 문제의 핵심입니다.

---

3. 실무 사례 분석: 쿼리 옵티마이저와 실행 계획의 분석 흐름

자회사 ID와 사원 ID로 이루어진 복합 기본키 인덱스가 아래와 같이 정의되어 있습니다.

CREATE UNIQUE INDEX EMPLOYEES_PK ON EMPLOYEES (SUBSIDIARY_ID, EMPLOYEE_ID);

이 상태에서 특정 자회사(subsidiary_id = 30) 소속의 특정 이름(last_name = 'WINAND')을 가진 직원을 찾는 쿼리를 실행합니다.

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30;

Step 1. 인덱스를 사용한 첫 실행 계획과 문제 발견

이 쿼리를 실행하면 다음과 같은 예시 실행 계획이 생성됩니다.

• 실행 계획 (인덱스 사용 시)

---------------------------------------------------------------
|Id |Operation                  | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT           |              |    1 |   30 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |   30 |
|*2 |  INDEX RANGE SCAN         | EMPLOYEES_PK |   40 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)

이 쿼리는 인덱스를 사용하고 비용도 30으로 책정되어 겉보기에는 문제가 없어 보입니다. 하지만 실제 실행에서는 응답 시간이 예상보다 길어질 수 있습니다.

원인을 분석해 보면, EMPLOYEES_PK 인덱스는 LAST_NAME 컬럼을 포함하고 있지 않습니다. 따라서 옵티마이저는 subsidiary_id = 30 조건으로 INDEX RANGE SCAN을 수행한 뒤, 해당 자회사의 사원 ROWID로 테이블 블록을 읽으면서 LAST_NAME='WINAND' 필터를 적용합니다.

Step 2. NO_INDEX 힌트를 통한 전체 테이블 스캔과의 비교 분석

인덱스가 성능 저하의 원인인지 확인하기 위해, 오라클의 NO_INDEX 힌트를 사용하여 인덱스 사용을 차단하고 테이블 스캔을 유도합니다.

SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEES_PK) */ first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30;

• 실행 계획 (Full Table Scan 강제 적용 시)

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30)

경우에 따라 TABLE ACCESS FULL이 인덱스 스캔보다 유리할 수 있습니다.

자회사 30번에 속한 직원이 실제로는 1,000명이라고 가정해 봅시다. 인덱스를 사용하면 1,000번의 개별 테이블 랜덤 액세스(Single-Block Read)가 발생할 수 있지만, 전체 테이블 스캔은 여러 테이블 블록을 한 번에 읽어 들이는 멀티 블록 I/O(Multi-Block Read) 로 작동하여 디스크 접근 횟수를 줄일 수 있습니다.

Step 3. 통계 정보(Statistics) 불일치와 시나리오 분석

그렇다면 쿼리 옵티마이저는 왜 인덱스 스캔을 선택했을까요? 원인은 데이터베이스의 **통계 정보(Statistics)**가 최신화되지 않았기 때문입니다.

옵티마이저는 데이터의 분포를 파악하기 위해 테이블 크기, 인덱스 깊이, 컬럼별 값의 분포(히스토그램) 등의 통계 정보를 참고하여 비용을 계산합니다. 아래의 비용과 행 수는 설명을 위한 예시이며, 실제 값은 환경과 데이터 분포에 따라 달라질 수 있습니다.

• 시나리오 A: 잘못된 통계 정보 통계 정보가 충분하지 않으면 오라클 옵티마이저는 기본값을 사용합니다. 이때 인덱스 스캔 결과가 매우 적을 것(예: Rows: 40)으로 평가하여 비용을 30으로 계산하고 인덱스 스캔을 선택할 수 있습니다. 실제 데이터가 더 많다면 랜덤 액세스가 늘어 성능이 저하됩니다.
• 시나리오 B: 정확한 통계 정보 제공 통계 정보를 최신으로 갱신하면 옵티마이저는 자회사 30번의 직원이 1,000명(Rows: 1000)임을 더 정확히 인지합니다. 이 경우 인덱스 스캔 비용이 680으로 늘어나고, 전체 테이블 스캔 비용(477)보다 높아지면 옵티마이저가 힌트 없이도 전체 테이블 스캔을 선택할 수 있습니다.

---

4. 근본적인 성능 개선 방안

가장 일반적인 튜닝 방법은 실제 조건절과 선택도를 함께 고려한 전용 인덱스를 생성하는 것입니다.

CREATE INDEX emp_name ON employees (last_name, subsidiary_id);

새로운 인덱스 EMP_NAME을 생성하면 실행 계획은 다음과 같이 변합니다. 이 예시는 last_name과 subsidiary_id를 활용해 불필요한 테이블 접근을 줄입니다.

• 실행 계획

--------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Cost |
--------------------------------------------------------------
|  0 | SELECT STATEMENT            |           |    1 |    3 |
|* 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    1 |    3 |
|* 2 |   INDEX RANGE SCAN          | EMP_NAME  |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SUBSIDIARY_ID"=30)
   2 - access("LAST_NAME"='WINAND')

• 효과: last_name과 subsidiary_id를 함께 사용하면 탐색 범위가 좁아져 INDEX RANGE SCAN이 가져오는 ROWID 수가 줄어듭니다. 그 결과 테이블 액세스 비용도 낮아져, 전체 테이블 스캔보다 유리한 계획을 기대할 수 있습니다.

인덱스 범위 스캔(INDEX RANGE SCAN)은 성능 편차가 매우 큽니다. 인덱스를 읽은 후 ROWID를 통해 실제 테이블 블록을 액세스하는 단계에서 클러스터링 팩터에 따라 비용이 폭증할 수 있기 때문입니다.

---

5. 인덱스 스캔 범위를 줄였는데도 느리다면? - 클러스터링 팩터 (Clustering Factor)

4절에서 전용 인덱스(emp_name)를 설계해 스캔 범위를 줄였더라도, 반환된 ROWID의 물리적 분포도에 따라 성능은 달라질 수 있습니다.

예를 들어 인덱스가 100건의 ROWID를 반환했을 때, 이 100개의 행이:

• 같은 테이블 블록 몇 개에 모여 있다면 → 블록 수 기준으로 수 번의 I/O로 끝납니다.
• 각기 다른 100개의 블록에 흩어져 있다면 → 블록마다 개별 디스크 읽기가 발생해 100번의 무작위 I/O가 발생합니다.

이처럼 인덱스의 정렬 순서와 실제 테이블 데이터의 물리적 배치가 얼마나 일치하는지를 나타내는 지표가 바로 **클러스터링 팩터(Clustering Factor)**입니다.

옵티마이저는 TABLE ACCESS BY INDEX ROWID의 비용을 계산할 때 이 클러스터링 팩터를 반영합니다. 인덱스를 사용하더라도 클러스터링 팩터가 나쁘면 비용이 커지고, 경우에 따라서는 오히려 전체 테이블 스캔보다 불리해질 수 있습니다.

클러스터링 팩터가 나쁜 케이스: 인덱스 필터 조건의 의도적 활용

다음과 같이 subsidiary_id로 특정 자회사 직원 전체를 조회하되, 성(last name)에 특정 문자열이 포함된 사원만 필터링하는 쿼리를 살펴보겠습니다.

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE subsidiary_id = ?
   AND UPPER(last_name) LIKE '%INA%';

subsidiary_id는 인덱스 액세스 조건(Access Predicate)으로 활용할 수 있습니다. 그러나 UPPER(last_name) LIKE '%INA%'처럼 앞부분에 와일드카드(%)가 붙은 LIKE 조건은 인덱스 트리 탐색에 사용할 수 없습니다. 즉, 인덱스 스캔 범위를 좁히는 데 전혀 기여하지 못합니다.

기존 기본키 인덱스(EMPLOYEE_PK)만으로 쿼리를 수행하면 다음과 같은 실행 계획이 나옵니다.

--------------------------------------------------------------
|Id | Operation                  | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT           |             |   17 |  230 |
|*1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   17 |  230 |
|*2 |   INDEX RANGE SCAN         | EMPLOYEE_PK |  333 |    2 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("LAST_NAME") LIKE '%INA%')
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))

INDEX RANGE SCAN 비용은 2이지만 TABLE ACCESS BY INDEX ROWID 단계에서 비용이 230으로 늘어납니다. 인덱스에서 333건을 가져와 테이블을 각각 열어본 뒤 LIKE 필터를 사후 적용해 최종 17건만 남기는 구조입니다.

333건의 행들이 각기 다른 블록에 흩어져 있을수록(클러스터링 팩터가 나쁠수록) 이 비용은 더욱 커집니다.

해결책: 결합 인덱스로 데이터를 논리적으로 클러스터링

물리적으로 테이블 데이터를 재정렬하는 것은 현실적으로 어렵지만, 결합 인덱스 뒤쪽에 필터 컬럼을 추가하는 전략은 가능합니다. 이렇게 하면 테이블 블록을 열기 전에 인덱스 단계에서 먼저 걸러낼 수 있어, 실제 테이블 방문 횟수를 줄일 수 있습니다.

CREATE INDEX empsubupnam ON employees (subsidiary_id, UPPER(last_name));

새 인덱스를 적용한 실행 계획입니다.

--------------------------------------------------------------
|Id | Operation                   | Name       | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT            |            |   17 |   20 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |   17 |   20 |
|*2 |   INDEX RANGE SCAN          | EMPSUBUPNAM|   17 |    3 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))
       filter(UPPER("LAST_NAME") LIKE '%INA%')

비용이 230에서 20으로 줄었습니다. Predicate Information에서 LIKE 필터가 INDEX RANGE SCAN의 filter 조건으로 표시된 것을 확인할 수 있습니다. 조건에 맞지 않는 행은 테이블을 열기 전에 인덱스 단계에서 제외되고, 최종 17건에 대해서만 테이블 액세스가 일어납니다.

인덱스는 스캔 범위를 좁히는 것뿐 아니라, 테이블 무작위 액세스 횟수를 줄이도록 데이터를 논리적으로 클러스터링하는 역할도 할 수 있습니다.

---

6. 커버링 인덱스

앞서 인덱스 필터 조건을 통해 테이블 무작위 액세스 횟수를 줄였습니다. 그렇다면 더 나아가 “테이블 방문 횟수를 아예 0으로 만들 수는 없을까?” 라는 질문이 생깁니다.

이것이 바로 **커버링 인덱스 (Covering Index)**의 활용입니다. 쿼리에서 가져오려는 컬럼과 가공하려는 컬럼 모두가 인덱스에 포함되어 있다면, 데이터베이스는 실제 테이블 블록을 방문하지 않고 인덱스만 읽어 결과를 반환할 수 있습니다.

-- subsidiary_id와 eur_value를 모두 인덱스에 포함
CREATE INDEX sales_sub_eur ON sales (subsidiary_id, eur_value);

-- 테이블 액세스 없이 인덱스만으로 집계를 처리
SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?;

실행 계획에서 TABLE ACCESS BY INDEX ROWID 연산이 사라진 것을 확인할 수 있습니다.

----------------------------------------------------------
| Id  | Operation         | Name          |  Rows | Cost |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |  104 |
|   1 |  SORT AGGREGATE   |               |     1 |      |
|*  2 |   INDEX RANGE SCAN| SALES_SUB_EUR | 40388 |  104 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))

수만 건을 집계하는 쿼리에서 테이블 무작위 I/O가 크게 줄어들기 때문에 응답 시간도 개선될 수 있습니다.

주의사항

커버링 인덱스는 강력한 만큼, 애플리케이션 변화에 민감합니다.

1. 사소한 쿼리 변경으로 인한 성능 저하: SELECT 절이나 WHERE 절에 인덱스가 커버하지 못하는 컬럼이 하나라도 추가되면 커버링 인덱스가 깨지고 테이블 액세스가 필요해질 수 있습니다.

   -- sale_date가 인덱스에 없으므로 커버링이 깨짐
   SELECT SUM(eur_value) FROM sales WHERE subsidiary_id = ? AND sale_date > ?;

   이 경우 더 좁은 범위를 필터링했더라도 성능이 오히려 나빠질 수 있습니다.

2. 함수 기반 인덱스의 제한: UPPER(last_name)으로 인덱스를 만든 상태에서 SELECT 절에 원본 last_name을 요구하면 커버링이 깨집니다. 가급적 실제 조회 패턴에 맞는 형태로 인덱스를 설계해야 합니다.

---

7. 테이블 자체를 인덱스 구조로 만들기 (IOT vs 힙 테이블)

“그렇다면 아예 테이블 전체 데이터를 인덱스 형태로 정렬해서 저장하면 어떨까?”라는 발상에서 **인덱스 조직 테이블(Index-Organized Table, IOT)**이 나왔습니다. 이는 오라클 고유의 기술 명칭으로, 타 데이터베이스에서 말하는 ‘클러스터형 인덱스(Clustered Index)‘와 본질적으로 완전히 같은 개념입니다.

일반적인 **힙 테이블(Heap Table)**은 데이터를 물리적으로 분산 저장하고 인덱스가 ROWID로 행을 가리킵니다. 반면 IOT는 실제 데이터 행이 PK 순서대로 B-Tree 리프 노드에 직접 저장됩니다.

• 장점: PK 기준 조회 시 테이블 액세스 단계를 줄일 수 있어, 조회 패턴에 따라 효율적일 수 있습니다.

보조 인덱스(Secondary Index)의 페널티

하지만 PK가 아닌 컬럼으로 검색하기 위해 **보조 인덱스(Secondary Index)**를 생성해야 할 때 독특한 페널티가 발생합니다.

B-Tree 구조 내부에서는 데이터 삽입이나 노드 분할(Node Split)로 인해 행의 물리적 위치가 달라질 수 있습니다. 따라서 보조 인덱스는 고정된 물리 주소(ROWID) 대신 행의 논리적 키값(Clustering Key) 을 저장합니다.

이로 인해 보조 인덱스를 통한 조회는 두 번의 인덱스 탐색을 거칩니다.

1. 보조 인덱스 탐색 (INDEX RANGE SCAN): 조건에 맞는 행의 PK 값을 가져옵니다.
2. 기본키 인덱스 탐색 (INDEX UNIQUE SCAN): PK 값으로 B-Tree 루트부터 다시 내려가 실제 데이터를 찾습니다. 이 과정이 매칭 건수만큼 반복됩니다.

-- sales_iot: sale_id(PK) 기반의 IOT, sales_iot_date: sale_date 컬럼 보조 인덱스

-- 보조 인덱스에서 PK만 조회 (추가 탐색 불필요)
SELECT sale_id FROM sales_iot WHERE sale_date = ?;
-- -> 보조 인덱스 내부에 PK(sale_id)가 이미 포함되어 단독으로 처리됩니다.

-- 보조 인덱스에서 일반 컬럼 조회 (이중 인덱스 탐색 발생)
SELECT eur_value FROM sales_iot WHERE sale_date = ?;
-- -> 보조 인덱스 스캔 후, 반환된 각 PK에 대해 기본키 인덱스를 UNIQUE SCAN으로 재탐색합니다.

---

8. 핵심 요약 및 결론

1. 인덱스 스캔 범위와 테이블 접근 횟수를 분리해서 생각하라: 스캔 범위를 줄이는 것만큼, 반환된 ROWID들이 실제로 몇 개의 블록을 방문하게 만드는지(클러스터링 팩터)도 성능에 결정적 영향을 미칩니다.
2. 인덱스 필터 조건을 의도적으로 설계하라: 액세스 조건이 될 수 없는 컬럼도 결합 인덱스 뒤쪽에 배치하면, 테이블 방문 전 인덱스 단에서 불필요한 행을 미리 걸러내 무작위 I/O를 획기적으로 줄일 수 있습니다.
3. 커버링 인덱스는 강력하지만 유지보수에 주의하라: 테이블 접근을 완전히 없애는 최고의 수단이지만, 쿼리가 조금만 바뀌어도 최적화가 깨지므로 주석 등으로 설계 의도를 명확히 기록해야 합니다.
4. 테이블 물리 모델을 조회 패턴에 맞춰 선택하라: PK 단독 검색이 지배적이면 클러스터형 테이블이, 다양한 컬럼에 걸친 복합 조회가 잦으면 힙 테이블과 커버링 인덱스 조합이 적합합니다.

---

Reference:

• SQL Performance Explained - Markus Winand