출처 : KACHISORI
전회의 「복합 색인(콤퍼짓(composite) 색인)이 유효한 케이스」에서는 콤퍼짓(composite) 색인을 유효하게 이용하기 위한 튜닝·테크닉을 설명했습니다. Oracle에는 지금까지 소개한 것 이외로 다양한 상황에 따라 사용할 수 있는 색인이 준비되어 있습니다. 이번은 그러한 색인의 사용 장소나 특징에 대해 설명합니다.
■ 함수·베이스 색인의 사용
Oracle8i부터 이용할 수 있는 함수·베이스 색인은 함수나 식의 값이 사전에 계산되어 그 결과가 색인내에 격납됩니다. 그러므로 WHERE구로 지정한 색인열이 함수나 산술로 수식되어 색인을 사용할 수 없는 케이스에 대해서도 이 함수·베이스 색인을 이용하면 색인 스캔이 가능하게 됩니다(자세한 것은, 제7회 「색인을 사용할 수 없는 케이스」를 참조).
함수·베이스 색인을 사용하기 위해서는 이하의 설정을 실시하거나 SQL중에 명시적으로 HINT구를 지정할 필요가 있습니다.
- 초기화 파라미터 「QUERY_REWRITE_ENABLED=TRUE」、초기화 파라미터 「QUERY_REWRITE_INTEGRITY=ENFORCED」 注1
- 초기화 파라미터「COMPATIBLE」가8.1이상
- 색인이 작성된 테이블의 통계 정보가 취득되서 코스트 베이스의 어프로치를 사용한다
| 注1：초기화 파라메터 SQL 함수를 사용한 함수·베이스 색인의 경우에는, 디폴트치(ENFORCED)에서도 사용할 수 있습니다.그러나 유저정의 함수를 사용한 함수·베이스 색인의 경우에는 「QUERY_REWRITE_INTEGRITY=TRUSTED」라고 설정했을 경우만 사용할 수 있습니다 |
그림 1은 펑션·베이스 색인의 작성예 및 작성 후의 확인 방법입니다.
 |
| 그림 1 펑션·베이스 색인의 작성, 확인 방법 |
실제로 WHERE구의 색인열이 함수에 의해서 수식되고 있는 SQL로 함수·베이스 색인을 사용했을 경우와 그렇지 않은 경우로의 실행 통계, 실행 계획을 비교해 봅시다.
 |
| 그림 2 통상의 B*Tree 색인은 사용되지 않고 풀테이블 스캔 되었을 경우 |
 |
| 그림 3 함수·베이스 색인을 사용했을 경우 |
그림 3에서는 힌트문으로 함수·베이스 색인을 사용하도록 옵티마이져에 지시하고 있습니다. 실행 계획을 보면 WHERE구의 조건열이 산술에 의해서 수식되고 있음에도 불구하고 함수·베이스 색인에 의한 색인 스캔이 실시되고 있는 것을 확인할 수 있습니다. 또 실행 시간, 액세스 블록수도 크게 감소하고 있는 것을 확인할수 있습니다 첫머리에서도 기술한 것처럼 함수등에 의한 부하가 높은 계산 처리 결과가 이미 색인에 격납되고 있으므로 검색 처리의 퍼포먼스를 크게 개선할 수 있을 가능성이 있습니다
■비트맵 인덱스의 사용
비트 맵 색인는 색인열의 값과 각 레코드가 그 값에 해당하는지 아닌지를 나타내는 비트 맵이 보관 유지되고 있어 검색 처리에서는 비트의 유무로 조건에 해당하는지 아닌지가 판정됩니다. 카디나리티가 낮은 복수의 열을 WHERE 조건으로 지정할 경우 등, 각 열에 비트 맵 색인을 작성해 두면 비트 연산에 의한 고속의 검색을 실시하는 것이 가능하게 됩니다. 또 제7회 「색인을 작성했는데 퍼포먼스가 나쁜 케이스」의 색인을 사용할 수 없는 케이스로 설명한 것처럼 NULL치의 검색도 가능합니다. 비트 맵 색인은 B*Tree 색인과 같이 각 레코드의 ROWID를 보관 유지할 필요가 없기 때문에 색인의 사이즈가 작습니다. 특히 레코드 건수가 많은 테이블의, 카디나리티가 낮은 열에 대해서 작성했을 경우에 현저히 차이가 납니다.
그림 4는, 10, 20, 30, 40의 4개의 값이 격납되고 있는 열에 비트 맵 색인을 작성했을 경우의 이미지도입니다. 그림으로부터 알 수 있듯이, 비트 맵 색인에서는 테이블의 레코드를 몇개의 그룹으로 분류해 관리하고 있으므로 그룹 마다 비트맵 세그먼트(segment)를 할당할 수 있습니다. 비트 맵 색인을 사용하는 경우 갱신 처리 실행시의 락 단위가 비트 맵 세그먼트(segment) 단위가 되는 점에 주의할 필요가 있습니다.
이 때문에 복수의 갱신 처리가 동시에 실행되는 OLTP계의 시스템보다 DWH이나 BI계의 참조 처리가 중심이 되는 시스템으로 이용하면 유리합니다.
 |
| 그림 4 비트맵 색인의 데이터 격납 이미지 |
그림 5는, 2 종류의 데이터(각 50만건)를 가지는 열에 B*Tree 색인과 비트맵 색인을 작성했을 때의 색인 사이즈입니다.
 |
| 그림 5 B*Tree 색인과 비트맵 색인의 사이즈의 비교 |
그림 5를 보듯 비트맵 색인이 색인 사이즈가 매우 작은것을 확인할 수 있습니다. 대규모 테이블에 대해서 복수의 색인을 작성하는 경우 등은, 자원면에서 큰 메리트를 얻을 수 있습니다.
또 아래와 같은 그림 6, 그림 7에서는 색인 작성열의 카디나리티가 높은(유니크) 경우와 카디나리티가 낮은 경우의 검색 시간, 색인 오브젝트 사이즈에 대해서 정리하고 있습니다.
 |
| 그림 6 유니크 데이터가 격납되고 있는 경우 |
 |
| 그림 7 카디나리티 5의 데이터가 격납되고 있는 경우 |
그림 6과 그림 7의 결과에서 사용 영역, 처리 시간의 관점에서는, 카디나리티가 높은 열에 대해서는 B*Tree 색인이 최적이고 카디나리티의 낮은 열에 대해서는 비트 맵 색인이 최적이다라고 하는 것을 확인할 수 있습니다
■ 역키 색인의 사용
역키 색인은 색인열의 데이터를 비트 단위로 반전시켜 그 반전시킨 데이터를 소트 해 색인에 격납합니다. 그러므로 색인열의 값이 승순으로 증가하는 INSERT 처리를 다중으로 실행했을 경우 색인의 블록 경합을 절감 시킬 수 있습니다.
다만, 비트로 반전되고 있기 때문에 “<”, “>”, “between”등의 범위 검색에서는 색인 스캔을 실시할 수 없습니다. 역키 색인을 사용하는 경우는 검색 처리의 패턴을 판별한 위에 사용할 필요가 있습니다.
 |
| 그림 8 역키 색인의 데이터 격납 이미지 |
예를 들면 수주 테이블을 생각해 보겠습니다. 수주 번호는 순서를 사용해 승순에 값이 차여 많은 단말로부터 동시에 INSERT 처리가 실행되고 있습니다. 이 때 수주 번호에 B*Tree 색인이 작성되고 있으면 그림 9와 같이 색인 트리의 마지막 블록에 삽입 처리가 집중해 버려 퍼포먼스가 열화 하는 원인이 됩니다.
 |
| 그림 9 B*Tree 색인으로의 특정 색인 블록에의 액세스 집중 |
이러한 경우 역키 색인을 사용하면 연속한 데이터에서도 그림 10과 같이 복수의 색인 블록에 분산해 삽입되게 되기 때문에 그림 9와 같은 특정 블록에의 액세스 집중에 의한 경합을 경감할 수 있습니다.
 |
| 그림 10 역키 색인의 사용에 의한 삽입 데이터의 분산 |
이하의 그림 11은 대량의 INSERT 처리를 실시했을 경우의 B*Tree 색인과 역키 색인으로의 경합 발생 상황을 V$WAITSTAT 동적 파포만스뷰의 「data block」항목으로 확인한 것입니다.
 |
|
그림 11 역키 색인에 의한 색인 블록 경합의 감소
|
상기의 예에서는 역키 색인을 사용하면 대기 회수가 「1148회」, 대기 시간이 「324.91초」감소하고 있습니다. 블록 경합을 한층 더 줄이기 위해서는 테이블을 해시·파티션화하는 것도 유효합니다.
■ ITL의 경합을 피하기
동시에 대량의 트랜잭션이 실행되는 시스템에서는 퍼포먼스의 열화의 원인으로서 테이블이나 색인의 데이터 블록에 ITL(Interested Transaction List)의 경합이 발생하고 있는 경우도 생각할 수 있습니다. ITL이란 각 데이터 블록내에 보관 유지되는 트랜잭션정보로 블록을 변경하는 트랜잭션은 최초로 ITL를 할당할 필요가 있습니다. ITL가 부족했을 경우 MAXTRANS의 값까지 자동적으로 확장됩니다만 빈영역이 없는등의 이유에 의해 확장할 수 없는 경우 트랜잭션은 ITL를 획득할 수 있을 때까지 대기하게 됩니다.
ITL의 개수는 오브젝트 작성시의 「INITRANS」파라미터에 의해서 정해지며 최대치는 블록 사이즈에 의존합니다. StatsPack나 v$segment_statistics 뷰에서 ITL의 경합이 다발하고 있는 것이 확인된 경우에는 PCTFREE나 INITRANS의 조정을 검토해야 합니다.
 | ||||||||||||
| ||||||||||||
| 그림 12 ITL의 경합에 의한 대기와 블록·사이즈당 ITL의 최대치 |
■ 색인 작성시의 DESC 옵션에 대해
색인 작성시 열명의 뒤에 DESC 옵션을 지정할 수 있습니다. 역키 색인의 REVERSE 옵션과 비슷한 이미지의 단어입니다만 실제로는 색인의 데이터를 내림차순(디폴트는 승순)으로 격납하는 옵션이 됩니다. DESC 옵션을 지정한 색인은 펑션 색인 으로 취급이 되므로 먼저 설명한 펑션 색인의 설정과 동일한 설정이 필요하게 됩니다.
◇
이번회에까지하여 색인을 사용한 튜닝·테크닉을 설명했습니다.다음회 부터는 결합에 관한 튜닝·테크닉을 소개합니다
댓글 없음:
댓글 쓰기