淺談oracle中重建索引 (ZT)

淺談oracle中重建索引

當我們建立索引時，oracle會為索引建立索引樹，表和索引樹透過rowid(偽列)來定位資料。當表裡的資料發生更新時，oracle會自動維護索引樹。但是在索引樹中沒有更新操作，只有刪除和插入操作。

例如在某表id列上建立索引，某表id列上有值“101”，當我將“101”更新為“110”時，oracle同時會來更新索引樹，但是oracle先將索引樹中的“101”標示為刪除（實際並未刪除，只是標示一下），然後再將“110”寫到索引樹中。

如果表更新比較頻繁，那麼在索引中刪除標示會越來越多，這時索引的查詢效率必然降低，所以我們應該定期重建索引。來消除索引中這些刪除標記。

一般不會選擇先刪除索引，然後再重新建立索引，而是rebuild索引。在rebuild期間，使用者還可以使用原來的索引，並且rebuild新的索引時也會利用原來的索引資訊，這樣重建索引會塊一些。

這個實驗來察看索引中的刪除標記，並且如何重建索引。

試驗環境：oracle 8.1.7

 

一、建立表、插入記錄和建立索引
SQL> create table ind (id number,name varchar2(100));

表已建立。

SQL> create or replace procedure sp_insert_ind
  2  is
  3  begin
  4  for i in 1..10000 loop
  5  insert into ind values(i,to_char(i)||'aaaaaaaaaa');
  6  end loop;
  7  end;
  8  /

過程已建立。

SQL> exec sp_insert_ind

PL/SQL 過程已成功完成。

SQL> create index ind_id_idx on ind(id);

索引已建立。

 

二、收集索引資訊

--收集資訊，沒有更新資料字典，所以沒有資訊
SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

未選定行

--更新資料字典
SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

索引已分析

--引數含義：
--LF_ROWS Number of values currently in the index
--LF_ROWS_LEN Sum in bytes of the length of all values
--DEL_LF_ROWS Number of values deleted from the index
--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                             
---------- ----------- ----------- ---------------                             
     10000      149801           0               0     

                       
--察看索引中已經標示為刪除的行除以總共的行的數量，目前為0
SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

INDEX_USAGE                                                                    
-----------                                                                    
          0      

 

三、更新索引，並且重新察看資訊
                                                            
--更新表中1000行記錄，這時會更新索引樹
SQL> update ind set id=id+1 where id>9000;

已更新1000行。

SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

索引已分析

--總共行的數量增加了1000行，並且標示為刪除了1000行記錄
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                             
---------- ----------- ----------- ---------------                             
     11000      164792        1000           14990                             

--察看索引中已經標示為刪除的行除以總共的行的數量，目前為 9.09631536，這個值如果查過20，肯定要重建索引了。
SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

INDEX_USAGE                                                                    
-----------                                                                    
9.09631536                                                                    

 

四、重建索引

--重建索引
SQL> alter index ind_id_idx rebuild;

索引已更改。

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

未選定行

 

---以下資訊又基本回到從前
SQL> 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                             
---------- ----------- ----------- ---------------                             
     10000      149802           0               0                             

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

INDEX_USAGE                                                                    
-----------                                                                    
          0