Index

인덱스란, DB의 색인을 만드는 작업으로 Optimizer가 참고하여 실행계획을 세우는데 사용된다.

 예를 두꺼운 책의 목차라고 생각해보면 이해가 빠를것이다.
빗대어보자면 사람이 서버프로세스, 목차가 인덱스, 페이지번호가 rowid 에 해당한다고 볼 수 있다. 만약 목차가 없다면 원하는 내용을 찾기 위해 책을 훑어봐야 할것이다

.

DB도 인덱스가 없다면 해당 테이블의 데이터 블록을 모두 메모리로 올려서 해당 row가 있는지 전부 검사하게 된다. 바로 Table Full Scan 이다. (full scan 이 나은 경우도 있지만 논외로 하자.)

 하지만 무조건 만든다고 좋은것이 아니며 적재적소에 사용해야 성능을 극대화시킬 수 있다. 잘못 사용하면 본전도 못찾는 경우도 있다.

인덱스를 사용을 추천하는 경우
- 큰 값이 많은 컬럼
- null 값이 많은 컬럼(왜 그럴까요? is null 검색시 사용하려고?)
- 조건절이나 join 조건절에 자주 사용되는 컬럼
- 테이블에서 조회되는 데이터가 전체 데이터중 2~4% 정도일때

인덱스 사용을 비추천하는 경우
- 자주 사용하지 않는 컬럼
- update가 자주 발생하는 테이블
- 테이블에 데이터가 적은 경우  

인덱스의 분류

하나의 컬럼 또는 다중 컬럼 인덱스(결합 인덱스)
인덱스를 생성시에 테이블의 하나의 컬럼에 대해 생성 할 수도 있고, 여러개의 컬럼을 묶어서 인덱스를 생성 할 수 있다. 최대 컬럼의 개수는 32개까지 가능하다.
결합 인덱스의 경우에는 컬럼 개수의 !(팩토리얼)만큼의 경우의 수가 발생한다. 그중에 단 한개의 방법으로 만들어진 인덱스가 최상의 성능을 발휘한다.



Unique 인덱스와 nonunique 인덱스
unique 인덱스는 중복이 없는 인덱스를 말한다. 값의 중복이 없으므로 당연히 성능이 가장 좋다.
nonunique 인덱스는 같은 값을 허용한다. 당연히 unique인덱스보다 성능이 떨어지겠지만 B-Tree 구조상 정렬되어 있기 때문에 큰 차이는 없다.

Unique 인덱스는 pk 또는 unique제약조건을 설정시 오라클 서버가 자동으로 생성한다.

CREATE TABLE EMP
(emp_id NUMBER(10)
                   PRIMARY KEY USING INDEX
                   (CREATE INDEX idx_emp_empid ON NEW_EMP(emp_id)),
name VARCHAR2(10),
....
);

또는

ALTER TABLE EMP ADD PRIMARY KEY (emp_id) USING INDEX
idx_emp_empid;

Function Baseed Index (FBI: 함수기반 인덱스)
데이터에 함수나 표현식으로 변경된 데이터로 인덱스를 생성한다. 실 데이터는 변경을 하지 않고 검색시 검색 효율을 높이기 위해 사용한다.

조회를 할때 대/소문자 때문에 조건절에 Upper 또는 Lower 를 사용하여 검색하는 경우가 있다.
이렇게 사용되는 컬럼은 처음부터 데이터를 입력할 때 대/소문자로 변환하여 입력하는 방법이 있는데, 만약 그 방법이 여의치 않을 경우 인덱스를 생성할 때 대문자나 소문자로 인덱스를 생성한다.
아래에 해당 예제가 있다. 

- 함수 기반 인덱스 생성

CREATE INDEX upper_emp_emp_name_idx
ON emp(UPPER(emp_name));

- 검색시

SELECT *
FROM emp
WHERE UPPER(emp_name) = 'IT';

Domain 인덱스
todo

---

인덱스의 종류


B-Tree 인덱스
가장 많이 사용하는 인덱스로 대부분의 상황에서 효율적인 성능을 보인다. 데이터의 검색을 스무고개하듯 검색한다. 

Bitmap 인덱스
약간 특수한 경우에 사용한다. 더이상 값이 증가하거나 수정되지 않는 조건에서 사용하는 인덱스이다. 만약 100만건의 데이터가 들어있는 테이블에 성별을 구분하는 컬럼이 있다. 이런 컬럼에는 bitmap 인덱스를 사용하는것이 b-tree 인덱스보다 훨씬 낫다. 

남자 : 000000000110001010010101010101010001010101010101010100101..............1
여자 : 111111111001110101101010101010101110101010101010101011010..............0

이런식의 인덱스가 생성되는데 단점이 있다. 
1. 데이터가 insert 되면 모든 인덱스 항목에 수정이 발생한다. 
2. 새로운 성별이 추가되면 새로운 항목에 대한 bitmap 을 생성하느라 모든 데이터에 대해 탐색이 발생한다.
즉, 성별과 같은 데이터의 구분값이 적고, 데이터의 수정작업이 적은곳에서 사용해야 한다는 것이다.

---

인덱스의 생성 및 삭제

대게 인덱스는 WHERE 에 조건으로 들어가는 컬럼이나 JOIN 조건에 들어가는 컬럼에 인덱스를 만든다.  인덱스는 사용자가 직접 생성하기도 하지만, PK나 UK 제약조건 생성시 자동으로 생성된다. 인덱스의 생성에서 중요한 첫번째  "인덱스는 대상 테이블과 같은 테이블스페이스내에 존재하지 않는게 좋다"이다. 같은 테이블 스페이스내에 있게되면 메모리상에서 같은 이름의 공간에서 작업되기 때문에 불리하다.
둘째 인덱스 명을 직접 설정해라. 자동으로 DB에서 설정하게 되면 사람이 기억하기 힘들다. 적절한 명명규칙을 사용하여 이름을 유추할 수 있는 방법을 사용한다. (예> 'idx_테이블명_컬럼명')
셋째 NOLOGGING 옵션을 사용하라


인덱스 생성

b-tree Index

CREATE INDEX idx_table_column
ON table(column)
PCTFREE 30
NOLOGGING
STORAGE ( INITIAL 200K NEXT 200K )
TABLESPACE tablespace; 

bitmap Index 

CREATE BITMAP INDEX idx_table_column
ON table(column) 
PCTFREE 30
NOLOGGING
STORAGE ( INITIAL 200K NEXT 200K ) 
TABLESPACE tablespace 

인덱스 삭제 
인덱스를 삭제하기 위해서는 인덱스 객체의 소유자이거나 'DROP ANY INDEX' 권한을 가지고 있어야 한다. 

DROP INDEX name;

---

인덱스의 조회 및 재구성


 인덱스 정보 Dictionary 
자신이 소유하고 있는 인덱스에 대한 정보는 'USER_INDEXES' 또는 'USER_IND_COLUMNS' 뷰를 통해 알 수 있다.


인덱스의 재구성
테이블에 dml이 자주 발생하면 B-tree 는 특성상 많이 망가진다. 그러므로 다시 만들거나 고치는 작업이 필요하게 되는데. 일반적으로 다시 만드는것이 권장 사항이다.

REBUILD
망가진 인덱스를 고치는 명령

ALTER INDEX name REBUILD [TABLESPACE name];

원래 REBUILD 중에는 인덱스를 사용 못하지만  ONLINE 옵션을 주면  사용이 가능하다. 하지만 이 옵션은 두마리 토끼를 잡으려다 두 마리 다 놓칠 수 있으니 주의하자. 

ALTER INDEX name REBUILD ONLINE;

참고

http://www.oracleclub.com/lecture/1035
Oracle Database 10g: SQL Fundamentals