xinyoulinglei

oracle重建index

一：考慮重建索引的場合
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并沒有發生變化

posted on 2013-07-01 19:29 Lenovo123 閱讀(128) 評論(0)  編輯  收藏 所屬分類: oracle