[Oracle] 오라클 #22, Index(인덱스) 개념 및 예제

인덱스, Index

- 색인

- 검색(select)을 빠른 속도로 하기 위해서 사용하는 도구

- 오라클은 테이블 생성 시 인덱스를 따로 생성하지 않아도 자동으로 생성된다. -> PK, Unique 컬럼 자동으로 색인 생성. 

    -> PK 컬럼 검색속도 >>>>> 일반 컬럼 검색속도  :  PK는 자동으로 Index를 생성하기 때문에

검색속도 차이 비교

먼저 일반컬럼을 검색했을때 반환되는 속도는 

select * from tblIndex where name ='정소은'; 0.08초

name 컬럼에 인덱스 생성하기

>> create index idxIndexName on tblIndex(name);

색인 후

select * from tblIndex where name ='정소은'; 0.003초

>> 훨씬 빨라진 모습을 보인다.

예를 들어,

고객관리 -> 관리자가 고객이름으로 검색을 많이한다면... -> 인덱스없이 검색하면 느리니까 이름에다가 인덱스를 부여하자 그럼 빨라진다.

인덱스, 장단점 정리

특징

1. 검색 처리 속도를 향상시킨다. 

2. 비용이 비싸다 -> 인덱스를 create 해주어야 하기 때문

인덱스 사용해야 하는 경우(중요)

 1. 테이블 행의 갯수가 많은 경우.(찾아야할 데이터)

 2. 인덱스를 적용한 컬럼이 where 절에서 많이 사용되는 경우 (*****)

 3. join할 때 사용하는 컬럼(on 부모테이블.PK = 자식테이블.FK) (*****)

 4. 검색 결과가 원본 테이블 데이터 2 ~ 4%에 해당하는 경우 (*****)

 5. 해당 컬럼이 null을 포함하는 경우(색인에 null이 제외)

인덱스 사용하면 안 좋은 경우

 1. 테이블의 행의 갯수가 적은 경우

 2. 검색결과가 원본테이블의 많은 비중을 차지하는 경우.

 3. 원본 테이블의 삽입, 수정, 삭제가 빈번한 경우(*****)

select * from tblAddressBook; 

비고유 인덱스

--job은 중복값이 존재하던 컬럼임

--인덱스가 걸린 컬럼 > job > 중복값 존재

>> create index idxIndexJob on tblIndex(job);

고유 인덱스

--인덱스가 걸린 컬럼 > seq > PK, Unique

>> create index idxIndexSeq on tblIndex(seq); -- PK라 색인도 중복되지 않는다.

단일 인덱스

--인덱스 걸 컬럼이 1개일때..

>> create index idxIndexEmail on tblIndex(email) email 컬럼에만 index 부여

    

select * from tblIndex where email = 'back@live.com'; --> idxIndexEmail동작 -> 빠른속도로 검색

select * from tblIndex where email = 'back@live.com' and age = 44; --> idxIndexEmail 동작 X --> 왜냐면 age와 같이 where 절의 조건으로 사용되었기 때문.

결합(다중) 인덱스

>> create index idxIndexEmailAge on tblIndex(email,age); --> 이렇게 해주어야 위의 상황에서 인덱스 효과를 받을 수 있다.

    

select * from tblIndex where name = '김길동';          --idxIndexName 동작 O 

select * from tblIndex where substr(name,1,1) = '김';  --idxIndexName 동작 X  --> 함수로 감싸여졌기 때문에.. --> 즉 순수컬럼값으로만 검색해야한다.

--> create index idxIndexLastName on tblIndex(substr(name,1,1));

select * from tblIndex where (height + weight)>200;

create index idxIndexBMI

    on tblIndex((height + weight));

--하나의 테이블에 여러개의 인덱스가 필요할 수 있다.