CH1. 인덱스 원리와 활용 - 06. IOT,클러스터 테이블 활용

오라클은 테이블을 인덱스 구조로 생성할 수 있는 IOT (Index Organized Table)라고 부르는 기능을 제공합니다.
create table [테이블명] (a number primary key, ...)
organization index;

위와 같은 구문을 사용해서 생성할 수 있습니다.

IOT 장점 & 단점
장점
인위적으로 클러스터링 팩터를 좋게 만드는 방법중 하나
Random 액세스가 아닌 Sequential 방식으로 데이터를 액세스.
넓은 범위 액세스 시 유리
선두 컬럼이 ‘=‘조건이 아니면 조회 대상 레코드들이 서로 흩어져 많은 스캔을 유발하지만 ,적어도 테이블 Random 액세느는 발생하지 않아 빠른 성능 제공 가능
PK인덱스를 위한 별도의 세그먼트를 생성하지 않아도 됨
단점
인덱스 분할(Split)시 발생량 차이 가 일반 테이블 + PK인덱스 구조에 비해 큼. IOT는 인덱스 구조이므로 중간에 꽉찬 블록에 새로운 값이 들어가는 스플릿이 발생할 수 있으며 IOT가 PK 이외에 많은 컬럼을 갖는다면 리프 블록에 저장해야 할 데이터량이 늘어나 그만큼 인덱스 분할 발생빈도가 높아집니다.
Direct Path Insert가 작동하지 않는 것도 단점

(2) IOT 언제 사용?
- 크기가 작고 NL조인으로 반복 룩업(LookUP)하는 테이블
- 폭이 좁고 긴(=로우 수가 많고 컬럼 수가 적은) 테이블
- 넓은 범위를 주로 검색하는 테이블
- 데이터 입력과 조회 패턴이 서로 다른 테이블

(3) Partitioned IOT
수 억건에 이르는 일별상품계좌별 거래 테이블이 있고 , 상품번호 + 거래일자를 기준으로 조회하는 쿼리가 가장 자주 수행됩니다.
이런 테이블은 상품별 거래 건수가 워낙 많아 인덱스를 사용하면 Random 액세스 부하가 심하게 발생합니다. 거래 일자 기준으로 월별 Range 파티셔닝 돼 있으면 인덱스를 이용하기보다 필요한 파티션만 Full Scan하는 편이 빠르지만 다른 종목의 거래 데이터까지 모두 읽는 비효율이 생깁니다.
상품번호 + 거래일자 순으로 IOT를 구성하면 성능은 향상되지만 크기가 큰 테이블이기 때문에 부담스러울 수 있으며, 기존에 인덱스를 이용한 다른 쿼리가 있으므로 어렵습니다.
이럴 때 Pattioned IOT를 구성하면 좋습니다.
-거래일자 기준 Rande 파티셔닝
-상품번호 + 거래일자 순으로 PK정의 후 IOT 구성
원래 쿼리는 IOT에서 원하는 파티션을 골라 필요한 종목 거래 데이터를 스캔할 수 있고, 다른 쿼리는 해당 조건에 맞는 파티션 세그먼트 전체를 Full Scan할 수 있습니다.

(4)Overflow 영역
IOT로 만들기 부담스러운 테이블(크기가 크고 컬럼이 많을 때)인데도 성능 향상을 위해 IOT 가 필요하다면 Overflow 기능이 도움이 될 수 있습니다.

위 테이블에서 생성자ID,수정자ID,생성일시,수정일시는 시스템 내부적인 필요에 의해 생겨난 관리 속성이지 업무요건에 의한 것은 아닙니다. 따라서 값은 저장해 두지만 줄력이나 조회조건으로는 거의 사용되지 않습니다.
만약 이런 컬럼들을 다른 주요 컬럼과 분리 저장할 수 있다면 IOT활용성을 높일 수 있는데 오라클이 그 기능을 제공합니다.
OVERFLOW TABLESPACE : overflow 세그먼트가 저장될 테이블 스페이스를 지정합니다.(sys_iot_over_123123등의 이름을 가진 세그먼트가 생성됩니다.)
PCTTHRESHOLD : Default는 50입니다. 이 값이 30이면 블록 크기의 30%를 초과하기 직전 컬럼까지만 인덱스 블록에 저장하고 그 뒤쪽 컬럼은 모두 overflow 세그먼트에 저장합니다. 물론 로우 전체 크기가 지정된 비율 크기보다 작다면 모두 인덱스 블록에 저장합니다. 테이블을 생성하는 시점에 모든 컬럼의 데이터 타입 Max 길이를 합산한 크기가 이 비율 크기보다 작다면 overflow 세그먼트는 불필요하지만 만약 초과한다면 오라클은 overflow tablespace 옵션을 반드시 지정하도록 강제하는 에러를 던집니다
INCLUDING : including에 지정한 컬럼까지만 인덱스 블록에 저장하고 나머지는 무조건 overflow 세그먼트에 저장합니다.

오라클은 pctthreshold 또는 including 둘 중 하나를 만족하는 컬럼을 overflow 영역에 저장합니다. including 이전에 위치한 컬럼이더라도 pctthreshold에 지정된 비율 크기를 초과한다면 overflow 영역에 저장됩니다. 반대의 경우도 마찬가지 입니다.
overflow 영역을 읽을 때도 건건이 Random 액세스가 발생한다는 사실입니다. 따라서 overflow 세그먼트에 저장된 컬럼 중 일부를 자주 액세스해야 하는 상황이 발생한다면 IOT 액세스 효율은 급격히 저하됩니다. 어떤 컬럼을 지정할지, 몇퍼센트를 지정할지를 신중히 고려해서 사용해야 합니다.
다행히 oveflow 영역에도 버퍼 Pinning효고가 나타나기 때문에 연속적으로 같은 oveflow 블록을 읽을 때는 Random 블록 I/O를 최소화할 수 있습니다.

(5) Secondary 인덱스
결론부터 말하면 IOT는 secondary인덱스 추가 가능성이 크지 않을 때만 선택하는 것이 바람직합니다.
오라클은 IOT를 개발하면서 키값과 rowid를 갖도록 하는 두가지 액세스 방식을 모두 사용할 수 있도록 설계하였습니다.

IOT 레코드의 위치는 영구적이기 않기 때문에 오라클은 secondary 인덱스로부터 IOT 레코드를 가리킬 때 물리적 주소 대신 logical rowid를 사용합니다. logical rowid 는 PK와 physical guess로 구성됩니다.
physical guess는 secondary 인덱스를 최초 생성하거나 재생성(rebuild)한 시점에 IOT 레코드가 위치했던 데이터 블록 주소(DBA)입니다. 인덱스 분할에 의해 IOT 레코드가 다른 블록으로 이동하더라도 secondary 인덱스에 저장된 physical guess 값은 갱신되지 않습니다.
그림에서 1은 physical guess를 통해 Direct로 블록에 접근, 2는 PK를 통해 IOT 레코드를 탐색하는 것을 표현한 것입니다.

PCT_DIRECT_ACCESS
dba/all/user_indexes 테이블을 조회하면 pct_direct_access 값을 확인할 수 있습니다. 이는 secondary 인덱스가 유효한 physical guess를 가진 비율(Direct 액세스 성공 비율)을 나타내는 지표로서 seconary인덱스 탐색 효율을 결정짓는 매우 중요한 값입니다.
통계쩡보 수집을 통해 얻어지는 이 값이 100% 미만이면 오라클은 바로 PK를 이용해 IOT를 탐색합니다. 100%일 때만 physical guess를 이용하는데, 레코드를 찾아갔을 때 해당 레코드가 다른 곳으로 이동하고 없으면 PK로 다시 IOT를 탐색합니다. 그런 비율이 높아지면 성능은 당연히 나빠집니다.
인덱스 최초 생성이나 재생성 시 pct_direct_access 값은 100입니다. 문제는 레코드 위치가 자주 변하는 IOT의 경우 시간이 지나면서 physical guess에 의한 액세스 실패 확률이 높아져 성능이 점점 저하된다는 데에 있습니다. 그럴 때는 통계정보를 다시 수집해 pct_direct_access가 실제 physical guess 성공률을 반영해주도록 해야합니다. 그러면 오라큰은 physical guess를 거치지 않고 곧바로 PK로 IOT를 탐색할 것입니다.
물론 아래 구문처럼 인덱스를 rebuild하거나 update block references 옵션을 이용해 physical guess를 주기적으로 갱신해 준다면 효과적입니다.
alter index iot_second_idx rebuild;
alter index iot_second_idx update block references;
secondary 인덱스 physical guess를 갱신하더라도, 통계정보를 재수집한 이후부터 Direct 액세스로 전환된다는 사실입니다.
인덱스 분할이 발생하더라도 통계정보를 재수집한 이후부터 PK를 이용하는 것과 마찬가지입니다.
Direct 액세스 성공 확률이 비교적 높은 상태에서 통계정보만 재수집하는 바람에 PK 액세스로 전환하는 일이 생겨도 문제입니다. 통계정보를 수집하는 순간 pct_direct_Access가 100미만으로 떨어지기 때문입니다.

비휘발성 IOT에 대한 secondary 인덱스 튜닝 방안
읽기전용이거나 IOT레코드 위치가 거의 변하지 않는 테이블이라면 Direct 액세스 성공률이 높을 것입니다. 따라서 pct_direct_access 값이 100을 가리키도록 유지하는 것이 효과적인 튜닝방안입니다. 데이터가 쌓이는 양에 따라 한 달에 한 번 또는 일년에 한 번 정도만 physical guess를 갱신해 주면 됩니다.
읽기전용 테이블이면 pct_direct_access 값이 100을 가리키도록 한 상태에서 더 이상 통계정보를 수집하지 않으면 되겠지만 맨 우측 블록에 지속적으로 값이 입력된느 경우라면 통계정보 수집이 필수적입니다. 그럴 때는 통계수집 직후에 프로시저를 통해 값을 직접 설정해주면 됩니다.
exec dbms_stats.set_index_stats (user,’t1_x1’,guessing => 100);

휘발성 IOT에 대한 secondary 인덱스 튜닝 방안
IOT 레코드 위치가 자주 변하는 IOT에 secondary 인덱스를 추가할 때는 각별한 주의가 필요하고 처음 IOT를 설계할 때부터 이에 대한 고려가 있어야 합니다.
휘발성이어서 physical guess에 의한 Direct 액세스 성공률이 낮다면 두 가지 선택을 할 수 있습니다.
1. 주기적으로 Physical guess를 정확한 값으로 갱신해 주는 것으로써 주로 secondary 인덱스 크기가 작을 때 쓸 수 있는 방법입니다.
2.아예 physical guess가 사용되지 못하도록 pct_direct_access 값을 100 미만으로 떨어뜨리는 것으로써, 인덱스 크기가 커서 주기적으로 physical guess를 갱신해 줄 수 없을 때 쓸 수 있는 방법입니다. 인덱스 분할이 어느 정도 발생한 상태에서 통계정보를 수집해 주면 됩니다.
2번 방법을 쓰면 일반 테이블을 인덱스 rowid로 액세스할 때보다 느려지겠지만 선택도가 매우 낮은 secondary 인덱스 위주로 구성해 주면 ,큰 비효율은 없습니다.

Right-Growing IOT에서 pct_direct_access가 100 미만으로 떨어지는 이유
맨 우측 블록에만 값이 입력되는 Right-Growing IOT(1~1000까지 차례대로 입력되는 IOT)라면 인덱스 분할이 발생하더라도 기존 레코드의 주소 값이 바뀔 이유가 없는 것이 사실입니다. 새로운 인덱스 블록을 맨 우측에 추가해 거기에 값을 입력하기 때문입니다. 그런데도 이를 가리키는 secondary 인덱스의 physical guess 정확도가 떨어진 이유가 무엇일까요?
이것은 인덱스 높이(height)가 2단계로 증가하면서 생기는 현상입니다.

위의 그림처럼 103번 블록이 추가되면 100번 블록 정보를 새로 할당한 103번 블록에 모두 복제하고 100번 블록은 다시 루트레벨로 올라갑니다. 이 때문에 레코드의 위치가 달라기제 되고 physical guess가 모두 부정확해진 것입니다.

오라클이 이런식으로 인덱스 레벨을 조정하는 이유는 인덱스 루트 블록은 매우 특혈한 블록이기 때문입니다. 인덱스를 탐색할 때는 항상 시작점으로 사용되기 때문입니다.(index fast full scan 제외)

IOT_REDUNDANT_PKEY_ELIM
dba/all/user_indexes를 조회하면 iot_redundant_pkey_elim 통계치를 볼 수 있는데, 이 값이 YES 면 secondary 인덱스 키와 PK컬럼 감에 하나 이상 중복 컬럼이 있어 오라클이 이를 제거 했음을 의미합니다.

(6)인덱스 클러스터 테이블
클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 두가지가 있습니다.

인덱스 클러스터 테이블은 클러스터 키 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조를 사용합니다. 한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결합니다.
심지어 여러 테이블 레코드가 물리적으로 같이 저장될 수 도 있습니다. 여러 테이블을 서로 조인한 상태로 저장해 두는 것인데 일반적으로는 하나의 데이터 블록이 여러 테이블에 의해 공유될 수 없습니다.
인덱스 클러스터는 키 값이 같은 데이터를 물리적으로 한 곳에 저장해 둘뿐 IOT처럼 정렬하지 않습니다.
인덱스 클러스터 테이블을 구성하려면 아래 같은 쿼리를 사용해야 합니다.
create cluster c_deptno# (deptno number(2)) index;
그리고 클러스터에 테이블을 담기 전에 아래와 같이 클러스터 인덱스를 반드시 정의해야 합니다. 클러스터 인덱스는 데이터 검색 용도 뿐아니라 데이터가 저장될 위치를 찾을 때도 사용되기 때문입니다.
create index i_deptno# on cluster c_deptno#
클러스터 인덱스도 일반적인 B*Tree인덱스 구조를 사용하지만, 해당 키 값을 저장하는 첫 번째 데이터 블록만을 가리킨다는 점에서 다릅니다.
클러스터 인덱스의 키 값은 항상 Unique하며 레이블 레코드와 1:M관계를 갖습니다. 일반 테이블에 생성한 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖습니다.
이런 구조적 특성 때문에 클러스터 인덱스를 스캔하면서 값을 찾을 때는 Random 액세스가 값 하나당 한 번씩 밖에 발생하지 않습니다. 클러스터에 도달해서는 SEquential 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다는게 핵심 원리입니다.
인덱스 클러스터 테이블에는 아래 두가지 유형이 있습니다.
-단일 테이블 인덱스 클러스터
- 다중 테이블 인덱스 클러스터

인덱스 클러스터는 넓은 범위를 검색할 때 유리
클러스터 인덱스를 ‘=‘ 조건으로 액세스할 때는 항상 Unique Scan이 나타납니다.

클러스터 테이블이 실무적으로 자주 활용되지 않는 이유는 DML부하때문입니다.
일반적인 힙 구조 테이블에 데이터를 입력할 때는 Freelist로부터 할당받은 공간에 정해진 순서 없이 값을 입력합니다. 반면 IOT는 정렬 상태를 유지하면서 값을 입력한다고 했습니다. 클러스터 테이블은 IOT처럼 정렬 상태를 유지하지는 않지만 정해진 블록을 찾아서 값을 입력해야 하기 때문에 DML 성능이 다소 떨어집니다. 특히 전에 없던 값을 입력할 때는 블록을 새로 할당 받아야 하기 때문에 더 느립니다.
하지만 클러스터를 구성하지 않는 대신 인덱스를 생성할거면 DML부하는 비슷하다고 볼 수 있습니다. 이미 블록이 할당된 클러스터 키 값을 입력할 때는 별 차이가 없고, 만약 계속 새로운 값이 입력돼 많이 느려진다면 클러스터 키를 잘못 선정한 경우라고 할 수 있습니다. 클러스터 테이블을 구성하면서 기존에 사용하던 인덱스 두 세개를 없앨 수 있다면 DML 부하가 오히려 감소할 수 있습니다.
수정이 자주 발생하는 컬럼은 클러스터 키로 선정하지 않는 것이 좋지만 삭제 작업 때문에 클러스터 테이블이 불리할 것은 없습니다. 다만 전체 데이터를 지우거나 테이블을 통째로 Drop할 때 성능 문제가 생길 수 있습니다.
전체 데이터를 지울 때는 Truncate table문장을 쓰는것이 빠른데 클러스터 테이블에는 쓸 수 없습니다. 단일 테이블 클러스터일 때도 마찬가지 입니다. 또한 테이블을 Drop 하려 할 때도 내부적으로 건건이 delete가 수행된다는 사실입니다.
따라서 전체 데이터를 빠르게 지우고 싶을 때는 클러스터를 Truncate 하거나 Drop 하는것이 가장 빠릅니다. 물론 다중 테이블 클러스터일 때는 클러스터링 된 테이블이 모두 삭제됩니다.
truncate cluster objs_cluster#;
drop cluster objs_cluster# including tables;
DML 부하 외에 클러스터 테이블과 관련해 고려해야 할 성능 이슈로는 아래와 같은 것들이 있습니다.
- Direct Path Loading을 수행할 수 없음
- 파티셔닝 기능을 함께 적용할 수 없음
- 타중 테이블 클러스터를 Full Scan할 때는 다른 테이블 데이터까지 스캔하기 때문에 불리함

SIZE 옵션
클러스터 키 하나당 레코드 개수가 많지 않을 때 클러스터마다 한 블록씩 통째로 할당하는 것은 낭비입니다. 그래서 오라클은 하나의 블록에 여러 키 값이 같이 상주할 수 있도록 SIZE옵션을 두었습니다.
SIZE 옵션은 한 블록에 여러 클러스터 키가 같이 담기더라도 하나당 가질 수 있는 최소 공간(바이트 단위)을 미리 예약하는 기능입니다. 예를들어 블록 크기가 8KB일 때 size옵션을 2000바이트를 지정하면 한 블록당 최대 4개 클러스터 키만을 담을 수 있습니다.
SIZE옵션 때문에 데이터 입력이 방해 받지는 않지만 대부분 클러스터 키 값이 한 블록씩 초과한다면 굳이 이 옵션을 두어 클러스터 체인이 발생하도록 할 이유는 없습니다. 너무 작게 설정하면 효과가 반감되고 크게 설정하면 공간을 낭비할 수 있습니다. 판단 기준은 클러스터 키마다의 평균 데이터 크기이며 SIZE 옵션을 지정하지 않으면 한 블록에 하나의 클러스터 키만 담깁니다.

(7)해시 클러스터 테이블
해시 함수에서 변환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조입니다. 클러스터 키로 데이터를 검색하고 저장할 위치를 찾을 때는 해시 함수를 사용합니다. 해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환해 줍니다.
두가지 유형이 있습니다.
- 단일 테이블 해시 클러스터
- 다중 테이블 해시 클러스터
해시 클러스터의 가장 큰 제약사항은 ‘=‘ 검색만 가능하다는 점입니다. 따라서 거의 대부분 ‘=‘조건으로만 검색되는 컬럼을 해시 키로 선정해야 합니다.
물리적인 인덱스를 따로 갖지 않기 때문에 해시 클러스터 키로 검색할 때는 그만큼 블록 I/O가 덜 발생한다는 이점이 생깁니다.