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

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

創(chuàng)新互聯(lián)公司從2013年成立，先為美蘭等服務建站，美蘭等地企業(yè)，進行企業(yè)商務咨詢服務。為美蘭企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務解決您的所有建站問題。

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

分類：數(shù)據(jù)庫技術 字號： 大大中中小小 索引什么時候需要重建和重建的方法

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

索引的目的是為了加快尋找數(shù)據(jù)的速度，但是如果對表經(jīng)常做改動，則索引也會相應改動，時間長了，查詢速度的效率就會降低，就有可能要重建索引，那么什么時候需要重建索引和用什么方法重建索引可能是大家關心的。

一. 索引在內(nèi)部進行自身的管理以確保對數(shù)據(jù)行的快速訪問。但是數(shù)據(jù)表中大量的活動會導致oracle索引動態(tài)地對自身的進行重新配置，這些配置包括三個方面：

1．索引分割

當新數(shù)據(jù)行產(chǎn)生的索引節(jié)點要建立在現(xiàn)有級別上時，出現(xiàn)此動作。

2．索引生成

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

3．索引節(jié)點的刪除

你可能了解到，刪除表中的數(shù)據(jù)行后，索引中相應的節(jié)點不會從物理意義上刪除，也沒有從索引中刪除此項目。而是從邏輯上刪除此索引項目，并在索引樹中留下了一個“死“節(jié)點，當索引刪除了葉節(jié)點或是生成了過深的的級別層次后，就需要進行重建。

二 索引的種類：

a.B-tree（B樹）索引

b.壓縮B樹索引

c.Bitmap（位圖）索引

d.函數(shù)索引

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

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

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

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

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

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

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

索引高度：索引高度是指由于數(shù)據(jù)行的插入操作而產(chǎn)生的索引層數(shù)，當表中添加大量數(shù)據(jù)時，oracle將生成索引的新層次以適應加入的數(shù)據(jù)行，因此， oracle索引可能有4層，但是這只會出現(xiàn)在索引數(shù)中產(chǎn)生大量插入操作的區(qū)域。Oracle索引的三層結構可以支持數(shù)百萬的項目，而具備4層或是更多層的需要重建。

每次索引訪問的讀取數(shù)：是指利用索引讀取一數(shù)據(jù)行時所需要的邏輯I/O操作數(shù)，邏輯讀取不必是物理讀取，因為索引的許多內(nèi)容已經(jīng)保存在數(shù)據(jù)緩沖區(qū)，然而，任何數(shù)據(jù)大于10的索引都需要重建。

1. B-tree（B樹）索引

是現(xiàn)代關系型數(shù)據(jù)庫中最常用的索引。除了存儲索引數(shù)據(jù)外，還存儲一個行ID，用來指出該行其余數(shù)據(jù)存儲在這個被索引表中的什么地方。該索引以一種數(shù)結構格式存儲這些值。

Oracle建議如果表經(jīng)過排序，當返回40%一下的數(shù)據(jù)時使用索引，如果高于40%則使用全表掃描，如果沒有經(jīng)過排序，則當返回7%以下時，使用索引?？幢硎欠衽判?，可以看dba_indexes字典中的CLUSTERING_FACTOR列，如果與表占用的數(shù)據(jù)塊數(shù)相近，則經(jīng)過了排序，如果與行數(shù)相近，則沒有排序。那么什么時候重建呢？我們可以利用analyze index …….. compute statistics 對表進行分析。然后察看dba_indexes中的blevel。這列是說明索引從根塊到葉快的級別，或是深度。如果級別大于等于4。則需要重建，如下：

Select index_name,blevel from dba_indexeswhere blevel=4.

另一個從重建中受益的指標顯然是當該索引中的被刪除項占總的項數(shù)的百分比。如果在20%以上時，也應當重建，如下

SQLanlyze index ------ validatestructure

SQLselect(del_lf_rows_len/lf_rows_len)*100 from index_stats where 刪除并從頭開始建立索引。

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

Alter index ------ rebuild tablespace -----。

這個命令的執(zhí)行步驟如下：

首先，逐一讀取現(xiàn)有索引，以獲取索引的關鍵字。

其次，按新的結構填寫臨時數(shù)據(jù)段。

最后，一旦操作成功，刪除原有索引樹，降臨時數(shù)據(jù)段重命名為新的索引。

需要注意的是alterindex ---rebuild 命令中必須使用tablespace字句，以保證重建工作是在現(xiàn)有索引相同的表空間進行。

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

2．壓縮B樹索引

當B樹索引基于大表時，尤其是當基于數(shù)據(jù)倉庫或決策支持系統(tǒng)中的大表時，這些索引會耗費大量的存儲空間，壓縮（compressed）B樹索引用來最大限度的減少某些類型的B樹索引使用的空間。當一個B樹索引得到壓縮時，被索引的獵的重復出現(xiàn)就被消除掉，進而減少了存儲索引的總的存儲空間。例如：

壓縮前：smith每次出現(xiàn)還要存儲它的相關的rowid.

姓 關聯(lián)rowid

smith AAABSOAAEAAAABTAAB

smith AAABSOAAEAAAABTAAC

smith AAABSOAAEAAAABTAAD

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

smith AAABSOAAEAAAABTAAB,AAABSOAAEAAAABTAAB, AAABSOAAEAAAABTAAB

創(chuàng)建方法：

SQLcreate index index_name ontable_name(column_name)

tablespace tablespace_name

compress;

另一種方法：

SQLalter index index_name rebuildcompress;

3． itmap(位圖)索引。

B樹索引在數(shù)據(jù)具有高基數(shù)的列工作的最好，對于低基數(shù)的列，位圖索引可能是更有效的選擇。位圖索引創(chuàng)建表行的一個二進制映像，并把映像存儲在索引塊中，這種類型的索引的DML操作少，長度大并且含有極少不同的值得列特別有用。位圖索引不應當用在頻繁發(fā)生insert,update,delete操作的表上，這些dml操作在性能方面的代價很高，因為，他們會引起位圖級的加鎖發(fā)生，而且要求動態(tài)的重建所有可能值的位圖。為圖索引最適合數(shù)據(jù)倉庫和決策支持系統(tǒng)。

4．基于函數(shù)的索引

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

5．反向鍵索引

是一種特殊類型的B樹索引，在索引基于含有序數(shù)的列時使非常有用的，如果一個傳統(tǒng)的B樹索引基于一個含有這種數(shù)據(jù)的列，往往會產(chǎn)生許多級，由于B樹索引有 4級以上的深度會降低性能，因此反向鍵索引更適合這種類型，反向鍵索引通過簡單的煩象被索引的列中的數(shù)據(jù)來解決問題，他首先反向每個列鍵值的字節(jié)，然后在反向后的新數(shù)據(jù)上進行索引，而新數(shù)據(jù)在值的范圍上的分布通常比原來的有序數(shù)更均勻。

6．索引組織表

由于B樹、位圖、反向鍵索引的使用而引起的性能將會導致這樣的事實，這些索引中的項目直接指向索引基表中對應數(shù)據(jù)的行ID,這是從表行沒有按任何特定的順序來物理地存儲表中檢索表行的一種有效方法，這種表叫做堆表，oracle大多數(shù)表中以一種堆疊方式存儲行數(shù)據(jù)，因為行以一種或多或少的隨機方式被分配給表內(nèi)的塊，之所以出現(xiàn)這種隨機性，是因為oracle在決定把一個行存儲在何處時并不考慮改行的內(nèi)容，oracle只是把該行存儲在它從該表的freelist 上所發(fā)現(xiàn)的第一個塊中。

如果希望按一種指定順序來存儲一個表的數(shù)據(jù)，就不能使用堆表，為此oracle提供了索引組織表，索引組織表不是存儲一個指向行數(shù)據(jù)的其余部分存儲在了何處的行的ID指針，而是把行數(shù)據(jù)全部存儲在索引本身內(nèi)，這產(chǎn)生了兩個性能好處：

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

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

例如：

sqlcreate 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;

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

下面說明各個參數(shù)的含義：

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

pctthreshold :指定整個數(shù)據(jù)塊的什么百分比要保持打開，以便存儲一個與主鍵值相關聯(lián)的行數(shù)據(jù)，其中主鍵值必須在0到50之間（50是默認值）

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

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

Mapping table:致使在創(chuàng)建索引組織表的位圖索引時所必需的一個關聯(lián)映像表的創(chuàng)建。

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

參考資料：ocp困惑racle9i性能調(diào)整

oracle statspack 高性能調(diào)整技術

[@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 -----。

這個命令的執(zhí)行步驟如下：

首先，逐一讀取現(xiàn)有索引，以獲取索引的關鍵字。

其次，按新的結構填寫臨時數(shù)據(jù)段。

最后，一旦操作成功，刪除原有索引樹，降臨時數(shù)據(jù)段重命名為新的索引。

需要注意的是alter index ---rebuild 命令中必須使用tablespace字句，以保證重建工作是在現(xiàn)有索引相同的表空間進行

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

oracle數(shù)據(jù)庫如何重建索引

用rebuile語句即可啊

Alter?indexindex_name?rebuild;

Alter?indexindex_name?rebuild?online;

也可以把索引刪除了重新建立

drop?index?indexindex_name;

create?index?indexindex_name?on?table_name(col_name);

如何讓Oracle表的索引暫時失效？然后恢復有效

如何讓Oracle表的索引暫時失效？然后恢復有效

這種現(xiàn)象多出現(xiàn)在分區(qū)表，之后創(chuàng)建和刪除分區(qū)的時候引起全局索引失效，這個一般的解決方法是重現(xiàn)創(chuàng)建索引，還有一種是把全局索引創(chuàng)建成local的就可以了。這個您可以上網(wǎng)查查全局索引和local索引的區(qū)別。

oracle索引何時失效的相關推薦

容易引起oracle索引失效的原因很多：

1、在索引列上使用函數(shù)。如SUBSTR,DECODE,INSTR等，對索引列進行運算.需要建立函數(shù)索引就可以解決了。

2、新建的表還沒來得及生成統(tǒng)計信息，分析一下就好了

3、基于cost的成本分析，訪問的表過小，使用全表掃描的消耗小于使用索引。

4、使用、not in 、not exist，對于這三種情況大多數(shù)情況下認為結果集很大，一般大于5%-15%就不走索引而走FTS。

5、單獨的、。

6、like "%_" 百分號在前。

7、單獨引用復合索引里非第一位置的索引列。

8、字符型字段為數(shù)字時在where條件里不添加引號。

9、當變量采用的是times變量，而表的字段采用的是date變量時.或相反情況。

10、索引失效，可以考慮重建索引，rebuild online。

11、B-tree索引 is null不會走,is not null會走,位圖索引 is null,is not null 都會走、聯(lián)合索引 is not null 只要在建立的索引列（不分先后）都會走。

ORACLE 索引失效問題求助

隨著表的增長，where條件出來的數(shù)據(jù)太多，大于15%，使得索引失效（會導致CBO計算走索引花費大于走全表）

統(tǒng)計信息失效????? 需要重新搜集統(tǒng)計信息

--重新搜集統(tǒng)計信息:Analyze table tablename compute statistics;

3.?索引本身失效????? 需要重建索引

--重建索引:ALTER INDEX? IDX_TEST_C1 REBUILD;

索引失效一般有以上幾個原因:

網(wǎng)站欄目：oracle索引失效如何重建,oracle 索引重建
文章出自：http://jinyejixie.com/article28/dsdgjjp.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供自適應網(wǎng)站、域名注冊、外貿(mào)建站、網(wǎng)站營銷、微信公眾號、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)