shrink 与rebuild对索引高度的影响对比

shrink 与rebuild对索引高度的影响对比

 

测试思路：
创建一个测试表t1，并创建索引，插入数据后，再将数据删除，然后对表和索引进行shrink操作，观察索引高度是否发生变化：

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index idx_t1 on t1(object_id,object_name) pctfree 90 tablespace idx_2k;

Index created.

SQL> analyze index idx_t1 validate structure;

Index analyzed.

SQL> select name,height,lf_blks,lf_rows,br_blks,br_rows from index_stats;

NAME HEIGHT LF_BLKS LF_ROWS BR_BLKS BR_ROWS
-------------------------------------------------- ---------- ---------- ---------- ---------- ----------
IDX_T1 3 9552 9552 57 9551

SQL> select count(*) from t1;

COUNT(*)
----------
9552

SQL> delete from t1;

9552 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze index idx_t1 validate structure;

Index analyzed.

SQL> select name,height,lf_blks,lf_rows,br_blks,br_rows from index_stats;

NAME HEIGHT LF_BLKS LF_ROWS BR_BLKS BR_ROWS
-------------------------------------------------- ---------- ---------- ---------- ---------- ----------
IDX_T1 3 9552 1608 57 9551

SQL> alter table t1 enable row movement;

Table altered.

----不加cascade，只对表shrink，索引的高度，leaf block等不变。

SQL> alter table t1 shrink space;

Table altered.

SQL> analyze index idx_t1 validate structure;

Index analyzed.

SQL> select name,height,lf_blks,lf_rows,br_blks,br_rows from index_stats;

NAME HEIGHT LF_BLKS LF_ROWS BR_BLKS BR_ROWS
-------------------------------------------------- ---------- ---------- ---------- ---------- ----------
IDX_T1 3 9552 1608 57 9551

----加cascade，对索引shrink，索引高度不变，leaf block，branch block减少，空间被回收，但维持高度不变

SQL> alter table t1 shrink space cascade;

Table altered.

SQL> analyze index idx_t1 validate structure;

Index analyzed.

SQL> select name,height,lf_blks,lf_rows,br_blks,br_rows from index_stats;

NAME HEIGHT LF_BLKS LF_ROWS BR_BLKS BR_ROWS
-------------------------------------------------- ---------- ---------- ---------- ---------- ----------
IDX_T1 3 1 1 2 0

----rebuild索引，高度降低。

SQL> alter index idx_t1 rebuild;

Index altered.

SQL> analyze index idx_t1 validate structure;

Index analyzed.

SQL> select name,height,lf_blks,lf_rows,br_blks,br_rows from index_stats;

NAME HEIGHT LF_BLKS LF_ROWS BR_BLKS BR_ROWS
-------------------------------------------------- ---------- ---------- ---------- ---------- ----------
IDX_T1 1 1 0 0 0

因此，相对于rebuild来说，在降低高水位的同时，shrink操作对索引的影响较小，没有降低索引高度，避免了root block分裂带来的风险。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22578826/viewspace-740171/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22578826/viewspace-740171/