[ Oracle ] index에 대하여..

 

 

안녕하세요. 

 

오늘은 index에 대하여 조금 자세히 정리해보려고합니다.

 

1. index란?

 

Oracle에서 인덱스(Index)란 테이블의 데이터 검색 성능을 향상시키기 위해 사용되는 데이터베이스 객체입니다.

인덱스는 특정 열(Column) 또는 열의 조합에 대한 데이터를 정렬된 구조로 저장하여,

SQL 쿼리가 데이터를 검색할 때 더 빠르게 검색할 수 있도록 합니다.

 

 

2. 생성 방식

- 단일 컬럼

create index 인덱스이름 on 테이블이름(컬럼이름);

 

- 복수 컬럼

create index 인덱스이름 on 테이블이름(컬럼이름1, 컬럼이름2);

 

여기까지는 일반적으로 SQL을 조금만 작성해보았다면 알고 있는 정보일 겁니다.

 

 

3. 질문. 인덱스는 순서가  중요하다(?)

 

예시를 들기위해 상황을 가정해보겠습니다.

 

테이블명 - member

컬럼명 - "성별","이름","나이","회원유형"

 

100명의 회원 데이터가 저장되어있습니다.

성별은 남자 50명 여자 50명

이름은 다양한 이름중 "이순신"이라는 동명이인이 3명 있습니다.

나이,회원유형은 다양합니다.(해당 예시에서 큰 의미가 없습니다.)

 

 

그리고 아래와 같이 index를 생성한다고 가정해보겠습니다.

 

create index idx_01 on member(성별,성함);

create index idx_02 on member(성함,성별);

 

이러한 index를 2개 생성하고 아래와 같은 쿼리를 작성하였다고 가정해보겠습니다.

 

select * from member where 성별='남' and 성함='이순신';

 

4. 과연 idx_01과 idx_02의 차이가 있을까요?

 

4_1. 구조

생성한 인덱스를 먼저 살펴보면 아래와 같습니다.

1. idx_01 (성별, 성함)
   • 성별 값에 따라 데이터가 정렬되고, 그다음 성함 값이 정렬됩니다.
     즉, 성별='남'인 데이터를 찾고, 그 중에서 성함='이순신'을 찾습니다.
2. idx_02 (성함, 성별)
   • 성함 값에 따라 데이터가 정렬되고, 그다음 성별 값이 정렬됩니다.
     즉, 성함='이순신'인 데이터를 찾고, 그 중에서 성별='남'을 찾습니다.

4_2. 실행 분석

(1) idx_01 사용 시 -

• 옵티마이저는 성별='남'을 기준으로 인덱스를 탐색합니다.
• 탐색된 데이터 블록 내에서 성함='이순신' 조건을 추가로 확인합니다.
• 최종적으로, 성별='남'인 50명의 데이터 중에서 성함='이순신'인 3건의 데이터를 반환합니다.

(2) idx_02 사용 시 -

• 옵티마이저는 성함='이순신'을 기준으로 인덱스를 탐색합니다.
• 탐색된 데이터 블록 내에서 성별='남' 조건을 추가로 확인합니다.
• 최종적으로, 성함='이순신'인 3명의 데이터 중에서 성별='남'인 3건의 데이터를 반환합니다.

 

4_3. 성능 비교

1. 인덱스 스캔 방식 차이:
   • 두 인덱스 모두 조건(성별과 성함)을 포함하고 있으므로, 인덱스 스캔은 효율적으로 수행됩니다.
   • Oracle 옵티마이저는 데이터 분포와 조건에 따라 둘 중 더 효율적인 인덱스를 선택합니다.
2. 블록 I/O 차이:
   • 인덱스는 B*Tree 구조를 사용하기에 수직->수평으로 동작합니다. 그렇기에 두 인덱스 모두 읽는 인덱스 블록 개수는 동일합니다. 
   • 예를 들어, 성별='남'을 기준으로 데이터를 찾든, 성함='이순신'을 기준으로 데이터를 찾든, 결국 같은 데이터 블록을 읽습니다.
3. 비교 연산 횟수 차이:
   • idx_01은 성별='남' 조건으로 50건을 필터링한 후 성함='이순신'을 추가로 확인합니다.
   • idx_02는 성함='이순신' 조건으로 3건을 필터링한 후 성별='남'을 추가로 확인합니다.
   • 비교 연산 횟수는 다를 수 있지만, 이는 CPU 연산으로 성능에 미치는 영향은 미미합니다.

 

4_4. 결론

• 블록 I/O 관점에서는 idx_01과 idx_02 모두 동일하므로 성능 차이는 없다고 볼 수 있습니다.
• 옵티마이저는 데이터 분포를 기준으로 더 효율적인 인덱스를 선택하며, 두 인덱스 중 어떤 것이 선택되더라도 쿼리 성능에 큰 차이는 발생하지 않습니다.
• 따라서 idx_01과 idx_02의 차이는 논리적인 차이일 뿐, 실질적인 성능 차이는 거의 없다고 결론 내릴 수 있습니다.