[바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 2장 인덱스 유형과 특징

새롭게 안 사실들

B-Tree 인덱스 구조를 가진 테이블의 엑세스 속도

100 건의 로우를 가진 테이블에서나 100만건의 로우를 가진 테이블에서나 한 건을 엑세스 하는 속도는 거의 같다는 점을 새롭게 알았다.

B-Tree의 B가 Binary외에 Balanced도 있다는 의미는 이미 알았지만 엑세스 하는 속도도 균형 잡았을 줄은 몰랐다.

인덱스의 종류들

B-Tree 인덱스의 구조 - 루트, 브랜치, 리프 블록

함수기반 인덱스에 대하여

NULL 값에 대하여

나는 단순히 NULL = 없음, 값은 값이지만 존재하지 않는 값으로 생각하고 있었다.

하지만 이 책에서는 NULL을 '미확정 값'이라고도 표현하고 있어 개념을 재확립 하였다.

---

공부한 내용들

제 2장 인덱스의 유형과 특징

인덱스의 개념

• 인덱스는 데이터베이스 내에서 특정 데이터를 빠르게 찾기 위한 구조로, 데이터를 정렬하여 탐색 속도를 향상시키는 역할을 한다.
• 물리적으로는 키(Key)와 그것이 가리키는 데이터의 주소(ROWID)로 구성된다.

목적

• 데이터 접근 속도 향상.
• 데이터 무결성 강화.
• 시스템 성능 최적화.

인덱스의 구성

• 키(Key) - 특정 데이터를 식별하기 위한 값.
• ROWID -데이터의 물리적 저장 위치를 식별하는 값.

인덱스의 유형

• B-Tree 인덱스 - 관계형 데이터베이스에서 가장 널리 사용되는 기본적인 인덱스.
• 비트맵 인덱스(Bitmap Index) - 중복 값이 많은 경우 사용.
• 리버스 키 인덱스(Reverse Key Index) - 고유 키 값의 불균형 문제를 해결.
• 해시(Hash) 클러스터 인덱스 - 해시 함수를 사용하여 데이터 저장.
• 함수 기반(Function-based) 인덱스 - 특정 함수 값에 대한 검색을 지원.

2.1 B-Tree 인덱스

정의

• 관계형 데이터베이스에서 가장 일반적으로 사용되는 인덱스.
• 데이터를 정렬된 상태로 저장하며, 효율적인 검색 및 삽입/삭제를 지원.

특징

• 범위 검색(Range Scan)과 단일 키 검색(Unique Scan)에 적합.
• 데이터가 균형 트리(Balanced Tree) 구조로 저장되어 탐색 속도가 일정.
• 리프 블록에는 정렬된 데이터의 ROWID가 포함.

2.1.1 B-Tree 인덱스의 구조

구조

• 루트 블록: 트리의 최상위 노드로, 데이터 검색의 출발점.
• 브랜치 블록: 트리의 중간 노드로, 데이터 검색 경로를 구성.
• 리프 블록: 데이터의 실제 위치를 나타내는 ROWID와 키 값을 저장.

작동 원리

• 루트 → 브랜치 → 리프 블록 순서로 탐색.
• 데이터는 항상 정렬 상태를 유지하여 검색 효율성 보장.
• 리프 블록 간에는 순차 접근을 위한 포인터가 포함.

B-Tree의 특징

• 데이터 삽입/삭제 후에도 균형 트리 상태 유지.
• 데이터가 물리적으로 정렬되어 탐색 속도가 빠르다.

B-Tree 인덱스의 탐색 과정

1. 루트 블록 탐색
   • 검색 키 값을 기준으로 브랜치 블록으로 이동.
2. 브랜치 블록 탐색
   • 키 값을 기준으로 하위 브랜치나 리프 블록으로 경로를 좁힘.
3. 리프 블록 탐색
   • 최종 키 값을 비교하여 해당 데이터의 ROWID를 반환.

ROWID의 구성

구조

• Data Object 번호 - 데이터베이스 객체 식별.
• Relative File 번호 - 데이터 파일 식별.
• Block 번호 - 데이터가 저장된 블록의 위치.
• Row 번호 - 블록 내 데이터 위치.

활용

• ROWID를 통해 테이블 데이터에 대한 정확한 물리적 주소 제공.
• 인덱스를 통해 데이터에 빠르게 접근 가능.

2.1.2 B-Tree 인덱스의 조작 (Operation)

가) 인덱스 생성 (Creation)

1. 정렬 과정 및 리프 블록(Leaf Block) 생성
   • 인덱스를 생성하려면 테이블의 데이터를 정렬(Sort)해야 하기 때문에 데이터는 지정된 정렬 영역(Sort Area)에 임시로 저장된 후 정렬됨.
   • 정렬된 데이터는 리프 블록(Leaf Block)에 순서대로 기록됨.
   • 각 리프 블록은 테이블 데이터의 정렬된 결과를 저장하며, 한 블록이 가득 차면 새로운 리프 블록이 생성됨.
2. 브랜치 블록(Branch Block) 생성
   • 리프 블록이 모두 생성된 후, 각 리프 블록의 첫 번째 로우 정보를 사용해 브랜치 블록을 생성.
   • 브랜치 블록은 리프 블록의 위치 정보를 저장하여 상위 계층 구조를 형성.
3. 루트 블록(Root Block) 생성
   • 브랜치 블록들이 생성된 후, 최종적으로 루트 블록이 생성
   • 루트 블록은 트리 구조의 최상위에 위치하며, 데이터 검색의 시작점 역할을 함.
   • 루트 블록을 통해 하위 브랜치 블록 및 리프 블록에 빠르게 접근할 수 있음.

PCTFREE 설정과 역할

PCTFREE란?

• PCTFREE는 블록 내 여유 공간을 설정하는 매개변수로, 새로운 데이터 추가 및 변경 시 공간 확보를 위한 기준을 제공함.

역할 및 효과

• 블록 내 저장 효율성 증가
  • PCTFREE 설정은 블록 내 데이터 추가 작업을 원활하게 하여 리프 블록(Leaf Block) 내 저장 공간 감소를 유도함.
• 트리 깊이 감소
  • 리프 블록 감소는 브랜치 블록과 루트 블록의 증대를 억제하며, 트리 구조의 깊이를 최소화하는 데 기여함.

고려사항

• 가능한 경우 최대한 많은 인덱스 컬럼 수를 줄이는 것이 효과적임.
• 큰 블록 크기(DB_BLOCK_SIZE)를 지정하면 효율적인 데이터 관리가 가능.
• 이 설정은 데이터베이스의 전반적인 정의에 국한되지 않으며, 테이블스페이스에 대한 세부 설정으로 구체화될 수 있음.

Key Compression (키 압축)

기능 및 사용 목적

• Key Compression은 인덱스 키 값이 중복될 때 발생하는 비효율성을 개선하기 위한 방식.
• 다중 컬럼이 인덱스 키로 지정된 경우, 반복되는 키 값을 하나의 압축된 키로 처리하여 저장 공간을 절약할 수 있음.

효과

• 데이터 저장 밀도를 증가시키며, 불필요한 키 중복 문제를 해결함.
• 압축된 키는 트리 구조 내에서 데이터를 효율적으로 유지하여 블록 분할을 방지함.

예시

• 아래 명령문은 Key Compreesion을 적용한 인덱스를 생성함.

CREATE INDEX ord_customer_idx
ON orders (customer_id, sales_id)
COMPRESS 1;

나) 인덱스 블록의 분할 (Split)

분할이 발생하는 이유

• 인덱스 로우는 정렬되어 저장되어야 하기 때문에, 새로운 로우가 기존 블록에 들어갈 공간이 부족할 경우 분할(Split)이 발생.
• 기존의 위치에 새로운 로우를 삽입하기 위해 블록의 분할 작업이 필요.

분할 과정

• PCTFREE 한계 도달
  • 기존 블록의 여유 공간(PCTFREE)을 모두 사용하면, 새로운 블록을 생성하여 데이터를 이동시켜야 함.
  • 새로운 블록은 기존 블록과 연결되어, 상위 브랜치 블록에 정보가 추가됨.
• 데이터 이동
  • 기존 블록에서 데이터를 분리하여 절반 정도를 새로운 블록으로 이동.
  • 이로 인해 상위 구조에도 변화가 발생하며, 브랜치 블록에 새로운 연결 정보가 추가.
• 분할의 결과
  • 새로 생성된 블록은 기존 블록과 연결된 상태로 저장되며, 트리 구조를 유지.
  • 데이터 검색 시 성능에 큰 영향을 미치지 않음.

중간 삽입의 문제점

• 중간 값 삽입으로 인해 정렬 순서를 유지해야 하므로 블록 분할이 빈번히 발생.
• 새로운 데이터가 삽입되면, 기존의 데이터를 재구성하는 작업이 필요.
• 중간 삽입 시 2/3 이상 채워진 블록에 분할이 일어나면 저장 공간이 크게 증가.

재생성(Rebuild)의 필요성

• 분할이 자주 발생하여 트리 깊이가 증가할 경우, 인덱스를 정기적으로 재생성해야 효율을 유지.
• 저장 공간 낭비와 블록 분할로 인한 성능 저하를 방지하기 위해, 재구성 작업이 필요.

다) 데이터의 삭제 및 갱신

데이터 삭제

• 테이블에서 로우를 삭제해도 인덱스에서는 해당 로우가 실제로 삭제되지 않고, 삭제 표시(Flag)가 추가된다.
• 이로 인해 삭제된 공간이 새로운 데이터 삽입에 사용되기보다는 인덱스 블록이 계속 스캔되어야 하는 상황이 발생한다.
• 만약 특정 리프 블록 내의 모든 로우가 삭제된 경우
  • 브랜치 블록에는 삭제된 리프 블록을 가리키는 삭제 표시가 남는다.
  • 브랜치 블록까지 영향을 미치며, 트리 깊이 증가와 검색 성능 저하를 초래할 수 있다.
  • 불필요한 블록에 접근하지 않도록 정기적인 블록 정리 작업(재구성)이 필요하다.

데이터 갱신

• 컬럼 값 변경 시의 처리
  • 갱신은 내부적으로 삭제와 삽입 작업으로 처리된다.
  • 기존 데이터를 삭제하고, 새로운 데이터를 삽입하는 방식으로 갱신이 이루어진다.
  • 이 과정에서 추가적인 저장 공간이 필요하며, 인덱스 깊이가 증가할 가능성이 있다.

삭제와 갱신 작업이 빈번하게 발생할 경우, 저장 공간 낭비와 검색 성능 저하라는 문제가 발생할 수 있다. 삭제된 데이터가 물리적으로 제거되지 않고 삭제 표시만 남는 경우, 불필요한 공간이 계속 축적되어 인덱스 트리의 깊이가 불필요하게 증가하게 되는데 이로 인해 데이터 접근 시간이 길어지고 시스템 성능 저하로 이어질 수 있다.

 

이러한 문제를 방지하려면 정기적인 인덱스 재구성(Rebuild) 작업이 필요하다. 이를 통해 불필요한 공간을 제거하고 트리 구조를 최적화할 수 있고, 삭제와 삽입 작업이 적은 컬럼을 인덱스로 사용하는 것이 성능 유지에 도움이 된다.

만약 DML 작업(삭제, 삽입, 갱신)이 많은 환경이라면, 이를 효율적으로 처리할 수 있는 관리 방법을 도입해 성능 저하를 방지해야 한다.

라) 인덱스를 경유한 검색

검색 과정

• 루트 블록에서 검색을 시작하여, 검색 조건에 해당하는 키 값을 따라 브랜치 블록으로 이동한다.
• 브랜치 블록을 순차적으로 탐색하여 해당 리프 블록에 접근한다.
• 리프 블록에서 검색 조건과 일치하는 ROWID를 찾아내고, 이를 기반으로 실제 테이블 데이터를 액세스한다.

상세 설명

• 브랜치 블록의 헤더에는 각 블록의 첫 번째 로우보다 작은 값을 갖는 하위 블록의 주소 정보(DBA, Data Block Address)가 저장된다.
• 검색 과정에서는 ‘Lmc’(Left Most Child Block)부터 시작해 조건에 부합하는 블록을 찾아 이동하며, 이는 순차적이고 효율적인 데이터 검색을 가능하게 한다.
• 리프 블록에 도달하면, 해당 조건에 부합하는 키 값을 가진 데이터를 찾아 테이블에 접근한다.

검색 예시

• 검색 조건이 SELECT * FROM tab1 WHERE col1 = 'B' AND col2 = 'ACC'인 경우, 검색 과정은 다음과 같이 진행됨.
  1. 루트 블록에서 시작
     • 루트 블록의 정보를 확인하며 가장 작은 값(Lmc: Left Most Child Block)을 기준으로 하위 브랜치 블록으로 이동한다.
  2. 브랜치 블록 탐색
     • 브랜치 블록에서 조건에 적합한 데이터 블록 주소(DBA: Data Block Address)를 찾고, 해당 정보를 따라 리프 블록으로 이동한다.
     • 브랜치 블록의 헤더에는 각 블록의 첫 번째 값보다 작은 값들을 가리키는 하위 블록의 DBA가 저장되어 있다.
  3. 리프 블록 접근 및 검색
     • 리프 블록에 도달한 후, 조건에 맞는 키 값을 가진 ROWID를 찾는다.
     • ROWID를 기반으로 테이블 데이터를 액세스하여 최종적으로 검색 결과를 반환한다.
  4. 범위 검색의 경우
     • 조건이 LIKE 'AC%'와 같은 범위 검색일 경우, 브랜치 블록과 리프 블록을 순차적으로 스캔하여 모든 값을 비교한다.
     • 이 과정은 각 블록의 값들을 순차적으로 접근하며, 조건에 부합하는 데이터만 반환한다.
• 이 구조는 B-Tree 인덱스가 제공하는 효율적인 검색 과정을 나타내며, 조건이 정확할수록 검색 속도가 향상된다는 점을 강조합니다.

이와 같은 방식으로, B-Tree 인덱스는 검색 조건에 따라 효율적인 데이터 액세스를 지원한다.

2.1.3 리버스 키 인덱스 (Reverse Key Index)

개념 및 정의

리버스 키 인덱스는 각 데이터의 컬럼 값을 역순으로 변환하여 저장하는 방식이다. 예를 들어, 값이 12345인 경우, 리버스 키 인덱스는 이를 54321로 변환하여 저장한다.
이 방법은 데이터가 순차적으로 증가하는 경우에도 값의 분포를 무작위로 만들어, 특정 블록에 데이터가 집중되는 현상을 방지한다.

리버스 키 인덱스의 특징

데이터의 무작위 분포

순차적으로 증가하는 값(예: 일련번호)의 경우, 리버스 키를 적용하면 데이터가 고르게 분포되어 특정 블록에 과부하가 발생하지 않는다.

• 예시: 11112, 11113 값은 역순 저장 시 21111, 31111로 분산된다.

저장 효율성 향상

데이터가 특정 블록에 집중되지 않아 클러스터링 팩터를 유지할 수 있으며, 랜덤한 액세스 성능이 개선된다.

제약 사항

• 범위 검색 (BETWEEN, LIKE)이 불가능하다.
• 리버스 키는 정확한 값(= 연산)을 기준으로 검색할 때만 유용하다.

제한 사항 및 단점

범위 검색의 한계

• 리버스 키는 데이터의 역순으로 저장되기 때문에 범위 검색이나 특정 값의 비교에서는 효율적으로 작동하지 않는다.
• LIKE나 BETWEEN과 같은 조건에서는 리버스 키 인덱스를 활용할 수 없는 한계가 있다.

NOSORT 옵션 사용 불가

리버스 키는 비트맵 인덱스와 마찬가지로 정렬을 기반으로 하지 않기 때문에 NOSORT 옵션을 사용할 수 없다.

2.2. 비트맵(Bitmap) 인덱스

개념

• 비트맵 인덱스는 데이터베이스에서 컬럼 값을 비트(Bit)의 최소 단위로 표현하여 저장하는 방식이다.
• ROWID를 비트로 연결하여 인덱스를 생성, 관리하며, 이를 통해 저장 공간을 크게 절약하고 효율적으로 관리할 수 있다.
• 기존 B-Tree 인덱스가 해결하지 못한 대규모 데이터 처리 문제를 비트맵 인덱스를 통해 해결할 수 있다.

특징

• 저장 공간 절약
  • 비트맵 방식으로 데이터를 관리함으로써 데이터 저장에 필요한 공간이 크게 감소한다.
• 효율적인 검색
  • 비트 연산을 사용해 빠른 검색이 가능하며, 특히 데이터를 집계하거나 필터링하는 작업에서 강점이 있다.
• 제한사항
  • 변경 작업(INSERT, UPDATE, DELETE)이 빈번한 환경에서는 성능이 저하될 수 있어 주로 읽기 전용 데이터나 변경 빈도가 낮은 데이터에 적합하다.

2.2.1. 비트맵 인덱스의 탄생 배경

기존 인덱스의 한계

• 관계형 데이터베이스에서 특정 데이터를 검색하기 위해 B-Tree 인덱스를 주로 사용했지만, 몇 가지 문제점이 있었다.

B-Tree 인덱스의 문제점

1. 컬럼 값의 저장 방식
   • B-Tree 인덱스는 실제 컬럼 값을 인덱스에 보관하고 있어야 하며, 이는 분명한 중복 문제를 초래하게 된다.
   • 특히 수많은 데이터가 동일한 값으로 이루어졌을 때, 데이터 저장과 관리의 효율성이 크게 저하된다.
2. 컬럼 값의 분포 제한
   • 컬럼 값의 분포도가 낮을 경우(즉, 데이터가 특정 값에 치중된 경우), 인덱스는 효과적으로 작동하지 않게 된다.
   • 이러한 문제를 해결하려면 다중 인덱스를 생성하거나, 효율적인 컬럼 설계가 필요하다.
3. 조건 제한
   • NULL 값, NOT 연산자, 복잡한 OR 조건을 사용하는 경우, B-Tree 인덱스는 값을 효율적으로 찾지 못한다.
   • 이러한 제한은 결과적으로 특정 쿼리 조건에 적합하지 않은 결과를 초래할 수 있다.
4. 범위 스캔의 비효율성
   • 조건과 일치하지 않는 데이터도 모두 확인해야 하기 때문에 많은 범위를 스캔해야 하는 구조적 한계가 존재한다.

비트맵 인덱스의 필요성

• 위와 같은 B-Tree 인덱스의 한계를 해결하기 위해 비트맵 인덱스가 등장하게 됐다.
• 비트맵 인덱스는 다음과 같은 상황에서 큰 효과를 발휘한다.
  1. 데이터 값이 한정적이거나 고정적일 경우.
  2. 대량의 데이터를 검색할 때도 효율적으로 동작할 수 있는 구조적 장점을 가진다.

비트맵 인덱스의 특징

• 비트맵 인덱스는 특정 컬럼의 값을 비트(bit)로 표현하여 저장 공간을 줄이고, 검색 효율을 크게 향상시킨다.
• B-Tree와 달리 컬럼 값의 분포나 중복 문제가 비트 단위로 해결되기 때문에, 검색에 더 적합한 조건을 제공할 수 있다.

비트맵 인덱스는 기존 B-Tree 방식으로는 해결할 수 없었던 문제를 기술적으로 완화하며, 특정 데이터 조건에서 성능 최적화를 실현하는데 이를 통해 대량 데이터를 효율적으로 처리할 수 있어 다양한 검색 조건에 적합한 인덱스 구조로 널리 활용되고 있다.

2.2.2 비트맵 인덱스의 구조와 특성

비트맵 인덱스의 구조

• 비트맵 인덱스는 루트 블록과 브랜치 블록이 B-Tree 인덱스와 유사한 구조로 되어 있지만, 리프 블록은 비트맵 형식으로 구성된다.
• 각 컬럼 값에 해당하는 비트맵을 저장하며, 이는 특정 컬럼 값이 존재하는 행(Row)의 위치를 나타낸다.
• 예시
  • Yellow 컬럼 값은 비트맵 1000100010으로 표현되어, 특정 행에 값이 존재하는 위치를 나타낸다.
  • 각 비트는 1로 설정되어 있으면 해당 컬럼 값이 존재함을 나타낸다.

저장 방식

• 비트를 저장하는 형식은 Start Rowid ~ End Rowid로 지정됩니다.
• 키 압축(Key Compression) 방식이 적용되어 저장 공간이 절약되며, 단일 로우가 효율적으로 저장됩니다.

비트맵 인덱스의 특성

1. 효율적인 데이터 액세스
   • 비트를 조합해 특정 조건에 맞는 데이터의 위치를 빠르게 파악한다.
   • AND, OR 연산자를 활용하여 조건을 만족하는 결과를 도출할 수 있다.
   • 예를 들어, OR 연산은 어느 하나라도 1인 경우 해당 로우가 조건을 만족하며, AND 연산은 모든 비트가 1이어야 한다.
2. 저장 공간 절약
   • 비트맵은 비트 단위로 저장되어, 기존 인덱스 방식보다 저장 공간이 훨씬 절약된다.
   • 이는 대량 데이터를 처리하는 데 매우 적합하다.
3. 특정 컬럼에 최적화
   • 비트맵 인덱스는 카디널리티가 낮은(중복도가 높은) 컬럼에 적합하다.
   • 반복되는 데이터가 많을수록 비트맵 인덱스의 효율성이 더욱 높아진다.

비트맵 인덱스의 한계

• 비트맵 인덱스는 범위 검색(LIKE, BETWEEN 등)에 적합하지 않다.
• 선형 형식으로 저장되므로, 수정이 빈번한 OLTP 환경에서는 비효율적이다.
• 수정 시 블록 레벨 잠금(Block-Level Locking)이 발생하여 성능 저하를 유발할 수 있다.

활용 범위

• 비트맵 인덱스는 데이터 웨어하우스나 OLAP 환경에서 주로 사용된다.
• OLTP에서도 데이터 수정이 적고 검색이 빈번한 환경에서는 제한적으로 적용 가능하다.2.2.3. 비트맵 인덱스의 액세스

2.2.3. 비트맵 인덱스의 액세스

비트맵 인덱스를 사용하는 과정

1. 비트맵 연산
   • 비트맵 인덱스를 활용하여 테이블을 액세스하기 전에 AND, OR 등의 연산을 수행한다.
   • (예시 쿼리) SELECT sum(weight) FROM parts WHERE size = 'MED' AND color = 'RED'
   • 비트맵 연산 결과에서 조건을 만족하는 비트맵 값(010010)을 추출한다.
2. ROWID 변환 (Bitmap Conversion)
   1. 추출된 비트맵 값을 ROWID로 변환하여 해당 테이블을 액세스한다.
   2. 변환된 ROWID는 테이블 데이터에 대한 직접적인 액세스를 가능하게 한다.

비트맵 액세스의 처리 단계

• BITMAP CONVERSION
  • 테이블 액세스를 위해 비트맵 값을 ROWID로 변환.
  • 반대로 ROWID를 비트맵으로 변환하기도 한다.
• BITMAP 연산
  • AND 연산
    • 두 개의 비트맵을 비교하여 공통되는 부분만 추출.
  • OR 연산
    • 두 개의 비트맵을 합쳐 조건을 만족하는 모든 영역을 추출.
  • BITMAP MINUS
    • 특정 조건을 제외한 나머지를 추출.
• 결과 조합
  • 여러 연산을 통해 최종적인 ROWID 값을 산출.
  • 해당 ROWID 값을 기반으로 테이블 데이터에 액세스한다.

처리 계획 (Execution Plan)

실행 계획은 다음과 같은 순서로 이루어진다.

1. SELECT STATEMENT
2. SORT (AGGREGATE)
3. BITMAP CONVERSION (TO ROWIDS)
4. BITMAP AND 연산
5. 개별 BITMAP INDEX 검색

COLOR_BIX와 SIZE_BIX 각각에 대해 단일 비트맵 검색 수행함.

비트맵의 효율성

• 비트맵 연산은 대량의 데이터를 효율적으로 처리하며, 조건에 맞는 데이터만 추출한다.
• 기존 B-Tree 방식과 비교하여 검색 조건의 다양성을 지원하고, 특정 조건에서 성능을 최적화한다.

2.3 함수기반 인덱스(FBI, Funtion-Based Index)

2.3.1. 함수기반 인덱스의 개념 및 구조

함수기반 인덱스란 무엇인가?

• 함수기반 인덱스는 테이블의 물리적 칼럼이 아닌, 논리적 칼럼을 인덱스로 생성한 구조.
• 일반적인 쿼리에서 사용되는 조건에 포함된 계산된 결과나 가공된 값들을 인덱스화여 성능을 최적화합니다.

구조 및 특징

논리적 컬럼의 생성

• 테이블의 컬럼 값을 기반으로 논리적 연산(예: CNT * PRICE)을 수행하여 새로운 컬럼 값을 생성.
• 이러한 논리적 결과는 테이블에 물리적으로 존재하지 않으며, 가상적으로 관리됨.
• 예를 들어, CREATE INDEX prod_idx1 ON prod (cnt * price);와 같은 형태로 생성.

사용 가능한 연산

• 산술식(Arithmetic Expression): 사칙연산 등 기본적인 연산 가능.
• 사용자 정의 함수(User-Defined Function): 개발자가 정의한 특정 함수의 결과를 인덱스로 생성 가능.
• SQL 내장 함수(Built-in Function): SQL에서 제공하는 함수도 적용 가능.

제한사항

• 집계 함수 사용 불가
  • SUM, AVG와 같은 집계 함수는 인덱스로 생성할 수 없습니다.
• 특정 데이터 타입 제한
  • LOB, REF, Object Type 컬럼에 대한 적용은 제한적.
• 논리적 단위 제한
  • 논리적 결과가 특정한 데이터 범위로 표현되지 않는 경우 적용하기 어렵습니다.

구조의 운영 및 관리

• 함수기반 인덱스는 생성 시 시스템에서 새로운 이름(SYS_Nnnnnnn)으로 관리.
• 쿼리에서 조건이 포함될 때, 시스템은 자동으로 함수기반 인덱스를 참조하여 데이터 접근을 최적화.

DBMS의 지원

• DBMS마다 약간의 제약이 있으며, 일부 DBMS에서는 FUNCTION-BASED INDEX 라는 이름으로 긴으을 지원함.
• 일부 DBMS에서는 해당 기능을 지원하지 않을 수 있음.

2.3.2. 함수기반 인덱스의 제약사항

일반적인 제약사항

• 함수기반 인덱스는 컬럼 값과 일반성을 유지하는 데 추가적인 관리가 필요함.
• 일부 경우, 칼럼 값이 일반성과 불일치하면 데이터 검색 결과가 예상과 다를 수 있음.
• 함수기반 인덱스는 특정 데이터 처리나 조건에서 사용할 수 없거나 비효율적 일 수 있다.

주요 제약사항

• 비용 문제
  • 생성과 유지 관리에서 비용이 발생하며, 적절한 환경에서만 사용해야 한다.
• 제약 설정
  • 통계 정보를 반드시 생성해야 한다.
  • DETERMINISTIC으로 선언된 함수만 사용할 수 있다.
  • QUERY_REWRITE_ENABLED와 QUERY_REWRITE_INTEGRITY가 설정되어야 한다.
• 사용자 권한
  • INDEX CREATE와 같은 권한이 필요하다.
• NULL값 처리
  • INDEX CREATE와 같은 권한이 필요하다.

시스템 관련 제약사항

• ALTER INDEX
  • 사용 가능한 상태(ENABLE) 또는 미사용 상태(UNUSABLE)를 설정하여 유지 관리해야 한다.
  • 재구축(Rebuild)이 필요한 경우 발생할 수 있다.
• 스칼라 서브쿼리
  • 특정 조건을 포함한 함수기반 인덱스 생성이 불가능하다.
• NLS 설정
  • NLS와 같은 설정이 적용될 경우, 예상치 못한 결과가 나올 수 있다.

테이블 및 데이터와 관련된 제약사항

• 참조 테이블
  • 참조 테이블에서 데이터가 변경되면 함수기반 인덱스가 무효화될 수 있다.
  • 논리적이나 물리적 구조에 영향을 미칠 가능성이 있다.
• 데이터 수정
  • 데이터 변경 작업이 빈번할 경우, 인덱스의 성능에 영향을 미칠 수 있다.

종합적인 관리 필요성

• 함수기반 인덱스는 특정한 환경에서 강력한 성능을 발휘하지만, 잘못된 설정은 검색 속도와 정확성에 악영향을 미칠 수 있다.
• 설정 및 유지 관리를 통해 제약을 해결하고 최적의 성능을 유지해야 한다.

2.3.3 함수기반 인덱스의 활용

함수기반 인덱스는 개념적으로 활용 가능성이 높음에도 불구하고 실제로는 기대보다 적게 활용되고 있음. 이는 함수기반 인덱스에 대한 잘못된 사용 방식이나 적절한 이해 부족에서 기인하였음. 하지만 함수기반 인덱스를 올바르게 이해하고 적합한 방식으로 적용했을 때, 데이터 처리 문제에 대한 훌륭한 해결책을 제공할 수 있다.

가. 테이블 설계상의 문제를 해결

함수기반 인덱스는 테이블 설계의 한계를 극복하고 데이터 처리의 효율성을 높이는 데 유용하게 활용된다. 이는 기존 설계에서 발생하는 데이터 처리 비효율성을 줄이고, 데이터 접근을 최적화하여 성능을 향상시킬 수 있다.

 

컬럼의 중간 부분 검색

• 문제
  • 과거에는 코드를 가장 중요하게 여기는 시절이 있었다. 당시 중요한 기밀 정보를 보호하기 위해 코드를 길게 작성하며 여러 데이터를 암호화하거나 중요한 정보를 포함했다. 그러나 이로 인해 컬럼의 중간 부분 검색이 필요한 경우가 자주 발생하였다. 예를 들어, 특정 데이터를 검색할 때 SUBSTR 함수로 중간값만 추출해야 하는 상황이 자주 나타난다. 이러한 설계는 원자 단위(Atomic Value)를 준수하지 않은 것으로, 효율적인 검색이 어렵다.
• 해결 방법
  • 함수기반 인덱스를 활용하여 SUBSTR 함수를 포함한 검색 조건을 최적화하는 인덱스를 생성함으로써 중간값 검색 시 발생하는 비효율성을 제거한다.

예제

CREATE INDEX from_loc_idx ON orders (SUBSTR(ship_id, 5, 3));
CREATE INDEX repair_ord_idx ON orders (SUBSTR(ship_id, 3, 2), ord_date);

 

조인 연결고리 컬럼이 대응하지 않는 경우

• 문제
  • 데이터 모델링 과정에서 설계된 조인 컬럼이 실제 데이터 조건과 맞지 않는 경우, 조인 시 성능 저하가 발생한다. 예를 들어, 다수의 컬럼을 결합하여 조인을 수행해야 하지만 인덱스가 이를 효율적으로 지원하지 못한다.
• 해결 방법
  • 조인 조건에 맞는 결합된 컬럼으로 함수기반 인덱스를 생성하여 문제를 해결한다.

예제

CREATE INDEX group_cd_idx ON item_group (class1 || class2 || class3);

일자 컬럼이 분할된 경우

• 문제
  • 초기 설계에서 날짜 데이터를 연도, 월, 일로 나누어 저장한 경우가 많다. 이러한 설계는 날짜 데이터를 조합하여 검색할 때 추가적인 처리 작업을 필요로 하며, 범위 검색 시 비효율적이다.
• 해결 방법
  • 날짜 데이터를 결합하여 효율적인 검색이 가능한 함수기반 인덱스를 생성한다.

예제

CREATE INDEX sal_date_idx ON sales (sal_yyyy || sal_mm || sal_dd);

 

데이터 타입이 상이한 조인 컬럼

• 문제
  • 서로 다른 테이블의 조인 컬럼 데이터 타입이 일치하지 않을 경우, 조인 시 성능 저하가 발생하고 인덱스를 활용하기 어렵다.
• 해결 방법
  • 데이터 타입 변환을 포함하는 함수기반 인덱스를 생성하여 데이터 타입 불일치로 인한 문제를 해결한다.

예제

CREATE INDEX deptno_idx ON emp (TO_NUMBER(deptno));

나. 오류 데이터의 검색 문제를 해결

오류 데이터의 문제는 데이터 정제(Cleansing)가 제대로 이루어지지 않아 발생한다. 이러한 데이터는 데이터베이스의 품질을 저하시킬 뿐만 아니라 검색 성능에도 부정적인 영향을 미친다.

실질적으로 데이터 정제는 비용과 시간이 많이 소모되는 작업으로, 함수기반 인덱스를 활용하여 오류 데이터를 검색하고 처리하는 효율적인 방법을 제안할 수 있다.

대소문자나 공백이 혼재된 컬럼의 검색

• 문제
  • 대소문자가 혼재되거나 공백이 포함된 데이터는 원하는 검색 결과를 얻기 어려움을 초래한다. 예를 들어 '성 과 명' 사이에 공백이 있거나 혼합된 경우를 들 수 있다.
• 해결 방법
  • UPPER 또는 REPLACE 함수 등을 활용한 함수기반 인덱스를 생성하여 이러한 문제를 해결한다.

예제

CREATE INDEX ename_upper_ix ON employees (UPPER(ename));
CREATE INDEX ename_upper_ix ON employees (UPPER(REPLACE(ename, ' ', '')));

 

NULL 값을 치환하여 검색

NULL값은 단순 10, 20과 같은 일종의 값이지만 독특한 특성을 가지고 있음.

단순히 공간을 줄여 주기 위해 사용하는 값이 아니라 '미확정 값'이란 의미를 가지고 있다.

• 문제
  • 컬럼에 NULL 값이 포함된 경우, 조건 검색 시 비교를 어렵게 만든다. 단순히 NULL값을 제외하거나 치환하지 않을 경우 쿼리 결과에 영향을 미칠 수 있다.
• 해결 방법
  • NVL 또는 COALESCE 함수를 적용한 함수기반 인덱스를 생성하여 NULL 값 문제를 해결할 수 있다.

예제

CREATE INDEX end_date_idx ON account_history (NVL(end_date, '99991231'));
CREATE INDEX start_end_idx ON account_history (NVL(end_date, '99991231'), start_date);

 

접두사(Prefix)를 채워서 검색

• 문제
  • 데이터의 일부 컬럼이 표준화되지 않아 특정 접두사를 가지지 않은 경우, 데이터 검색 및 조인에 문제가 발생.
• 해결 방법
  • DECODE나 CONCAT과 같은 함수와 조합하여 접두사를 포함하는 함수기반 인덱스를 생성한다.

예제

CREATE INDEX call_number_idx ON call_data (
  DECODE(SUBSTR(call_number, 1, 3), '018', '', '016') || call_number
);

다. 가공처리 결과의 검색

가공처리는 데이터 처리 과정에서 매우 다양하게 일어나며, 이를 효율적으로 처리하기 위해 함수기반 인덱스를 활용할 수 있는데 특히, 복잡한 계산 결과나 특정 조건을 만족하는 데이터를 검색할 때 성능을 크게 향상시킬 수 있다.

복잡한 계산 결과의 검색

• 문제
  • 주문 데이터를 처리하면서 판매금액 계산 등의 복잡한 연산을 수행해야 하는 경우, 기존 방식으로는 효율적인 처리가 어렵다.
• 해결 방법
  • 함수기반 인덱스를 생성하여 복잡한 계산 결과를 인덱스화하면, 효율적인 검색이 가능하다.

예제

CREATE INDEX order_amount_idx ON order_items (ITEM_CD, (order_price - NVL(order_discount, 0)) * order_count);

SELECT /*+ INDEX_DESC(x order_amount_idx) */ *
FROM order_items x
WHERE item_cd = :b1
AND ROWNUM <= 100;

위와 같은 방법으로 튜닝된 결과, 실행 시간이 0.1초 이하로 단축되었다.

 

말일(末日), 단가, 할당 비율의 검색

• 문제
  • 특정 달의 말일이나 단가 계산, 할당 비율 등을 기준으로 데이터를 검색해야 하는 경우, 기존 방식으로는 역순 데이터 정렬 등으로 인해 검색 효율이 저하된다.
• 해결 방법
  • 함수기반 인덱스를 활용하여 말일 계산, 단가, 비율 등을 사전에 인덱스화하여 검색 성능을 개선할 수 있다.

예제

CREATE INDEX sal_amount_idx ON sales (LAST_DAY(sal_date), sal_amount);

CREATE INDEX price_idx ON sales (ROUND(sal_amount / sal_quantity));

기간 및 컬럼 길이 검색

• 문제
  • 업무 처리 기간이나 컬럼 길이를 기준으로 데이터를 검색해야 하는 경우, 효율적인 데이터 필터링이 어렵다.
• 해결 방법
  • 기간이나 텍스트 길이를 계산하는 함수기반 인덱스를 생성하여 검색 성능을 최적화 한다.

예제

CREATE INDEX term_idx ON activities (expire_date - start_date);
CREATE INDEX source_length_idx ON print_media (text_length(source_text));

라. 오브젝트 타입의 인덱스 검색

오브젝트 타입(Object Type)을 활용하여 함수기반 인덱스를 생성하고 검색 효율을 높이는 방법에 대해 설명하고 있다.

이를 통해 복잡한 데이터 구조에서 사용자 정의 메서드(Method)를 활용하여 데이터를 효과적으로 필터링할 수 있다.

오브젝트 타입 정의

cube라는 오브젝트 타입을 생성하고, length, width, height 컬럼으로 육면체의 부피를 계산하는 volume() 메서드를 정의하는 예시임.

CREATE TYPE cube AS OBJECT
(
    length NUMBER,
    width NUMBER,
    height NUMBER,
    MEMBER FUNCTION volume RETURN NUMBER DETERMINISTIC
);

CREATE OR REPLACE TYPE BODY cube AS
MEMBER FUNCTION volume RETURN NUMBER IS
BEGIN
    RETURN (length * width * height);
END;
END;

테이블 및 함수기반 인덱스 생성

cube 오브젝트 타입을 사용한 CUBE_TAB 테이블을 생성하고, volume() 메서드 기반의 함수기반 인덱스를 생성하는 예시임.

CREATE TABLE cube_tab OF cube;

CREATE INDEX volume_idx ON cube_tab x (x.volume());

데이터 검색

함수기반 인덱스를 활용하여 volume() 메서드의 결과가 특정 값 이상인 데이터를 효율적으로 검색하는 예시임.

SELECT * 
FROM cube_tab x 
WHERE x.volume() > 100;

마. 배타적 관계의 인덱스 검색

배타적 관계의 인덱스 검색은 데이터 모델링 상에서 서로 다른 속성을 가진 컬럼들이 동일한 속성으로 통합되어야 할 때 활용된다.

예를 들어, 고객 테이블에서 "개인고객"의 주민등록번호와 "법인 고객"의 사업자등록번호를 동일한 "고객공식별번호" 칼럼으로 관리할 수 있다.

 

배타적 관계의 유일성 보장

배타적 관계에서 속성의 유일성을 보장하려면 조건부 인덱스를 생성하여 처리 단순화 및 시스템의 효율성을 높일 수 있다.

예를 들어 개인고객의 주민등록번호와 법인고객의 사업자등록번호를 조건부로 결합한 유일성을 검사하려는 경우 다음과 같은 인덱스를 생성할 수 있다.

CREATE UNIQUE INDEX official_id_idx ON customers (
  CASE WHEN cust_type = 1 THEN resident_id ELSE business_id END
);

SELECT * FROM customers
WHERE (CASE WHEN cust_type = 1 THEN resident_id ELSE business_id END) = :b1;

이 인덱스는 특정 범위 내의 데이터에 대해 유일성을 보장하도록 활용될 수 있다.

 

배타적 관계의 결합 인덱스

서로 배타적 관계에 있지 않은 비록 컬럼들이라도 특정 업무에서 배타적인 구분값을 기준으로 선택되는 경우, 결합 인덱스를 생성하여 처리 속도를 높일 수 있다.

예를 들어, 특정 주문 유형(Order Type)별로 선택적 배타성을 필요로 하는 경우에 적합하다.

CREATE INDEX order_delivery_idx1 ON order_delivery (
  order_dept,
  CASE WHEN order_type = 1 THEN delivery_date ELSE shipping_date END,
  item_type
);

Start : 24. 12. 31
End: 25. 01. 13