by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

5.3 테스트를 통한 Index Block 분석




테스트를 통한 Index Block 분석

  • 인덱스는 DBMS에서 쿼리 성능을 향상시키기 위한 가장 중요한 요소.
  • DBMS의 여러 인덱스 유형 중에서 가장 대표적인 B*Tree 인덱스를 대상으로 테스트.
    • 인덱스의 브랜치(branch) 블록에는 어떤 값들이 저장될까?
    • 결합인덱스의 브랜치(branch) 블록에는 어떤 값들이 저장될까?
    • 인덱스의 PCTFREE 는 어떠한 의미를 가질까?
    • 인덱스를 리빌드(rebuild)하면 인덱스 클러스터링 팩터가 향상될까?

Case 1.인덱스 브랜치(branch) 블록에는 어떤 값들이 저장될까?

  • B*Tree 인덱스는 일반적으로 루트(root) 블록, 브랜치(branch) 블록, 리프(leaf) 블록으로 이루어 진다.
  • 리프 블록에는 인덱스 키 값과 ROWID를 저장.

1. 인덱스 컬럼에 모두 다른 글자를 입력한 경우


-- 테이블/인덱스 생성
drop table t1;
create table t1(id number, name char(2000), day date);
create index t1_i1 on t1(name);

-- 테스트 데이터 입력
insert into t1 values(1, 'ABC', sysdate);
insert into t1 values(2, 'DEF', sysdate);
insert into t1 values(3, 'GHI', sysdate);
insert into t1 values(4, 'JKL', sysdate);
insert into t1 values(5, 'MNO', sysdate);

commit;

-- 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T1_I1';
OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T1_I1                                                                                                                         52560

alter session set events 'immediate trace name treedump level 52560';
----- begin tree dump
branch: 0x18000a4 25165988 (0: nrow: 2, level: 1)
leaf: 0x18000a5 25165989 (-1: nrow: 3 rrow: 3)
leaf: 0x18000a6 25165990 (0: nrow: 2 rrow: 2)
end tree dump
  • 블록타입 (branch, leaf) : 인덱스를 구성하는 블록의 유형을 정의
  • 블록주소(0x18000a4, 0x18000a5, 0x18000a6) : 각 블록의 DBA를 나타냄
  • 포지션(0, -1, 0) : 루트 블록은 0으로 시작, 브랜치 블록과 리프 블록은 -1부터 시작
  • nrows : 해당 블록에 저장되었던 최대 인덱스 엔트리 수. 인덱스 키 값이 삭제되어도 줄어들지 않음.
  • rrows : 현재, 해당 블록에 저장된 인덱스 엔트리 수, 인덱스 키 값이 삭제되면 줄어듦.
  • level : 브랜치 블록 레벨 (리프 블록은 내부적으로 0으로 인식함)
  • 첫 번째 리프 블록에는 3개의 키 값이 저장.
  • 두 번째 리프 블록에는 2개의 키 값이 저장.
  • 브랜치 블록에는 어떠한 값이 저장되 있을까?
    @dba2fb
     INPUT DBA  : 18000a4
    
         FILE#     BLOCK#
    ---------- ----------
             6        164
             
    alter system dump datafile 6 block 164;
    
    Branch block dump
    =================
    header address 80774220=0x4d0844c
    kdxcolev 1
    KDXCOLEV Flags = - - -
    kdxcolok 1
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 1
    kdxconro 1
    kdxcofbo 30=0x1e
    kdxcofeo 8053=0x1f75
    kdxcoavs 8023
    kdxbrlmc 25165989=0x18000a5
    kdxbrsno 0
    kdxbrbksz 8060
    kdxbr2urrc 0
    row#0[8053] dba: 25165990=0x18000a6
    col 0; len 1; (1): 4a
    col 1; TERM
    end of branch block dump -----
  • 브랜치 블록에는 row#0이 존재하고 col 0에 '4a'가 저장
    @hex2chr
    1의 값을 입력하십시오: 4a
    
    CH
    --
    J
    

2. 인덱스 컬럼에 첫 글자가 같은 데이터 일 경우


-- 테이블/인덱스 생성
drop table t1;
create table t1(id number, name char(2000), day date);
create index t1_i1 on t1(name);

-- 테스트 데이터 입력
insert into t1 values(1, 'AAB', sysdate);
insert into t1 values(2, 'ACD', sysdate);
insert into t1 values(3, 'AEF', sysdate);
insert into t1 values(4, 'AGH', sysdate);
insert into t1 values(5, 'AIJ', sysdate);

commit;

-- 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T1_I1';
OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T1_I1                                                                                                                         52562

alter session set events 'immediate trace name treedump level 52562';
----- begin tree dump
branch: 0x18000b4 25166004 (0: nrow: 2, level: 1)
leaf: 0x18000b5 25166005 (-1: nrow: 3 rrow: 3)
leaf: 0x18000b6 25166006 (0: nrow: 2 rrow: 2)
end tree dump
  • 첫 번째 리프 블록에는 3개의 키 값이 저장.
  • 두 번째 리프 블록에는 2개의 키 값이 저장.
  • 브랜치 블록에는 어떠한 값이 저장되 있을까?
    @dba2fb
     INPUT DBA  : 18000b4
    
         FILE#     BLOCK#
    ---------- ----------
             6        180
             
    alter system dump datafile 6 block 180;
    
    Branch block dump
    =================
    header address 80774220=0x4d0844c
    kdxcolev 1
    KDXCOLEV Flags = - - -
    kdxcolok 1
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 1
    kdxconro 1
    kdxcofbo 30=0x1e
    kdxcofeo 8052=0x1f74
    kdxcoavs 8022
    kdxbrlmc 25166005=0x18000b5
    kdxbrsno 0
    kdxbrbksz 8060
    kdxbr2urrc 0
    row#0[8052] dba: 25166006=0x18000b6
    col 0; len 2; (2): 41 47
    col 1; TERM
    end of branch block dump -----
  • 브랜치 블록에는 row#0이 존재하고 col 0에 '41 47'가 저장
    @hex2chr
    1의 값을 입력하십시오: 41
    
    CH
    --
    A
    
    @hex2chr
    1의 값을 입력하십시오: 47
    
    CH
    --
    G
    

3. 인덱스 컬럼에 앞에 두글자는 같고 나머지가 틀린 경우


-- 테이블/인덱스 생성
drop table t1;
create table t1(id number, name char(2000), day date);
create index t1_i1 on t1(name);

-- 테스트 데이터 입력
insert into t1 values(1, 'AAA', sysdate);
insert into t1 values(2, 'AAB', sysdate);
insert into t1 values(3, 'AAC', sysdate);
insert into t1 values(4, 'AAD', sysdate);
insert into t1 values(5, 'AAE', sysdate);

commit;

-- 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T1_I1';
OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T1_I1                                                                                                                         52564

alter session set events 'immediate trace name treedump level 52564';
----- begin tree dump
branch: 0x18000c4 25166020 (0: nrow: 2, level: 1)
leaf: 0x18000c5 25166021 (-1: nrow: 3 rrow: 3)
leaf: 0x18000c6 25166022 (0: nrow: 2 rrow: 2)
end tree dump
  • 첫 번째 리프 블록에는 3개의 키 값이 저장.
  • 두 번째 리프 블록에는 2개의 키 값이 저장.
  • 브랜치 블록에는 어떠한 값이 저장되 있을까?
    @dba2fb
     INPUT DBA  : 18000c4
    
         FILE#     BLOCK#
    ---------- ----------
             6        196
             
    alter system dump datafile 6 block 196;
    
    Branch block dump
    =================
    header address 80774220=0x4d0844c
    kdxcolev 1
    KDXCOLEV Flags = - - -
    kdxcolok 1
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 1
    kdxconro 1
    kdxcofbo 30=0x1e
    kdxcofeo 8051=0x1f73
    kdxcoavs 8021
    kdxbrlmc 25166021=0x18000c5
    kdxbrsno 0
    kdxbrbksz 8060
    kdxbr2urrc 0
    row#0[8051] dba: 25166022=0x18000c6
    col 0; len 3; (3): 41 41 44
    col 1; TERM
    end of branch block dump -----
  • 브랜치 블록에는 row#0이 존재하고 col 0에 '41 41 44'가 저장
    @hex2chr
    1의 값을 입력하십시오: 41
    
    CH
    --
    A
    
    @hex2chr
    1의 값을 입력하십시오: 44
    
    CH
    --
    D
    

4. 인덱스 칼럼의 데이터가 모두 동일한 경우


-- 테이블/인덱스 생성
drop table t1;
create table t1(id number, name char(2000), day date);
create index t1_i1 on t1(name);

-- 테스트 데이터 입력
insert into t1 values(1, 'AAA', sysdate);
insert into t1 values(2, 'AAA', sysdate);
insert into t1 values(3, 'AAA', sysdate);
insert into t1 values(4, 'AAA', sysdate);
insert into t1 values(5, 'AAA', sysdate);

commit;

-- 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T1_I1';
OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T1_I1                                                                                                                         52566

alter session set events 'immediate trace name treedump level 52566';
----- begin tree dump
branch: 0x18000d4 25166036 (0: nrow: 2, level: 1)
leaf: 0x18000d5 25166037 (-1: nrow: 3 rrow: 3)
leaf: 0x18000d6 25166038 (0: nrow: 2 rrow: 2)
end tree dump
  • 첫 번째 리프 블록에는 3개의 키 값이 저장.
  • 두 번째 리프 블록에는 2개의 키 값이 저장.
  • 브랜치 블록에는 어떠한 값이 저장되 있을까?
    @dba2fb
     INPUT DBA  : 18000d4
    
         FILE#     BLOCK#
    ---------- ----------
             6        212
             
    alter system dump datafile 6 block 212;
    
    Branch block dump
    =================
    header address 80774220=0x4d0844c
    kdxcolev 1
    KDXCOLEV Flags = - - -
    kdxcolok 1
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 1
    kdxconro 1
    kdxcofbo 30=0x1e
    kdxcofeo 6049=0x17a1
    kdxcoavs 6019
    kdxbrlmc 25166037=0x18000d5
    kdxbrsno 0
    kdxbrbksz 8060
    kdxbr2urrc 0
    row#0[6049] dba: 25166038=0x18000d6
    col 0; len 2000; (2000):
    41 41 41 20 20 ...
    col 1; len 4; (4): 01 80 00 ce
    end of branch block dump -----
  • 브랜치 블록에는 row#0이 존재하고 col 0에 '41 41 41 20 ...'가 저장
  • 저장된 키 값의 길이는 2,000bytes.

추가1. 리프 블록이 두개 이상일 때의 브랜치 블록 저장??


-- 테이블/인덱스 생성
drop table t1;
create table t1(id number, name char(2000), day date);
create index t1_i1 on t1(name);

-- 테스트 데이터 입력
insert into t1 values(1, 'AAA', sysdate);
insert into t1 values(2, 'AAB', sysdate);
insert into t1 values(3, 'AAC', sysdate);
insert into t1 values(4, 'AAD', sysdate);
insert into t1 values(5, 'AAE', sysdate);
insert into t1 values(6, 'AAF', sysdate);
insert into t1 values(7, 'AAG', sysdate);
insert into t1 values(8, 'AAH', sysdate);
insert into t1 values(9, 'AAI', sysdate);
insert into t1 values(10, 'AAJ', sysdate);
insert into t1 values(11, 'AAK', sysdate);
insert into t1 values(12, 'AAL', sysdate);
insert into t1 values(13, 'AAM', sysdate);

commit;

-- 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T1_I1';
OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T1_I1                                                                                                                         52570

alter session set events 'immediate trace name treedump level 52570';
----- begin tree dump
branch: 0x180020c 25166348 (0: nrow: 5, level: 1)
leaf: 0x180020d 25166349 (-1: nrow: 3 rrow: 3)
leaf: 0x180020e 25166350 (0: nrow: 3 rrow: 3)
leaf: 0x180020f 25166351 (1: nrow: 3 rrow: 3)
leaf: 0x1800210 25166352 (2: nrow: 3 rrow: 3)
leaf: 0x1800211 25166353 (3: nrow: 1 rrow: 1)
end tree dump
  • 브랜치 블록에는 어떠한 값이 저장되 있을까?
    @dba2fb
     INPUT DBA  : 180020c
    
         FILE#     BLOCK#
    ---------- ----------
             6        524
             
    alter system dump datafile 6 block 524;
    
    Branch block dump
    =================
    header address 80774220=0x4d0844c
    kdxcolev 1
    KDXCOLEV Flags = - - -
    kdxcolok 1
    kdxcoopc 0x81: opcode=1: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 1
    kdxconro 4
    kdxcofbo 36=0x24
    kdxcofeo 8024=0x1f58
    kdxcoavs 7988
    kdxbrlmc 25166349=0x180020d
    kdxbrsno 3
    kdxbrbksz 8060
    kdxbr2urrc 0
    row#0[8051] dba: 25166350=0x180020e
    col 0; len 3; (3): 41 41 44
    col 1; TERM
    row#1[8042] dba: 25166351=0x180020f
    col 0; len 3; (3): 41 41 47
    col 1; TERM
    row#2[8033] dba: 25166352=0x1800210
    col 0; len 3; (3): 41 41 4a
    col 1; TERM
    row#3[8024] dba: 25166353=0x1800211
    col 0; len 3; (3): 41 41 4d
    col 1; TERM
    end of branch block dump -----
  • 브랜치 블록에는 row#0이 존재하고 col 0에 '41 41 44'가 저장
  • 브랜치 블록에는 row#1이 존재하고 col 0에 '41 41 47'가 저장
  • 브랜치 블록에는 row#2이 존재하고 col 0에 '41 41 4a'가 저장
  • 브랜치 블록에는 row#3이 존재하고 col 0에 '41 41 4d'가 저장
    @hex2chr
    1의 값을 입력하십시오: 41
    
    CH
    --
    A
    
    @hex2chr
    1의 값을 입력하십시오: 44
    
    CH
    --
    D
    
    @hex2chr
    1의 값을 입력하십시오: 47
    
    CH
    --
    G
    
    @hex2chr
    1의 값을 입력하십시오: 4a
    
    CH
    --
    J
    
    @hex2chr
    1의 값을 입력하십시오: 4d
    
    CH
    --
    M
    

추가1. 같은 값이 두개 이상의 리프 블록을 가지고 있을 경우 브랜치 블록 저장??


-- 테이블/인덱스 생성
drop table t1;
create table t1(id number, name char(2000), day date);
create index t1_i1 on t1(name);

-- 테스트 데이터 입력
insert into t1 values(1, 'AAA', sysdate);
insert into t1 values(2, 'AAA', sysdate);
insert into t1 values(3, 'AAA', sysdate);
insert into t1 values(4, 'AAA', sysdate);
insert into t1 values(5, 'AAA', sysdate);
insert into t1 values(6, 'AAA', sysdate);
insert into t1 values(7, 'AAA', sysdate);
insert into t1 values(8, 'AAA', sysdate);
insert into t1 values(9, 'AAA', sysdate);
insert into t1 values(10, 'AAA', sysdate);
insert into t1 values(11, 'AAA', sysdate);
insert into t1 values(12, 'AAA', sysdate);
insert into t1 values(13, 'AAA', sysdate);

commit;

-- 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T1_I1';
OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T1_I1                                                                                                                         52572

alter session set events 'immediate trace name treedump level 52572';
----- begin tree dump
branch: 0x1800224 25166372 (0: nrow: 2, level: 2)
branch: 0x180022a 25166378 (-1: nrow: 3, level: 1)
leaf: 0x1800225 25166373 (-1: nrow: 3 rrow: 3)
leaf: 0x1800229 25166377 (0: nrow: 1 rrow: 1)
leaf: 0x1800226 25166374 (1: nrow: 3 rrow: 3)
branch: 0x180022c 25166380 (0: nrow: 2, level: 1)
leaf: 0x1800227 25166375 (-1: nrow: 3 rrow: 3)
leaf: 0x1800228 25166376 (0: nrow: 3 rrow: 3)
end tree dump
  • 루트 블록에는 어떠한 값이 저장되 있을까?
    @dba2fb
     INPUT DBA  : 1800224
    
         FILE#     BLOCK#
    ---------- ----------
             6        548
             
    alter system dump datafile 6 block 548;
    
    Branch block dump
    =================
    header address 80774220=0x4d0844c
    kdxcolev 2
    KDXCOLEV Flags = - - -
    kdxcolok 1
    kdxcoopc 0x83: opcode=3: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 2
    kdxconro 1
    kdxcofbo 30=0x1e
    kdxcofeo 6049=0x17a1
    kdxcoavs 6019
    kdxbrlmc 25166378=0x180022a
    kdxbrsno 2
    kdxbrbksz 8060
    kdxbr2urrc 0
    row#0[6049] dba: 25166380=0x180022c
    col 0; len 2000; (2000):
    41 41 41 20 20 ...
    col 1; len 4; (4): 01 80 02 1f
    end of branch block dump -----
  • 브랜치 블록에는 어떠한 값이 저장되 있을까?
    @dba2fb
     INPUT DBA  : 180022a
    
         FILE#     BLOCK#
    ---------- ----------
             6        554
             
    alter system dump datafile 6 block 554;
    
    Branch block dump
    =================
    header address 80774220=0x4d0844c
    kdxcolev 1
    KDXCOLEV Flags = - - -
    kdxcolok 1
    kdxcoopc 0x87: opcode=7: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 1
    kdxconro 2
    kdxcofbo 32=0x20
    kdxcofeo 4036=0xfc4
    kdxcoavs 4004
    kdxbrlmc 25166373=0x1800225
    kdxbrsno 17
    kdxbrbksz 8060
    kdxbr2urrc 3
    row#0[4036] dba: 25166377=0x1800229
    col 0; len 2000; (2000):
    41 41 41 20 20 ...
    col 1; len 6; (6): 01 80 02 1d 00 02
    row#1[6049] dba: 25166374=0x1800226
    col 0; len 2000; (2000):
    41 41 41 20 20 ...
    col 1; len 4; (4): 01 80 02 1e
    end of branch block dump -----
  • 브랜치 블록에는 row#0이 존재하고 col 0에 '41 41 41 20 20 ..'가 저장
  • 브랜치 블록에는 row#1이 존재하고 col 0에 '41 41 41 20 20 ..'가 저장
  • 같은 값이라도 각각의 리프 블록의 주소를 가지고 있다.
  • 인덱스 블록 분석
    select rowid, substr(rowid, 1, 15), id from t1;
    
    ROWID              SUBSTR(ROWID,1,15)                     ID
    ------------------ ------------------------------ ----------
    AAAM1bAAGAAAAIcAAA AAAM1bAAGAAAAIc                        13
    AAAM1bAAGAAAAIdAAA AAAM1bAAGAAAAId                         1
    AAAM1bAAGAAAAIdAAB AAAM1bAAGAAAAId                         2
    AAAM1bAAGAAAAIdAAC AAAM1bAAGAAAAId                         3
    AAAM1bAAGAAAAIeAAA AAAM1bAAGAAAAIe                         4
    AAAM1bAAGAAAAIeAAB AAAM1bAAGAAAAIe                         5
    AAAM1bAAGAAAAIeAAC AAAM1bAAGAAAAIe                         6
    AAAM1bAAGAAAAIfAAA AAAM1bAAGAAAAIf                         7
    AAAM1bAAGAAAAIfAAB AAAM1bAAGAAAAIf                         8
    AAAM1bAAGAAAAIfAAC AAAM1bAAGAAAAIf                         9
    AAAM1bAAGAAAAIgAAA AAAM1bAAGAAAAIg                        10
    AAAM1bAAGAAAAIgAAB AAAM1bAAGAAAAIg                        11
    AAAM1bAAGAAAAIgAAC AAAM1bAAGAAAAIg                        12
    
    AAAM1b : 데이터 오브젝트 번호
    AAG : 상대 파일 번호 (각각의 데이터파일에 할당되는 번호)
    AAAAIg : 블록번호 (데이터 블록의 위치를 알려주는 번호)
    AAC : 블록내의 행 번호 (오라클 블록의 해더에 저장된 row directory slot의 위치를 알려주는 고유번호)
    
@dba2fb
 INPUT DBA  : 1800225

     FILE#     BLOCK#
---------- ----------
         6        549
         
alter system dump datafile 6 block 549;
Leaf block dump
===============
header address 80774244=0x4d08464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 2003=0x7d3
kdxcoavs 1961
kdxlespl 0
kdxlende 0
kdxlenxt 25166377=0x1800229
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[2003] flag: ------, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1c 00 00
row#1[4014] flag: ---S, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1d 00 00
row#2[6025] flag: ---S, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1d 00 01
end of leaf block dump -----
@dba2fb
 INPUT DBA  : 1800229

     FILE#     BLOCK#
---------- ----------
         6        553
         
alter system dump datafile 6 block 553;
Leaf block dump
===============
header address 80774244=0x4d08464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x87: opcode=7: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 6025=0x1789
kdxcoavs 5987
kdxlespl 0
kdxlende 0
kdxlenxt 25166374=0x1800226
kdxleprv 25166373=0x1800225
kdxledsz 0
kdxlebksz 8036
row#0[6025] flag: ---S, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1d 00 02
end of leaf block dump -----
@dba2fb
 INPUT DBA  : 1800226

     FILE#     BLOCK#
---------- ----------
         6        550
         
alter system dump datafile 6 block 550;
Leaf block dump
===============
header address 80774244=0x4d08464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x89: opcode=9: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 2003=0x7d3
kdxcoavs 1961
kdxlespl 0
kdxlende 0
kdxlenxt 25166375=0x1800227
kdxleprv 25166377=0x1800229
kdxledsz 0
kdxlebksz 8036
row#0[2003] flag: ---S, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1e 00 00
row#1[4014] flag: ---S, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1e 00 01
row#2[6025] flag: ---S, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1e 00 02
end of leaf block dump -----
@dba2fb
 INPUT DBA  : 1800227

     FILE#     BLOCK#
---------- ----------
         6        551
         
alter system dump datafile 6 block 551;
Leaf block dump
===============
header address 80774244=0x4d08464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 2003=0x7d3
kdxcoavs 1961
kdxlespl 0
kdxlende 0
kdxlenxt 25166376=0x1800228
kdxleprv 25166374=0x1800226
kdxledsz 0
kdxlebksz 8036
row#0[2003] flag: ---S, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1f 00 00
row#1[4014] flag: ---S, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1f 00 01
row#2[6025] flag: ---S, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 1f 00 02
end of leaf block dump -----
@dba2fb
 INPUT DBA  : 1800228

     FILE#     BLOCK#
---------- ----------
         6        552
         
alter system dump datafile 6 block 552;
Leaf block dump
===============
header address 80774244=0x4d08464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 2003=0x7d3
kdxcoavs 1961
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 25166375=0x1800227
kdxledsz 0
kdxlebksz 8036
row#0[6025] flag: ------, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 20 00 00
row#1[4014] flag: ------, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 20 00 01
row#2[2003] flag: ------, lock: 2, len=2011
col 0; len 2000; (2000):
41 41 41 20 20 ...
col 1; len 6; (6): 01 80 02 20 00 02
end of leaf block dump -----

Case 2. 결합인덱스의 브랜치 블록에는 어떤 값들이 저장될까?

  • 결합인덱스인 경우에는 브랜치 블록에 어떤 값들이 저장될까?
  • '분포도가 불량한 컬럼+분포도가 양호한 컬럼'으로 구성된 인덱스와 '분포도가 양호한 컬럼+분포도가 불량한 컬럼'으로 구성된 인덱스의 비교.
    
    -- 테이블/인덱스 생성
    drop table t2;
    create table t2 (col1 char(1000), col2 char(1000), day date);
    create index t2_i1 on t2(col1, col2);
    create index t2_i2 on t2(col2, col1);
    
    -- 테스트 값 입력
    insert into t2 values('AA', 'ABC', sysdate);
    insert into t2 values('AA', 'ADE', sysdate);
    insert into t2 values('AA', 'AFG', sysdate);
    insert into t2 values('AA', 'AHI', sysdate);
    insert into t2 values('AA', 'AJK', sysdate);
    insert into t2 values('AA', 'ALM', sysdate);
    insert into t2 values('AA', 'ANO', sysdate);
    insert into t2 values('AA', 'APQ', sysdate);
    insert into t2 values('AA', 'ARS', sysdate);
    insert into t2 values('AA', 'ATU', sysdate);
    insert into t2 values('AA', 'AVW', sysdate);
    insert into t2 values('AA', 'AXY', sysdate);
    
    commit;
    
    select object_name, object_id from dba_objects
    where object_name in ('T2_I1', 'T2_I2');
    OBJECT_NAME                                                                                                               OBJECT_ID
    -------------------------------------------------------------------------------------------------------------------------------- ----------
    T2_I1                                                                                                                         52580
    T2_I2                                                                                                                         52581
    
    alter session set events 'immediate trace name treedump level 52580';
    alter session set events 'immediate trace name treedump level 52581';
    
    ----- begin tree dump T2_I1 인덱스
    branch: 0x180026c 25166444 (0: nrow: 4, level: 1)
    leaf: 0x180026d 25166445 (-1: nrow: 3 rrow: 3)
    leaf: 0x180026e 25166446 (0: nrow: 3 rrow: 3)
    leaf: 0x180026f 25166447 (1: nrow: 3 rrow: 3)
    leaf: 0x1800270 25166448 (2: nrow: 3 rrow: 3)
    end tree dump
    ----- begin tree dump T2_I2 인덱스
    branch: 0x1800274 25166452 (0: nrow: 4, level: 1)
    leaf: 0x1800275 25166453 (-1: nrow: 3 rrow: 3)
    leaf: 0x1800276 25166454 (0: nrow: 3 rrow: 3)
    leaf: 0x1800277 25166455 (1: nrow: 3 rrow: 3)
    leaf: 0x1800278 25166456 (2: nrow: 3 rrow: 3)
    end tree dump
  • T2_I1, T2_I2 인덱스 모두 4개의 리프블록에, 각각 3개의 키 값을 저장하고 있음.
  • 브랜치 블록에는 어떠한 값이 저장되어 있을까?
    @dba2fb
     INPUT DBA  : 180026c
    
         FILE#     BLOCK#
    ---------- ----------
             6        620
    
    @dba2fb
     INPUT DBA  : 1800274
    
         FILE#     BLOCK#
    ---------- ----------
             6        628
    
    alter system dump datafile 6 block 620;
    alter system dump datafile 6 block 628;
    
    Branch block dump T2_I1 인덱스
    =================
    header address 80905292=0x4d2844c
    kdxcolev 1
    KDXCOLEV Flags = - - -
    kdxcolok 0
    kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
    kdxconco 3
    kdxcosdc 1
    kdxconro 3
    kdxcofbo 34=0x22
    kdxcofeo 5030=0x13a6
    kdxcoavs 4996
    kdxbrlmc 25166445=0x180026d
    kdxbrsno 2
    kdxbrbksz 8060
    kdxbr2urrc 0
    row#0[7050] dba: 25166446=0x180026e
    col 0; len 1000; (1000):
    41 41 20 20 20 ...
    col 1; len 2; (2): 41 48
    col 2; TERM
    row#1[6040] dba: 25166447=0x180026f
    col 0; len 1000; (1000):
    41 41 20 20 ...
    col 1; len 2; (2): 41 4e
    col 2; TERM
    row#2[5030] dba: 25166448=0x1800270
    col 0; len 1000; (1000):
    41 41 20 20 ...
    col 1; len 2; (2): 41 54
    col 2; TERM
    end of branch block dump -----
  • 브랜치 블록에 저장된 col0 키 값들은 각각 1,002bytes를 차지.
Branch block dump T2_I2 인덱스
=================
header address 80905292=0x4d2844c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 1
kdxconro 3
kdxcofbo 34=0x22
kdxcofeo 8036=0x1f64
kdxcoavs 8002
kdxbrlmc 25166453=0x1800275
kdxbrsno 2
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8052] dba: 25166454=0x1800276
col 0; len 2; (2): 41 48
col 1; TERM
row#1[8044] dba: 25166455=0x1800277
col 0; len 2; (2): 41 4e
col 1; TERM
row#2[8036] dba: 25166456=0x1800278
col 0; len 2; (2): 41 54
col 1; TERM
end of branch block dump -----
  • p. 201 그림 참고

-- 신규 레코드 13건 입력
insert into t2 values('AA', 'BBC', sysdate);
insert into t2 values('AA', 'BDE', sysdate);
insert into t2 values('AA', 'BFG', sysdate);
insert into t2 values('AA', 'BHI', sysdate);
insert into t2 values('AA', 'BJK', sysdate);
insert into t2 values('AA', 'BLM', sysdate);
insert into t2 values('AA', 'BNO', sysdate);
insert into t2 values('AA', 'BPQ', sysdate);
insert into t2 values('AA', 'BRS', sysdate);
insert into t2 values('AA', 'BTU', sysdate);
insert into t2 values('AA', 'BVW', sysdate);
insert into t2 values('AA', 'BXY', sysdate);
insert into t2 values('AA', 'CBC', sysdate);

commit;

alter session set events 'immediate trace name treedump level 52580';
alter session set events 'immediate trace name treedump level 52581';
----- begin tree dump T2_I1 인덱스
branch: 0x180026c 25166444 (0: nrow: 2, level: 2)
branch: 0x180027f 25166463 (-1: nrow: 8, level: 1)
leaf: 0x180026d 25166445 (-1: nrow: 3 rrow: 3)
leaf: 0x180026e 25166446 (0: nrow: 3 rrow: 3)
leaf: 0x180026f 25166447 (1: nrow: 3 rrow: 3)
leaf: 0x1800270 25166448 (2: nrow: 3 rrow: 3)
leaf: 0x1800279 25166457 (3: nrow: 3 rrow: 3)
leaf: 0x180027a 25166458 (4: nrow: 3 rrow: 3)
leaf: 0x180027c 25166460 (5: nrow: 3 rrow: 3)
leaf: 0x180027d 25166461 (6: nrow: 3 rrow: 3)
branch: 0x1800280 25166464 (0: nrow: 1, level: 1)
leaf: 0x180027e 25166462 (-1: nrow: 1 rrow: 1)
end tree dump
----- begin tree dump T2_I2 인덱스
branch: 0x1800274 25166452 (0: nrow: 9, level: 1)
leaf: 0x1800275 25166453 (-1: nrow: 3 rrow: 3)
leaf: 0x1800276 25166454 (0: nrow: 3 rrow: 3)
leaf: 0x1800277 25166455 (1: nrow: 3 rrow: 3)
leaf: 0x1800278 25166456 (2: nrow: 3 rrow: 3)
leaf: 0x1800281 25166465 (3: nrow: 3 rrow: 3)
leaf: 0x1800282 25166466 (4: nrow: 3 rrow: 3)
leaf: 0x1800284 25166468 (5: nrow: 3 rrow: 3)
leaf: 0x1800285 25166469 (6: nrow: 3 rrow: 3)
leaf: 0x1800286 25166470 (7: nrow: 1 rrow: 1)
end tree dump
  • 위의 내용을 통해 T2_I1 인덱스 Blevel이 2로 증가.
* 통계 정보를 생성해서 확인

exec dbms_stats.gather_table_stats(ownname=>'KJWON', tabname=>'T2', cascade=>TRUE);

select index_name, blevel from dba_indexes
where index_name in ('T2_I1', 'T2_I2');

INDEX_NAME                         BLEVEL
------------------------------ ----------
T2_I1                                   2
T2_I2                                   1

-- 브랜치 블록 덤프
@dba2fb -- T2_I1 인덱스 브랜치 블록 #1
 INPUT DBA  : 180027f

     FILE#     BLOCK#
---------- ----------
         6        639
         
@dba2fb -- T2_I1 인덱스 브랜치 블록 #2
 INPUT DBA  : 1800280

     FILE#     BLOCK#
---------- ----------
         6        640

@dba2fb -- T2_I1 인덱스 루트 블록 
 INPUT DBA  : 180026c

     FILE#     BLOCK#
---------- ----------
         6        620
         
@dba2fb -- T2_I2 인덱스 브랜치 블록 
 INPUT DBA  : 1800274

     FILE#     BLOCK#
---------- ----------
         6        628

alter system dump datafile 6 block 639;
alter system dump datafile 6 block 640;
alter system dump datafile 6 block 620;
alter system dump datafile 6 block 628;
Branch block dump – T2_I1 인덱스 브랜치 블록 #1
=================
header address 80905292=0x4d2844c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 1
kdxconro 7
kdxcofbo 42=0x2a
kdxcofeo 991=0x3df
kdxcoavs 949
kdxbrlmc 25166445=0x180026d
kdxbrsno 4490
kdxbrbksz 8060
kdxbr2urrc 13
row#0[991] dba: 25166446=0x180026e
col 0; len 1000; (1000):
41 41 20 20 ...
col 1; len 2; (2): 41 48
col 2; TERM
row#1[2001] dba: 25166447=0x180026f
col 0; len 1000; (1000):
41 41 20 20 ...
col 1; len 2; (2): 41 4e
col 2; TERM
row#2[3011] dba: 25166448=0x1800270
col 0; len 1000; (1000):
41 41 20 20 ...
col 1; len 2; (2): 41 54
col 2; TERM
row#3[4021] dba: 25166457=0x1800279
col 0; len 1000; (1000):
41 41 20 20 ...
col 1; len 1; (1): 42
col 2; TERM
row#4[5030] dba: 25166458=0x180027a
col 0; len 1000; (1000):
41 41 20 20 ...
col 1; len 2; (2): 42 48
col 2; TERM
row#5[6040] dba: 25166460=0x180027c
col 0; len 1000; (1000):
41 41 20 20 ...
col 1; len 2; (2): 42 4e
col 2; TERM
row#6[7050] dba: 25166461=0x180027d
col 0; len 1000; (1000):
41 41 20 20 ...
col 1; len 2; (2): 42 54
col 2; TERM
end of branch block dump -----
Branch block dump – T2_I1 인덱스 브랜치 블록 #2
=================
header address 80905292=0x4d2844c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x87: opcode=7: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 1
kdxconro 0
kdxcofbo 28=0x1c
kdxcofeo 8060=0x1f7c
kdxcoavs 8032
kdxbrlmc 25166462=0x180027e
kdxbrsno 6610
kdxbrbksz 8060
kdxbr2urrc 6
end of branch block dump -----
Branch block dump – T2_I1 인덱스 루트 블록
=================
header address 80905292=0x4d2844c
kdxcolev 2
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x83: opcode=3: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 2
kdxconro 1
kdxcofbo 30=0x1e
kdxcofeo 7051=0x1b8b
kdxcoavs 7021
kdxbrlmc 25166463=0x180027f
kdxbrsno 6
kdxbrbksz 8060
kdxbr2urrc 0
row#0[7051] dba: 25166464=0x1800280
col 0; len 1000; (1000):
41 41 20 20 ...
col 1; len 1; (1): 43
col 2; TERM
end of branch block dump -----
Branch block dump – T2_I2 인덱스 브랜치 블록
=================
header address 80905292=0x4d2844c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x81: opcode=1: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 1
kdxconro 8
kdxcofbo 44=0x2c
kdxcofeo 7998=0x1f3e
kdxcoavs 7954
kdxbrlmc 25166453=0x1800275
kdxbrsno 7
kdxbrbksz 8060
kdxbr2urrc 0
row#0[8052] dba: 25166454=0x1800276
col 0; len 2; (2): 41 48
col 1; TERM
row#1[8044] dba: 25166455=0x1800277
col 0; len 2; (2): 41 4e
col 1; TERM
row#2[8036] dba: 25166456=0x1800278
col 0; len 2; (2): 41 54
col 1; TERM
row#3[8029] dba: 25166465=0x1800281
col 0; len 1; (1): 42
col 1; TERM
row#4[8021] dba: 25166466=0x1800282
col 0; len 2; (2): 42 48
col 1; TERM
row#5[8013] dba: 25166468=0x1800284
col 0; len 2; (2): 42 4e
col 1; TERM
row#6[8005] dba: 25166469=0x1800285
col 0; len 2; (2): 42 54
col 1; TERM
row#7[7998] dba: 25166470=0x1800286
col 0; len 1; (1): 43
col 1; TERM
end of branch block dump -----
  • p.206, 208 그림 참고
  • 결합 인덱스의 컬럼 구성 순서와 브랜치 블록의 저장 효율성과는 밀접한 관계를 가짐을 알 수 있다.
  • 이러한 점을 고려하여 결합 인덱스의 칼럼 구성 순서를 선정할 필요가 있다.
  • 물론, 이에 앞서 조건절의 조건 입력 유형에 대한 분석이 선행 되어야 한다.

Case 3. 인덱스 PCTFREE는 어떠한 의미를 가질까?

  • 테이블의 PCTFREE의 용도 : 레코드의 길이가 길어지는 UPDATE 작업에 의한 예약 공간으로 사용.
  • UPDATE가 빈번한 테이블의 경우에는 PCTFREE의 수치를 상향 설정하여 UPDATE에 의한 로우 마이그레이션을 최소화하는 것이 일반적이 가이드.
  • 인덱스의 PCTFREE는 어떤 의미??
  • B*Tree 인덱스 구조에서는 인덱스에 대한 UPDATE가 발생하지 않는다.
  • 키 값에 대한 변경을 DELETE & INSERT 방식으로 처리.

1. 테이블 생성 -> 인덱스생성(PCTFREE 기본설정 값) -> 데이터 입력 순으로 테스트

1) 테이블 생성
drop table t3;
create table t3(id number, name char(2000), day date);

2) PCTFREE를 기본설정 값으로 하여 인덱스 생성
create index t3_i1 on t3(name)
pctfree 10;

3) 테스트 값 입력
insert into t3 values(1, 'A', sysdate);
insert into t3 values(2, 'C', sysdate);
insert into t3 values(3, 'D', sysdate);
insert into t3 values(4, 'E', sysdate);
insert into t3 values(5, 'F', sysdate);
insert into t3 values(6, 'G', sysdate);

commit;

4) 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T3_I1';

OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T3_I1                                                                                                                         52583

alter session set events 'immediate trace name treedump level 52583';
----- begin tree dump
branch: 0x180029c 25166492 (0: nrow: 2, level: 1)
leaf: 0x180029d 25166493 (-1: nrow: 3 rrow: 3)
leaf: 0x180029e 25166494 (0: nrow: 3 rrow: 3)
end tree dump
  • T3_I1 인덱스에는 2개의 리프 블록, 각각 3개의 키값을 저장.

2. 테이블 생성 -> 인덱스생성(PCTFREE 40) -> 데이터 입력 순으로 테스트

1) 테이블 생성
drop table t3;
create table t3(id number, name char(2000), day date);

2) PCTFREE를 40으로 하여 인덱스 생성
create index t3_i1 on t3(name)
pctfree 40;

3) 테스트 값 입력
insert into t3 values(1, 'A', sysdate);
insert into t3 values(2, 'C', sysdate);
insert into t3 values(3, 'D', sysdate);
insert into t3 values(4, 'E', sysdate);
insert into t3 values(5, 'F', sysdate);
insert into t3 values(6, 'G', sysdate);

commit;

4) 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T3_I1';

OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T3_I1                                                                                                                         52585

alter session set events 'immediate trace name treedump level 52585';
----- begin tree dump
branch: 0x18002ac 25166508 (0: nrow: 2, level: 1)
leaf: 0x18002ad 25166509 (-1: nrow: 3 rrow: 3)
leaf: 0x18002ae 25166510 (0: nrow: 3 rrow: 3)
end tree dump
  • Case #1(PCTFREE 10) 과 동일

3. 테이블 생성 -> 데이터 입력 -> 인덱스생성(PCTFREE 40) 순으로 테스트

1) 테이블 생성
drop table t3;
create table t3(id number, name char(2000), day date);

2) 테스트 값 입력
insert into t3 values(1, 'A', sysdate);
insert into t3 values(2, 'C', sysdate);
insert into t3 values(3, 'D', sysdate);
insert into t3 values(4, 'E', sysdate);
insert into t3 values(5, 'F', sysdate);
insert into t3 values(6, 'G', sysdate);

commit;

3) PCTFREE를 40으로 하여 인덱스 생성
create index t3_i1 on t3(name)
pctfree 40;

4) 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T3_I1';

OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T3_I1                                                                                                                         52587

alter session set events 'immediate trace name treedump level 52587';
----- begin tree dump
branch: 0x18002bc 25166524 (0: nrow: 3, level: 1)
leaf: 0x18002bd 25166525 (-1: nrow: 2 rrow: 2)
leaf: 0x18002be 25166526 (0: nrow: 2 rrow: 2)
leaf: 0x18002bf 25166527 (1: nrow: 2 rrow: 2)
end tree dump
  • T3_I1 인덱스에는 3개의 리프 블록, 각각 2개의 키값을 저장.
  • 인덱스의 생성시점과 PCTFREE의 연관성이 있을 것이라 추측 할 수 있다.

4. 테이블 생성 -> 인덱스생성(PCTFREE 40) -> 데이터 입력 후 중간 값 입력 순으로 테스트

1) 테이블 생성
drop table t3;
create table t3(id number, name char(2000), day date);

2) PCTFREE를 40으로 하여 인덱스 생성
create index t3_i1 on t3(name)
pctfree 40;

3) 테스트 값 입력
insert into t3 values(1, 'A', sysdate);
insert into t3 values(2, 'C', sysdate);
insert into t3 values(3, 'D', sysdate);
insert into t3 values(4, 'E', sysdate);
insert into t3 values(5, 'F', sysdate);
insert into t3 values(6, 'H', sysdate);

commit;

4) 중간값 입력
insert into t3 values(7, 'B', sysdate);
insert into t3 values(8, 'G', sysdate);

commit;

5) 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T3_I1';

OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T3_I1                                                                                                                         52597

alter session set events 'immediate trace name treedump level 52597';
----- begin tree dump
branch: 0x18002cc 25166540 (0: nrow: 4, level: 1)
leaf: 0x18002ce 25166542 (-1: nrow: 3 rrow: 3)
leaf: 0x18002d0 25166544 (0: nrow: 1 rrow: 1)
leaf: 0x18002cf 25166543 (1: nrow: 3 rrow: 3)
leaf: 0x18002cd 25166541 (2: nrow: 1 rrow: 1)
end tree dump
  • T3_I1 인덱스는 추가적인 2건의 입력으로 인해, 2개의 리프 블록이 4개로 증가,
  • 내부적으로 인덱스 스플릿(index split)이 발생.

5. 테이블 생성 -> 데이터 입력 -> 인덱스생성(PCTFREE 40) 후 중간 값 입력 순으로 테스트

1) 테이블 생성
drop table t3;
create table t3(id number, name char(2000), day date);

2) 테스트 값 입력
insert into t3 values(1, 'A', sysdate);
insert into t3 values(2, 'C', sysdate);
insert into t3 values(3, 'D', sysdate);
insert into t3 values(4, 'E', sysdate);
insert into t3 values(5, 'F', sysdate);
insert into t3 values(6, 'H', sysdate);

commit;

3) PCTFREE를 40으로 하여 인덱스 생성
create index t3_i1 on t3(name)
pctfree 40;

4) 중간값 입력
insert into t3 values(7, 'B', sysdate);
insert into t3 values(8, 'G', sysdate);

commit;

5) 인덱스 트리 덤프 수행
select object_name, object_id from dba_objects
where object_name = 'T3_I1';

OBJECT_NAME                                                                                                               OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T3_I1                                                                                                                         52599

alter session set events 'immediate trace name treedump level 52599';
----- begin tree dump
branch: 0x18002dc 25166556 (0: nrow: 3, level: 1)
leaf: 0x18002dd 25166557 (-1: nrow: 3 rrow: 3)
leaf: 0x18002de 25166558 (0: nrow: 2 rrow: 2)
leaf: 0x18002df 25166559 (1: nrow: 3 rrow: 3)
end tree dump
  • T3_I1 인덱스는 추가적인 2건의 입력으로 인해 리프 블록의 증가는 없으며, 1번, 3번째 리프 블록에 키 값이 1개씩 더 저장되었다.
  • 인덱스 PCTFREE는 인덱스 생성 시점에만 의미가 있다.
  • 인덱스 생성시점에 리프 블록마다 PCTFREE만클의 공간을 남겨두어, 향후 입력되는 레코드에 대한
    키 값의 저장을 목적으로 한다. (인덱스 스프릿 발생을 지연시키는 효과)
  • UPDATE가 발생하지 않는 테이블에 속한 인덱스인 경우에도, 인덱스 생성 시에 PCTFREE를 아주 작게,
    혹은 0으로 설정하는 것은 리프 블록의 스플릿 현상을 과다하게 발생시키는 원인.
  • 인덱스 PCTFREE는 리프 블록에만 적용되며, 브랜치 블록에는 적용되지 않는다.

Case 4. 인덱스를 리빌드하면 인덱스 클러스터링 팩터가 향상될까?

  • 인덱스 클러스터링 팩터를 향샹시키기 위해, 인덱스를 리빌드하는 경우가 종종 있다.
  • 과연, 인덱스 리빌드를 통해 인덱스 클러스터링 팩터를 향상시킬 수 있는 것일까??
  • 인덱스 클러스터링 팩터 : 인덱스의 키 값의 정렬 순서와 해당 레코드가 테이블에 저장된 순서와 불일치 정도를 수치화한 것.
  • 인덱스 클러스터링 팩터의 수치는 가장 일치가 잘 되었을 때 "테이블의 블록 수"와 동일하며,
    가장 불일치되어었을 때 "테이블의 로우 수"와 동일.
1) 테이블/인덱스 생성
-- 데이터 블록 및 인덱스 블록 1개에 10건의 레코드 저장
drop table t4;
create table t4(name char(700), day date);

2) 테스트 값 입력
-- 100건 입력, 클러스터링 팩터를 가장 높게 나타나도록 입력합
begin
    for i in 1..10 loop
        for j in 0..9 loop
            insert into t4 values(lpad(i+(j*10), 3, '0'), sysdate);
        end loop;
    end loop;
    commit;
end;
/

3) 인덱스 생성
create index t4_i1 on t4(name);

4) 통계 정보 생성 후 통계정보 확인
exec dbms_stats.gather_table_stats(ownname=>'KJWON', tabname=>'T4', cascade=>TRUE);

select a.table_name, 
          b.index_name,
          a.num_rows,
          a.blocks "TAB_BLK",
          b.leaf_blocks "INID_LEAF_BLK",
          b.clustering_factor "IND_CF"
from  dba_tables a, dba_indexes b
where a.table_name = 'T4'
and     a.table_name = b.table_name;

TABLE_NAME                     INDEX_NAME                       NUM_ROWS    TAB_BLK INID_LEAF_BLK     IND_CF
------------------------------ ------------------------------ ---------- ---------- ------------- ----------
T4                             T4_I1                                 100         13            10     100
  • T4_I1 인덱스의 클러스터링 팩터는 테이블의 로우 수와 동일. (불일치 정도가 가장 높다.)
  • 인덱스 클러스터링 팩터를 계산하는 방법은 인덱스 FULL SCAN을 수행하면서, 키 값에 해당되는
    ROWID의 1~15번째 값이 변경될 때 마다, 인덱스 클러스터링 팩터가 1씩 증가.
5) 데이터 삭제 후 통계 정보 재생성 후 확인
begin
    for i in 1..5 loop
        delete t4 where name like '__' || i || '%';
    end loop;
    commit;
end;
/

exec dbms_stats.gather_table_stats(ownname=>'KJWON', tabname=>'T4', cascade=>TRUE);

select a.table_name, 
          b.index_name,
          a.num_rows,
          a.blocks "TAB_BLK",
          b.leaf_blocks "INID_LEAF_BLK",
          b.clustering_factor "IND_CF"
from  dba_tables a, dba_indexes b
where a.table_name = 'T4'
and     a.table_name = b.table_name;

TABLE_NAME                     INDEX_NAME                       NUM_ROWS    TAB_BLK INID_LEAF_BLK     IND_CF
------------------------------ ------------------------------ ---------- ---------- ------------- ----------
T4                             T4_I1                                  50         13            10      50
  • 인덱스를 리빌드하면 인덱스 클러스터링 팩터가 향상될 것인가??
alter index t4_i1 rebuild;

exec dbms_stats.gather_table_stats(ownname=>'KJWON', tabname=>'T4', cascade=>TRUE);

select a.table_name, 
          b.index_name,
          a.num_rows,
          a.blocks "TAB_BLK",
          b.leaf_blocks "INID_LEAF_BLK",
          b.clustering_factor "IND_CF"
from  dba_tables a, dba_indexes b
where a.table_name = 'T4'
and     a.table_name = b.table_name;

TABLE_NAME                     INDEX_NAME                       NUM_ROWS    TAB_BLK INID_LEAF_BLK     IND_CF
------------------------------ ------------------------------ ---------- ---------- ------------- ----------
T4                             T4_I1                                  50         13             5      50
  • 여전히 인덱스 클러스터링 팩터는 50.
  • 리빌드로 인해 인덱스 리프 블록의 수가 변경.
    • 인덱스 클러스터링 팩터를 향상시키기 위한 방법은, 해당 인덱스의 키 칼럼의 순서로 테이블을 정렬하여 재생성하는 방법뿐이다.

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.