학습자료(~2017)/오라클

20. 오라클 - INDEX 종류 및 활용

단세포소년 2011. 3. 31. 07:28
반응형


EMPNO로 검색을 많이 한다면 INDEX를 EMPNO로 오름차순한다.
검색 속도를 빠르게 해준다. INSERT나 DELETE에는 속도가 떨어진다.


인덱스 생성
CREATE INDEX indexname ON table(column) 
STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 0) TABLESPACE tablespacename;
 - > indexname 이 table의 column에서 사용될 것이다. 기본은 오름차순, TABLESPACE가 필요(물리공간 필요)

. ORACLE은 기본적으로 B*-Tree Index를 제공
. 검색(SELECT) 속도를 향상시키기 위해 INDEX를 생성한다.
. 갱신, 삭제, 삽입 등의 작업에서는 INDEX 처리를 위해 OverHead가 발생한다.
. INDEX를 저장하기 위한 물리적인 공간이 필요하다.
. 최적화를 위해 분리된 TABLESPACE에 생성한다.(가능하다면 별도의 Disk)
 - > 입,출력을 따로하여 성능을 향상시키기 위해
. 가능한 수정이 빈번하지 않은 Column을 대상으로 INDEX를 생성한다.
 - > 읽기 전용 테이블..
. Column 분포도가 10~15% 이내인 경우에 Index를 생성한다.(때로는 Full Scan이 효율적인 경우도 있다.)

인덱스 변경
ALTER INDEX indexname STORAGE(PCTINCREASE 0);

인덱스 제거
DROP INDEX indexnamel;
 - > 테이블 삭제시 INDEX도 삭제 된다.





BITMAP INDEX


. 각 값에 대한 하나의 Bitmap, 각 행에 대한 하나의 Entry를 갖는다.
. 논리 연산(AND, OR등)을 통한 효율적인 Bitmap의 조합
. 다른 Indexing 기법과 비교하여 실질적인 공간 사용 감소
. 질의(SELECT) 이외의 작업에는 유지 비용이 많이 발생

비트맵 인덱스 생성
CREATE BITMAP INDEX indexname ON table(column)
STORAGE(INITIAL 500K NEXT 500K PCTINCREASE 0)
TABLESPACE tablespacename;

b*-Tree Index 보다 나은 성능을 발휘하는 경우
. Very Large Table
. 낮은 Cardinality를 갖는 Column(성별, 결혼 유무, 연령, 지역등 적은 유한개의 값을 가질때)
. 질의가 WHERE절에 있는 모든 Column에 대한 Bitmap Index를 가지는 경우
. 질의의 결과가 많은 수의 행을 만족하는 각각의 WHERE절 조건을 가지는 경우

※ 높은 Cardinality 를 갖는 Column에 Bitmap Index를 사용하면 심각한 성능 저하 발생(주민등록번호, 주소 등)




 
UNIQUE INDEX
Unique Index는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있다. Primary key 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스 이다.

UNIQUE INDEX 생성
CREATE UNIQUE INDEX indexname ON table(column);





NON-UNIQUE INDEX
Non-Unique Index는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있다.

NON-UNIQUE INDEX 생성
CREATE  INDEX indexname ON table(column);




CONCATENATED INDEX
결합인덱스는 두 컬럼을 비교하여 검색 할때 많이 쓰인다.

index ON table(A, B ,C) 의 경우 앞에서 부터 정렬된다. 즉 A정렬후 B정렬, B정렬후 C정렬
즉 검색시 중요 조건(컬럼)부터 index 생성시 앞에 두어야 한다.

SELECT * FROM table WHERE A=20 AND B=30 AND C=10; 일 경우 빠르다.
SELECT * FROM table WHERE A=20 일 경우 빠르다.
SELECT * FROM table WHERE C=20 AND B=30 AND A=10; 일 경우 INDEX의 효과가 없다.






REVERSE KEY INDEX
생성
CREATE INDEX indexname ON table(column) REVERSE;

. Reverse Key Index는 Index Column의 Data를 Byte 단위로 역순으로 저장한다. 예) 123 - > 321 로 저장
. Data 123, 124, 125, 126 ... 와 같이 만들어지고, 삭제도 그와 같은 순서로 발생한다고 했을때 일반적인 B*-Tree Index를 사용하면 Tree가 한쪽으로 쏠리는 현상이 발생하게 된다. 이경우 Reverse Key Index를 사용하면 321, 421, 521, 621... 등과 같이 Index가 저장되므로 B*-Tree가 한쪽으로 쏠리는 현상을 방지할 수 있다.





DESCENDING INDEX
생성
CREATE INDEX indexname ON table(column DESC);
. 내림차순으로 index 생성
. ORDER BY 작업을 줄임으로써 검색 속도를 향상시킬 수 있다.





INDEX가 존재 해도 사용할 수 없는 경우
. INDEX COLUMN의 변형
- > SELECT * FROM table WHERE UPPER(ename) ='SCOTT' ;
. NOT 연산자 사용
- > SELECT * FROM table WHERE job <> 'SALESMAN';
. NULL 비교
- > SELECT * FROM table WHERE comm IS NOT NULL;
. Optimizer에 의한 선택
- > 인덱스가 여러가지일때 최적화에 따라 사용이 안될수 있다.

반응형