Oracle index block split 현상

Block split은 새로운 index key가 들어왔을때 기존에 할당된 block내에 저장할 영역이 없어 새로운 block을 할당 받는 index segment관련 operation 입니다.

Index Block Split은 새로 들어오는 index key 데이터에 따라 2개의 다른 방식으로 이루어 집니다.

1. index key 값이 기존의 index key 값에 비해 제일 큰 값이 아닐 경우 50/50  
   block split이 발생한다. 50/50 split은 기존에 존재하던 old block과 새로
   만들어진 new block에 50%의 데이터씩 채워져 split이 발생하게 된다.

2. index key 값이 기존의 index key 값에 비해 제일 큰값이 들어올 경우 99/1
   block split 이 발생한다. 99/1 split은 기존에 존재하던 old block에 99%의
   데이터가 있고 new block엔 새로운 데이터가 저장되게 된다.

이러한 index key의 저장 영역의 확인은 "analyze .. validate structure" 수행 후 index_stats view의 조회를 통해 확인 할 수 있습니다.

다음의 예는 간단한 테스트 입니다.

1. 계속 증가되는 데이터에 의해 99/1 split이 발생하는 예제 입니다.

SQL> drop table t1;
SQL> create table t1 (name varchar2(10),nr number) pctfree 0;
SQL> create index i1 on t1(nr);
SQL> declare
     i number;
     begin
     for i in 1..50000
     loop
     insert into t1 values('XX',i);
     end loop;
     end;
/

SQL> analyze index i1 validate structure;
SQL> select blocks, lf_blks, pct_used from index_stats;

Results:

BLOCKS: 104 LF_BLKS: 99 PCT_USED: 99

2. 데이터의 입력 순서를 바꿔 50/50 split이 발생하는 예제입니다.

SQL> drop table t2;
SQL> create table t2 (name varchar2(10),nr number) pctfree 0;
SQL> create index i2 on t2(nr);
SQL> declare
     i number;
     begin
     for i in 25000..50000
     loop
      insert into t2 values('XX',i);
     end loop;
     for i in 1..25000
     loop
      insert into t2 values('XX',i);
     end loop;
     end;
/

SQL> analyze index i2 validate structure;
SQL> select blocks, lf_blks, pct_used from index_stats;

Results:

BLOCKS: 256 LF_BLKS: 146 PCT_USED: 68

마지막의 blocks의 결과를 보시면 데이터의 입력 순서에 따라 데이터의 저장영역이 2배 이상 차이가 나는 것을 확인 할 수 있습니다.

이거 보시고 99/1로 index split을 하면 저장역역을 아낄 수 있겠지만,
예제1의 t1이 OLTP에서 여러 세션에 의해 insert 되는 table의 index라면
t1에 대한 insert 세션들이 index split을 기다리는 "enq: TX - index contention"라는
block contention event를 오랫동안 만나실 수도 있습니다. ㅎㅎ

이 내용은 metalink note 183612.1을 참고했습니다.