ITPub博客

首页 > Linux操作系统 > Linux操作系统 > shrink 与rebuild对索引高度的影响对比

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

原创 Linux操作系统 作者:lsq_008 时间:2010-06-25 09:25:28 0 删除 编辑

测试思路:
创建一个测试表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博客 ” ,链接:https://blog.itpub.net/10972173/viewspace-666208/ ,如需转载,请注明出处,否则将追究法律责任。

下一篇: 初试EM
请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    335
  • 访问量
    1369146