인덱스 스플릿(INDEX SPLIT)

인덱스 스플릿(index split)이란? B-tree index에서 새로운 index key가 들어왔을 때 기존에 할당된 블록 내에 저장할 영역이 없어 새로운 블록을 할당하는 것입니다.

인덱스 스플릿은 새로 들어오는 index key 데이터에 따라 2가지 방식으로 이루어집니다.

 

1.  50:50 index split

index key값이 기존의 index key 값에 비해 제일 큰 값이 아닌 경우 50/50 block split이 발생합니다. 기존에 존재하던 old block과 새로운 new block에 50%씩 데이터가 채워져 스플릿이 발생하는 것을 말합니다. 최대값이 아닌 값이 들어오면 old와 new block 중 어느 곳에 들어갈 지 모르기 때문에 50:50으로 스플릿을 합니다.

 

2.  90:10 index split (99:1 또는 100:0 split이라고 불리기도 함)

index key값이 기존의 index key 값에 비해 제일 큰 값이 들어올 경우 90/10 block split이 발생합니다. New block에는 새로 추가된 키 값만이 추가됩니다. 즉, 기존의 꽉 찬 old block의 키 값을 재분배하지 않으며index key 값이 단방향으로 증가하면서 키 값이 삽입되는 경우 발생합니다. 최대값인 인덱스 키 값이 들어오면 계속 큰 값이 들어올 가능성이 높기 때문에 90:10으로 스플릿을 합니다.

 

-branch node와 root node는 항상 50:50 split을 수행한다.

 

 

Index split 통계값 확인

Sql> select * from v$statname where name like '%split%' and name like '%node%';

 

STATISTIC# NAME                       CLASS    STAT_ID

---------- ------------------------- ---------- ----------

       445 leaf node splits                 128 1417124053

       446 leaf node 90-10 splits           128 1285138251

       447 branch node splits               128  399027615

       448 root node splits                 128  708722985

 

4 rows selected. 

- 현재시스템(11g R2)에서 index split과 관련된 통계값들의 이름을 확인할 수 있습니다.

 

 

90:10 스플릿 유발

같은 쿼리를 순서만 바꿔서 돌렸을 때 인덱스 스플릿이 일어나는지 테스트합니다.

 

▶ 테스트용 테이블 생성

Sql> create table tt1(name varchar2(10), nr number) pctfree 0;

 

▶ 데이터 삽입 전 스플릿 확인

Sql> select a.statistic#,a.name,a.class,a.stat_id,b.value

from v$statname a,v$mystat b

where a.statistic# = b.statistic#

and a.name like '%split%'

and a.name like '%node%'

and b.sid = (select sid from v$session where username = 'SYSTEM');

 

STATISTIC# NAME                          CLASS    STAT_ID      VALUE

---------- ------------------------ ---------- ---------- ----------

       992 leaf node splits                128 1417124053          0

       993 leaf node 90-10 splits          128 1285138251          0

      1002 branch node splits              128  399027615          0

      1003 root node splits                128  708722985          0

 

4 rows selected.

Elapsed: 00:00:00.02

 

▶ 인덱스 스플릿 확인을 위한 데이터 삽입

declare

i number;

begin

for i in 1..50000

loop

insert into tt1 values ('XX',i);

end loop;

end;

/

 

▶ 테스트용 인덱스 생성

Sql> create index tt1 on tt1(nr);

 

Index created.

 

▶ index key 저장영역의 확인을 위해 analyze…structure 분석 후 index_stats 실행

Sql> analyze index tt1 validate structure;

 

Index analyzed.

 

Elapsed: 00:00:00.03

 

Sql>select blocks,lf_blks,pct_used from index_stats;

    BLOCKS    LF_BLKS   PCT_USED

---------- ---------- ----------

       112        110         90

 

1 row selected.

Sql> select a.statistic#,a.name,a.class,a.stat_id,b.value

from v$statname a,v$mystat b

where a.statistic# = b.statistic#

and a.name like '%split%'

and a.name like '%node%'

and b.sid = (select sid from v$session where username = 'SYSTEM');

 

STATISTIC# NAME                         CLASS    STAT_ID      VALUE

---------- ----------------------- ---------- ---------- ----------

       992 leaf node splits               128 1417124053          0

       993 leaf node 90-10 splits         128 1285138251          0

      1002 branch node splits             128  399027615          0

      1003 root node splits               128  708722985          0

 

4 rows selected.

 

▶ 데이터 50000건 삽입 확인

Sql> select count(*) from tt1;

 

  COUNT(*)

----------

     50000

 

테이블 생성 -> 데이터 삽입 -> 인덱스 생성시에는 스플릿이 발생되지 않습니다.

 

같은 쿼리를 순서만 바꿔서 진행하여 스플릿을 유발합니다.

 

 

▶ 테스트용 테이블 생성

Sql> create table t1 (name varchar2(10), nr number) pctfree 0;

 

▶ 테스트용 인덱스 생성

Sql> create index t1 on t1(nr);

 

▶ 데이터 삽입 전 스플릿 확인

Sql> select a.statistic#,a.name,a.class,a.stat_id,b.value

from v$statname a,v$mystat b

where a.statistic# = b.statistic#

and a.name like '%split%'

and b.sid = (select sid from v$session where username = 'SYSTEM');

 

STATISTIC# NAME                         CLASS    STAT_ID      VALUE

---------- ----------------------- ---------- ---------- ----------

       445 leaf node splits               128 1417124053          0

       446 leaf node 90-10 splits         128 1285138251          0

       447 branch node splits             128  399027615          0

       448 root node splits               128  708722985          0

       463 queue splits                   128 1168925905          0

 

▶ 인덱스 스플릿 확인을 위한 데이터 삽입

Sql> declare

i number;

begin

for i in 1..50000

loop

insert into t1 values ('XX',i);

end loop;

end;

/

 

▶ index key 저장영역의 확인을 위해 analyze…structure 분석 후 index_stats 실행

Sql> analyze index t1 validate structure;

Sql> select blocks,lf_blks,pct_used from index_stats;

BLOCKS    LF_BLKS   PCT_USED

---------- ---------- ----------

 112         99         99

 

Sql> select a.statistic#,a.name,a.class,a.stat_id,b.value

from v$statname a,v$mystat b

where a.statistic# = b.statistic#

and a.name like '%split%'

and b.sid = (select sid from v$session where username = 'SYSTEM');

 

           STATISTIC# NAME             CLASS    STAT_ID      VALUE

---------- ----------------------- ---------- ---------- ----------

       445 leaf node splits               128 1417124053         98

       446 leaf node 90-10 splits         128 1285138251         98

       447 branch node splits             128  399027615          0

       448 root node splits               128  708722985          0

       463 queue splits                   128 1168925905          0

 

 

▶ 데이터 50000건 삽입 확인

Sql> select count(*) from t1;

COUNT(*)

----------

     50000

 

테이블 생성 -> 인덱스 생성 -> 데이터 삽입 시(데이터가 순차적으로 증가하는 데이터) 90:10 split의 값이 증가하는 것을 확인 할 수 있습니다.

 

50:50 스플릿 유발

▶ 테스트용 테이블 생성

Sql> create table tt2(name varchar2(10), nr number) pctfree 0;

 

Table created.

▶ 데이터 삽입 전 스플릿 확인

Sql> select a.statistic#,a.name,a.class,a.stat_id,b.value

from v$statname a,v$mystat b

where a.statistic# = b.statistic#

and a.name like '%split%'

and a.name like '%node%'

and b.sid = (select sid from v$session where username = 'SYSTEM');

 

STATISTIC# NAME                          CLASS    STAT_ID      VALUE

---------- ------------------------ ---------- ---------- ----------

       992 leaf node splits                128 1417124053          0

       993 leaf node 90-10 splits          128 1285138251          0

      1002 branch node splits              128  399027615          0

      1003 root node splits                128  708722985          0

 

4 rows selected.

Elapsed: 00:00:00.02

 

▶ 인덱스 스플릿 확인을 위한 데이터 삽입

Sql> declare

i number;

begin

for i in 25001..50000

loop

insert into tt2 values ('XX',i);

end loop;

for i in 1..25000

loop

insert into tt2 values('XX',i);

end loop;

end;

/

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:04.07

▶ 테스트용 인덱스 생성

Sql> create index tt2 on tt2(nr);

 

Index created.

▶ index key 저장영역의 확인을 위해 analyze…structure 분석 후 index_stats 실행

Sql> analyze index tt2 validate structure;

Sql> select blocks,lf_blks,pct_used from index_stats;

 

Index analyzed.

 

Elapsed: 00:00:00.02

Sql>

    BLOCKS    LF_BLKS   PCT_USED

---------- ---------- ----------

       112        110         90

 

1 row selected.

 

Sql> select a.statistic#,a.name,a.class,a.stat_id,b.value

from v$statname a,v$mystat b

where a.statistic# = b.statistic#

and a.name like '%split%'

and a.name like '%node%'

and b.sid = (select sid from v$session where username = 'SYSTEM');

 

STATISTIC# NAME                         CLASS    STAT_ID      VALUE

---------- ----------------------- ---------- ---------- ----------

       992 leaf node splits               128 1417124053          0

       993 leaf node 90-10 splits         128 1285138251          0

      1002 branch node splits             128  399027615          0

      1003 root node splits               128  708722985          0

 

4 rows selected.

 

▶ 데이터 50000건 삽입 확인

Sql> select count(*) from tt2;

 

  COUNT(*)

----------

     50000

 

테이블 생성 -> 데이터 삽입 -> 인덱스 생성시에는 스플릿이 발생하지 않았습니다.

 

다시한번 순서만 바꾸어서 쿼리를 진행해보겠습니다.

 

▶ 테스트용 테이블 생성

Sql> create table t2 (name varchar2(10), nr number) pctfree 0;

 

▶ 테스트용 인덱스 생성

Sql> create index t2 on t1(nr);

 

▶ 데이터 삽입 전 스플릿 확인

Sql> select a.statistic#,a.name,a.class,a.stat_id,b.value

from v$statname a,v$mystat b

where a.statistic# = b.statistic#

and a.name like '%split%'

and b.sid = (select sid from v$session where username = 'SYSTEM');

 

STATISTIC# NAME                   CLASS    STAT_ID      VALUE

---------- ----------------------- ---------- ---------- ----------

       445 leaf node splits               128 1417124053          98

       446 leaf node 90-10 splits         128 1285138251          98

       447 branch node splits             128  399027615          0

       448 root node splits               128  708722985          0

       463 queue splits                   128 1168925905          0

 

 

▶ 인덱스 스플릿 확인을 위한 데이터 삽입

Sql> declare

i number;

begin

for i in 25001..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;

/

▶ index key 저장영역의 확인을 위해 analyze…structure 분석 후 index_stats 실행

Sql> analyze index t2 validate structure;

Sql> select blocks,lf_blks,pct_used from index_stats;

 

 

BLOCKS    LF_BLKS   PCT_USED

---------- ---------- ----------

 256        146         68

 

Sql> select a.statistic#,a.name,a.class,a.stat_id,b.value

from v$statname a,v$mystat b

where a.statistic# = b.statistic#

and a.name like '%split%'

and b.sid = (select sid from v$session where username = 'SYSTEM');

 

STATISTIC# NAME                         CLASS    STAT_ID      VALUE

---------- ----------------------- ---------- ---------- ----------

       445 leaf node splits               128 1417124053        242

       446 leaf node 90-10 splits         128 1285138251        147

       447 branch node splits             128  399027615          0

       448 root node splits               128  708722985          0

       463 queue splits                   128 1168925905          0

 

인덱스 스플릿이 일어난 블록의 개수

leaf node splits 242 – 98 = 144

leaf node 90-10 splits 147 – 98 = 49

 

- 50:50 인덱스 스플릿을 확인하였을 때 90:10 인덱스 스플릿보다 스플릿이 많이 일어났습니다.

 

▶ 데이터 50000건 삽입 확인

Sql> select count(*) from t2;

COUNT(*)

----------

     50000

 

- 50:50의 경우 25001~50000의 데이터를 우선 삽입하고 그 후 1~25000의 데이터를 삽입하였습니다. 뒤에 삽입된 index key가 최대값이 아니기 때문에 50:50으로 인덱스 스플릿이 되어 블록수가 90:10보다 많아지게 되었습니다.

 

결론

Index key 값이 최대값으로 들어가는 경우 90:10 인덱스 스플릿이 발생되고 최대값이 아닌 random 값이 들어가는 경우 50:50 인덱스 스플릿이 발생합니다.

99:10의 인덱스 스플릿을 하면 블록의 저장영역을 아낄 수 있습니다.