테스트를 통한 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';

• 첫 번째 리프 블록에는 3개의 키 값이 저장.
• 두 번째 리프 블록에는 2개의 키 값이 저장.
• 브랜치 블록에는 어떠한 값이 저장되 있을까?

@dba2fb
 INPUT DBA  : 18000a4

     FILE#     BLOCK#
---------- ----------
         6        164
         
alter system dump datafile 6 block 164;

• 브랜치 블록에는 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';

• 첫 번째 리프 블록에는 3개의 키 값이 저장.
• 두 번째 리프 블록에는 2개의 키 값이 저장.
• 브랜치 블록에는 어떠한 값이 저장되 있을까?

@dba2fb
 INPUT DBA  : 18000b4

     FILE#     BLOCK#
---------- ----------
         6        180
         
alter system dump datafile 6 block 180;

• 브랜치 블록에는 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';

• 첫 번째 리프 블록에는 3개의 키 값이 저장.
• 두 번째 리프 블록에는 2개의 키 값이 저장.
• 브랜치 블록에는 어떠한 값이 저장되 있을까?

@dba2fb
 INPUT DBA  : 18000c4

     FILE#     BLOCK#
---------- ----------
         6        196
         
alter system dump datafile 6 block 196;

• 브랜치 블록에는 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';

• 첫 번째 리프 블록에는 3개의 키 값이 저장.
• 두 번째 리프 블록에는 2개의 키 값이 저장.
• 브랜치 블록에는 어떠한 값이 저장되 있을까?

@dba2fb
 INPUT DBA  : 18000d4

     FILE#     BLOCK#
---------- ----------
         6        212
         
alter system dump datafile 6 block 212;

• 브랜치 블록에는 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';

• 브랜치 블록에는 어떠한 값이 저장되 있을까?

@dba2fb
 INPUT DBA  : 180020c

     FILE#     BLOCK#
---------- ----------
         6        524
         
alter system dump datafile 6 block 524;

• 브랜치 블록에는 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';

• 루트 블록에는 어떠한 값이 저장되 있을까?

@dba2fb
 INPUT DBA  : 1800224

     FILE#     BLOCK#
---------- ----------
         6        548
         
alter system dump datafile 6 block 548;

• 브랜치 블록에는 어떠한 값이 저장되 있을까?

@dba2fb
 INPUT DBA  : 180022a

     FILE#     BLOCK#
---------- ----------
         6        554
         
alter system dump datafile 6 block 554;

• 브랜치 블록에는 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;

@dba2fb
 INPUT DBA  : 1800229

     FILE#     BLOCK#
---------- ----------
         6        553
         
alter system dump datafile 6 block 553;

@dba2fb
 INPUT DBA  : 1800226

     FILE#     BLOCK#
---------- ----------
         6        550
         
alter system dump datafile 6 block 550;

@dba2fb
 INPUT DBA  : 1800227

     FILE#     BLOCK#
---------- ----------
         6        551
         
alter system dump datafile 6 block 551;

@dba2fb
 INPUT DBA  : 1800228

     FILE#     BLOCK#
---------- ----------
         6        552
         
alter system dump datafile 6 block 552;

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';

• 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;

• 브랜치 블록에 저장된 col0 키 값들은 각각 1,002bytes를 차지.

• 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';

• 위의 내용을 통해 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;

• 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';

• 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';

• 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';

• 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';

• 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';

• T3_I1 인덱스는 추가적인 2건의 입력으로 인해 리프 블록의 증가는 없으며, 1번, 3번째 리프 블록에 키 값이 1개씩 더 저장되었다.

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.
• 리빌드로 인해 인덱스 리프 블록의 수가 변경.

문서에 대하여

• 최초작성자 : 김종원
• 최초작성일 : 2011년 06월 17일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)엑셈에서 출간한 Transaction Internals in Oracle 10gR2'를 참고하였습니다.*