글
6. 진화하는 다른 인덱스 알아보기
먹고는 살아야지;;;
2009/01/06 23:07
1. B-Tree 인덱스를 사용하지 못하는 경우
1) NOT 연산자 사용
B*Tree 인덱스의 경우 NOT 조건을 사용하는 경우, NULL 값을 찾는 경우, 복잡한 OR 조건 사용시 Bitmap 인덱스를 사용한다. 외부적인 변형(supressing)이 있을 때에는 Function Based Index를 사용함.
- 5블록 이하의 경우
FULL TABLE SCAN이 무조건 유리하므로 인덱스를 사용하지 않음 I/O의 최소 단위는 블록인데 전체 5블록 중 한 블록을 읽게 되면 20%가 되어 인덱스의 활용 손익분기점인 0~15%를 넘어가므로 FTS의 성능이 더 좋음.
- 6블록 이상인 경우
같지않다(!=)는 '테이블의 단일 값과 같지 않다'는 의미이므로 대부분의 경우에는 결과가 테이블의 15% 이상일 가능성이 높음
예를 들어 10블록인 경우 데이터를 읽어 들이는 양은 조건에서 같을 경우의 한블록을 제외하면 되므로 (10-1)/10 = 90%에 이르기 때문에, 손익분기점을 넘어가므로 FULL TABLE SCAN 이 유리함.
- 인덱스 칼럼이 아닐때
만약 인덱스 칼럼이 아니라면 당연히 FULL TABLE SCAN 사용
※ 비트맵 인덱스는 구조상 NOT 연산에 대해서도 좋은 성능을 나타낼 수 있음.
2) IS NULL, IS NOT NULL
B*tree 의 경우 미지정값이 NULL에 대한 정보를 인덱스에 추가하지 않는다. 따라서 해당 조건이 추가되면 정보를 FTS로 접근하게 된다.
결합인덱스의 경우 PK가 아니라면 컬럼중 하나가 NULL인 경우를 상수화 시켜서 허용, 모든 칼럼이 NULL 인 경우는 허용하지 않음.
※ 비트맵 인덱스는 NULL 값에 대한 비트맵도 구성하므로 인덱스를 사용
3) 옵티마이저의 취사선택
- 한 테이블에 여러 인덱스가 WHERE 절에 사용된 경우
RBO는 RANK에 의해, CBO는 통계치를 기준으로 선택
선택되지 않은 인덱스의 WHERE절 조건은 테이블에서 읽은 데이터의 체크 조건으로 사용되고 인덱스로는 사용되지 않음
- 옵티마이저의 목표에 따라 인덱스를 사용하지 않을 경우
옵티마이저가 테이블의 데이터가 적은 경우 인덱스를 경유하는 것보다 FTS가 유리하다고 판단하면 인덱스를 사용하지 않음
옵티마이저의 자의적인 판단을 제어하기 위해 힌트를 사용
4) 외부적인 변형 (External Supressing)
- 인덱스 서프레싱
칼럼을 변형함으로써 인덱스를 사용하지 못하게 되는 것
사용자가 함수(function)나 연산자(operator)등을 사용하여 의도적으로 칼럼 값에 변경을 가해 인덱스를 사용하지 못하도록 하는 외부적인 변형
데이터 베이스에 의해 자동적으로 수행되는 내부적인 변형
질의문에서 인덱스를 사용하기 위해서는 인덱스를 구성하는 칼럼에 함수를 이용한 변형을 해서는 안됀다. 함수를 사용하는 경우 옵티마이저는 해당 조건이 인덱스의 구성 칼럼과 값이 다르다고 판단하여 해당 인덱스를 선택하지 않는다.
※ 서프레싱을 꼭 사용해야 하거나 자주 사용하는 경우라면, 함수 기반 인덱스(FBI)를 사전에 생성하여 인덱스를 활용할 수도 있다.
2. 비트맵 인덱스 이해
1) 비트맵 인덱스
- 조회하는 일정 조건에 의해서 테이블의 rowid를 얻어 테이블 자료에 빠르게 접근하기 위해서 생성
- 인덱스는 이러한 조건에 부합하기 위하여 row 단위로 인덱스 칼럼 값과 rowid 를 정렬하여 리스트 형식으로 저장
- 비트맵 인덱스는 이러한 인덱스 칼럼 값을 비트맵(1 or 0) 으로 변환시켜서 저장
- 내부적으로 B*Tree 같은 일반적인 인덱스와는 다른 방식으로 저장되지만 비트맵 변환 함수를 이용하여 특정 값에 일치하는 rowid 를 얻을 수 있음.
※ 비트맵 인덱스는 oracle enterprise edition 에만 있음.
2) 비트맵 인덱스의 이점
수정 작업이 적고 반복되어 사용되지 않는 질의에 대해서 유리함. 동일한 값이 많은 데이터에 대해서도 유리함.
- 수행 속도에 대한 경우
where 절에 분포도가 낮거나 중간 칼럼 조건들이 여러가지 있을 경우
분포도가 낮거나 중간의 칼럼 조건이 많은 row 가 포함되어 있을 경우
비트맵 인덱스가 분포도가 낮거나 중간 정도인 몇개의 칼럼 또는 전체에 생성되어 있을 경우
질의되는 테이블에 row 수가 많은 경우
- 공간에 대한 경우
b*tree 인덱스와 비교하여 상당한 저장 공간을 절약 가능
b*tree 구조를 사용하는 데이터베이스는 하나의 질의문에 여러 칼럼 조건들을 사용할 경우 결합 인덱스를 구할 수 있는데 수행 속도는 빨라지지만, 저장 공간 측면에서는 칼럼 값을 모두 정렬해서 저장해야 하므로 안 좋은 점이 있다.
비트맵 인덱스는 압축된 형태로 저장되므로, 일반적인 b*tree 구조의 인덱스와 비교하여 약 20%~50% 이상의 저장공간을 절약 가능
특히 분포도가 낮고 비트맵 인덱스 칼럼 값으로 정렬이 되어 있다면 저장 공간을 훨씬 더 줄일 수 있음.
3) 비트맵 결합 인덱스
인덱스를 생성하는 목적은 조건 칼럼의 내용과 데이터가 저장된 위치인 rowid 를 함께 저장하여 데이터를 빠르게 뽑아내기 위함이다.
4) Reverse Key 인덱스
1) Reverse Key 인덱스의 이해
2) reverse key 인덱스 적용
- reverse key index의 생성 및 변경
SQL> create index empno_rvs on emp(empno) reverse;
index created.
SQL> alter index empno_rvs rebuild noreverse;
index altered.
- reverse key index의 단점
reverse key index는 bitmap 인덱스의 IOT를 지원하지 않는다.
또한 = 조건이 아니라면 인덱스에 대한 range scan을 지원하지 않으므로 대소 비교등의 조건은 인덱스 full scan 을 한다.
3) reverse key 인덱스 활용
4. Function Based Index
1) Function Based Index 이해
- 함수기준 인덱스란?
함수 기준 인덱스(FBI)는 인덱스로 사용되는 칼럼의 함수를 통한 변환된 모습이나 연산식의 결과 값을 인덱스에 저장하는 것
- 함수 기준 인덱스의 특성
함수 기준 인덱스는 옵티마이저 모드 중 비용 기준(cost based) 모드에서만 사용할 수 있음
옵티마이저가 인덱스를 사용하여 실행 계획을 수립하기 위해서는 반드시 테이블에 analyze 명령을 수행하여 통계 값을 생성해야함
init 팡렝서 optimizer_mode 를 first_rows나 all_rows 로 지정하거나 hint 를 사용하여 비용 기준 옵티마이저가 사용되도록 해야함
※ FBI는 b*tree, 비트맵 인덱스 모두 생성 가능함.
2) Function Based Index 예제 - 함수 이용
3) Function BAsed Index 예제 - 계산식 이용
4) Function Based Index 예제 - Function Based 와 Descending 인덱스의 복합 예제
5) IOT (Index Organized Table)
1) IOT 구조
인덱스만으로 구성된 테이블로 사용은 테이블과 유사하게 사용하지만 그 구성하는 방법은 인덱스의 구성과 동일한 테이블을 말함.
- IO 방법
일반 테이블은 하나 이상의 인덱스 블록을 읽어 rowid 를 읽고, rowid 에 기초하여 테이블에 대한 IO가 발생하는 반면, (vs) Index Organized Table 은 모든 행이 인덱스의 leaf 노드에 있으므로 인덱스 블록만 read 하면 되므로 효율적이다.
- IOT 인덱스의 사용
IOT 인덱스를 검색한 후 rowid 를 통해 테이블 자료를 랜덤 액세스하지 않고, 테이블 자체에서 바로 읽어 들이기 때문에 일반적인 테이블보다 key 에 근거한 액세스 속도가 더 빠름, 따라서 인덱스가 전체 칼럼에서 많은 비중을 차지하거나, 트랜잭션이 많이 일어나는 마스터성 테이블을 IOT로 구성하면 유용
인덱스와 테이블을 각각의 객체로 유지하지 않기 때문에 인덱스의 rowid 영역이 없어져 저장 공간을 절약 가능
2) IOT 특성
- IOT 비적용 대상
랜덤하게 저장되는 일반 테이블에 비해 IOT는 정렬에 대한 부담 때문에 데이터 입력, 수정시 DBMS에 부하가 많이 걸려 저장되는 데이터가 많거나 변경이 많은 경우는 IOT 를 사용하지 않음
- IOT 스토리지 구조
☞ IOT는 모든 테이블 데이터를 b-tree 구조로 저장
☞테이블 기본 키에 기초한 b-tree 인덱스는 인덱스처럼 조직
☞이 구조의 leaf 블록은 인덱스 leaf 엔트리의 두번째 구성 요소에 rowid 대신 non-key 열을 가심
☞테이블과 인덱스라는 별도의 두 세그먼트를 갖지 않음
☞IOT의 index row 들은 인덱스 키 값과 non-key 값을 포함
☞IOT의 index에는 rowid 정보가 없음
- IOT 적용 대상
☞자주 사용되는 마스터성 테이블
일부 마스터성 엔티티의 경우 트랜잭션이 집중되면서 시스템 성능의 병목지점으로 작용할 가능성이 큰데, IOT 사용시 효과적
☞인덱스가 대부분인 테이블
인덱스는 칼럼수가 적은 것이 좋지만 부득이하게 전체 칼럼의 대부분을 인덱스로 가질 수 있다. 테이블과 인덱스에 같은 데이터를 중복해서 가질 필요 없이 IOT를 적용하면 효과를 볼 수 있음
※ 접근 방법은 틀리지만 비슷한 효과를 볼 수 있는 것이 MMDB(main memory db)의 hot table/data 와 ssd(solid state disk)의 hot file 이 있음
- IOT 장점
☞ range search, exact match를 수행하는 경우 일반적인 table 보다 빠른 key-baed access 가 가능
☞ FTS 시 primary key 에 대한 full index scan 이 이루어지므로 자동적인 ordering 이 이루어짐
☞ index key column 과 rowid 에 대한 storage 중복을 피할 수 있어 storage 가 절약
2) IOT 특성 - row overflow 영역을 가진 IOT
organization index : IOT 생성을 나타내는 keyword
pctthreshold : IOT의 단일 row 가 db_block_size * (pctthreshold 값 / 100)보다 크다면, including column 이후의 칼럼 data는 overflow tablespace 저장
including column : 지정되지 않으면 primary key column 이외의 column data가 overflow tablespace 에 저장
overflow : 테이블 스페이스를 지정하지 않으면 user default tablespace가 사용됨
3) 인덱스 구조 테이블의 생성
6. 이것만은 꼭!
1) NOT 연산자 사용
- 5블록 이하의 경우
FULL TABLE SCAN이 무조건 유리하므로 인덱스를 사용하지 않음 I/O의 최소 단위는 블록인데 전체 5블록 중 한 블록을 읽게 되면 20%가 되어 인덱스의 활용 손익분기점인 0~15%를 넘어가므로 FTS의 성능이 더 좋음.
- 6블록 이상인 경우
같지않다(!=)는 '테이블의 단일 값과 같지 않다'는 의미이므로 대부분의 경우에는 결과가 테이블의 15% 이상일 가능성이 높음
예를 들어 10블록인 경우 데이터를 읽어 들이는 양은 조건에서 같을 경우의 한블록을 제외하면 되므로 (10-1)/10 = 90%에 이르기 때문에, 손익분기점을 넘어가므로 FULL TABLE SCAN 이 유리함.
- 인덱스 칼럼이 아닐때
만약 인덱스 칼럼이 아니라면 당연히 FULL TABLE SCAN 사용
※ 비트맵 인덱스는 구조상 NOT 연산에 대해서도 좋은 성능을 나타낼 수 있음.
2) IS NULL, IS NOT NULL
결합인덱스의 경우 PK가 아니라면 컬럼중 하나가 NULL인 경우를 상수화 시켜서 허용, 모든 칼럼이 NULL 인 경우는 허용하지 않음.
※ 비트맵 인덱스는 NULL 값에 대한 비트맵도 구성하므로 인덱스를 사용
3) 옵티마이저의 취사선택
- 한 테이블에 여러 인덱스가 WHERE 절에 사용된 경우
RBO는 RANK에 의해, CBO는 통계치를 기준으로 선택
선택되지 않은 인덱스의 WHERE절 조건은 테이블에서 읽은 데이터의 체크 조건으로 사용되고 인덱스로는 사용되지 않음
- 옵티마이저의 목표에 따라 인덱스를 사용하지 않을 경우
옵티마이저가 테이블의 데이터가 적은 경우 인덱스를 경유하는 것보다 FTS가 유리하다고 판단하면 인덱스를 사용하지 않음
옵티마이저의 자의적인 판단을 제어하기 위해 힌트를 사용
4) 외부적인 변형 (External Supressing)
이런 경우 질의문을 변경하여 개발자의 의도대로 실행계획을 변경하도록 할 수 있다.
- 인덱스 서프레싱
칼럼을 변형함으로써 인덱스를 사용하지 못하게 되는 것
사용자가 함수(function)나 연산자(operator)등을 사용하여 의도적으로 칼럼 값에 변경을 가해 인덱스를 사용하지 못하도록 하는 외부적인 변형
데이터 베이스에 의해 자동적으로 수행되는 내부적인 변형
※ 서프레싱을 꼭 사용해야 하거나 자주 사용하는 경우라면, 함수 기반 인덱스(FBI)를 사전에 생성하여 인덱스를 활용할 수도 있다.
2. 비트맵 인덱스 이해
1) 비트맵 인덱스
- 조회하는 일정 조건에 의해서 테이블의 rowid를 얻어 테이블 자료에 빠르게 접근하기 위해서 생성
- 인덱스는 이러한 조건에 부합하기 위하여 row 단위로 인덱스 칼럼 값과 rowid 를 정렬하여 리스트 형식으로 저장
- 비트맵 인덱스는 이러한 인덱스 칼럼 값을 비트맵(1 or 0) 으로 변환시켜서 저장
- 내부적으로 B*Tree 같은 일반적인 인덱스와는 다른 방식으로 저장되지만 비트맵 변환 함수를 이용하여 특정 값에 일치하는 rowid 를 얻을 수 있음.
※ 비트맵 인덱스는 oracle enterprise edition 에만 있음.
2) 비트맵 인덱스의 이점
수정 작업이 적고 반복되어 사용되지 않는 질의에 대해서 유리함. 동일한 값이 많은 데이터에 대해서도 유리함.
- 수행 속도에 대한 경우
where 절에 분포도가 낮거나 중간 칼럼 조건들이 여러가지 있을 경우
분포도가 낮거나 중간의 칼럼 조건이 많은 row 가 포함되어 있을 경우
비트맵 인덱스가 분포도가 낮거나 중간 정도인 몇개의 칼럼 또는 전체에 생성되어 있을 경우
질의되는 테이블에 row 수가 많은 경우
- 공간에 대한 경우
b*tree 인덱스와 비교하여 상당한 저장 공간을 절약 가능
b*tree 구조를 사용하는 데이터베이스는 하나의 질의문에 여러 칼럼 조건들을 사용할 경우 결합 인덱스를 구할 수 있는데 수행 속도는 빨라지지만, 저장 공간 측면에서는 칼럼 값을 모두 정렬해서 저장해야 하므로 안 좋은 점이 있다.
비트맵 인덱스는 압축된 형태로 저장되므로, 일반적인 b*tree 구조의 인덱스와 비교하여 약 20%~50% 이상의 저장공간을 절약 가능
특히 분포도가 낮고 비트맵 인덱스 칼럼 값으로 정렬이 되어 있다면 저장 공간을 훨씬 더 줄일 수 있음.
3) 비트맵 결합 인덱스
인덱스를 생성하는 목적은 조건 칼럼의 내용과 데이터가 저장된 위치인 rowid 를 함께 저장하여 데이터를 빠르게 뽑아내기 위함이다.
4) Reverse Key 인덱스
1) Reverse Key 인덱스의 이해
2) reverse key 인덱스 적용
- reverse key index의 생성 및 변경
SQL> create index empno_rvs on emp(empno) reverse;
index created.
SQL> alter index empno_rvs rebuild noreverse;
index altered.
- reverse key index의 단점
reverse key index는 bitmap 인덱스의 IOT를 지원하지 않는다.
또한 = 조건이 아니라면 인덱스에 대한 range scan을 지원하지 않으므로 대소 비교등의 조건은 인덱스 full scan 을 한다.
3) reverse key 인덱스 활용
4. Function Based Index
1) Function Based Index 이해
- 함수기준 인덱스란?
함수 기준 인덱스(FBI)는 인덱스로 사용되는 칼럼의 함수를 통한 변환된 모습이나 연산식의 결과 값을 인덱스에 저장하는 것
- 함수 기준 인덱스의 특성
함수 기준 인덱스는 옵티마이저 모드 중 비용 기준(cost based) 모드에서만 사용할 수 있음
옵티마이저가 인덱스를 사용하여 실행 계획을 수립하기 위해서는 반드시 테이블에 analyze 명령을 수행하여 통계 값을 생성해야함
init 팡렝서 optimizer_mode 를 first_rows나 all_rows 로 지정하거나 hint 를 사용하여 비용 기준 옵티마이저가 사용되도록 해야함
※ FBI는 b*tree, 비트맵 인덱스 모두 생성 가능함.
2) Function Based Index 예제 - 함수 이용
3) Function BAsed Index 예제 - 계산식 이용
4) Function Based Index 예제 - Function Based 와 Descending 인덱스의 복합 예제
5) IOT (Index Organized Table)
1) IOT 구조
- IO 방법
일반 테이블은 하나 이상의 인덱스 블록을 읽어 rowid 를 읽고, rowid 에 기초하여 테이블에 대한 IO가 발생하는 반면, (vs) Index Organized Table 은 모든 행이 인덱스의 leaf 노드에 있으므로 인덱스 블록만 read 하면 되므로 효율적이다.
- IOT 인덱스의 사용
IOT 인덱스를 검색한 후 rowid 를 통해 테이블 자료를 랜덤 액세스하지 않고, 테이블 자체에서 바로 읽어 들이기 때문에 일반적인 테이블보다 key 에 근거한 액세스 속도가 더 빠름, 따라서 인덱스가 전체 칼럼에서 많은 비중을 차지하거나, 트랜잭션이 많이 일어나는 마스터성 테이블을 IOT로 구성하면 유용
인덱스와 테이블을 각각의 객체로 유지하지 않기 때문에 인덱스의 rowid 영역이 없어져 저장 공간을 절약 가능
2) IOT 특성
- IOT 비적용 대상
랜덤하게 저장되는 일반 테이블에 비해 IOT는 정렬에 대한 부담 때문에 데이터 입력, 수정시 DBMS에 부하가 많이 걸려 저장되는 데이터가 많거나 변경이 많은 경우는 IOT 를 사용하지 않음
- IOT 스토리지 구조
☞ IOT는 모든 테이블 데이터를 b-tree 구조로 저장
☞테이블 기본 키에 기초한 b-tree 인덱스는 인덱스처럼 조직
☞이 구조의 leaf 블록은 인덱스 leaf 엔트리의 두번째 구성 요소에 rowid 대신 non-key 열을 가심
☞테이블과 인덱스라는 별도의 두 세그먼트를 갖지 않음
☞IOT의 index row 들은 인덱스 키 값과 non-key 값을 포함
☞IOT의 index에는 rowid 정보가 없음
- IOT 적용 대상
☞자주 사용되는 마스터성 테이블
일부 마스터성 엔티티의 경우 트랜잭션이 집중되면서 시스템 성능의 병목지점으로 작용할 가능성이 큰데, IOT 사용시 효과적
☞인덱스가 대부분인 테이블
인덱스는 칼럼수가 적은 것이 좋지만 부득이하게 전체 칼럼의 대부분을 인덱스로 가질 수 있다. 테이블과 인덱스에 같은 데이터를 중복해서 가질 필요 없이 IOT를 적용하면 효과를 볼 수 있음
※ 접근 방법은 틀리지만 비슷한 효과를 볼 수 있는 것이 MMDB(main memory db)의 hot table/data 와 ssd(solid state disk)의 hot file 이 있음
- IOT 장점
☞ range search, exact match를 수행하는 경우 일반적인 table 보다 빠른 key-baed access 가 가능
☞ FTS 시 primary key 에 대한 full index scan 이 이루어지므로 자동적인 ordering 이 이루어짐
☞ index key column 과 rowid 에 대한 storage 중복을 피할 수 있어 storage 가 절약
2) IOT 특성 - row overflow 영역을 가진 IOT
pctthreshold : IOT의 단일 row 가 db_block_size * (pctthreshold 값 / 100)보다 크다면, including column 이후의 칼럼 data는 overflow tablespace 저장
including column : 지정되지 않으면 primary key column 이외의 column data가 overflow tablespace 에 저장
overflow : 테이블 스페이스를 지정하지 않으면 user default tablespace가 사용됨
3) 인덱스 구조 테이블의 생성
6. 이것만은 꼭!
더보기
