---20090409 test delete row 对index 空间的影响
---DB version ：8174 & 10204
--建立环境
CREATE TABLE mestst.xmTEST1
(
ID VARCHAR2(10 ),
NAME VARCHAR2(10 )
)
TABLESPACE USERS;
CREATE INDEX TEAT_IDX1 ON mestst.xmTEST1
(ID)
LOGGING
TABLESPACE USERS
;
--no row 的index 状态
analyze index TEAT_IDX1 VALIDATE STRUCTURE;
SELECT height, blocks, NAME, lf_rows, lf_blks, lf_rows_len, lf_blk_len,
del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key,
btree_space, used_space, pct_used
FROM index_stats
WHERE NAME = 'TEAT_IDX1';
--insert 1 笔data
insert into mestst.xmtest1 values('1','dd');
commit;
analyze index TEAT_IDX1 VALIDATE STRUCTURE;
SELECT height, blocks, NAME, lf_rows, lf_blks, lf_rows_len, lf_blk_len,
del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key,
btree_space, used_space, pct_used
FROM index_stats
WHERE NAME = 'TEAT_IDX1';
--delete 这笔data
delete from mestst.xmtest1 values('1','fd');
commit；
analyze index TEAT_IDX1 VALIDATE STRUCTURE;
SELECT height, blocks, NAME, lf_rows, lf_blks, lf_rows_len, lf_blk_len,
del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key,
btree_space, used_space, pct_used
FROM index_stats
WHERE NAME = 'TEAT_IDX1';
--insert 另一笔数据
insert into mestst.xmtest1 values('2','fd');
commit;
analyze index TEAT_IDX1 VALIDATE STRUCTURE;
SELECT height, blocks, NAME, lf_rows, lf_blks, lf_rows_len, lf_blk_len,
del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key,
btree_space, used_space, pct_used
FROM index_stats
WHERE NAME = 'TEAT_IDX1';
--insert 第二笔数据
insert into mestst.xmtest1 values('4','gg');
commit;
analyze index TEAT_IDX1 VALIDATE STRUCTURE;
SELECT height, blocks, NAME, lf_rows, lf_blks, lf_rows_len, lf_blk_len,
del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key,
btree_space, used_space, pct_used
FROM index_stats
WHERE NAME = 'TEAT_IDX1';
--比较index_stat的状态
A：no data B：1 row C：delete 1 data D：insert 1 data E：insert 第二笔data
HEIGHT 1 1 1 1 1
BLOCKS 16 16 16 16 16
LF_ROWS 0 1 1 1 2
LF_BLKS 1 1 1 1 1
LF_ROWS_LEN 0 13 13 13 26
LF_BLK_LEN 7980 7980 7980 7980 7980
DEL_LF_ROWS 0 0 1 0 0
DEL_LF_ROWS_LEN 0 0 13 0 0
DISTINCT_KEYS 0 1 1 1 2
MOST_REPEATED_KEY 0 1 1 1 1
BTREE_SPACE 7980 7980 7980 7980 7980
USED_SPACE 0 13 13 13 26
PCT_USED 0 1 1 1 1
Summary：
从A可以看出没有data的状态下，建立index后会自动分一个block，block_size 为8192，除了头部存放一些信息外剩下7980可以存放data；
从B可以看出每个leaf 的长度为13，已使用的空间为13
从C可以看出delete 唯一的一行data后，index占用的block不变，使用的空间还是13，没有release出来，而delete的leaf row为1；
从D可以看出重新插入新的值后，如果长度能够存放在delete的leaf 中会先使用那个leaf，而used space也没有增加，delete leaf row 为0.因为已经填补了
从E可以看出再插入新值后，leaf row会2，leaf rows 长度从13变为26了，used space变为26了
Remark：
INDEX_STATS
statistics on the b-tree
Columns
___________________________
HEIGHT
height of the b-tree
BLOCKS
blocks allocated to the segment
NAME
name of the index
PARTITION_NAME
name of the index partition,if partitioned
LF_ROWS
number of leaf rows (values in the index)
LF_BLKS
number of leaf blocks in the b-tree
LF_ROWS_LEN
sum of the lengths of all the leaf rows
LF_BLK_LEN
useable space in a leaf block
DEL_LF_ROWS
number of deleted leaf rows in the index
DEL_LF_ROWS_LEN
total length of all deleted rows in the index
DISTINCT_KEYS
number of distinct keys in the index
MOST_REPEATED_KEY
how many times the most repeated key is repeated
BTREE_SPACE
total space currently allocated in the b-tree
USED_SPACE
total space that is currently being used in the b-tree
PCT_USED
percent of space allocated in the b-tree that is being used
