oracle 索引什麼時候重建和重建方法討論

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

oracle 索引什麼時候重建和重建方法討論　

分類：資料庫技術 字號： 大大 中中 小小 索引什麼時候需要重建和重建的方法

一提到索引，大家都知道，但是怎樣建索引，什麼時候重建索引，重建索引用什麼方法，可能有的就不太清楚了，我根據一些資料簡單的整理一點，如果哪裡不對或是不妥請大家指點，希望大家有更好經驗也share出來。

索引的目的是為了加快尋找資料的速度，但是如果對錶經常做改動，則索引也會相應改動，時間長了，查詢速度的效率就會降低，就有可能要重建索引，那麼什麼時候需要重建索引和用什麼方法重建索引可能是大家關心的。

一. 索引在內部進行自身的管理以確保對資料行的快速訪問。但是資料表中大量的活動會導致oracle索引動態地對自身的進行重新配置，這些配置包括三個方面：

1． 索引分割

當新資料行產生的索引節點要建立在現有級別上時，出現此動作。

2． 索引生成

在某些位置，索引達到此級索引的最大容量的時候，就會生成更深一級的索引結構。

3． 索引節點的刪除

你可能瞭解到，刪除表中的資料行後，索引中相應的節點不會從物理意義上刪除，也沒有從索引中刪除此專案。而是從邏輯上刪除此索引專案，並在索引樹中留下了一個“死“節點，當索引刪除了葉節點或是生成了過深的的級別層次後，就需要進行重建。

二 索引的種類：

a.B-tree（B樹）索引

b.壓縮B樹索引

c.Bitmap（點陣圖）索引

d.函式索引

e.Reverse Key Index（反向鍵索引）

f.Index Organized Table（索引組織表）

三 下面分別對各種索引進行說明

在進行介紹前先說明幾個術語：

高基數：簡單理解就是表中列的不同值多

低基數：建單理解就是表中的列的不同值少

以刪除的葉節點數量：指得是資料行的delete操作從邏輯上刪除的索引節點的數量，要記住oracle在刪除資料行後，將“死“節點保留在索引中，這樣做可以加快sql刪除操作的速度，因此oracle刪除資料行後可以不必重新平衡索引。

索引高度：索引高度是指由於資料行的插入操作而產生的索引層數，當表中新增大量資料時，oracle將生成索引的新層次以適應加入的資料行，因此， oracle索引可能有4層，但是這隻會出現在索引數中產生大量插入操作的區域。Oracle索引的三層結構可以支援數百萬的專案，而具備4層或是更多層的需要重建。

每次索引訪問的讀取數：是指利用索引讀取一資料行時所需要的邏輯I/O運算元，邏輯讀取不必是物理讀取，因為索引的許多內容已經儲存在資料緩衝區，然而，任何資料大於10的索引都需要重建。

1. B-tree（B樹）索引

是現代關係型資料庫中最常用的索引。除了儲存索引資料外，還儲存一個行ID，用來指出該行其餘資料儲存在這個被索引表中的什麼地方。該索引以一種數結構格式儲存這些值。

Oracle建議如果表經過排序，當返回40%一下的資料時使用索引，如果高於40%則使用全表掃描，如果沒有經過排序，則當返回7%以下時，使用索引。看錶是否排序，可以看dba_indexes字典中的CLUSTERING_FACTOR列，如果與表佔用的資料塊數相近，則經過了排序，如果與行數相近，則沒有排序。那麼什麼時候重建呢？我們可以利用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 刪除並從頭開始建立索引。

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選項不能用來講一個索引轉移到其他表空間。

2．壓縮B樹索引

當B樹索引基於大表時，尤其是當基於資料倉儲或決策支援系統中的大表時，這些索引會耗費大量的儲存空間，壓縮（compressed）B樹索引用來最大限度的減少某些型別的B樹索引使用的空間。當一個B樹索引得到壓縮時，被索引的獵的重複出現就被消除掉，進而減少了儲存索引的總的儲存空間。例如：

壓縮前：smith每次出現還要儲存它的相關的rowid.

姓 關聯rowid

smith AAABSOAAEAAAABTAAB

smith AAABSOAAEAAAABTAAC

smith AAABSOAAEAAAABTAAD

壓縮後:smith項和rowid指儲存一次。

smith AAABSOAAEAAAABTAAB, AAABSOAAEAAAABTAAB, AAABSOAAEAAAABTAAB

建立方法：

SQL>create index index_name on table_name(column_name)

tablespace tablespace_name

compress;

另一種方法：

SQL>alter index index_name rebuild compress;

3． itmap(點陣圖)索引。

B樹索引在資料具有高基數的列工作的最好，對於低基數的列，點陣圖索引可能是更有效的選擇。點陣圖索引建立錶行的一個二進位制映像，並把映像儲存在索引塊中，這種型別的索引的DML操作少，長度大並且含有極少不同的值得列特別有用。點陣圖索引不應當用在頻繁發生insert,update,delete操作的表上，這些dml操作在效能方面的代價很高，因為，他們會引起點陣圖級的加鎖發生，而且要求動態的重建所有可能值的點陣圖。為圖索引最適合資料倉儲和決策支援系統。

4． 基於函式的索引

當把一個函式運用於被索引的列上時，該列德索引都變得無效，基於函式的索引就是為了解決這個問題。

5． 反向鍵索引

是一種特殊型別的B樹索引，在索引基於含有序數的列時使非常有用的，如果一個傳統的B樹索引基於一個含有這種資料的列，往往會產生許多級，由於B樹索引有 4級以上的深度會降低效能，因此反向鍵索引更適合這種型別，反向鍵索引透過簡單的煩象被索引的列中的資料來解決問題，他首先反向每個列鍵值的位元組，然後在反向後的新資料上進行索引，而新資料在值的範圍上的分佈通常比原來的有序數更均勻。

6． 索引組織表

由於B樹、點陣圖、反向鍵索引的使用而引起的效能將會導致這樣的事實，這些索引中的專案直接指向索引基表中對應資料的行ID,這是從錶行沒有按任何特定的順序來物理地儲存表中檢索錶行的一種有效方法，這種表叫做堆表，oracle大多數表中以一種堆疊方式儲存行資料，因為行以一種或多或少的隨機方式被分配給表內的塊，之所以出現這種隨機性，是因為oracle在決定把一個行儲存在何處時並不考慮改行的內容，oracle只是把該行儲存在它從該表的free list 上所發現的第一個塊中。

如果希望按一種指定順序來儲存一個表的資料，就不能使用堆表，為此oracle提供了索引組織表，索引組織表不是儲存一個指向行資料的其餘部分儲存在了何處的行的ID指標，而是把行資料全部儲存在索引本身內，這產生了兩個效能好處：

n 錶行按索引順序來儲存。

n 使用B樹索引時引起的先讀取索引後讀取表鎖使用的額外I/O操作得到消除。

例如：

sql>create table emp

(last_name varchar2(9) primary key,

first_name varchar2(9),

hire_date date)

organization index tablespace users

pctthreshold 25

including first name

overflow tablespace qyl

mapping table;

所有索引組織表在將要作為索引基礎的那一列上都必須有一個主鍵約束，索引組織表不能含有唯一性約束或是被聚簇。

下面說明各個引數的含義：

organization index：說明該表是索引組織表

pctthreshold :指定整個資料塊的什麼百分比要保持開啟，以便儲存一個與主鍵值相關聯的行資料，其中主鍵值必須在0到50之間（50是預設值）

including : 指定在行長度超過pctthershold中所設定的大小時按那一列 把行分解成兩段

overflow tablespace ：指定在行長度超過pctthreshold中設定的大小時行數的的另一部分儲存到的表空間。

Mapping table:致使在建立索引組織表的點陣圖索引時所必需的一個關聯映像表的建立。

以上是我根據一些資料對索引的一個簡單闡述，大家可能有不同的見解，希望對大家有幫助，那些不妥的地方還希望大家提出來。

參考資料：ocp困惑racle9i效能調整

oracle statspack 高效能調整技術

[@more@]

analyze index t_id_ind validate structure

select (del_lf_rows_len/lf_rows_len)*100 from index_stats

>20%

b. 使用alter index t_id_ind rebuild 命令重建索引
c. 使用alter index t_id_ind coalesce命令重建索引。

alter indext_id_ind rebuild online.

但是,由於新舊索引在建立時同時存在，因此，使用這種技巧則需要有額外的磁碟空間可臨時使用，當索引建完後把老索引刪除，如果沒有成功，也不會影響原來的索引。利用這種辦法可以用來將一個索引以到新的表空間。

Alter index ------ rebuild tablespace -----。
這個命令的執行步驟如下：
首先，逐一讀取現有索引，以獲取索引的關鍵字。
其次，按新的結構填寫臨時資料段。
最後，一旦操作成功，刪除原有索引樹，降臨時資料段重新命名為新的索引。
需要注意的是alter index ---rebuild 命令中必須使用tablespace字句，以保證重建工作是在現有索引相同的表空間進行

alter index ----- coalesce 使用帶有coalesce引數時重建期間不需要額外空間，它只是在重建索引時將處於同一個索引分支內的葉塊拼合起來，這最大限度的減少了與查詢過程中相關的潛在的加鎖問題，但是，coalesce選項不能用來講一個索引轉移到其他表空間