oracle重建索引

一：考慮重建索引的場合
1：表上頻繁發生update,delete操作
2：表上發生了alter table ..move操作（move操作導致了rowid變化）

二：判斷重建索引的標準
  索引重建是否有必要，一般看索引是否傾斜的嚴重，是否浪費了空間；
  那應該如何才可以判斷索引是否傾斜的嚴重，是否浪費了空間，如下：
1，        對索引進行結構分析
Analyze index indexname validate structure;
2, 在執行步驟1的session中查詢index_stats表，不要到別的session去查詢
   select height,DEL_LF_ROWS/LF_ROWS from index_stats;
3, 在步驟2查詢出來的height>=4或者DEL_LF_ROWS/LF_ROWS>0.2的場合，該索引考慮重建；
Example:
   SQL> select count(*) from test_index;
         COUNT(*)
----------
                   2072327
SQL> analyze index pk_t_test validate structure;
Index analyzed
        SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
                     HEIGHT         DEL_LF_ROWS/LF_ROWS
---------- -------------------
         3                   0
SQL> delete from test_index where rownum<250000;
          249999 rows deleted
        SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
                    HEIGHT                 DEL_LF_ROWS/LF_ROWS
---------- -------------------
                         3                   0
        SQL> analyze index pk_t_test validate structure;
                Index analyzed
SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
                    HEIGHT                  DEL_LF_ROWS/LF_ROWS
---------- -------------------
                 3                           0.0777430939338362

三：重建索引的方式
    1：drop 原來的索引，然後再建立索引;
    2：alter index indexname rebuild (online);
    方式一：耗時間，無法在24*7環境中實現
    方式二：比較快，可以在24*7環境中實現
    建議使用方式二

四：alter index rebuid內部過程和注意點
    1：alter index rebuild 和alter index rebuild online的區別
(1)        掃描方式不同
Rebuild以index fast full scan（or table full scan）方式讀取原索引中的資料來構建一個新的索引，有排序的操作; rebuild online 執行表掃描獲取資料，有排序的操作;
Rebuild  方式 (index fast full scan  or  table full scan  取決於統計資訊的cost)
Eg1:
SQL> explain plan for alter index idx_policy_id2 rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id  | Operation              |  Name           | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |                 |   999K|  4882K|  3219 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_POLICY_ID2  |       |       |       |
|   2 |   SORT CREATE INDEX     |                 |   999K|  4882K|       |
|   3 |    INDEX FAST FULL SCAN | IDX_POLICY_ID2  |   999K|  4882K|       |
---------------------------------------------------------------------
Eg2:
SQL>  explain plan for alter index idx_policy_id rebuild;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id  | Operation              |  Name          | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |                |  2072K|     9M|   461 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_POLICY_ID  |       |       |       |
|   2 |   SORT CREATE INDEX    |                |  2072K|     9M|       |
|   3 |    TABLE ACCESS FULL   | TEST_INDEX     |  2072K|     9M|   461 |


Eg3： (注意和Eg1比較)
Rebuil online 方式：
SQL> explain plan for alter index idx_policy_id2 rebuild online;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
| Id  | Operation              |  Name           | Rows  | Bytes | Cost  |
---------------------------------------------------------------------|   0 | ALTER INDEX STATEMENT  |                 |   999K|  4882K|  3219 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_POLICY_ID2  |       |       |       |
|   2 |   SORT CREATE INDEX    |                 |   999K|  4882K|       |
|   3 |    TABLE ACCESS FULL   | TEST_INDEX2     |   999K|  4882K|  3219 |
(2)        rebuild 會阻塞dml操作，rebuil online 不會阻塞dml操作；
(3)        rebuild online時系統會產生一個SYS_JOURNAL_xxx的IOT型別的系統臨時日誌表，所有rebuild online時索引的變化都記錄在這個表中，當新的索引建立完成後，把這個表的記錄維護到新的索引中去，然後drop掉舊的索引，rebuild online就完成了。

注意點：
1，        執行rebuild操作時，需要檢查表空間是否足夠；
2，        雖然說rebuild online操作允許dml操作，但是還是建議在業務不繁忙時間段進行；
3，        Rebuild操作會產生大量redo log ；

五：重建分割槽表上的分割槽索引
   1：重建分割槽索引方法：
     Alter index indexname rebuild partition paritionname tablespace tablespacename;
     Alter index indexname rebuild subpartition partitioname tablespace tablespacename;
     Partition name 可以從user_ind_partitions查詢
     Tablepace 引數允許alter index操作更改索引的儲存空間；

 

六：索引狀態描述

在資料字典中檢視索引狀態，發現有三種：   
  VALID   
  N/A   
  UNUSABLE   

valid:當前索引有效
N/A :分割槽索引有效
unusable:索引失效

 

七：術語

  高基數：簡單理解就是表中列的不同值多
  低基數：建單理解就是表中的列的不同值少
  以刪除的葉節點數量：指得是資料行的delete操作從邏輯上刪除的索引節點的數量，要記住oracle在刪除資料行後，將“死“節點保留在索引中，這樣做可以加快sql刪除操作的速度，因此oracle刪除資料行後可以不必重新平衡索引。
   索引高度：索引高度是指由於資料行的插入操作而產生的索引層數，當表中新增大量資料時，oracle將生成索引的新層次以適應加入的資料行，因此，oracle索引可能有4層，但是這隻會出現在索引數中產生大量插入操作的區域。Oracle索引的三層結構可以支援數百萬的專案，而具備4層或是更多層的需要重建。
    每次索引訪問的讀取數：是指利用索引讀取一資料行時所需要的邏輯I/O運算元，邏輯讀取不必是物理讀取，因為索引的許多內容已經儲存在資料緩衝區，然而，任何資料大於10的索引都需要重建。

    那麼什麼時候重建呢？我們可以利用analyze index …….. compute statistics 對錶進行分析。然後察看dba_indexes中的blevel。這列是說明索引從根塊到葉快的級別，或是深度。如果級別大於等於4。則需要重建，如下：
Select index_name,blevel from dba_indexes where blevel>=4.
   另一個從重建中受益的指標顯然是當該索引中的被刪除項佔總的項數的百分比。如果在20%以上時，也應當重建，如下
SQL>anlyze index ------ validate structure
SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name=’------‘
就能看到是否這個索引被刪除的百分比。
上面只是判斷，那麼，怎樣重建會更好呢？
建索引的辦法：
a.        刪除並從頭開始建立索引。
b.        使用alter index -------- rebuild 命令重建索引
c.        使用alter index -------- coalesce命令重建索引。
下面討論一下這三種方法的優缺點：
1).刪除並從頭開始建索引：方法是最慢的，最耗時的。一般不建議。
2).Alter index ---- rebuild 快速重建索引的一種有效的辦法，因為使用現有索引項來重建新索引，如果客戶操作時有其他使用者在對這個表操作，儘量使用帶online引數來最大限度的減少索引重建時將會出現的任何加鎖問題，alter index ------- rebuild online.但是,由於新舊索引在建立時同時存在，因此，使用這種技巧則需要有額外的磁碟空間可臨時使用，當索引建完後把老索引刪除，如果沒有成功，也不會影響原來的索引。利用這種辦法可以用來將一個索引以到新的表空間。
Alter index ------ rebuild  tablespace -----。
  這個命令的執行步驟如下：
   首先，逐一讀取現有索引，以獲取索引的關鍵字。
   其次，按新的結構填寫臨時資料段。
   最後，一旦操作成功，刪除原有索引樹，降臨時資料段重新命名為新的索引。
   需要注意的是alter index ---rebuild 命令中必須使用tablespace字句，以保證重建工作是在現有索引相同的表空間進行。
3).alter index ----- coalesce 使用帶有coalesce引數時重建期間不需要額外空間，它只是在重建索引時將處於同一個索引分支內的葉塊拼合起來，這最大限度的減少了與查詢過程中相關的潛在的加鎖問題，但是，coalesce選項不能用來講一個索引轉移到其他表空間。

八：其他
   1：truncate 分割槽操作和truncate 普通表的區別
      Truncate 分割槽操作會導致全域性索引失效; truncate 普通表對索引沒有影響；
      Truncate 分割槽操作不會釋放全域性索引中的空間，而truncate 普通表會釋放索引所佔空間；
   2：rename 表名操作對索引沒有影響，因為rename操作只是更改了資料字典，表中資料行的rowid並沒有發生變化