oracle中重建索引

来源：互联网发布：ping测试网络什么意思编辑：程序博客网时间：2024/04/29 15:56

原文出自：http://space.itpub.net/7199859/viewspace-555288

当我们创建索引时，oracle会为索引创建索引树，表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时，oracle会自动维护索引树。但是在索引树中没有更新操作，只有删除和插入操作。

例如在某表id列上创建索引，某表id列上有值“101”，当我将“101”更新为“110”时，oracle同时会来更新索引树，但是oracle先将索引树中的“101”标示为删除（实际并未删除，只是标示一下），然后再将“110”写到索引树中。

如果表更新比较频繁，那么在索引中删除标示会越来越多，这时索引的查询效率必然降低，所以我们应该定期重建索引。来消除索引中这些删除标记。

一般不会选择先删除索引，然后再重新创建索引，而是rebuild索引。在rebuild期间，用户还可以使用原来的索引，并且rebuild新的索引时也会利用原来的索引信息，这样重建索引会块一些。

G:\-N'Ac%[0这个实验来察看索引中的删除标记，并且如何重建索引。

试验环境：oracle 8.1.7

一、创建表、插入记录和创建索引
:Z4Sto'k n0SQL> create table ind (id number,name varchar2(100));

表已创建。

SQL> create or replace procedure sp_insert_ind
&Z#xK!^p&qq)J0 2 isITPUB个人空间XNw0_b6J@0aZ
3 beginITPUB个人空间J/H-N*{2H5}7S(b;_
4 for i in 1..10000 loopITPUB个人空间(M Cn-f f
5 insert into ind values(i,to_char(i)||'aaaaaaaaaa');ITPUB个人空间j;nk+v#x-x[F{JUs
6 end loop;ITPUB个人空间GuC/R9K"{!S:C-E
7 end;
:Cu0[8e�v`.L0 8 /

过程已创建。

SQL> exec sp_insert_ind

PL/SQL 过程已成功完成。

ITPUB个人空间Kkg!m].A3jq
SQL> create index ind_id_idx on ind(id);

索引已创建。

二、收集索引信息

--收集信息，没有更新数据字典，所以没有信息ITPUB个人空间 \vT"a#ZP#oex
SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

未选定行

5})YlX5K6F Y7B\oc0--更新数据字典
_V [v9ZIS7BI0SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

索引已分析

o[s?I+T0P?0--参数含义：ITPUB个人空间[7DT$T$zZMy"Ol
--LF_ROWS Number of values currently in the index
%\!_hW%N)lS:?8}0--LF_ROWS_LEN Sum in bytes of the length of all values
.bD`1Q'vNF0--DEL_LF_ROWS Number of values deleted from the index
DN2zoM`^l'U0--DEL_LF_ROWS_LEN Length of all deleted values

SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

   LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN                             ITPUB个人空间*wkKJ$yv5b;M
---------- ----------- ----------- ---------------
uY7CFY:J5Lv\O0     10000      149801           0               0

!\A,A'{3QT7c4R7a0--察看索引中已经标示为删除的行除以总共的行的数量，目前为0ITPUB个人空间8|4Bcy ^y
SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats;

INDEX_USAGE                                                                    ITPUB个人空间d:G6i'C4Z:I+c%h
-----------
V%v)[K,C!u0          0

三、更新索引，并且重新察看信息
s?,a3Xx5M8D0 ITPUB个人空间G4OY|5vuh
--更新表中1000行记录，这时会更新索引树
8N6V"G%}m4Nh*a+NT0SQL> update ind set id=id+1 where id>9000;

已更新1000行。

ITPUB个人空间/rk`j3kA]�|T
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

索引已分析

Y{@A\`E#O&p0--总共行的数量增加了1000行，并且标示为删除了1000行记录ITPUB个人空间!N;f#Fav{9Bk8}
SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

   LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN                             ITPUB个人空间4|&u:z)Vt\i0n.NpW
---------- ----------- ----------- ---------------                             ITPUB个人空间)rXb6W|]E0A1|U
     11000      164792        1000           14990

/E#\|A+S|q ~0--察看索引中已经标示为删除的行除以总共的行的数量，目前为 9.09631536，这个值如果查过20，肯定要重建索引了。
iSW%PX/S0SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats;

INDEX_USAGE
[X uU/UL0-----------
]Ek)V{!])?s09.09631536

四、重建索引

--重建索引
VH~7~r,ON9W \0SQL> alter index ind_id_idx rebuild;

索引已更改。

SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

未选定行

---以下信息又基本回到从前
I]bN/Y9_v-L;rt"H0SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

索引已分析

SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

   LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN                             ITPUB个人空间%Y0q3^ecX8@&I.yW
---------- ----------- ----------- ---------------                             ITPUB个人空间m1E*v5K`:y9uT)`"w
     10000      149802           0               0

SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats;

INDEX_USAGE
y|6T],O X y&Z0-----------                                                                    ITPUB个人空间2l|@hAt3FH
          0