인덱스 2

4 인덱스의 종류

OLTP 환경 : 주로 B-Tree 인덱스를 많이 사용

OLAP 환경 : 주로 Bitmap 인덱스를 많이 사용

[ 참고 ]

OLTP( OnLine Transaction Processing 실시간 트랜잰션 처리용 ) 

거의 대부분이 실시간으로 데이터가 입력되고 수정되는 환경

우리가 흔히 접하는 대부분의 경우

OLAP ( OnLine  Analytical Processing 온라인 분석 처리용 )   

대량의 데이터를 한꺼번에 입력 한 후 주로 분석이나 통계정보 등을 출력할 때 사용하는 환경

실시간으로 데이터가 자주 변하지는 않음, 빅데이터 분야

1) B-Tree 인덱스

인덱스가 생성될 경우 leaf block -> branch block -> root block의 순서

leaf block : 실제 데이터를 저장하고 있는 데이터 블록들의 주소

branch block : leaf blocks 에 대한 정보

root block : branch block 에 대한 정보

데이터를 찾는 순서 

root block -> branch block -> leaf block 정보를 찾음 

-> 해당데이터의 rowid 를 찾은 후 데이터가 들어있는 블록을 메모리로 복사 

특징  

B-Tree 에서 B는 Binary이란 의미와 함께 Balance의 의미도 있음

Root block를 기준으로 왼쪽과 오른쪽에 들어있는 데이터의 Balance가 맞을 때, 성능이 가장 좋음

일반적으로 가장 많이 사용됨

아래는 다양한 B-Tree의 인덱스들...

(1) UNIQUE INDEX

인덱스를 만드는 key 값에 중복되는 데이터가 없다는 뜻

해당 테이블 컬럼에 중복된 값이 없고, 앞으로도도 중복된 값이 들어올 수 없다는 의미

일반 인덱스와 비교시에도 속도가 빠르기 때문에 둘 중에서는 유니크 인덱스 권함

키 컬럼의 데이터들이 중복없는 유일성을 보장하는 인덱스라서 속도는 빠름

주의 - 나중에 중복되는 데이터가 들어올 수 없는 단점이 있기 때문에 잘 생각해서 사용해야 함

생성문법

CREATE UNIQUE INDEX 인덱스명

ON 테이블이름(컬럼명1 ASC|DESC, 컬럼명2,.....);

 

생성예제

CREATE UNIQUE INDEX IDX_DEPT2_DNAME

ON dept2(dname);

(2) NON-UNIQUE INDEX

중복되는 데이터가 들어가야 하는 컬럼일 경우에는 UNIQUE INDEX 를 생성할 수 없기 때문에

NON-UNIQUE INDEX 를 생성함

생성문법

CREATE INDEX 인덱스명

ON 테이블명(컬럼명1 ASC|DESC, 컬럼명2, ....);

생성예제

dept2 테이블의 area 컬럼에 NON UNIQUE INDEX 를 생성

CRAETE INDEX idx_dept2_area

ON dept2(area);

(3) FUNCTION BASED INDEX(FBI-함수기반 인덱스)

인덱스는 WHERE 절에 오는 조건에 컬럼이나 조인컬럼 등에 만들어야 함 *

(단 몇 가지 특별한 경우에 SELECT에 있는 컬럼에 생성하기도 함)

WHERE pay=1000이란 조건일 경우 pay 컬럼에 인덱스를 만들어야 함

pay 컬럼에 인덱스를 생성 후 위와 같이 사용하면 인덱스를 이용함 

WHERE pay + 1000 = 2000 의 조건 검색은 인덱스 이용 못 함 - 연산자를 사용하는 경우 

WHERE ename != 'FORD'; 인덱스 이용 못 함- NOT을 사용하는 경우

=> INDEX Suppressing Error : 인덱스는 잘 생성하고, SQL 를 잘못 작성해서 인덱스를 사용할 수 없는 경우

인덱스를 사용하려면 WHERE 절의 조건을 절대로 다른 형태로 가공해서 사용하면 안 됨

WHERE pay+1000 = 2000 을 인덱스를 사용하고 싶으면?

(pay+1000) 이 형태로 사용해 줌

이런 형태의 인덱스를 함수기반 인덱스라고 함

pay+1000 이라는 컬럼이 없지만 함수처럼 연산을 해서 인덱스를 만들어준다는 의미

생성예제

CREATE INDEX idx_prof_pay_fbi

ON professor(pay+1000);

단점

pay+1000 이라는 컬럼이 생성됨, 오라클이 인덱스를 만들 때, 이 연산을 이용해 인덱스를 만들어 주기 때문

하지만! 임시적인 해결책임, pay+1000 의 조건이 변하면 인덱스도 다시 만들어줘야하는 단점이 있고

FBI는 기존 인덱스를 활용할 수 없음 

(4) DESCENDING INDEX( 내림차순 인덱스 )

여러 인덱스를 생성할 때 큰 값이 먼저오게, 내림차순 순으로 인덱스를 생성함

1000

999

998 

...

이런식으로 인덱스 생성

사용 

큰 값을 많이 조회하는 SQL에 사용하는 것이 좋음

예) 

계좌조회시 최근 날짜부터 나오게 하는 경우(최근 날짜가 큰 날짜)

매출 테이블의 상위 매장이나 사원을 보는 경우(매출이 큰 값을 먼저 조회하는 경우)

문제) 하나의 메뉴에 오름차순과 내림차순을 한꺼번에 조회할 경우는?

A 최근 날짜 부터 계좌조회  

B 예전 날짜 부터 계좌조회

인덱스를 오름차순, 내림차순으로 두번이나 만들면 (인덱스가 많으면) DML 성능에 악영향

해결 : 인덱스를 위나 아래에서 읽게 하는 힌트라는 방법을 제공

         힌트들을 이용해 튜닝에서는 정렬하지 않고, 정렬효과를 내고, 최소값과 최대값을 구하기도 함

(5) COMPOSITE INDEX ( 결합인덱스 )

인덱스를 생성할 때 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것

사용

SQL문장에서 WHERE 절의 조건 컬럼이 2개 이상이 AND로 연결되어 함께 사용되는 경우에 사용

아주 많이 사용되나 , 인덱스를 잘못 생성하게 되면 굉장히 나쁜 영향을 미침

예) emp 테이블에 100명의 인원 중 남자(M)은 50명, 여자(F) 50명

50명의 남자 중 이름이 'SMITH'인 사람이 단, 2명 있다고 가정

일반쿼리문

SELECT ename, sal

FROM emp

WHERE ename='SMITH'

AND sex='M';

결합인덱스 생성 구문 예

CREATE INDEX idx_emp_comp

ON emp(ename, sex); 이 부분이 위와 같음  WHERE ename='SMITH'

  AND sex='M';

해당 데이터를 찾을때 두 개의 컬럼을 동시에 만족하는 블록을 검색하겠다는 의미

같은 테이블에 같은 SQL이지만 결합 인덱스를 어떻게 생성하는가에 따라서 속도나 검사횟수가 완전히 달라질 수도 있음

ON emp(ename, sex) 100건의 성별 중 2건의 이름 찾은 후 성별검사 - 2건 출력

On emp(sex, ename)  100건의 성별 중 50건을 찾은 후 다시 이름을 검색해서 - 2건 출력

=> 첫번째 조건에서 최대한 많은 데이터를 걸러서 두번째 검사를 쉽게 만들어야 함

결합인덱스는 경우의 수도 많고, 고려해야할 사항이 많으므로, 아주 신중하게 고려하고 사용해야 함