[Oracle]DB 인덱스 정의, 특징, 주의사항

인덱스

테이블에 저장되어있는 데이터를 스캔할 때 table full scan을 수행하면 O(n)만큼의 시간복잡도를 가진다. 저장된 데이터가 많아질 수록 검색속도가 느려지게 되는데, 이 때 인덱스를 이용하면 검색속도를 향상시킬 수 있다.

 

인덱스 생성 

인덱스는 자동으로 생성되는 인덱스와 사용자가 수동으로 생성하는 인덱스가 있다. 대부분의 RDBMS에서는 테이블 정의에서 PRIMARY KEY 또는 UNIQUE 제약 조건을 정의하면 인덱스가 자동으로 생성되는데 이를 고유 인덱스라고 하며, 사용자가 직접 생성한 인덱스를 비고유 인덱스라고한다. 

 

생성쿼리

CREATE [UNIQUE][BITMAP]INDEX 인덱스이름 ON 테이블 (column[, column]...)

 

인덱스가 기반으로 하는 열의 값이 고유해야 함을 나타내려면 UNIQUE를 지정하면된다.

각 행을 별도로 인덱스화하지 않고 각 구분 키에 대한 비트맵을 사용하여 인덱스가 생성되도록 하려면 BITMAP을 지정하면 된다.

한 번 생성된 인덱스는 수정할 수 없으므로 인덱스를 변경하려면 삭제 후 다시 생성해야한다.

단일 인덱스만으로 처리하기 힘든 대용량 데이터베이스 에서는 두 개 이상의 컬럼을 하나의 인덱스에 지정해서 생성하기도 하는데 이를 결합인덱스라고 한다. (결합인덱스 지정 순서에 관한 글 참조) 

*이해를 돕기위한 예시이며 실제 인덱스는 행열 형태가 아닌 B-Tree 또는 B+Tree 형태로 저장되어있다.

 

인덱스를 생성하면 테이블과 매핑된 또 다른 테이블(=오브젝트)이 하나 생성된다고 볼 수 있다. 인덱스가 동작할 때는 기존 테이블과 매핑된 ①인덱스에서 데이터를 찾은 다음 → ②테이블에 접근해서 데이터를 가져온다.

인덱스는 기본적으로 정렬되어 있기 때문에 B-tree 혹은 B+tree와 같은 탐색기법으로 O(logN)의 속도로 탐색 가능하며, 인덱스에 저장된 물리적주소를 이용해 테이블에 접근하므로 table full scan이 이루어지지 않는다.

 

인덱스 선정기준

인덱스를 많이 생성한다고 반드시 좋은 것(쿼리속도 향상)은 아니다. DML 작업이 커밋되면 변경사항을 인덱스에도 반영해야하는데, 테이블과 연관된 인덱스가 많을 수록 관련 인덱스를 모두 갱신해야 함으로 서버의 부담이 증가한다. 따라서 다음과 같은 경우에만 인덱스를 생성해야한다.

 

1) 열(Column)에 많은 NULL 값이 포함된 경우

 - NULL 값을 제외하고 검색해야하는 경우 인덱스를 사용하면 검색 속도를 향상시킬 수 있다.

2) 열(Column)에 광범위한 값이 포함된 경우

 - 인덱스 컬럼에 다양한 값이 있는 경우 인덱스를 사용하면 검색 속도를 향상시킬 수 있다.

3) WHERE절 혹은 JOIN 조건에 자주 사용되는 경우

4) 테이블이 크고 대부분의 쿼리가 테이블에서 2~4% 미만의 행을 검색할 것으로 예상되는경우

 -  Index range scan이 table full scan 보다 느려지는 조회 건수 지점을 인덱스 손익분기점 이라 하는데, 테이블 전체 데이터양의 10 ~ 15% 이상을 출력하게 되면 오히려 table full scan이 효율적일 수 있다.

5) ORDER BY 절에 자주 사용되는 경우

 - 인덱스는 기본적으로 정렬되어있어서 order by를 수행할 필요가 없다.

 

* 손익분기점 2~4%는 공식문서상의 수치이며  디스크 I/O, 메모리 I/O 비용 간의 비용 차이를 고려하여 결정되므로 환경에 따라 다르다. 

 

또한, 다음과같은 경우에는 인덱스를 생성 또는 지정하지 말아야한다.

 

1) 열(Column)이 조건절에서 자주 사용되지 않는 경우

2) 테이블이 작거나 대부분의 query가 테이블에서 2%~4% 이상의 행을 검색할 것으로 예상되는 경우

3) 테이블이 자주 갱신되는 경우

 - SELECT는 빨라질 수 있지만 인덱스를 INSERT하는 경우 위치탐색이 필요하며 UPDATE되는 경우에도 변경사항을 인덱스에 반영해주어야한다.

4) 인덱스화된 열이 표현식의 일부로 참조되는 경우

 

 

주의사항

쿼리상에서 다음과 같이 인덱스를 이용하면 인덱스를 활용했음에도 속도가 느릴 수 있다.

1) 인덱스 컬럼 가공

- 인덱스의 기본 원리는 키 값(Key Value)을 사용하여 레코드를 신속하게 찾는 것이므로 WHERE 절에 사용된 열과 인덱스의 첫 번째 열이 일치해야 효과적인 인덱스 스캔(Index Scan)이 이루어진다.
SUBSTR(인덱스, 1, 4) = ‘2023’ 
▶ LIKE 인덱스||’%’ 

2) 묵시적 형 변환 이용 (인덱스가 DATE형 일 경우 예시)
인덱스 = '20230331’ 
▶ 인덱스 = TO_DATE('20230301', 'YYYYMMDD’)

3) 인덱스 컬럼 부정형 비교

- 부정형 비교는 인덱스를 탐색하는 데 필요한 키를 정의하지 못하기 때문에 인덱스를 사용할 수 없게 된다.
인덱스 != '10’ 
▶ 인덱스 IN ('20', '30’) 

4) LIKE %가 앞에 위치할 경우 
- full scan이 수행됨

5) OR 조건 사용 

 

 

 

 

참조

Oracle Database 11g: SQL Fundamentals

인덱스를 타면 왜 빨라지는지 아니? - SQL전문가 정미나