오라클 인덱스 구조와 동작 방식 정리 (Oracle Index)

데이터베이스 성능 최적화를 위해 가장 중요한 요소 중 하나가 바로 인덱스(INDEX)입니다. 오라클(Oracle) 데이터베이스에서 인덱스는 대량의 데이터를 빠르게 검색하는 핵심 요소이며, 잘 설계된 인덱스는 쿼리 성능을 획기적으로 향상시킬 수 있습니다. 이번 포스팅에서는 오라클 인덱스의 구조와 동작 방식을 상세히 분석해보겠습니다.

---

1. 인덱스란?

인덱스는 데이터베이스 테이블의 특정 컬럼을 기준으로 정렬된 별도의 데이터 구조입니다. 마치 책의 색인과 비슷한 개념으로, 특정 데이터를 찾기 위해 테이블을 처음부터 끝까지 탐색하는 Full Table Scan을 피하고 빠르게 원하는 데이터를 찾을 수 있도록 도와줍니다.

인덱스의 장점

• 검색 속도 향상: 인덱스를 활용하면 특정 조건을 만족하는 데이터를 훨씬 빠르게 찾을 수 있습니다.
• 정렬 성능 향상: ORDER BY 절을 사용할 때 인덱스를 활용하면 정렬 속도가 개선됩니다.
• 조인 성능 개선: 여러 테이블을 조인할 때 인덱스가 존재하면 탐색 속도가 증가합니다.

인덱스의 단점

• DML(INSERT, UPDATE, DELETE) 성능 저하: 인덱스는 데이터를 삽입하거나 수정할 때 추가적인 연산이 필요하므로 성능 저하가 발생할 수 있습니다.
• 디스크 공간 추가 사용: 인덱스도 별도의 저장 공간을 차지하므로, 테이블이 많을수록 스토리지 비용이 증가할 수 있습니다.

---

2. 오라클 인덱스의 종류

오라클에서는 다양한 유형의 인덱스를 제공합니다. 각각의 인덱스는 특정한 용도와 특징이 있으며, 올바르게 사용해야 최적의 성능을 유지할 수 있습니다.

2.1 B-Tree 인덱스

오라클에서 가장 기본적이고 널리 사용되는 인덱스입니다. B-Tree(Balanced Tree) 구조를 활용하여 검색을 빠르게 수행하며, 데이터가 정렬된 상태로 유지됩니다.

B-Tree 인덱스의 구조

• 루트 노드(Root Node): 트리의 최상단 노드로, 여러 개의 브랜치(Branch) 노드를 가리킵니다.
• 브랜치 노드(Branch Node): 데이터가 저장된 리프 노드로 가는 경로를 제공하는 중간 노드입니다.
• 리프 노드(Leaf Node): 실제 데이터의 ROWID가 저장되며, 검색 시 해당 ROWID를 활용해 데이터를 빠르게 찾습니다.

B-Tree 인덱스의 동작 방식

1. 검색할 키 값을 루트 노드에서 확인합니다.
2. 해당 키 값이 포함된 브랜치 노드로 이동합니다.
3. 리프 노드에서 정확한 ROWID를 찾아 데이터가 저장된 위치를 반환합니다.

사용 사례: WHERE 조건이 자주 사용되는 컬럼, 중복 값이 많지 않은 컬럼에 적합합니다.

 

2.2 비트맵(Bitmap) 인덱스

비트맵 인덱스는 데이터 값의 종류가 적고 중복도가 높은 컬럼에서 유용합니다. 각 값에 대해 비트 배열을 생성하고, 해당 값이 존재하는 ROWID 위치를 비트 값(0 또는 1)으로 표시하는 방식입니다.

사용 사례: 성별, 지역, 상태 코드와 같은 중복도가 높은 컬럼에 적합합니다.

 

2.3 함수 기반(Function-Based) 인덱스

컬럼 자체가 아니라 컬럼 값에 함수 연산을 적용한 결과를 인덱스로 생성하는 방식입니다.

사용 사례:

• UPPER(name) 처럼 대소문자를 무시하고 검색하는 경우
• 날짜 변환이 자주 필요한 경우 (TO_CHAR(hire_date, 'YYYY-MM'))

 

2.4 도메인(Clustered) 인덱스

도메인 인덱스는 사용자가 정의한 특수한 인덱스로, 일반적으로 텍스트 검색(Oracle Text), 공간 데이터(Spatial Data) 등에 활용됩니다.

사용 사례: 텍스트 검색, XML 데이터 검색, 이미지 및 비디오 데이터 검색 등

---

반응형

3. 인덱스 성능 최적화

인덱스가 무조건 성능을 향상시키는 것은 아닙니다. 잘못된 인덱스 설계는 오히려 성능을 저하시킬 수도 있습니다. 다음은 인덱스 성능을 최적화하는 핵심 팁입니다.

인덱스 성능 최적화 전략

1. 자주 사용하는 컬럼에만 인덱스를 적용하라
   • 모든 컬럼에 인덱스를 생성하면 오히려 DML 성능이 저하됩니다.
2. 중복도가 낮은 컬럼에는 B-Tree 인덱스를 사용하고, 중복도가 높은 컬럼에는 비트맵 인덱스를 사용하라
3. 인덱스 컬럼의 순서를 고려하라
   • 다중 컬럼 인덱스를 사용할 경우, 조회 조건에서 자주 사용되는 컬럼을 앞쪽에 배치하는 것이 좋습니다.
4. ANALYZE 및 통계 정보를 주기적으로 갱신하라
   • DBMS_STATS.GATHER_TABLE_STATS 명령어를 사용해 최신 통계 정보를 유지하면 옵티마이저가 최적의 실행 계획을 수립할 수 있습니다.
5. 불필요한 인덱스는 삭제하라
   • 사용되지 않는 인덱스는 삭제하여 불필요한 디스크 공간 낭비를 줄이고, DML 성능을 향상시킵니다.

---

4. 결론

오라클 인덱스는 데이터베이스 성능을 최적화하는 중요한 요소 중 하나입니다. 하지만 무조건 인덱스를 추가한다고 성능이 좋아지는 것은 아니며, 올바른 인덱스 유형을 선택하고 주기적인 관리가 필요합니다.

 

핵심 요약

• B-Tree 인덱스는 가장 일반적인 인덱스로, 검색 속도를 향상시킴.
• 비트맵 인덱스는 중복도가 높은 컬럼에 적합함.
• 함수 기반 인덱스는 특정 연산 결과를 인덱스로 활용 가능함.
• 인덱스 최적화를 위해 자주 사용하는 컬럼을 분석하고 필요 없는 인덱스는 제거할 것.

 

index hint 사용법에 대한 포스팅 ↓

https://biesil.tistory.com/51

오라클 SQL 최적화를 위한 Oracle 인덱스 힌트(Index Hint) 사용법과 예제