ORACLE 9i INDEX BLOCK
from DBMS INTERNALS VOL3 | EXEM
1. INDEX BLOCK
A.
흔히 사용되는 B-TREE 를 가지고 이야기 하고자 한다.
B.
인덱스의 구조와 확장을 간단히 살펴보겠다.
C.
인덱스 블록은 루트, 브랜치, 리프블록으로 이루어진다.
2. 예제 TABLE 생성
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_block_size integer 4096
(여기서 테이블,인덱스는 4K를
사용한다.)
SQL> create table t1
2 (num char(1200));
Table created.
(컬럼의 값으로 고정길이를 사용하였고 결과적으로 한 블록에 3개의 값이 들어가게
하였다.)
SQL> create index t1_num_idx on t1(num);
Index created.
(컬럼에 인덱스를 생성하였다.)
3. 인덱스 블록 구조변화
A.
값을 하나 입력했을 때(누적)
SQL> insert into t1 values('1100');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_id from
dba_objects
2 where object_name =
'T1_NUM_IDX';
OBJECT_ID
----------
6461
SQL> alter session set events 'immediate
trace name treedump level 6461';
Session altered.
<<udump의 trace file>>
----- begin tree dump
leaf: 0xc00154 12583252 (0: nrow: 1 rrow: 1)
----- end tree dump
괄호 ‘(‘ 옆에 숫자가 있다. 이 숫자가 0부터 시작하는 것은 루트블록을 의미한다.
B.
값을 네개 입력했을 때(누적)
(1200,1300,1400 을 입력후 commit , treedump)
----- begin tree dump
branch: 0xc00154 12583252 (0: nrow: 2,
level: 1)
leaf: 0xc0015d 12583261 (-1: nrow: 3 rrow:
3)
leaf: 0xc0015e 12583262 (0: nrow: 1 rrow: 1)
----- end tree dump
(leaf 노드는 -1 값부터 시작한다.)
각 블록에 대해 블록덤프를 실시하였다.
c00154
c0015d
c0015e
(아래의 스크립트는 DBMS INTERNALS 의 것을 사용하였다.)
SQL> @dba2fb
Enter value for dba: c00154
old
2: l_dba number :=
to_number('&dba','XXXXXXXX');
new
2: l_dba number :=
to_number('c00154','XXXXXXXX');
alter system dump datafile 3 block 340;
SQL> @dba2fb
Enter value for dba: c0015d
old
2: l_dba number :=
to_number('&dba','XXXXXXXX');
new
2: l_dba number :=
to_number('c0015d','XXXXXXXX');
alter system dump datafile 3 block 349;
SQL> @dba2fb
Enter value for dba: c0015e
old
2: l_dba number := to_number('&dba','XXXXXXXX');
new
2: l_dba number :=
to_number('c0015e','XXXXXXXX');
alter system dump datafile 3 block 350;
<<root 노드(c00154)>>
row#0[3952] dba: 12583262=0xc0015e
col 0; len 2; (2): 31 34
col 1; TERM
----- end of branch block dump -----
SQL> @hex2chr
Enter value for 1: 31
old
1: select chr(to_number('&1','XXXXXXXX')) from dual
new
1: select chr(to_number('31','XXXXXXXX')) from dual
C
-
1
1 row selected.
(아래의 스크립트는 DBMS INTERNALS 의 것을 사용하였다.)
SQL> @hex2chr
Enter value for 1: 34
old
1: select chr(to_number('&1','XXXXXXXX')) from dual
new
1: select chr(to_number('34','XXXXXXXX')) from dual
C
-
4
1 row selected.
(root 노드에 14란 값이 들어가있다.)
<<leaf 노드(c0015d)>>
row#0[303] flag: -----, lock: 0
col 0; len 1200; (1200):
31 31
30 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~중략~~
20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1; len 6; (6): 00 c0 01 4c 00 00
row#1[1514] flag: ----S, lock: 2
col 0; len 1200; (1200):
31 32
30 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~중략~~
20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1; len 6; (6): 00 c0 01 4c 00 01
row#2[2725] flag: ----S, lock: 2
col 0; len 1200; (1200):
31 33
30 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~중략~~
20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1; len 6; (6): 00 c0 01 4d 00 00
----- end of leaf block dump -----
(위 내용을 보면
세개의 row가 들어가있음을 알 수 있다.)
위에서의 hex2chr 로
30 31 32 33 에 대응하는 값은 0, 1, 2, 3이다.
row#0은 1100 (다섯자리부터의 20은 무시 – 빈값이다. )
row#1은 1200
row#2는 1300 이다.
<<leaf노드(c0015e)>>
row#0[2725] flag: -----, lock: 2
col 0; len 1200; (1200):
31 34
30 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~ 중략 ~~
20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1; len 6; (6): 00 c0 01 4d 00 01
----- end of leaf block dump -----
31 34 30 30 을 char 값으로 변환하면 1400 이다.
위에서 얻은 값을 가지고 그림을 그리면 다음과 같다.
C.
값을 일곱개 입력했을 때(누적)
b에서 실시했던 과정을 반복하였다. ( 1500,1600,1700 을 입력 )
----- begin tree dump
branch: 0xc00154 12583252 (0: nrow: 3, level: 1)
leaf: 0xc0015d
12583261 (-1: nrow: 3 rrow: 3)
leaf: 0xc0015e
12583262 (0: nrow: 3 rrow: 3)
leaf: 0xc0015f
12583263 (1: nrow: 1 rrow: 1)
----- end tree dump
leaf 블록이 하나 늘어났다.
각 블록에
대해 덤프를 떠봤다.
<<c00154>>
alter system dump datafile 3 block 340;
row#0[3952] dba: 12583262=0xc0015e
col 0; len 2; (2):
31 34
col 1; TERM
row#1[3944] dba: 12583263=0xc0015f
col 0; len 2; (2):
31 37
col 1; TERM
----- end of branch block dump -----
컬럼의
내용은 14, 17 이다.
<<c0015d>>
alter system dump datafile 3 block 349;
row#0[303] flag: -----, lock: 0
col 0; len 1200; (1200):
31 31 30 30 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~ 중략 ~~
20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1; len 6; (6):
00 c0 01 4c 00 00
row#1[1514] flag: ----S, lock: 2
col 0; len 1200; (1200):
31 32 30 30 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~ 중략 ~~
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20
col 1; len 6; (6):
00 c0 01 4c 00 01
row#2[2725] flag: ----S, lock: 2
col 0; len 1200; (1200):
31 33 30 30 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~ 중략 ~~
20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1; len 6; (6):
00 c0 01 4d 00 00
----- end of leaf block dump -----
각 row는 1100, 1200, 1300 이다.
<<c0015e>>
alter system dump datafile 3 block 350;
row#0[303] flag: -----, lock: 0
col 0; len 1200; (1200):
31 34 30 30 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~ 중략 ~~
20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1; len 6; (6):
00 c0 01 4d 00 01
row#1[1514] flag: ----S, lock: 2
col 0; len 1200; (1200):
31 35 30 30 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~ 중략 ~~
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20
col 1; len 6; (6):
00 c0 01 4e 00 00
row#2[2725] flag: ----S, lock: 2
col 0; len 1200; (1200):
31 36 30 30 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~ 중략 ~~
20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1; len 6; (6):
00 c0 01 4e 00 01
----- end of leaf block dump -----
각각의 row의 내용은 1400, 1500, 1600 이다.
<<c0015f>>
alter system dump datafile 3 block 351;
col 0; len 1200; (1200):
31 37 30 30 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20
~~ 중략 ~~
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20
col 1; len 6; (6):
00 c0 01 4f 00 00
----- end of leaf block dump -----
row의 내용은 1700 이다.
위 결과를
도식화 하면 다음과 같다.
D.
값을 열개 입력했을 때(누적)
b에서 실시했던 과정을 반복하였다.(1800, 1900, 2000 을 입력)
----- begin tree dump
branch: 0xc00154 12583252 (0: nrow: 4, level: 1)
leaf: 0xc0015d
12583261 (-1: nrow: 3 rrow: 3)
leaf: 0xc0015e
12583262 (0: nrow: 3 rrow: 3)
leaf: 0xc0015f
12583263 (1: nrow: 3 rrow: 3)
leaf: 0xc00160
12583264 (2: nrow: 1 rrow: 1)
----- end tree dump
leaf 노드가 하나더 추가되었다.
(블록덤프 확인하는 과정 생략하고
바로 들어가있는 로우값을 이야기 하겠다.)
<< c00154>>
alter system dump datafile 3 block 340;
14, 17, 2 값이 들어가있다.
<<c0015d>>
alter system dump datafile 3 block 349;
1100, 1200, 1300 값이
들어가있다.
<<c0015e>>
alter system dump datafile 3 block 350;
1400, 1500, 1600 값이
들어가있다.
<<c0015f>>
alter system dump datafile 3 block 351;
1700, 1800, 1900 값이
들어가있다.
<< c00160>>
alter system dump datafile 3 block 352;
2000 값이 들어가있다.
도식화하면
다음과 같다.
E.
값을 열세개 입력했을 때
b에서 실시했던 과정을 반복한다.
----- begin tree dump
branch: 0xc00154 12583252 (0: nrow: 5, level: 1)
leaf: 0xc0015d
12583261 (-1: nrow: 3 rrow: 3)
leaf: 0xc0015e
12583262 (0: nrow: 3 rrow: 3)
leaf: 0xc0015f
12583263 (1: nrow: 3 rrow: 3)
leaf: 0xc00160
12583264 (2: nrow: 3 rrow: 3)
leaf: 0xc00155
12583253 (3: nrow: 1 rrow: 1)
----- end tree dump
위의 결과를 보았을 때 루트블록이 브랜치로 분할되기 위해서는 해당 블록을 다 사용되야 한다는 걸 알
수 있다. 인덱스는 지금까지의 루트블록을 보면 알수 있듯이 최소한의 데이터로 값을 나누도록 사용된다. 위 도식도에서 1400 1700 2000 이 아닌 이유는 그러한
이유이다.
브랜치로 분화되는 걸 보기위해 대량의 데이터를 넣어보겠다.
현재 인덱스 블록이 4k 이고 헤더라던가 pctfree라던가를 고려해 4000byte 정도 저장이 가능하다고 가정하면 인덱스에 2000여개의 데이터는 들어가야 분화할 거라 생각한다.
SQL> begin
2 for i in 23..2000 loop
3 insert into t1
values(i*100);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
데이터를 넣고 treedump를 떠보았다.
(leaf block은 생략하였다)
----- begin tree dump
branch: 0xc00154 12583252 (0: nrow: 4,
level: 2)
branch: 0xc005d9 12584409 (-1: nrow: 160, level: 1)
branch: 0xc00799 12584857 (0: nrow: 160, level: 1)
branch: 0xc00966 12585318 (1: nrow: 201, level: 1)
branch: 0xc005dd 12584413 (2: nrow: 200, level: 1)
level 값이
올라간 것을 볼 수 있다. 루트노드와 브랜치노드 네개로 구성된 것을 확인할 수 있다. 첫번째 브랜치 로드를 덤프떠보겠다.
row#0[2412] dba: 12584217=0xc00519
col 0; len 4; (4): 31 30 30 32
col 1; TERM
row#1[2422] dba: 12584289=0xc00561
col 0; len 4; (4): 31 30 30 35
col 1; TERM
row#2[2432] dba: 12584293=0xc00565
col 0; len 4; (4): 31 30 30 38
col 1; TERM
1002, 1005, 1008 이란 값이 나왔다. 다시 treedump를
사용해 -1 ( 리프노드의 시작) 포지션값을 찾아보았다.
----- begin tree dump
branch: 0xc00154 12583252 (0: nrow: 4,
level: 2)
branch: 0xc005d9 12584409 (-1: nrow: 160, level: 1)
leaf: 0xc0015d 12583261 (-1: nrow: 3 rrow: 3)
<<c0015d>>
alter system dump datafile 3 block 349;
-1 포지션의
블록을 확인한 결과 10000, 100000, 100100 값이 있음을 확인하였다. 결과적으로 인덱스 리프블록의 값 비교는 자릿수에 관계없이 좌측의 실값부터 이루어진다.
F.
Object 주소 변화추이
<<height 1>>
leaf: 0xc00154 12583252 (0: nrow: 1 rrow: 1)
<<height 2>>
branch: 0xc00154 12583252 (0: nrow: 2,
level: 1)
leaf: 0xc0015d 12583261 (-1: nrow: 3 rrow:
3)
leaf: 0xc0015e 12583262 (0: nrow: 1 rrow: 1)
branch: 0xc00154 12583252 (0: nrow: 3,
level: 1)
leaf: 0xc0015d 12583261 (-1: nrow: 3 rrow: 3)
leaf: 0xc0015e 12583262 (0: nrow: 3 rrow: 3)
leaf: 0xc0015f 12583263 (1: nrow: 1 rrow: 1)
branch: 0xc00154 12583252 (0: nrow: 4,
level: 1)
leaf: 0xc0015d 12583261 (-1: nrow: 3 rrow: 3)
leaf: 0xc0015e 12583262 (0: nrow: 3 rrow: 3)
leaf: 0xc0015f 12583263 (1: nrow: 3 rrow: 3)
leaf: 0xc00160 12583264 (2: nrow: 1 rrow: 1)
branch: 0xc00154 12583252 (0: nrow: 5,
level: 1)
leaf: 0xc0015d 12583261 (-1: nrow: 3 rrow: 3)
leaf: 0xc0015e 12583262 (0: nrow: 3 rrow: 3)
leaf: 0xc0015f 12583263 (1: nrow: 3 rrow: 3)
leaf: 0xc00160 12583264 (2: nrow: 3 rrow: 3)
leaf: 0xc00155 12583253 (3: nrow: 1 rrow: 1)
<<height 3>>
branch: 0xc00154 12583252 (0: nrow: 4,
level: 2)
branch: 0xc005d9 12584409 (-1: nrow: 160, level: 1)
branch: 0xc00799 12584857 (0: nrow: 160, level: 1)
branch: 0xc00966 12585318 (1: nrow: 201, level: 1)
branch: 0xc005dd 12584413 (2: nrow: 200, level: 1)
위의 루트블록주소를 보면 변하지 않았음을 알 수 있다. HEIGHT3의 경우 BRANCH 블록은 새롭게 할당받은 블록으로 기존에 생성되었던 블록을 재사용하거나 하지 않는다. (상식적으로도 변할 부분만 변하는 것이 코스트가 낮다.)
G. Object 주소 변화추이
A.
부록
위에서 사용한 스크립트의 상세내용이다. ( 출처 : DBMS INTERNALS )
RH3@PROD : /home/oracle/tmp>$cat
dba2fb.sql
set feedback off
set serveroutput on
declare
l_dba number := to_number('&dba','XXXXXXXX');
l_file number := dbms_utility.data_block_address_file(l_dba);
l_block number :=
dbms_utility.data_block_address_block(l_dba);
begin
dbms_output.put_line('alter system dump datafile '||l_file||' block
'||l_block||';');
end;
/
set feedback on
RH3@PROD : /home/oracle/tmp>$cat
hex2chr.sql
select chr(to_number('&1','XXXXXXXX'))
from dual;
