xinyoulinglei

oracle重建index

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

二：判斷重建索引的標(biāo)準(zhǔn)
  索引重建是否有必要，一般看索引是否傾斜的嚴(yán)重，是否浪費了空間；
  那應(yīng)該如何才可以判斷索引是否傾斜的嚴(yán)重，是否浪費了空間，如下：
1，        對索引進(jìn)行結(jié)構(gòu)分析
Analyze index indexname validate structure;
2, 在執(zhí)行步驟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 原來的索引，然后再創(chuàng)建索引;
    2：alter index indexname rebuild (online);
    方式一：耗時間，無法在24*7環(huán)境中實現(xiàn)
    方式二：比較快，可以在24*7環(huán)境中實現(xiàn)
    建議使用方式二

四：alter index rebuid內(nèi)部過程和注意點
    1：alter index rebuild 和alter index rebuild online的區(qū)別
(1)        掃描方式不同
Rebuild以index fast full scan（or table full scan）方式讀取原索引中的數(shù)據(jù)來構(gòu)建一個新的索引，有排序的操作; rebuild online 執(zhí)行表掃描獲取數(shù)據(jù)，有排序的操作;
Rebuild  方式 (index fast full scan  or  table full scan  取決于統(tǒng)計信息的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時系統(tǒng)會產(chǎn)生一個SYS_JOURNAL_xxx的IOT類型的系統(tǒng)臨時日志表，所有rebuild online時索引的變化都記錄在這個表中，當(dāng)新的索引創(chuàng)建完成后，把這個表的記錄維護(hù)到新的索引中去，然后drop掉舊的索引，rebuild online就完成了。

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

五：重建分區(qū)表上的分區(qū)索引
   1：重建分區(qū)索引方法：
     Alter index indexname rebuild partition paritionname tablespace tablespacename;
     Alter index indexname rebuild subpartition partitioname tablespace tablespacename;
     Partition name 可以從user_ind_partitions查找
     Tablepace 參數(shù)允許alter index操作更改索引的存儲空間；

六：索引狀態(tài)描述

在數(shù)據(jù)字典中查看索引狀態(tài)，發(fā)現(xiàn)有三種：   
  VALID   
  N/A   
  UNUSABLE   

valid:當(dāng)前索引有效
N/A :分區(qū)索引有效
unusable:索引失效

七：術(shù)語

  高基數(shù)：簡單理解就是表中列的不同值多
  低基數(shù)：建單理解就是表中的列的不同值少
  以刪除的葉節(jié)點數(shù)量：指得是數(shù)據(jù)行的delete操作從邏輯上刪除的索引節(jié)點的數(shù)量，要記住oracle在刪除數(shù)據(jù)行后，將“死“節(jié)點保留在索引中，這樣做可以加快sql刪除操作的速度，因此oracle刪除數(shù)據(jù)行后可以不必重新平衡索引。
   索引高度：索引高度是指由于數(shù)據(jù)行的插入操作而產(chǎn)生的索引層數(shù)，當(dāng)表中添加大量數(shù)據(jù)時，oracle將生成索引的新層次以適應(yīng)加入的數(shù)據(jù)行，因此，oracle索引可能有4層，但是這只會出現(xiàn)在索引數(shù)中產(chǎn)生大量插入操作的區(qū)域。Oracle索引的三層結(jié)構(gòu)可以支持?jǐn)?shù)百萬的項目，而具備4層或是更多層的需要重建。
    每次索引訪問的讀取數(shù)：是指利用索引讀取一數(shù)據(jù)行時所需要的邏輯I/O操作數(shù)，邏輯讀取不必是物理讀取，因為索引的許多內(nèi)容已經(jīng)保存在數(shù)據(jù)緩沖區(qū)，然而，任何數(shù)據(jù)大于10的索引都需要重建。

    那么什么時候重建呢？我們可以利用analyze index …….. compute statistics 對表進(jìn)行分析。然后察看dba_indexes中的blevel。這列是說明索引從根塊到葉快的級別，或是深度。如果級別大于等于4。則需要重建，如下：
Select index_name,blevel from dba_indexes where blevel>=4.
   另一個從重建中受益的指標(biāo)顯然是當(dāng)該索引中的被刪除項占總的項數(shù)的百分比。如果在20%以上時，也應(yīng)當(dāng)重建，如下
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命令重建索引。
下面討論一下這三種方法的優(yōu)缺點：
1).刪除并從頭開始建索引：方法是最慢的，最耗時的。一般不建議。
2).Alter index ---- rebuild 快速重建索引的一種有效的辦法，因為使用現(xiàn)有索引項來重建新索引，如果客戶操作時有其他用戶在對這個表操作，盡量使用帶online參數(shù)來最大限度的減少索引重建時將會出現(xiàn)的任何加鎖問題，alter index ------- rebuild online.但是,由于新舊索引在建立時同時存在，因此，使用這種技巧則需要有額外的磁盤空間可臨時使用，當(dāng)索引建完后把老索引刪除，如果沒有成功，也不會影響原來的索引。利用這種辦法可以用來將一個索引以到新的表空間。
Alter index ------ rebuild  tablespace -----。
  這個命令的執(zhí)行步驟如下：
   首先，逐一讀取現(xiàn)有索引，以獲取索引的關(guān)鍵字。
   其次，按新的結(jié)構(gòu)填寫臨時數(shù)據(jù)段。
   最后，一旦操作成功，刪除原有索引樹，降臨時數(shù)據(jù)段重命名為新的索引。
   需要注意的是alter index ---rebuild 命令中必須使用tablespace字句，以保證重建工作是在現(xiàn)有索引相同的表空間進(jìn)行。
3).alter index ----- coalesce 使用帶有coalesce參數(shù)時重建期間不需要額外空間，它只是在重建索引時將處于同一個索引分支內(nèi)的葉塊拼合起來，這最大限度的減少了與查詢過程中相關(guān)的潛在的加鎖問題，但是，coalesce選項不能用來講一個索引轉(zhuǎn)移到其他表空間。

八：其他
   1：truncate 分區(qū)操作和truncate 普通表的區(qū)別
      Truncate 分區(qū)操作會導(dǎo)致全局索引失效; truncate 普通表對索引沒有影響；
      Truncate 分區(qū)操作不會釋放全局索引中的空間，而truncate 普通表會釋放索引所占空間；
   2：rename 表名操作對索引沒有影響，因為rename操作只是更改了數(shù)據(jù)字典，表中數(shù)據(jù)行的rowid并沒有發(fā)生變化

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