6. IOT, 클러스터 테이블 활용
1. IOT(Index Organized Table) 란?
오라클은 Random 액세스가 발생하지 않도록 테이블 아예 인덱스 구조로 생성하는 방법을 제공하는데, 이를 'IOT' 라고 한다.
IOT 는 인덱스 구조 테이블이믈 정렬 상태를 유지하며 데이터를 삽입한다.
IOT 는 SQL-Server나 Sybases에서 말하는 '클러스터형 인덱스'와 비슷한 개념이다.
오라클 IOT는 PK 컬럼 순으로만 정렬할 수 있다는 점은 다르다.
✅ IOT의 장점과 단점
IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나이다.
같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있기 때문에 Random 액세스가 아닌 Sequential 액세스 방식으로 데이터를 액세스할 수 있고, 이 때문에 더 넓은 범위를 액세스할 때 유리하다.
PK 컬럼 기준으로 데이터가 모여 있더라도 선행 컬럼이 '=' 조건이 아니면 조회 대상 레코드들이 서로 흩어져 많은 스캔을 유발하지만, 적어도 테이블 Random 액세스는 발생하지 않아 빠른 성능을 낼 수 있다.
테이블을 IOT로 생성하면 PK 인덱스를 위한 별도의 세그먼트를 생성하지 않아도 된다는 이점도 있다.
IOT의 가장 큰 단점은 데이터 입력 시 성능이 느리다는 점이다.
IOT는 인덱스 구조이므로 중간에 꽉 찬 블록에 새로운 값을 입력할 일이 종종 생기고 그럴 때 인덱스 분할이 발생한다.
IOT가 PK 이외에 많은 컬럼을 갖는다면 리프 블록에 저장해야할 데이터량이 늘어나 그만큼 인덱스 분할 발생빈도도 높아진다.
컬럼 수가 많은 테이블이라면 인덱스 스캔 효율 때문에 IOT 대상으로는 부적합하다.
IOT에 Direct Path Insert가 작동하지 않는 것도 큰 제약 중 하나이며, 이 때문에 성능이 느리다.
2. 그럼 IOT 테이블을 언제 사용할까?
✅ 크기가 작고 NL 조인으로 반복 룩업하는 테이블
코드성 테이블이 주로 여기에 속한다.
NL 조인에서 Inner 쪽 룩업 테이블로서 액세스되는 동안 건건이 인덱스와 테이블 블록을 다 읽는다면 비효율적이다.
따라서 그런 테이블을 IOT로 구성해 주면 적어도 테이블은 반복 액세스하지 않아도 된다.
✅ 폭이 좁고(=컬럼이 적고) 긴(=로우 수가 많은) 테이블
두 테이블 간 M:M 관계를 해소하기 위한 Association 테이블이 주로 여기에 속한다.
IOT로 구성해 주면 중복을 피할 수 있다.
✅ 넓은 범위를 주로 검색하는 테이블
주로 Between, Like 같은 조건으로 넓은 범위를 검색하는 테이블이라면, IOT 구성을 고려해 볼만하다.
PK 이외 컬럼이 별로 없는 통계성 테이블에는 최적이다.
✅ 데이터 입력과 조회 패턴이 서로 다른 테이블
예를 들어, 일자 별로 데이터를 입력했는데 조회는 사원 별로 하는 경우이다.
일자 별로 클러스터링 팩터가 좋지만 사원별로 읽으면 클러스터링 팩터가 매우 안좋다.
그럴 때, 사원이 첫 번째 정렬 기준이 되도록 IOT를 구성해 주면, 한 블록만 읽고 처리할 수 있다.
3. Partitioned IOT
Partitioned IOT 를 사용하면 Partitioning의 장점과 IOT의 장점을 혼합해서 사용할 수 있다.
4. Overflow 영역
IOT는 PK 이외 컬럼이 많은 테이블일수록 부적합하지만 성능 향상을 위해 IOT가 꼭 필요한 상황이라면 Overflow 기능을 사용할 수 있다.
✅ Overflow 기능 시 제공하는 옵션들
-
OVERFLOW TABLESPACE : Overflow 세그먼트가 저장될 테이블스페이스를 지정한다.
-
PCTTHRESHOLD : DEFAULT 값은 50이다. 이 값이 30이면, 블록 크기의 30%를 초과하기 직전 컬럼까지만 인덱스 블록에 저장하고 그 뒤쪽 컬럼은 모두 Overflow 세그먼트에 저장한다.
-
INCLUDING : Including에 지정한 컬럼까지만 인덱스 블록에 저장하고 나머지는 무조건 Overflow 세그먼트에 저장한다.
오라클은 Pctthreshold 또는 Including 둘 중 만족하는 컬럼을 Overflow 영역에 지정한다.
Overflow 영역을 읽을 때도 건건이 Random 액세스가 발생하기에 Overflow 세그먼트에 저장된 컬럼 중 일부를 자주 액세스해야하는 상황이 발생한다면 IOT 액세스 효율은 급격히 저하된다.
Overflow 영역애도 버퍼 Pinning 효과가 나타나기 때문에 연속적으로 같은 Overflow 블록을 읽을 때는 Random 블록 I/O를 최소화할 수 있다.
5. Secondary 인덱스
IOT에 추가적인 secondary 인덱스를 생성할 때 고려해야 할 몇 가지 주요 이슈가 있는데, IOT는 secondary 인덱스 추가 가능성이 크지 않을 때만 선택하는 것이 바람직하다.
✅ MS-SQL 서버의 비클러스터형 인덱스 진화 과정
SQL Server에서의 IOT 처럼 인덱스 구조로 생성한 테이블을 '클러스터형 인덱스'라 부르고, 여기에 추가로 생성한 2차 인덱스들은 '비클러스터형 인덱스'라고 부른다.
SQL Server 6.5 이전에 비클러스터형 인덱스가 클러스터형 인덱스 레코드를 직접 가리키는 rowid를 갖도록 하였다.
DML 부하가 너무 심하다고 느껴 7.0 버전부터 비클러스터형 인덱스가 rowid 대신 클러스터형 인덱스의 키 값을 갖도록 구조를 변경하였다.
구조를 변경하니 키 값을 갱신하지 않는 한, 인덱스 분할 때문에 비클러스터형 인덱스를 갱신할 필요가 없어진 것이다.
하지만 DML 부하가 줄어든 대신, 비클러스터형 인덱스를 이용할 때 더 많은 I/O가 발생하는 부작용이 생겼다.
✅ 오라클 Logical Rowid
오라클은 IOT를 개발하면서 SQL Server의 두 가지 액세스 방식을 모두 사용할 수 있도록 설계하였다.
IOT 레코드의 위치는 영구적이지 않기 때문에 오라클은 secondary 인덱스로부터 IOT 레코드를 가리킬 때 물리적 주소 대신 logical rowid를 사용한다.
logical rowid는 PK와 physical guess로 구성된다.
Logical Rowid = PK + physical guess
physical guess는 secondary 인덱스를 "최초 생성하거나 재생성한 시점"에 IOT 레코드가 위치했던 데이터 블록 주소이다.
인덱스 분할에 의해 IOT 레코드가 다른 블록으로 이동하더라도 secondary 인덱스에 저장된 physical guess 값은 갱신되지 않는다.
이렇게 오라클은 physical guess를 통해 IOT 레코드를 직접 액세스하고, PK를 통해 IOT를 탐색하는 것이 가능해졌다.
✅ PCT_DIRECT_ACCESS
dba/all/user_indexex 테이블을 조회하면 pct_direct_access 값을 확인할 수 있다.
이는 secondary 인덱스가 유효한 physical guess를 가진 비율을 나타내는 지표로서, secondary 인덱스 탐색 효율을 결정짓는 매우 중요한 값이다.
인덱스를 최초 생성하거나 재생성하면 pct_direct_access 값은 100이다.
이떄는 physical guess로 바로 액세스하고, 성공률도 100%라 비효율도 없다.
문제는, 휘발성이 강한 IOT의 경우 시간이 지나면서 pct_direct_access에 의한 액세스 실패 확률이 높어져 성능이 점점 저하된다는 것이다.
그럴 때는 통계정보를 다시 수집해 pct_direct_access가 실제 physical guess 성공률을 반영하도록 해 주어야 한다. 그러면 다시 값이 100이 된다.
secondary 인덱스 phyiscal guess를 갱신하더라도 통계정보를 재수집한 이후부터 Direct 액세스로 전환된다.
✅ 비휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안
비휘발성 테이블이라면 Direct 액세스 성공률이 높을 것이라서 pct_direct_access 값이 100을 가리키도록 유지하는 것이 좋다.
데이터가 쌓이는 양에 따라 한 달이나 일년에 한 번 physical guess 값을 갱신해주면 된다.
✅ 휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안
휘발성이 강한 IOT에 secondary 인덱스를 추가할 때는 주의가 필요하고, 처음 IOT를 설계할 때부터 생각을 해야 한다.
첫 번째는 주기적으로 physical guess를 정확한 값으로 갱신해 주는 것으로, 주로 secondary 인덱스 크기가 작을 때 쓰는 방법이다.
두 번째는 아예 physical guess가 사용되지 못하도록 pct_direct_access 값을 100 미만으로 떨어뜨리는 것으로, 인덱스 크기가 커서 주기적으로 physical guess 값을 갱신해 줄 수 없을 때 사용한다.
6. 오라클의 클러스터 테이블
오라클의 클러스터 테이블은 인덱스 클러스터 테이블과 해시 클러스터 테이블로 나뉜다.
인덱스 클러스터 테이블은 클러스터 키 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조를 사용한다.
한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결한다.
인덱스 클러스터는 테이블은 키 값이 같은 데이터를 물리적으로 한 곳에 저장해 둘 뿐, IOT 처럼 정렬하지는 않는다.
인덱스 클러스터 테이블을 구성하려면 먼저 클러스터를 생성하고, 클러스터에 테이블을 담기 전에 클러스터 인덱스를 반드시 정의해야 한다.
클러스터 인덱스는 데이터 검색 용도로 사용될 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용되기 때문이다.
클러스터 인덱스도 일반적으로 B*Tree 인덱스 구조를 사용하지만, 해당 키 값을 저장하는 첫 번째 데이터 블록만을 가리킨다는 점에서 다르다.
클러스터 인덱스의 키 값은 항상 Unique 하며, 테이블 레코드와 1:M 관계를 갖는다.
이러한 구조적 특성 때문에 클러스터 인덱스를 스캔하면서 값을 찾을 때는 Random 액세스가 값 하나당 한 번씩만 발생한다.
클러스터에 도달해서는 Sequential 방식으로 스캔하기 때문에 넓은 범위를 읽더라도 비효율이 없다.
인덱스 클러스터는 넓은 범위를 검색할 때 유리하다.
클러스터 인덱스를 '=' 조건으로 액세스할 때는 항상 Unique Scan이 나타난다.
✅ 클러스터 테이블의 단점
-
DML 부하가 심하다.
-
Direct Path Loading을 수행할 수 없다.
-
파티셔닝 기능을 함께 적용할 수 없다.
✅ SIZE 옵션
클러스터 키 하나당 레코드 개수가 많지 않을 때 클러스터마다 할 블록씩 통째로 할당하는 것은 낭비다.
그래서 오라클은 하나의 블록에 여러 키 값이 같이 상주할 수 있도록 SIZE 옵션을 두었다.
SIZE 옵션은 한 블록에 여러 클러스터 키가 같이 담기더라도 하나당 가질 수 있는 최소 공간을 미리 예약하는 기능이다.
7. 해시 클러스터 테이블
해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조다.
클러스터 키로 데이터를 검색하고 저장할 위치를 찾을 때는 해시 함수를 사용한다.
해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환해 준다.
해시 클러스터의 가장 큰 제약사항은 '=' 검색만 가능하다는 것이다.
따라서 거의 대부분 '=' 조건으로만 검색되는 컬럼을 해시 키로 선정해야 한다.
물리적인 인덱스를 따로 갖지 않기 때문에 해시 클러스터 키로 검색할 때는 그만큼 블록 I/O가 덜 발생한다는 이점이 생긴다.
