02. 더 빠른 검색을 위한 인덱스
인덱스란?
색인이라는 뜻의 인덱스(INDEX)는 책 내용을 찾는 것과 마찬가지로 오라클 데이터베이스에서 데이터 검색 성능의 향상을 위해 테이블 열에 사용하는 객체를 뜻한다. 테이블에 보관된 특정 행 데이터의 주소, 즉 위치 정보를 책 페이지처럼 목록으로 만들어 놓은 것이다. 인덱스는 테이블 열을 여러가지 분석을 통해 선정하여 설정할 수 있다.
인덱스 사용 여부에 따라 데이터 검색 방식을 Table Full Scan, Index Scan으로 구분한다. 테이블 데이터를 처음부터 끝까지 검색하여 원하는 데이터를 찾는 방식은 Table Full Scan, 인덱스를 통해 데이터를 찾는 방식은 Index Scan이라고 한다. 인덱스도 오라클 데이터베이스 객체이므로 소유사용자와 사용권한이 존재한다.
SCOTT계정으로 접속하여 소유한 인덱스 정보와 소유한 인덱스 컬럼 정보를 알아보자.
SELECT * FROM USER_INDEXES;
SELECT * FROM USER_IND_COLUMNS;
인덱스는 사용자가 직접 특정 테이블의 열에 지정할 수도 있지만 열이 기본키(pk) 또는 고유키(uk)일 경우에 자동으로 생성된다.
인덱스 생성
오라클 데이터베이스에서 자동으로 생성해 주는 인덱스 외에 사용자가 직접 인덱스를 만들때는 CREATE문을 사용한다. CREATE문에서는 인덱스를 생성할 테이블 및 열을 지정하며 열은 하나 또는 여러 개 지정할 수 있다. 지정한 각 열별로 인덱스 정렬 순서를 정할 수도 있다.
CREATE INDEX 인덱스이름 ON 테이블이름 ( 열이름1 ASC or DESC, 열이름2 ASC or DESC, ...) ; 인덱스 이름은 IDX_컬럼이름 식으로 작성하는게 보통이다. 인덱스 정렬 옵션은 미지정시 오름차순이 기본값이다. 인덱스가 걸린열을 WHERE의 검색조건으로 하여 테이블을 조회하면 출력 속도가 빨라질 것이라 예상할 수 있는데, 인덱스를 지정할 열의 선정은 데이터의 구조 및 데이터의 분포도 등 여러 조건을 고려해서 이루어져야 한다. 인덱스를 지정하면 데이터 조회가 반드시 빨라진다고 보장할 수는 없다.
목적에 따라 여러 방식으로 생성할수 있는 인덱스를 간단히 알아보자.
1) 단일 인덱스(single index) : CREATE INDEX IDX_NAME ON EMP(SAL);
2) 복합 인덱스(concatenated index), 결합 인덱스(composite index)
- 두 개 이상의 열로 만들어지는 인덱스.
- WHERE절의 두 열이 AND 연산으로 묶이는 경우
: CREATE INDEX IDX_NAME ON EMP(SAL, ENAME, ...);
3) 고유 인덱스(unique index)
- 열에 중복 데이터가 없을 때 사용
- UNIQUE 키워드를 지정하지 않으면 비고유 인덱스(non unique index)가 기본값
: CREATE INDEX IDX_NAME ON EMP(EMPNO);
4) 함수 기반 인덱스(function based index)
- 열에 산술식 같은 데이터 가공이 진행된 결과로 인덱스 생성
: CREATE INDEX IDX_NAME ON EMP(SAL*12+COMM) ;
5) 비트맵 인덱스(bitmap index)
- 데이터 종류가 적고 같은 데이터가 많이 존재할 때 주로 사용
: CREATE BITMAP INDEX IDX_NAME ON EMP(JOB);
인덱스 삭제
인덱스 삭제는 DROP 명령어로 할 수 있다. DROP INDEX 인덱스이름;
인덱스는 데이터 접근 및 검색 속도 향상을 위해 사용하는 객체이지만 인덱스 생성이 항상 좋은 결과로 이어지지는 않는다. 정확한 데이터 분석에 기반을 두지 않은 인덱스의 무분별한 생성은 오히려 성능을 떨어뜨리는 원인이 되기도 한다. 인덱스는 데이터 종류, 분포도, 조회하는 SQL의 구성, 데이터 조작 관련 SQL문의 작업 빈도, 검색 결과가 전체 데이터에서 차지하는 비중 등 많은 요소를 고려하여 생성한다.
* 인덱스 사용 및 생성과 관련하여 더 자세하게 알고싶다면 SQL튜닝에 대해서 찾아볼것