---刪除老索引
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，掃描不必要的索引塊，增加邏輯讀

alter index rebuild與index_stats

相關文章