---删除老索引
SQL> drop table t_detail purge;
 
Table dropped
 
SQL> create table t_detail(a int) tablespace tbs_16k;
 
Table created
 
SQL> insert into t_detail select level from dual connect by level<5e4;
 
49999 rows inserted
 
SQL> commit;
 
Commit complete
--创建索引
SQL> create index idx_t_detail on t_detail(a);
 
Index created

--分析索引
SQL> analyze index idx_t_detail validate structure;
 
Index analyzed

--blocks为索引总分配的数据块数，free_blocks为空闲数据块数
SQL> select name,height,blocks,blocks-br_blks-lf_blks as free_blocks from index_stats;
 
NAME                               HEIGHT     BLOCKS FREE_BLOCKS
------------------------------ ---------- ---------- -----------
IDX_T_DETAIL                            2        128          17

--删除一些条目，是否free_blocks会多呢
SQL> delete from t_detail where rownum<=3000;
 
3000 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> alter session set events '10224 trace name context off';
 
Session altered

--查看trace文件,可知已经把删除的索引块释放给了free list
*** 2013-03-19 10:22:48.388
*** SESSION ID:(8.55) 2013-03-19 10:22:48.388
 
kdimod adding block to free list,dba 0x02835cec,time 10:22:48.388
kdimod adding block to free list,dba 0x02835ced,time 10:22:48.415
kdimod adding block to free list,dba 0x02835cee,time 10:22:48.443
kdimod adding block to free list,dba 0x02835cef,time 10:22:48.467
kdimod adding block to free list,dba 0x02843770,time 10:22:48.496
kdimod adding block to free list,dba 0x02843771,time 10:22:48.522

---但索块空块还是17
SQL> select name,height,blocks,blocks-br_blks-lf_blks as free_blocks from index_stats;
 
NAME                               HEIGHT     BLOCKS FREE_BLOCKS
------------------------------ ---------- ---------- -----------
IDX_T_DETAIL                            2        128          17

---看下索引的结构

---索引删除前                                           --索引删除后
branch: 0x2835ceb 42163435 (0: nrow: 110, level: 1)   branch: 0x2835ceb 42163435 (0: nrow: 110, level: 1)
   leaf: 0x2835cec 42163436 (-1: nrow: 485 rrow: 0)      leaf: 0x2835cec 42163436 (-1: nrow: 485 rrow: 0)
   leaf: 0x2835ced 42163437 (0: nrow: 479 rrow: 0)      leaf: 0x2835ced 42163437 (0: nrow: 479 rrow: 0)
   leaf: 0x2835cee 42163438 (1: nrow: 479 rrow: 0)      leaf: 0x2835cee 42163438 (1: nrow: 479 rrow: 0)
   leaf: 0x2835cef 42163439 (2: nrow: 479 rrow: 0)      leaf: 0x2835cef 42163439 (2: nrow: 479 rrow: 0)
   leaf: 0x2843770 42219376 (3: nrow: 479 rrow: 0)      leaf: 0x2843770 42219376 (3: nrow: 479 rrow: 0)
   leaf: 0x2843771 42219377 (4: nrow: 479 rrow: 0)      leaf: 0x2843771 42219377 (4: nrow: 479 rrow: 0)
   leaf: 0x2843772 42219378 (5: nrow: 479 rrow: 359)      leaf: 0x2843772 42219378 (5: nrow: 359 rrow: 0)
   leaf: 0x2843773 42219379 (6: nrow: 479 rrow: 479)      leaf: 0x2843773 42219379 (6: nrow: 479 rrow: 0)
   leaf: 0x2843774 42219380 (7: nrow: 479 rrow: 479)      leaf: 0x2843774 42219380 (7: nrow: 479 rrow: 0)
   leaf: 0x2843775 42219381 (8: nrow: 479 rrow: 479)      leaf: 0x2843775 42219381 (8: nrow: 479 rrow: 0)
   leaf: 0x2843776 42219382 (9: nrow: 479 rrow: 479)      leaf: 0x2843776 42219382 (9: nrow: 479 rrow: 0)
   leaf: 0x2843777 42219383 (10: nrow: 479 rrow: 479)      leaf: 0x2843777 42219383 (10: nrow: 479 rrow: 0)--可知索引条目已删除,rrow=0,删除后索引目结构还是保留下来，未删除
   leaf: 0x2843779 42219385 (11: nrow: 479 rrow: 479)      leaf: 0x2843779 42219385 (11: nrow: 479 rrow: 0)
   leaf: 0x284377a 42219386 (12: nrow: 479 rrow: 479)      leaf: 0x284377a 42219386 (12: nrow: 479 rrow: 0)
   leaf: 0x284377b 42219387 (13: nrow: 479 rrow: 479)      leaf: 0x284377b 42219387 (13: nrow: 479 rrow: 0)
   leaf: 0x284377c 42219388 (14: nrow: 479 rrow: 479)      leaf: 0x284377c 42219388 (14: nrow: 479 rrow: 0)
   leaf: 0x284377d 42219389 (15: nrow: 479 rrow: 479)      leaf: 0x284377d 42219389 (15: nrow: 479 rrow: 0)
   leaf: 0x284377e 42219390 (16: nrow: 479 rrow: 479)      leaf: 0x284377e 42219390 (16: nrow: 479 rrow: 0)
   leaf: 0x284377f 42219391 (17: nrow: 479 rrow: 479)      leaf: 0x284377f 42219391 (17: nrow: 479 rrow: 0)
   leaf: 0x2843800 42219520 (18: nrow: 479 rrow: 479)      leaf: 0x2843800 42219520 (18: nrow: 479 rrow: 0)
   leaf: 0x2843801 42219521 (19: nrow: 475 rrow: 475)      leaf: 0x2843801 42219521 (19: nrow: 475 rrow: 0)
   leaf: 0x2843802 42219522 (20: nrow: 449 rrow: 449)      leaf: 0x2843802 42219522 (20: nrow: 449 rrow: 0)
   leaf: 0x2843803 42219523 (21: nrow: 449 rrow: 449)      leaf: 0x2843803 42219523 (21: nrow: 449 rrow: 0)
   leaf: 0x2843804 42219524 (22: nrow: 449 rrow: 449)      leaf: 0x2843804 42219524 (22: nrow: 449 rrow: 0)
   leaf: 0x2843805 42219525 (23: nrow: 449 rrow: 449)      leaf: 0x2843805 42219525 (23: nrow: 449 rrow: 0)
---中间略
   leaf: 0x2843860 42219616 (108: nrow: 426 rrow: 426)      leaf: 0x2843860 42219616 (108: nrow: 426 rrow: 426)

--重构索引再看下上述索引结构是否变化，查看删除的叶子块从索引结构移除  
SQL> alter index idx_t_detail rebuild online;
 
Index altered
 
SQL> alter session set events 'immediate trace name treedump level 69800';
 
Session altered  

--重构索引后索引的删除的索引块已回收，不会在体现在索引结构树中，但也要注意：索引块的dba已变化，说明oracle已重新对索引进行了物理存储
branch: 0x2843873 42219635 (0: nrow: 83, level: 1)
   leaf: 0x2843874 42219636 (-1: nrow: 449 rrow: 449)
  --中间略
   leaf: 0x28438cb 42219723 (81: nrow: 181 rrow: 181)
----- end tree dump

--重构索引须马上重新分析索引
SQL> select name,height,blocks,blocks-br_blks-lf_blks as free_blocks from index_stats;
 
NAME                               HEIGHT     BLOCKS FREE_BLOCKS
------------------------------ ---------- ---------- -----------
 
SQL> analyze index idx_t_detail validate structure;
 
Index analyzed

---空块变化了
SQL> select name,height,blocks,blocks-br_blks-lf_blks as free_blocks from index_stats;
 
NAME                               HEIGHT     BLOCKS FREE_BLOCKS
------------------------------ ---------- ---------- -----------
IDX_T_DETAIL                            2         96          12

---叶子块变化了,叶子块的个数自上述的trace也可知
SQL> select name,height,blocks,blocks-br_blks-lf_blks as free_blocks,br_blks,lf_blks from index_stats;
 
NAME                               HEIGHT     BLOCKS FREE_BLOCKS    BR_BLKS    LF_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
IDX_T_DETAIL                            2         96          12          1         83

小结：1,用analyze index idx_t_detail validate structure可分析并查看索引的结构信息
      2,如果基于索引的表发生大量的delete,要定期重构索引，不然会造成空间浪费，更为
        重要的是，会增加io，扫描不必要的索引块，增加逻辑读