오라클 인덱스 종류 및 활용

오라클 인덱스 종류 및 활용 ==============================================================

-- B-tree index (일반적인방법)
create index [이름]            // <- 인덱스 이름
on [테이블명] ([칼럼명])    // <- 테이블명과 칼럼명
storage (initial 500k next 500k pctincrease 0)
tablespace [테이블스페이스명];

 

* 최적화를 위해 분리된 테이블 스페이스에 생성한다.

-- 변경
alter index [이름] storage (pctincpease 0);

-- 제거
drop index [이름];

-- Bitmap index
create bitmap index [이름]
on [테이블명] ([칼럼명])
storage (initial 500k next 500k pctincrease 0)
tablespace [테이블스페이스명];

 

bitmap index는 활용법
Very Large Table(수백만 개의 Row)
낮은 Cardinality를 갖는 Column(성별,결혼유무,연령,지역 등)
질의가 where절에 있는 모든 Column에 대한 Bitmap Index를 가지는 경우
질의의 결과가 많은 수의 행을 만족하는 각각의 where절 조건을 가지는 경우

유니크 인덱스 // <- primary key를 생성하면 기본적으로 생성된다.
넌유니크 인덱스

결합 인덱스   // <- 2개이상에 테이블을 조인해서 검색할때 하나에 인덱스로 만들어서 사용하는 방법
Concatenated Index

 

인덱스를 활용하지 못하는경우
-- Index Column의 변형
where upper(인덱스칼럼) = '...';
-- NOT 연산자 사용
where 인덱스칼럼 <> '...';
-- NULL 비교
where 인덱스칼럼 is not null;
-- Optimizer에 의한 선택
where 인덱스칼럼 = '...' and 다른인덱스칼럼 = '...'; //<- 2개중 1개를 선택하여 인덱스동작

-- Reverse key Index
create index [이름] on [테이블명] ([칼럼명]) reverse;

데이타가 순차적으로 만들어지고 삭제도 그와 같은 순서로
발생한다면 리버스키인덱스를 사용하면 좋다.
이때 B-Tree Index를 사용하면 쏠리는 현상이 발생하는데
이걸 방지할 수 있다.

 

-- function based Index (FBI)
인덱스가 걸린 컬럼에 사칙연산을 가하면 인덱스 이용이 불가능하다.
그렇다면 인덱스를 만들때 사칙연산을 가해 저장하면 된다.

create index [이름] on [테이블명] ([칼럼명]*12);

* 사칙연산 권한이 있는 사용자만 가능하다.
grant query rewrite to [사용자명];     // <- 권한 부여

 

-- Descending Index
create index [이름] on [테이블명] ([칼럼명] DESC);

인덱스를 디센딩으로 정렬하여 생성하고
order by 작업을 줄임으로써 검색 속도를 향상시킬 수 있다.

 

-- 테이블에 걸린 인덱스 확인
select index_name,index_type
from user_indexes
where table_name = '...';