Oracle數據訪問和索引的使用

· 通過全表掃描的方式訪問數據；

成都創新互聯公司是一家專業的成都網站建設公司，我們專注網站制作、網站設計、網絡營銷、企業網站建設，外鏈，1元廣告為企業客戶提供一站式建站解決方案，能帶給客戶新的互聯網理念。從網站結構的規劃UI設計到用戶體驗提高，創新互聯力求做到盡善盡美。

· 通過ROWID訪問數據；

· 通過索引的方式訪問數據；

· Oracle順序讀取表中所有的行，并逐條匹配WHERE限定條件。

· 采用多塊讀的方式進行全表掃描，可以有效提高系統的吞吐量，降低I/O次數。

· 即使創建索引，Oracle也會根據CBO的計算結果，決定是否使用索引。

注意事項：

· 只有全表掃描時才可以使用多塊讀。該方式下，單個數據塊僅訪問一次。

· 對于數據量較大的表，不建議使用全表掃描進行訪問。

· 當訪問表中的數據量超過數據總量的5%—10%時，通常Oracle會采用全表掃描的方式進行訪問。

· 并行查詢可能會導致優化器選擇全表掃描的方式。1.2ROWID訪問表

· Rowid是數據存放在數據庫中的物理地址，能夠唯一標識表中的一條數據。

· Rowid指出了一條記錄所在的數據文件、塊號以及行號的位置，因此通過ROWID定位單行數據是最快的方法。

注意事項：

· Rowid作為一個偽列，其數值并不存儲在數據庫中，當查詢時才進行計算。

· Rowid除了在同一集簇中可能不唯一外，每條記錄的Rowid唯一。1.3 INDEX訪問表

· 通過索引查找相應數據行的Rowid，再根據Rowid查找表中實際數據的方式稱為“索引查找”或者“索引掃描”。

· 一個Rowid對應一條數據行（根據Rowid查找結果，僅需要對Rowid相應數據的數據塊進行一次I/O操作），因此該方式屬于“單塊讀”。

· 對于索引，除了存儲索引的數據外，還保存有該數據對應的Rowid信息。

· 索引掃描分為兩步：1）掃描索引確定相應的Rowid信息。 2）根據Rowid從表中獲得對應的數據。

注意事項：

· 對于選擇性高的數據行，索引的使用會提升查詢的性能。但對于DML操作，尤其是批量數據的操作，可能會導致性能的降低。

· 全表掃描的效率不一定比索引掃描差，關鍵看數據在數據塊上的具體分布。

索引是關系數據庫中用于存放每一條記錄的一種對象，主要目的是加快數據的讀取速度和完整性檢查。建立索引是一項技術性要求高的工作。一般在數據庫設計階段的與數據庫結構一道考慮。應用系統的性能直接與索引的合理直接有關。

(1) 單列索引

單列索引是基于單個列所建立的索引。

(2) 復合索引

復合索引是基于兩列或是多列的索引，在同一張表上可以有多個索引，但是要求列的組合必須不同。

(1) 重命名索引

(2) 合并索引

（表使用一段時間后在索引中會產生碎片，此時索引效率會降低，可以選擇重建索引或者合并索引,合并索引方式更好些，無需額外存儲空間，代價較低）

(3) 重建索引

方式一：刪除原來的索引，重新建立索引

當不需要時可以將索引刪除以釋放出硬盤空間。命令如下：

例如：

注：當表結構被刪除時，有其相關的所有索引也隨之被刪除。

方式二： Alter index 索引名稱 rebuild;

· 通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

· 索引可以大大加快數據的檢索速度，這是創建索引的最主要的原因。

· 可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

· 在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。

· 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

· 索引的層次不要超過4層。

· 創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。

· 除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。

· 當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。

· 更新數據的時候，系統必須要有額外的時間來同時對索引進行更新，以維持數據和索引的一致性。

1) 不恰當的索引不但于事無補，反而會降低系統性能。因為大量的索引在進行插入、修改和刪除操作時比沒有索引花費更多的系統時間。

1) 應該建索引的列

· 在經常需要搜索的列上，可以加快搜索的速度；

· 在作為主鍵的列上，強制該列的唯一性和組織表中數據的排列結構；

· 在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；

· 在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍是連續的；

· 在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；

· 在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。

2) 不應該建索引的列

· 在大表上建立索引才有意義，小表無意義。

· 對于那些在查詢中很少使用或者參考的列不應該創建索引。

· 對于那些只有很少數據值的列也不應該增加索引。比如性別，在查詢的結果中，結果集的數據行占了表中數據行的很大比例，。增加索引，并不能明顯加快檢索速度。

· 對于那些定義為blob數據類型的列不應該增加索引。這是因為，這些列的數據量要么相當大，要么取值很少。

· 當修改性能遠遠大于檢索性能時，不應該創建索引。

一個表中有幾百萬條數據，對某個字段加了索引，但是查詢時性能并沒有什么提高，這主要可能是oracle的索引限制造成的。Oracle的索引有一些索引限制，在這些索引限制發生的情況下，即使已經加了索引，oracle還是會執行一次全表掃描，查詢的性能不會比不加索引有所提高，反而可能由于數據庫維護索引的系統開銷造成性能更差。

下面的查詢即使在djlx列有索引，查詢語句仍然執行一次全表掃描。

把上面的語句改成如下的查詢語句，這樣，在采用基于規則的優化器而不是基于代價的優化器（更智能）時，將會使用索引。

特別注意：通過把不等于操作符改成OR條件，就可以使用索引，避免全表掃描。

使用IS NULL或IS NOT NULL同樣會限制索引的使用。因此在建表時，把需要索引的列設成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會使用這個索引（除非索引是一個位圖索引）。

如果不使用基于函數的索引，那么在SQL語句的WHERE子句中對存在索引的列使用函數時，會使優化器忽略掉這些索引。 下面的查詢不會使用索引（只要它不是基于函數的索引）

也是比較難于發現的性能問題之一。比如：bdcs_qlr_xz中的zjh是NVARCHAR2類型,在zjh字段上有索引。如果使用下面的語句將執行全表掃描。

因為Oracle會自動把查詢語句改為

特別注意：不匹配的數據類型之間比較會讓Oracle自動限制索引的使用,即便對這個查詢執行Explain Plan也不能讓您明白為什么做了一次“全表掃描”。

(1) 索引無效

(2) 索引有效

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_indexeswhere blevel=4.

另一個從重建中受益的指標顯然是當該索引中的被刪除項占總的項數的百分比。如果在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命令重建索引。

下面討論一下這三種方法的優缺點：

1).刪除并從頭開始建索引：方法是最慢的，最耗時的。一般不建議。

2).Alter index ---- rebuild 快速重建索引的一種有效的辦法，因為使用現有索引項來重建新索引，如果客戶操作時有其他用戶在對這個表操作，盡量使用帶online參數來最大限度的減少索引重建時將會出現的任何加鎖問題，alter index ------- rebuild online.但是,由于新舊索引在建立時同時存在，因此，使用這種技巧則需要有額外的磁盤空間可臨時使用，當索引建完后把老索引刪除，如果沒有成功，也不會影響原來的索引。利用這種辦法可以用來將一個索引以到新的表空間。

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

這個命令的執行步驟如下：

首先，逐一讀取現有索引，以獲取索引的關鍵字。

其次，按新的結構填寫臨時數據段。

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

需要注意的是alterindex ---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

創建方法：

SQLcreate index index_name ontable_name(column_name)

tablespace tablespace_name

compress;

另一種方法：

SQLalter index index_name rebuildcompress;

3． itmap(位圖)索引。

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

4．基于函數的索引

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

5．反向鍵索引

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

6．索引組織表

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

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

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;

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

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

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

高手救我，oracle中如何對頻繁更新的表索引進行維護

你說的慢一半，是在你插入了50萬數據的時候，估計是查詢慢了吧。因為按你說的還沒到刪除的時候呢。查詢慢要看看你的查詢的語句的執行計劃，是否使用了合適的索引。如果索引錯了，那么慢是正常的。可以用hint指定索引，從而保證執行計劃穩定。

對于你這樣的周期性維護的大表，可以做31個分區，每天一個，一個月內循環使用。不需要保留的數據可以以天為單位truncate掉一個或多個分區的數據，索引采用分區索引，truncate分區不影響索引的使用。

有一種猜測，是你的索引建的不對。

你的查詢語句的查詢條件是什么？是單列查詢嗎？如果pwd指password，至少我覺得不會有按password單列查詢的語句吧。

Oracle統計信息（表、索引）更新怎么操作？

A. oracle 提供了收集數據庫統計信息的系統包，例如統計scott用戶信息只需要執行:

exec dbms_stats.gather_schema_stats(

ownname = 'SCOTT',

options = 'GATHER AUTO',

estimate_percent = dbms_stats.auto_sample_size,

method_opt = 'for all columns size repeat',

degree = 15

);

dbms_stat.gather_schema_stats 的參數說請查相關資料，這里就不詳述了。

B. 要定期執行用戶統計信息很簡單，只要在 oracle job 中調用這個過程就可以了，設置好初次調用時間和調用時間間隔即可。

以上的操作都能在plsql中操作。

請教：oracle中如何重新創建索引？

------索引名不能重復，如果要重新建同名索引，必須把以前的索引刪掉在建索引

-----刪除索引

drop index 索引名;

-----創建索引

create index 索引名 on 表名 (表中的字段,表中的字段)

tablespace 指定的表空間

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

oracle數據庫如何重建索引

當索引的碎片過多時，會影響執行查詢的速度，從而影響到我們的工作效率。這時候采取的最有利的措施莫過于重建索引了。本文主要介紹了Oracle數據庫中檢查索引碎片并重建索引的過程，接下來我們就開始介紹這一過程。 重建索引的步驟如下： 1. 確認基本信息 登入數據庫，找到專門存放index 的tablespace，并且這個tablespace下所有index的owner都是tax.將index專門存放在一個獨立的tablespace, 與數據表的tablespace分離，是常用的數據庫設計方法。 2. 查找哪些index需要重建 通過anlyze index .... validate structure命令可以分析單個指定的index，并且將單個index 分析的結果存放到 index_stats試圖下。一般判斷的依據是： height 4 pct_used 50% del_lf_rows / lf_rows +0.001 0.03 g ) 3. google上下載了遍歷所有index腳本 發現anlyze index .... validate structure只能填充單個index分析信息，于是google了下，從網上下了個Loop 腳本，遍歷索引空間下所有的索引名字，并且可以把所有index的分析信息存放到自己建立的一個用戶表中。 4. anlyze index 鎖定index 發現下載的腳本不好用，應為anlyze index在分析索引前要爭取獨占鎖，鎖住index，很明顯有些index正在被應用系統的使用，所以運行anlyze失敗。這里吸取的教訓是，盡量晚上做這種事。但是本人比較喜歡準時回家，所以在語句中添加Exception Handler，拋出anlyze index執行失敗的那些index 名稱，使腳本正常運行完畢。并且根據打印到前臺的index name手動執行那些index分析。 5. 總結 雖然發現522個index中有160個符合上面的判斷的依據。但是發現索引都不大，而那些擁有百萬leaf的索引又沒有符合上面的判斷條件，所以結論是無需index rebuild online. 沒有啥碎片。 6.什么時候可以rebuild index呢? rebuild index online，對那些有大量DML操作的大索引是有益的。可以每個月季度做一次針對較大索引的rebuild。

名稱欄目：oracle怎么刷新索引 怎么更新索引
鏈接分享：http://m.newbst.com/article44/hihjhe.html

成都網站建設公司_創新互聯，為您提供網站收錄、網站導航、網站制作、定制開發、做網站、品牌網站制作

廣告

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