[SQL] 인덱스(INDEX)의 종류와 생성, 변경, 삭제, 조회 방법

우리가 책 중간에 북마크 용도로 무언가를 붙이거나 표시해두는 것처럼,

관계형 데이터베이스 상에서 테이블의 특정 데이터를 빠르게 찾기 위해 인덱스를 사용한다.

이때 인덱스는 데이터베이스 객체이자 자료 구조를 의미한다.

 

인덱스는 사용자가 직접 생성해줘야 사용할 수 있는데,

예외적으로 PK 인덱스는 특정 컬럼을 기본 키(PK)로 지정하게 되면

데이터베이스에 의해 자동으로 생성된다.

 

 

인덱스는 특성에 따라 하나의 인덱스도 여러 가지 분류에 속할 수 있다.

예를 들어 어떤 인덱스가 단일 인덱스이면서 UNIQUE인덱스에 해당할 수 있다.

인덱스의 종류

• 인덱스 구성 컬럼 개수에 따른 분류 : 단일 인덱스, 복합(결합) 인덱스
• 유일성 여부에 따른 분류 : UNIQUE 인덱스, NON-UNIQUE 인덱스
• 인덱스 내부 구조에 따른 분류 : B-tree  인덱스, 비트맵 인덱스, 함수 기반 인덱스

여기서 가장 일반적으로 사용되는 B-tree 인덱스이다.

 

이어서 각 인덱스의 종류와 특징을 요약해보았다.

종류	설명
순서 인덱스 (Ordered Index)	- 데이터가 정렬된 순서로 생성되는 인덱스 - B-tree 알고리즘 활용(오름차순/내림차순 지정 가능)
해시 인덱스 (Hash Index)	- 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스  - 데이터 접근 비용이 균일하며 튜플(row) 양에 무관함
비트맵 인덱스 (Bitmap Index)	- 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스 - 수정 변경이 적을 경우 유용함(생년월일, 상품번호 등)
함수기반 인덱스 (Funtional Index)	- 수식이나 함수를 적용하여 만든 인덱스
단일 인덱스 (Singled Index)	- 하나의 컬럼으로만 구성한 인덱스 - 주 사용 컬럼이 하나일 경우 사용
결합 인덱스 (Concatenated Index)	- 두 개 이상의 컬럼으로 구성한 인덱스 - WHERE 조건으로 사용하는 빈도가 높은 경우 사용
클러스터드 인덱스 (Clutered Index)	- 기본 키(PK) 기준으로 레코드를 묶어서 저장하는 인덱스 - 저장 데이터의 물리적 순서에 따라 인덱스가 생성됨 - 특정 범위 검색 시 유리함

 

 

인덱스 생성

 

인덱스는 테이블에 있는 한 개 이상의 컬럼으로 만들 수 있다.

문법은 다음과 같다. 

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명 (컬럼명1, 컬럼명2, ...) ;

* UNIQUE는 필수로 작성해야 하는 것은 아니다.
중복 값을 허용하지 않을 경우에만 사용한다.

 

** 컬럼명의 경우도 단일 인덱스라면 해당 컬럼 하나를 작성하고,

두개 이상일 경우 위와 같이 ,로 구분하여 작성한다.

*** 데이터의 정렬도 필요한 경우라면
(컬럼명1 desc)와 같이 입력할 수 있다. 
기본 오름차순(asc) 정렬이므로 내림차순 정렬이 필요한 경우 사용해보자.

예)
CREATE INDEX ind_type
ON member (type);

설명) 인덱스명 : ind_type
member 테이블에서 컬럼명이 type인 데이터를 조회하기 위한 인덱스
만약 컬럼 type(회원구분)의 값에 '일반', 'vip', 'vvip'등이 올 수 있다면
중복값을 허용하는 것이 적절할 것이다.
따라서 이 경우에는 UNIQUE 인덱스로 생성하지 않았다. 
이러한 인덱스를 NON-UNIQUE 인덱스라 한다.

 

인덱스 조회

SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name = '테이블명';

 

이 select문을 실행하면 where절에 입력하는 테이블명에 따라

특정 테이블의 인덱스 정보를 조회할 수 있다.

이때 테이블명은 꼭 대문자로 작성해야 한다.

 

조회 시 확인할 수 있는 정보는 다음과 같다. 

아래는 모두 고정된 값으로  컬럼명처럼 매번 바꿔서 오는 값이 아니다.

select 다음에 아래 값을 정확하게 써야 

인덱스에 대한 정보를 조회할 수 있다.

• index_name : 인덱스의 이름
• table_name : 인덱스가 속한 테이블의 이름
• uniqueness : 인덱스의 고유성

    - UNIQUE : 해당 인덱스는 고유한 값만을 가짐

    - NONUNIQUE : 중복값 허용

•  status : 인덱스의 상태

- VALID : 활성화

- UNUSABLE : 비활성화(인덱스 무효화, 손상 시)

- N/A 또는 NULL : 인덱스의 상태를 결정할 수 없는 경우.

  시스템 뷰에 정보가 없는 경우나 사용자에게 조회권한이 없는 경우에 발생할 수 있음)

• user_indexes : 현재 사용자가 소유한 인덱스의 일반 정보를 포함하는 시스템 뷰

435번 라인부터 437번 라인까지의 select문을 실행하면

이미지에 등장하는 테이블이 조회된다.

 

"member_index"는 433번 라인에서 사용자가 명시적으로 생성한 인덱스로서

"name"과 "department" 컬럼을 이용한 인덱스를 말한다.  

그렇다면 두 번째 index는 무엇인지 의아할 수 있다.

 

앞서 글 처음에 언급한대로,

Oracle에서는 테이블을 생성할 때 기본적으로 PRIMARY KEY 제약 조건을

만족하는 컬럼에 대해 자동으로 UNIQUE 인덱스를 생성한다.

이 UNIQUE 인덱스의 이름은 자동으로 생성되는 이름으로서

"SYS_C"로 시작하고 일련번호가 붙는 형태이다.

 

member3이라는 테이블을 생성할 때

"id" 컬럼이 PRIMARY KEY로 지정되었기 때문에

해당 컬럼에 대한 UNIQUE 인덱스가 자동으로 생성되었고,

이에 대한 인덱스명이 "SYS_C0012479"로 자동 부여된 것이다.

 

이렇게 두 개의 인덱스가 생성되었기 때문에

"index_name"에는 두 개의 값이 조회된다.

 

 

인덱스 삭제

DROP INDEX 인덱스명;

 

인덱스명 변경

ALTER INDEX 기존 인덱스명 TO 새 인덱스명;

 

위와 같이 인덱스의 이름을 ALTER문으로 변경할 수 있지만

해당 인덱스의 컬럼을 다른 컬럼으로 바꾸고 싶은 경우라면

이를 ALTER문으로 바꿀 수 없다.

이 경우에는 해당 인덱스를 삭제하고 다시 생성해야 한다.