[oracle] 인덱스(INDEX)

1.인덱스 란?

인덱스라는 것은 우리가 책의 특정 부분을 빠르게 찾아가기 위해서 책의 맨 뒤에 존재하는 찾아보기 목록에서 

해당 단어가 들어있는 페이지를 찾을때 자주 사용하곤 한다. 즉, 특정 단어나 주제를 빨리 찾고자 할 때 활용하는 것이다.

데이터베이스에서도 마찬가지로 특정 테이블에 저장되어 있는 데이터를 좀 더 효율적이고 빠르게 찾기 위해 사용된다.

책 뒤편에 찾아보기 목록을 따로 구성해 놓듯이 데이터베이스에서도 테이블에서 설정한 인덱스(PRIMARY KEY, UNIQUE = UNIQUE 

인덱스) 컬럼의 데이터에 대한 인덱스 정보(인덱스 컬럼값 + ROWID)가 별도로 저장된다.

인덱스는 테이블과 같이 별도의 데이터베이스 오브젝트로써 테이블과 동등한 레벨의 객체로 존재한다.

여기서 제약조건들은 제약조건 일뿐 데이터베이스의 오브젝트는 아니다.

-.인덱스 생성

CREATE [UNIQUE] INDEX [스키마명.]인덱스명

ON [스키마명.]테이블명(컬럼 리스트...);

CREATE UNIQUE INDEX GRUART.PK_EPISODE_ID ON GRUART.STAR_WARS(EPISODE_ID)

-.인덱스 삭제

DROP INDEX [스키마명.]인덱스명;

-.인덱스명 변경

ALTER INDEX 이전_인덱스명 RENAME TO 새로운_인덱스명;

인덱스를 생성하면 오라클은 인덱스 정보를 별도로 저장하는데, 저장되는 인덱스 정보에는 인덱스를 설정한 컬럼의 컬럼값과

ROWID 정보를 담고 있다.

즉, 인덱스를 설정한 컬럼을 기준으로 정렬을 한 뒤 이 컬럼값과 ROWID값을 저장하며,

검색시 저장된 인덱스 정보를 먼저 찾은 다음 인덱스 정보를 통해 실제 테이블에 접근, 찾고자 하는 데이터를 검색하게 된다.

인덱스 정보에는 인덱스를 설정한 컬럼값과 ROWID 정보가 담겨 있기 때문에

새로운 데이터의 행이 삽입되거나 업데이트, 삭제에 의해 데이터 변경이 일어나면 오라클은 자동으로 저장된 인덱스 정보를 갱신한다.

따라서 한 테이블에 인덱스를 너무 많이 생성하게 되면 SELECT에는 도움이 될 수 있을지 모르겠지만

데이터가 변경될 때마다 인덱스 정보도 같이 갱신 되어야 해서, 전체적으로는 인덱스 관리에 오라클의 자원들이 많이 할당되어

성능에 좋지 않은 영향을 끼치게 된다. 따라서 꼭 필요한 인덱스만 생성하도록 해야한다.

인덱스 생성	데이터 조회
인덱스 정보 저장(인덱스가 설정된 컬럼값 + ROWID)	인덱스 정보 조회 -> 원본 테이블 접근 -> 실제 데이터 검색

2.인덱스의 종류

유일성 여부에 따라	UNIQUE 인덱스: 인덱스가 설정된 컬럼에 유일한 값만 입력 가능, 중복값 불허
	NON-UNIQUE 인덱스: 인덱스가 설정된 컬럼에 중복값 입력 가능
인덱스 구성 컬럼의 개수에 따라	단일 인덱스: 인덱스 구성 컬럼 갯수가 한개
	복합 인덱스: 인덱스 구성 컬럼 개수가 두개 이상
인덱스 생성자에 따라(B-트리 인덱스)	수동 인덱스: CREATE INDEX 문을 사용하여 사용자가 생성
	자동 인덱스: UNIQUE나 PRIMARY KEY 생성에 따라 오라클이 자동으로 생성.  자동으로 생성된 인덱스의 이름은 보통 SYS_로 시작한다.

-.B-트리 인덱스

가장 일반적인 형태의 인덱스.

CREATE INDEX 문이나 오라클이 생성하는 자동 인덱스는 모두 B-트리 인덱스이다. 즉, 디폴트 인덱스라고 생각하면 된다.

이 인덱스는 순차적으로 정렬된 인덱스 컬럼 값을, 실제 값이 저장된 테이블 로우의 저장 위치와 연계한 형태로 구성되며 

별도의 인덱스 영역에 키 값을 기준으로 정렬되어 있고 이 인덱스 영역은 실제 데이터의 ROWID를 가지고 있다.

-.비트맵 인덱스

B-트리 인덱스가 저장되는 정보는 인덱스 컬럼값과 ROWID인데 반하여, 비트맵 인덱스는 ROWID값 대신 각 키에 대한 

비트맵값(0 OR 1)으로 구성된다.

이 인덱스는 OLTP성보다 OLAP성 시스템에 유리하고 대용량의 데이터를 가진 테이블에서 조회시 B-트리 인덱스보다 큰효율을 보인다.

인덱스 컬럼의 카디널리티(컬럼값으로 입력될수 있는 값의 갯수 구별: 값의 종류 척도)가 낮은 컬럼의 경우 비트맵 인덱스에 적당한 컬럼이라 할수 있다.

일반적으로 데이터의 저장, 갱신, 삭제가 빈번히 이루어지는 OLTP성 시스템에서는 B-트리 인덱스가 유리하며,

카디널리티가 낮고 대량의 데이터에 대한 조회작업이 많은 OLAP성 시스템이나 의사결정 시스템의 경우에는 비트맵 인덱스가 유리하다고 할수 있다.

3.인덱스 생성시 고려해야 할 사항

일반적으로 인덱스는 테이블에 있는 데이터를 빠르고 저비용으로 검색하기 위해 사용된다.

데이터 변경이 일어나면 오라클은 인덱스 정보를 자동으로 갱신하게 된다. 따라서 인덱스가 너무 많이 생성되어 있으면 데이터의 

입력, 수정, 삭제 등의 작업에 의해서 인덱스 정보의 갱신도 빈번히 일어나서 당연히 성능에 문제가 발생한다.

또한 테이블에 저장된 데이터의 양이 적은 경우에는 인덱스를 통해 데이터를 검색하는 것보다는 직접 테이블 데이터를 검색하는 것이 더 빠를수 있다.

①자주 조회되는 컬럼을 인덱스 컬럼으로 선택한다.

②참조 제약조건이 있는 컬럼에 생성한다.

③데이터 량이 많은 테이블에 있어 전체 데이터의 15% 이하의 데이터를 조회할 경우에 인덱스를 생성한다.

조회된 ROW수가 15%를 넘어갈 경우 인덱스를 통해 조회하는것 보다 테이블 전체를 검색하여 조회하는 것이 더 빠를수도 있다.

④테이블 간 조인에 사용되는 컬럼을 인덱스 컬럼으로 선택하면 조인 성능이 향상 된다.

⑤테이블 전체 로우가 적은 경우에는 굳이 인덱스를 만들 필요가 없다.

⑥카디널리티가 높은 경우에는 일반적인 인덱스로, 카디널리티가 낮은 경우에는 비트맵 인덱스로 설정한다.

카디널리티: 컬럼값의 종류의 척도라고 하며 컬럼내의 값의 종류가 적을 경우에는 카디널리티가 낮다고 하며, 여러가지 다양한

종류의 값들이 올수 있으면 카디널리티가 높다라고 한다.

⑦복합 인덱스를 구성할 경우 컬럼의 순서는 SELECT문의 WHERE절에서 좀더 자주 사용되는 컬럼을 먼저오게 한다.

⑧LONG와 LONG RAW 타입 컬럼은 인덱스로 만들수 없다.

⑨복합 인덱스로 만들수 있는 컬럼 수량은 32개이다.

⑩삽입, 갱신, 삭제가 많이 발생하는 테이블에는 인덱스를 너무 많이 만들지 않도록 한다.

왜냐하면 데이터 변경이 발생하면 그에 따른 인데긋 정보도 갱신되기 때문에 성능에 영향을 줄수 있다.

⑪인덱스가 설정된 해당 컬럼에 입력되는 값은 유일해야 한다.(UNIQUE 인덱스)

⑫UNIQUE키나 기본키를 생성하면 UNIQUE 인덱스가 자동으로 생성된다.

하지만 UNIQUE 인덱스를 생성한다고 해서 UNIQUE나 기본키 제약조건이 자동으로 생성되지는 않는다.

⑬인덱스는 테이블과 같이 엄연한 데이터베이스 오브젝트이고 제약조건은 말 그대로 제약조건일 뿐이다.

따라서 기본키나 UNIQUE 키를 생성하면 인덱스명과 제약조건의 이름이 동일할 수 있다.

⑭인덱스 생성

수동인덱스: CREATE INDEX를 사용하여 생성된 인덱스

자동인덱스: 기본키나 UNIQUE 제약조건을 생성시 자동으로 생성되는 인덱스를 자동인덱스 라고 한다.

일반적으로 자동인덱스일 경우에는 이름이 SYS_로 시작한다.