Index Coalesce VS. Shrink

delete from t1 where c1 = 'GRACE';

이와 같은 방식으로 random으로 많은 양의 데이터가 삭제될 경우,
인덱스의 leaf block이 드문드문하게 채워져 있게 된다. (Index Fragmentaion)

물론, 장기적으로는 삭제된 공간은 재활용 될 수 있지만
인덱스는 sorting 되어야 한다는 특징에 의하여
Key 값이 들어갈 수 있는 자리가 정해져 있으므로 삭제된 공간을 재활용하지 못할 수 있다.

[Oracle Virus] - 왜 Index가 불필요하게 커지는가? 를 참조하면, 위 내용을 이해할 수 있다.

이 경우,
INDEX OFFLINE REBUILD
INDEX ONLINE REBUILD
INDEX SHRINK
INDEX COALESCE
를 통해 fragmentation 된 leaf 블록들을 정리한다.

이중, coalesce와 shrink는 Oracle 10g부터 동일한 방법(delete + insert)으로 수행되고,
Leaf block 간의 key 값을 compact 하는 것이므로 index의 높이에는 변화가 없다.

단, coalesce는 compact를 통해 확보된 index의 Free block들을 반환하지 않으므로,
Index의 fragmentation을 정리하지만 Index의 크기는 변하지 않는다. 

반면, Shrink는 compact를 통해 확보된 index의 Free Block들을 반환하고 HWM를 조정하므로
Index의 Fragmentation을 정리함과 동시에 Index의 크기를 줄인다.

# Coalesce TEST 

-- Create Table
drop table t1 purge;

create table t1(c1 number, c2 varchar(10));

insert into t1
select level, 'GRACE'
from dual
connect by level <= 10000;

commit;

-- Create index on C1 column
create index t1_idx on t1(c1);

-- See Index Structure
analyze index t1_idx validate structure;

select height, blocks, lf_blks, br_blks, btree_space, pct_used
from index_stats;

-- t1_idx 는 총 32블록으로 이루어져 있으며, 21개 leaf block에
-- 176,032Byte의 공간을 사용하고 있다.

HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------
         2         32         21          1      176032         86
 
-- Delete 25% of rows
 
delete t1 WHERE mod(c1,4) = 1;
 
commit;
 
-- View before and after value of redo size
set serveroutput on
 
exec print_table('-
select n.name, s.value -
from v$mystat s, v$statname n -
where s.statistic# = n.statistic# -
and n.name = ''redo size''');
 
NAME                          : redo size
VALUE                         : 6196312
 
-- Coalesce the index
 
alter index t1_idx coalesce;
 
-- View before and after value of redo size
exec print_table('-
select n.name, s.value -
from v$mystat s, v$statname n -
where s.statistic# = n.statistic# -
and n.name = ''redo size''');
 
NAME                          : redo size
VALUE                         : 6543908

--Coalesce 를 통해 347,596Bytes의 Redo가 발생하였다.
 
-- See Index Structure
analyze index t1_idx validate structure;
 
select height, blocks, lf_blks, br_blks, btree_space, pct_used
from index_stats;
 
-- coalesce 후에도 t1_idx 는 총 32블록으로 이루어짐은 변화가 없고
-- leaf block의 수가 21-> 16개로 변화하였다.
-- 136,032 Byte의 공간을 사용하고 있다.
 
HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------
         2         32         16          1      136032         83

# Shrink TEST 

-- Create Table
drop table t1 purge;
 
create table t1(c1 number, c2 varchar(10));
 
insert into t1
select level, 'GRACE'
from dual
connect by level <= 10000;
 
commit;
 
-- Create index on C1 column
create index t1_idx on t1(c1);
 
-- See Index Structure
analyze index t1_idx validate structure;
 
select height, blocks, lf_blks, br_blks, btree_space, pct_used
from index_stats;
 
-- t1_idx 는 총 32블록으로 이루어져 있으며, 21개 leaf block에
-- 176,032Byte의 공간을 사용하고 있다.

HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------
         2         32         21          1      176032         86
 
-- Delete 25% of rows
 
delete t1 WHERE mod(c1,4) = 1;
 
commit;
 
-- View before and after value of redo size
set serveroutput on
 
exec print_table('-
select n.name, s.value -
from v$mystat s, v$statname n -
where s.statistic# = n.statistic# -
and n.name = ''redo size''');
 
NAME                          : redo size
VALUE                         : 9655888
 
alter index t1_idx shrink space compact;
 
-- View before and after value of redo size
exec print_table('-
select n.name, s.value -
from v$mystat s, v$statname n -
where s.statistic# = n.statistic# -
and n.name = ''redo size''');
NAME                          : redo size
VALUE                         : 10071192
 
-- Shrink space compact 시에 415,304 Byte의 Redo가 발생하였다.
-- Coalesce 시 발생한 347,596Bytes보다 더 많은 양의 Redo가 발생하였다.
 
-- See Index Structure
analyze index t1_idx validate structure;
 
select height, blocks, lf_blks, br_blks, btree_space, pct_used
from index_stats;
 
-- Shrink space compact 수행 후는 coalesce 와 같은 결과를 보인다.
-- t1_idx 는 총 32블록으로 이루어짐은 변화가 없고
-- leaf block의 수가 21-> 16개로 변화하였다.
-- 136,032 Byte의 공간을 사용하고 있다.
 
HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------
         2         32         16          1      136032         83
 
shrink space 명령을 수행한다.
 
exec print_table('-
select n.name, s.value -
from v$mystat s, v$statname n -
where s.statistic# = n.statistic# -
and n.name = ''redo size''');
NAME                          : redo size
VALUE                         : 10071872
 
-- SHRINK SPACE the index
ALTER INDEX t1_idx SHRINK SPACE;
 
exec print_table('-
select n.name, s.value -
from v$mystat s, v$statname n -
where s.statistic# = n.statistic# -
and n.name = ''redo size''');
 
NAME                          : redo size
VALUE                         : 10081736
 
-- Shrink space 시에 9,634 Byte의 Redo가 발생하였다.
-- Shrink space compact 후 shrink space를 수행하므로
-- coalesce 보다는 더 많은 양의 Redo가 발생한다
 
-- See Index Structure
analyze index t1_idx validate structure;
 
select height, blocks, lf_blks, br_blks, btree_space, pct_used
from index_stats;
 
-- Shrink space 수행 후, t1_idx 는 총 32-> 24블록으로 줄어들었다.
-- Free Block들이 Tablespace로 반환되었음을 알 수 있다.

HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   PCT_USED
---------- ---------- ---------- ---------- ----------- ----------
         2         24         16          1      136032         83
 
출처 : http://graceoracle.tistory.com/