Oracle IOT

IOT

---

테이블 전체가 인덱스 구조로 이루어진 테이블을 IOT(Index-Organized Table)이라고 부르며 테이블을 찾아가기 위한 rowid를 갖는 일반 인덱스와는 달리 모든 데이터를 리프 블록에 저장하고 있다. (인덱스 리프 블록 == 데이터 블록)

IOT 구조로 테이블을 구성하기 위해선 테이블 시작 시 설정하여야하는데, 그 방법은 아래와 같다.

create table index_org t (a number primary key, b varchar(10)) organization index;
//IOT 생성

특징

---

• 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여있기 때문에 Rnadom 액세스가 아닌, Sequential 방식으로 데이터를 액세스할 수 있다. → 넓은 범위를 액세스할 때 유리하다.
• 데이터 입력 시 성능이 느리다. (모두 인덱스로 구성되어있으므로)
• 인덱스 분할로 인해 성능 저하가 발생할 수 있다. → 칼럼 수가 많은 테이블이라면 IOT 대상으로 부적합)
• Direct Path Insert가 작동하지 않는다.

SQL Server 클러스터형 인덱스과 비교

---

SQL Server에서도 IOT와 비슷한 기능을 클러스터형 인덱스로 지원한다. 다만, 조금의 차이가 있는데 오라클은 PK 칼럼으로만 IOT를 정의할 수 있지만, 클러스터형 인덱스는 PK가 아닌 칼럼으로도 생성할 수 있다. 다만, 그렇기에 중복된 키 값 식별을 위해 uniquifier라는 4바이트 크기의 값을 함께 저장한다.

사용 상황

---

1. 크기가 작고 NL 조인으로 반복 Lookup하는 테이블
   1. 코드성 테이블 등.
   2. NL 조인에서 Inner 쪽 Lookup 테이블로써 액세스되는 동안 건건이 인덱스와 테이블 블록을 다 읽지 않아도 된다.
2. 폭이 좁고 긴(=로우 수가 많은)테이블

1. M:M 관계를 해소하기 위한 테이블이 주로 여기에 속한다.

3. 넓은 범위를 주로 검색하는 테이블
   1. 주로 Between , Like 조건으로 넓은 범위 검색하는 테이블일 시, IOT 테이블 구성을 고려할만 하다.
   2. PK 이외에 컬럼이 별로 없는 테이블이 최적의 솔루션이라고 할 수 있다.
4. 데이터 입력과 조회 패턴이 다른 테이블
   1. 영업 사원과 사원 실적 테이블이 있고, 매일 모든 영업 사원의 데이터를 입력하지만 조회는 사원별로 조회하는 경우.
      1. 매일 모든 영업 사원 데이터가 입력된다면, 클러스터링 팩터가 매우 안좋을 수 밖에 없다. 그렇기에 이를 IOT로 구성한다면 성능 향상에 도움이 될 수 있다.

Partitioned IOT

---

수억 건에 이르는 일별 상품별계좌별거래 테이블을 IOT로 구성 시 읽기 성능은 획지적으로 향상될 수 있지만, 수억 건에 이르는 테이블을 단일 IOT로 구성할 시 한 번의 쿼리에 수억 건의 데이터를 Full Scan할 수도 있으며, 뿐만 아니라 관리상으로도 부담스럽기에 IOT 파티셔닝하여 구성할 수 있다. → 파티션 프루닝을 통한 범위 검색 가능

Overflow 영역

---

PK 이외의 칼럼이 많은 테이블일수록 인덱스 분할, DML 부하, 검색을 위한 스캔량도 늘어나기에 IOT로 구성하기에 부적합하다. 이에 일정 비율을 지정하여 비율이 넘어가게되면 Overflow 세그먼트에 저장할 수 있도록 할 수 있다.

CREATE TABLE IOTAB
(
COL_PK NUMBER PRIMARY KEY,
COL2 VARCHAR2(500),
COL3 NUMBER,
COL4 VARCHAR2(1000)
)
ORGANIZATION INDEX TABLESPACE USERS
PCTTHRESHOLD 10 INCLUDING COL2
OVERFLOW TABLESPACE USERS;

• OVERFLOW TBLESPACE: Overflow 세그먼트가 저장될 테이블 스페이스를 지정한다.

• PCTTHRESHOLD: Default 값은 50이며, 이 값이 30일 시 블록 크기의 30%를 초과하기 직전 컬럼까지만 인덱스에 저장하고 그 뒤쪽 칼럼은 모두 Overflow 세그먼트에 저장한다.

• INCLUDING: Including에 지정한 컬럼까지만 인덱스 블록에 저장하고 나머지는 무조건 Overflow 세그먼트에 저장하도록 한다.

  Overflow 영역을 읽을 떄에도 건건이 Random 액세스가 발생하며, Overflow 세그먼트에 저장된 칼럼 중 일부를 자주 액세스해야하는 상황 발생 시 성능이 급격히 저하된다.

  Overflow 영역에서도 버퍼 Pinning은 작동하기 때문에 연속적으로 같은 Overflow 블록을 읽을 시 Random Block I/O를 최소화할 수 있다.

Secondary 인덱스

---

SQL Server에서 비클러스터형 인덱스(Secondary 인덱스)

---

SQL 서버에서 IOT처럼 인덱스 구조로 생성한 테이블을 ‘클러스터형 인덱스’라 부르고, 여기에 추가로 생성한 2차 인덱스들은 ‘비클러스터형 인덱스’라고 부른다.

SQL Server 6.5 이전에는 좌측처럼 비클러스터형 인덱스가 클러스터형 인덱스 레코드를 직접 가르키는 rowid를 갖도록 하여 클러스터형 인덱스 레코드 위치가 인덱스 분할 등으로 인해 변경될 때마다 비 클러스터형 인덱스가 갖는 모든 rowid를 갱신해 주어야했으나,

SQL Server 7.0 부터는 비클러스터형 인덱스가 rowid 대신 인덱스 키 값을 갖도록 구조를 변경하여 rowid를 갱신할 필요가 없어졌다. 대신, 비클러스터형 인덱스 이용 시 이전보다 더 많은 I/O가 발생하는 부작용을 갖게 되었다.

Oracle에서 Secondary 인덱스

---

오라클은 secondary 인덱스로부터 IOT 레코드를 가르킬 때마다 물리적 주소 대신, logical rowid를 사용한다.

(logical rowid = PK + Physical guess)

• Physical Guess는 secondary 인덱스를 최초 생성하거나 재생성된 시점에 IOT레코드가 위치했던 데이터 블록 주소이며, 인덱스 분할되어도 갱신되지 않는다.
• SQL Server 6.5 이전에 사용하였던 이유와 같이 DML 부하를 줄이기 위해 이용하며, 실제 위치가 아닐 수 있기에 guess라는 표현을 사용한다.
  • 위 그림에서 1은 physical guess를 통해 직접 액세스하는 것을 의미하고, 2는 PK를 통해 IOT를 탐색하는 것을 표현한 것이다.

PCT_DIRECT_ACCESS

---

DBA_ALL_USER_INDEXES 테이블 조회 시 PCT_DIRECT_ACCESS 값을 확인할 수 있으며, 이는 secondary 인덱스가 유효한 physical guess를 가진 비율을 나타내는 지표로써, seconday 인덱스 탐색 효율을 결정짓는 매우 중요한 요소이다.

최초 IOT secondary 인덱스 생성 시 pct_direct_access 값은 100이며, 이후 레코드 위치가 자주 바뀌는 IOT의 경우 시간이 지나며 성능이 저하되는데, 이 때 아래처럼 인덱스를 Rebuild 하거나 update block references 옵션을 이용해 physical guess를 주기적으로 갱신해 준다면 효과적이다.

alter index iot_second_idx REBUILD;
alter index iot_second_idx UPDATE BLOCK REFERENCES;

비휘발성 IOT secondary 인덱스 튜닝

---

• 비휘발성 테이블이라면 Direct 액세스 성공률이 높을 거시므로 pct_direct_access 값이 100을 가르키도록 유지하는 것이 효과적이다.

• 데이터가 쌓이는 양에 따라 한달, 또는 일 년에 한 번씩 physical guess를 갱신해주면 된다.

  통계정보 수집 직후에 아래 프로시저를 이용해 값을 직접 설정해주면 된다.

exec dbms_stats.set_index_stats (user, 't1_x1', guessq => 100);

휘발성 IOT Secondary 인덱스 튜닝

---

• 휘발성이 강한 IOT에 Secondary index를 추가할 때는 각별한 주의가 필요하며, 처음 설계부터 이에 대한 고려가 있어야 한다.
• 넓은 범위를 액세스 하도록 Secondary index를 구성한다.
  • 상품번호, 거래일자, 고객번호로 인덱스 구성 시 상품번호, 거래일자 between 조회 시 넓은 범위를 조회할 가능성이 높으므로 secondary 인덱스를 고객번호, 거래일자로 구성한다면 고객별 주문량 또한 대개 소수이므로 액세스 과정에서 발생하는 비효율이 미미할 것이다.

인덱스 클러스터 테이블

---

인덱스 클러스터 테이블은 클러스터 키값이 같은 레코드가 한 블록에 모이도록 저장하는 구조를 사용한다. ⇒ (클러스터링 팩터 최적화)

• 한 블록에 모두 담지 못할 시 새로운 블록을 할당해 클러스터 체인으로 연결한다.

  클러스터 테이블에 담기 전에 아래와 같이 클러스터 인덱스 정의가 선행되어야 한다.

create idnex i_deptno# on cluster c_deptno#;

클러스터 인덱스도 일반적인 B-Tree 인덱스 구조를 사용하지만, 해당 키 값을 저장하는 데이터 블록만을 가리킨다는 점에서 IOT와 다르다. 클러스터 인덱스 키값은 항상 Unique 하며, 레코드와 1:M 관계를 갖는다.

클러스터 인덱스 스캔 시 Random 액세스가 값 하나 당 한 번씩 밖에 발생하지 않는다.

해시 클러스터 테이블

---

• 해시 클러스터 테이블은 해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조이다.
• 클러스터 키로 데이터를 검색하고 저장할 위치를 찾을 때는 해시 함수를 사용한다.
• 해시 함수가 인덱스 역할을 대신하는 것이며, 해싱 알고리즘을 이용해 클러스터 키 값을 데이터 블록 주소로 변환해 준다.
• 해시 클러스터 테이블은 ‘=’ 검색만 가능하다.
• 물리적인 인덱스를 따로 갖지 않기에 해시 클러스터 키로 검색 시 크만큼 블록 I/O가 덜 발생한다.