Oracle新手入門：如何提高索引創(chuàng)建速度？

默認(rèn)情況下，數(shù)據(jù)庫系統(tǒng)是不允許DML操作與創(chuàng)建索引的操作同時進(jìn)行的。也就是說，在創(chuàng)建索引的過程中，是不允許其他用戶對其所涉及的表進(jìn)行任何的DML操作。這主要是因為對基礎(chǔ)表進(jìn)行DML操作時，會對基礎(chǔ)表進(jìn)行加鎖。所以在基礎(chǔ)表上的DDL事務(wù)沒有遞交之前，即沒有對基礎(chǔ)表進(jìn)行解鎖之前，是無法對這基礎(chǔ)表創(chuàng)建索引的。反之亦然。顯然此時數(shù)據(jù)庫沒有采用這個ONLIE選項，繼之DML操作與創(chuàng)建索引操作同時進(jìn)行，主要是從創(chuàng)建索引的效率出發(fā)的。防止因為兩個作業(yè)相互沖突，從而延長某個作業(yè)的運行時間。 但是有時會我們必須允許他們進(jìn)行同時操作。如用戶可能一刻都不能夠離開數(shù)據(jù)庫系統(tǒng)，需要時時刻刻對數(shù)據(jù)庫基礎(chǔ)表進(jìn)行DML操作。而此時由于某些原因，數(shù)據(jù)庫管理員又需要重新建立索引時，那么不得不在創(chuàng)建索引的語句中加入這個ONLINE選項。讓他們同時運行。此時雖然可能會延長索引創(chuàng)建作業(yè)的時間，但是可以保障用戶DML操作能夠正常進(jìn)行。有時候犧牲這個代價是值得的。用戶是不能夠等的，而我們數(shù)據(jù)庫管理員則可以勉強(qiáng)的等一會兒。 當(dāng)然，如果用戶對于這個DML操作及時性沒有這么高。如數(shù)據(jù)庫管理員在晚上員工沒有使用數(shù)據(jù)庫時創(chuàng)建索引時，則可以不帶這個選項。在限制用戶對基礎(chǔ)表進(jìn)行DML操作的同時，提高數(shù)據(jù)庫創(chuàng)建索引的效率。 可選項五：PARALLEL，多服務(wù)進(jìn)程創(chuàng)建索引 默認(rèn)情況下，Oracle數(shù)據(jù)庫系統(tǒng)不采用這個選項。這并不是說這個選項不可用，而是因為大多數(shù)情況下企業(yè)部署Oracle數(shù)據(jù)庫時所采用的數(shù)據(jù)庫服務(wù)器往往只有單個CPU。此時數(shù)據(jù)庫系統(tǒng)是用一個服務(wù)進(jìn)程來創(chuàng)建索引的。 如果企業(yè)的服務(wù)器有多個CPU的話，則可以在創(chuàng)建索引時采用這個選項。因為只要采用了這個選項，則數(shù)據(jù)庫就會使用多個服務(wù)進(jìn)程來并行的創(chuàng)建索引，以提高索引創(chuàng)建的速度。為此，在同等條件下，多服務(wù)并行創(chuàng)建進(jìn)索引并單服務(wù)創(chuàng)建索引速度要快的多。所以如果服務(wù)器中有多個CPU，而且需要創(chuàng)建的索引比較多或者基礎(chǔ)表中記錄比較多的話，則采用這個選項能夠大幅度的提高索引的創(chuàng)建效率。 故筆者建議，如果采用多CPU的服務(wù)器時，最好在創(chuàng)建索引時使用這個選項。不能夠浪費了服務(wù)器的CPU呀。不然的話，多CPU服務(wù)器的優(yōu)勢就體現(xiàn)不出來了。為此采用這個選項，也是物盡其用。希望本文講到的內(nèi)容對大家能有所幫助。

創(chuàng)新互聯(lián)建站是專業(yè)的拱墅網(wǎng)站建設(shè)公司，拱墅接單;提供成都網(wǎng)站建設(shè)、成都網(wǎng)站制作,網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行拱墅網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊,希望更多企業(yè)前來合作!

Oracle數(shù)據(jù)訪問和索引的使用

· 通過全表掃描的方式訪問數(shù)據(jù)；

· 通過ROWID訪問數(shù)據(jù)；

· 通過索引的方式訪問數(shù)據(jù)；

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

· 采用多塊讀的方式進(jìn)行全表掃描，可以有效提高系統(tǒng)的吞吐量，降低I/O次數(shù)。

· 即使創(chuàng)建索引，Oracle也會根據(jù)CBO的計算結(jié)果，決定是否使用索引。

注意事項：

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

· 對于數(shù)據(jù)量較大的表，不建議使用全表掃描進(jìn)行訪問。

· 當(dāng)訪問表中的數(shù)據(jù)量超過數(shù)據(jù)總量的5%—10%時，通常Oracle會采用全表掃描的方式進(jìn)行訪問。

· 并行查詢可能會導(dǎo)致優(yōu)化器選擇全表掃描的方式。1.2ROWID訪問表

· Rowid是數(shù)據(jù)存放在數(shù)據(jù)庫中的物理地址，能夠唯一標(biāo)識表中的一條數(shù)據(jù)。

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

注意事項：

· Rowid作為一個偽列，其數(shù)值并不存儲在數(shù)據(jù)庫中，當(dāng)查詢時才進(jìn)行計算。

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

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

· 一個Rowid對應(yīng)一條數(shù)據(jù)行（根據(jù)Rowid查找結(jié)果，僅需要對Rowid相應(yīng)數(shù)據(jù)的數(shù)據(jù)塊進(jìn)行一次I/O操作），因此該方式屬于“單塊讀”。

· 對于索引，除了存儲索引的數(shù)據(jù)外，還保存有該數(shù)據(jù)對應(yīng)的Rowid信息。

· 索引掃描分為兩步：1）掃描索引確定相應(yīng)的Rowid信息。 2）根據(jù)Rowid從表中獲得對應(yīng)的數(shù)據(jù)。

注意事項：

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

· 全表掃描的效率不一定比索引掃描差，關(guān)鍵看數(shù)據(jù)在數(shù)據(jù)塊上的具體分布。

索引是關(guān)系數(shù)據(jù)庫中用于存放每一條記錄的一種對象，主要目的是加快數(shù)據(jù)的讀取速度和完整性檢查。建立索引是一項技術(shù)性要求高的工作。一般在數(shù)據(jù)庫設(shè)計階段的與數(shù)據(jù)庫結(jié)構(gòu)一道考慮。應(yīng)用系統(tǒng)的性能直接與索引的合理直接有關(guān)。

(1) 單列索引

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

(2) 復(fù)合索引

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

(1) 重命名索引

(2) 合并索引

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

(3) 重建索引

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

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

例如：

注：當(dāng)表結(jié)構(gòu)被刪除時，有其相關(guān)的所有索引也隨之被刪除。

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

· 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

· 索引可以大大加快數(shù)據(jù)的檢索速度，這是創(chuàng)建索引的最主要的原因。

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

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

· 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

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

· 創(chuàng)建索引和維護(hù)索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。

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

· 當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時候，索引也要動態(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

· 更新數(shù)據(jù)的時候，系統(tǒng)必須要有額外的時間來同時對索引進(jìn)行更新，以維持?jǐn)?shù)據(jù)和索引的一致性。

1) 不恰當(dāng)?shù)乃饕坏谑聼o補(bǔ)，反而會降低系統(tǒng)性能。因為大量的索引在進(jìn)行插入、修改和刪除操作時比沒有索引花費更多的系統(tǒng)時間。

1) 應(yīng)該建索引的列

· 在經(jīng)常需要搜索的列上，可以加快搜索的速度；

· 在作為主鍵的列上，強(qiáng)制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)；

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

· 在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引，因為索引已經(jīng)排序，其指定的范圍是連續(xù)的；

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

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

2) 不應(yīng)該建索引的列

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

· 對于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引。

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

· 對于那些定義為blob數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。

· 當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時，不應(yīng)該創(chuàng)建索引。

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

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

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

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

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

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

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

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

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

(1) 索引無效

(2) 索引有效

oracle 如何使用索引，更快

索引不是一句兩句能講清楚的

select * from test1 t where a in (select a from test2);

test1 大 ,test2 小，用in快