創(chuàng)建索引選擇合適的可選項(xiàng)
導(dǎo)讀：有些新手在Oracle數(shù)據(jù)庫中創(chuàng)建索引時(shí)往往不會(huì)使用可選項(xiàng)。其實(shí)，有時(shí)候在合適的場合使用一些可選項(xiàng)，可以提高索引的創(chuàng)建速度。如為了大批量導(dǎo)入數(shù)據(jù)，我們往往會(huì)先取消索引其以提高插入的速度。然后等數(shù)據(jù)導(dǎo)入完畢后再重新創(chuàng)建索引。在這個(gè)過程中如果能夠采用一些可選項(xiàng)，則可以縮短索引創(chuàng)建的時(shí)間。在Oracle數(shù)據(jù)庫中提供了豐富的可選項(xiàng)。我們常用的可選項(xiàng)主要有以下這些。
可選項(xiàng)一：NOSORT，記錄排序可選項(xiàng)
默認(rèn)情況下，在表中創(chuàng)建索引的時(shí)候，會(huì)對(duì)表中的記錄進(jìn)行排序，排序成功后再創(chuàng)建索引。但是當(dāng)記錄比較多的是，這個(gè)排序作業(yè)會(huì)占用比較多的時(shí)間，這也就增加了索引建立的時(shí)間(排序作業(yè)是在索引創(chuàng)建作業(yè)中完成)。有時(shí)候，我們導(dǎo)入數(shù)據(jù)的時(shí)候，如采用insert into 語句插入數(shù)據(jù)過程中同時(shí)采用Order by子句對(duì)索引字段進(jìn)行了排序。此時(shí)如果在索引創(chuàng)建過程中再進(jìn)行排序的話，就有點(diǎn)脫褲子放屁，多此一舉了。為此在重新創(chuàng)建索引時(shí)，如果表中的數(shù)據(jù)已經(jīng)排好序了(按索引字段排序)，那么在創(chuàng)建索引時(shí)就不需要為此重新排序。此時(shí)在創(chuàng)建索引時(shí)，數(shù)據(jù)庫管理員就可以使用NOSORT可選項(xiàng)，告訴數(shù)據(jù)庫系統(tǒng)不需要對(duì)表中當(dāng)記錄進(jìn)行重新排序了。
采用了這個(gè)選項(xiàng)之后，如果表中的記錄已經(jīng)按順序排列，那么在重新創(chuàng)建索引的時(shí)候，就不會(huì)重新排序，可以提高索引創(chuàng)建的時(shí)間，節(jié)省內(nèi)存中的排序緩存空間。相反，如果表中的記錄是不按索引關(guān)鍵字排序的話，那么此時(shí)采用NOSORT關(guān)鍵字的話，系統(tǒng)就會(huì)提示錯(cuò)誤信息，并拒絕創(chuàng)建索引。所以在使用NOSORT可選項(xiàng)的時(shí)候，數(shù)據(jù)庫管理員盡管放心大膽的使用。因?yàn)槠鋵?shí)在不能夠使用這個(gè)選項(xiàng)的時(shí)候，數(shù)據(jù)庫也會(huì)明確的告知。為此其副作用就比較少，數(shù)據(jù)庫管理員只需要把這個(gè)可選項(xiàng)去掉然后重新執(zhí)行一次即可。不過這里需要注意的是，如果表中的記錄比較少的話，那么使用NOSORT選項(xiàng)的效果并不是很明顯。當(dāng)采用insert into批量導(dǎo)入數(shù)據(jù)，并在這個(gè)過程中采用了Order by子句對(duì)索引關(guān)鍵字進(jìn)行了排序的話，則此時(shí)采用NOSORT選項(xiàng)的話，往往能夠起到比較好的效果。
可選項(xiàng)二：NOLOGGING，是否需要記錄日志信息
在創(chuàng)建索引的時(shí)候，系統(tǒng)會(huì)把相關(guān)的信息存儲(chǔ)到日志信息中去。如果表中的記錄比較多，則需要一一的把這些信息記錄到日志文件中，這顯然會(huì)讓數(shù)據(jù)庫增加很大的工作量。從而增加索引創(chuàng)建的時(shí)間。為此在創(chuàng)建索引的過程中，如果有必要時(shí)，我們可以采用NOLOGGING選項(xiàng)，讓數(shù)據(jù)庫在創(chuàng)建索引的過程中，不產(chǎn)生任何重做日志信息。此時(shí)當(dāng)表中的記錄比較多時(shí)，就可以明顯提高速度。
但是默認(rèn)情況下，數(shù)據(jù)庫在在創(chuàng)建索引時(shí)，是不采用這個(gè)選項(xiàng)的，即會(huì)把相關(guān)的信息保存到重做日志中去。這雖然降低了索引創(chuàng)建的效率，但是如果遇到什么意外的話，卻可以利用重做日志來進(jìn)行恢復(fù)。所以，此時(shí)數(shù)據(jù)庫管理員就比較難以抉擇了。一方面是數(shù)據(jù)的安全，另一方面是索引創(chuàng)建的速度。根據(jù)筆者的經(jīng)驗(yàn)，只要數(shù)據(jù)庫服務(wù)器比較穩(wěn)定，而數(shù)據(jù)庫中約束機(jī)制又比較完善的話，那么在創(chuàng)建索引的過程中一般不會(huì)出現(xiàn)問題，可以放心大膽的使用這個(gè)可選項(xiàng)。
但是如果數(shù)據(jù)庫已經(jīng)使用了好幾年了。后來因?yàn)槟撤N原因需要重建索引。在這種情況下，由于數(shù)據(jù)庫使用過程中很多因素?cái)?shù)據(jù)庫管理員無法控制。此時(shí)為這種類型的數(shù)據(jù)庫創(chuàng)建索引時(shí)，為了保險(xiǎn)起見還是不要采用這個(gè)選項(xiàng)好。因?yàn)榇藭r(shí)遇到錯(cuò)誤的幾率相對(duì)來說會(huì)搞一點(diǎn)。為此此時(shí)犧牲一下索引創(chuàng)建的速率，而提高數(shù)據(jù)的安全性還是有必要的。萬一遇到什么問題時(shí)，可以通過重做日志來及時(shí)的恢復(fù)數(shù)據(jù)，為企業(yè)用戶減少損失。
可選項(xiàng)三：COMPUTE STATISTICS，是否生成統(tǒng)計(jì)信息
如果管理員在創(chuàng)建索引時(shí)采用了這個(gè)選項(xiàng)，則數(shù)據(jù)庫將在創(chuàng)建索引的過程中以非常小的代價(jià)直接生成關(guān)于索引的相關(guān)統(tǒng)計(jì)信息，然后把這些信息存儲(chǔ)在數(shù)據(jù)字典中。這就可以避免以后對(duì)索引進(jìn)行分析統(tǒng)計(jì)，而且優(yōu)化器在優(yōu)化SQL語句的時(shí)候可以隨機(jī)使用這些統(tǒng)計(jì)信息，以確定是否生成使用該索引的執(zhí)行計(jì)劃。通常情況下，在生成索引的過程中統(tǒng)計(jì)索引的相關(guān)信息，其所花的代價(jià)是最小的。無論從時(shí)間上，還是從硬件資源的耗費(fèi)上，都是非常小的。所以，在創(chuàng)建索引的過程中統(tǒng)計(jì)相關(guān)的索引信息是非常有用的。
但是默認(rèn)情況下，數(shù)據(jù)庫是不采用這個(gè)選項(xiàng)的。這主要是因?yàn)橐恍┦挛锾幚硐到y(tǒng)，索引的信息是經(jīng)常需要發(fā)生變化的。如果在索引創(chuàng)建的過程中統(tǒng)計(jì)了相關(guān)信息。這些信息隨著索引的調(diào)整等等原因會(huì)很快的過時(shí)。所以說，其在默認(rèn)情況下沒有采用這個(gè)選項(xiàng)。可見這個(gè)選項(xiàng)并不是在任何情況下都能夠起到效果。但是如果這個(gè)數(shù)據(jù)庫系統(tǒng)是一個(gè)決策支持系統(tǒng)。其數(shù)據(jù)、索引等等在一段時(shí)間內(nèi)基本上是穩(wěn)定不變的。此時(shí)在創(chuàng)建索引時(shí)可以使用這個(gè)選項(xiàng)。如此的話，在生成索引時(shí)可以以最小的代價(jià)生成這些統(tǒng)計(jì)信息，方便優(yōu)化器使用。筆者在部署數(shù)據(jù)庫應(yīng)用的時(shí)候，對(duì)于事務(wù)型的數(shù)據(jù)庫系統(tǒng)，一般不會(huì)啟用這個(gè)選項(xiàng)。但是對(duì)于一些決策性的數(shù)據(jù)庫系統(tǒng)或者數(shù)據(jù)倉庫中，創(chuàng)建索引時(shí)則筆者喜歡采用這個(gè)選項(xiàng)。這有助于提高數(shù)據(jù)庫的性能。因?yàn)閮?yōu)化器在生成執(zhí)行計(jì)劃時(shí)，可以直接采用這個(gè)統(tǒng)計(jì)信息。所以，數(shù)據(jù)庫能夠在最短的時(shí)間內(nèi)確定需要采用的執(zhí)行計(jì)劃。而且在執(zhí)行計(jì)劃制定中參考了這個(gè)索引統(tǒng)計(jì)信息，為此所生成的執(zhí)行計(jì)劃在同等條件下可能更加的合理。
可選項(xiàng)四：ONLINE，DML操作與創(chuàng)建索引操作是否可以同時(shí)進(jìn)行
默認(rèn)情況下，數(shù)據(jù)庫系統(tǒng)是不允許DML操作與創(chuàng)建索引的操作同時(shí)進(jìn)行的。也就是說，在創(chuàng)建索引的過程中，是不允許其他用戶對(duì)其所涉及的表進(jìn)行任何的DML操作。這主要是因?yàn)閷?duì)基礎(chǔ)表進(jìn)行DML操作時(shí)，會(huì)對(duì)基礎(chǔ)表進(jìn)行加鎖。所以在基礎(chǔ)表上的DDL事務(wù)沒有遞交之前，即沒有對(duì)基礎(chǔ)表進(jìn)行解鎖之前，是無法對(duì)這基礎(chǔ)表創(chuàng)建索引的。反之亦然。顯然此時(shí)數(shù)據(jù)庫沒有采用這個(gè)ONLIE選項(xiàng)，繼之DML操作與創(chuàng)建索引操作同時(shí)進(jìn)行，主要是從創(chuàng)建索引的效率出發(fā)的。防止因?yàn)閮蓚€(gè)作業(yè)相互沖突，從而延長某個(gè)作業(yè)的運(yùn)行時(shí)間。
但是有時(shí)會(huì)我們必須允許他們進(jìn)行同時(shí)操作。如用戶可能一刻都不能夠離開數(shù)據(jù)庫系統(tǒng)，需要時(shí)時(shí)刻刻對(duì)數(shù)據(jù)庫基礎(chǔ)表進(jìn)行DML操作。而此時(shí)由于某些原因，數(shù)據(jù)庫管理員又需要重新建立索引時(shí)，那么不得不在創(chuàng)建索引的語句中加入這個(gè)ONLINE選項(xiàng)。讓他們同時(shí)運(yùn)行。此時(shí)雖然可能會(huì)延長索引創(chuàng)建作業(yè)的時(shí)間，但是可以保障用戶DML操作能夠正常進(jìn)行。有時(shí)候犧牲這個(gè)代價(jià)是值得的。用戶是不能夠等的，而我們數(shù)據(jù)庫管理員則可以勉強(qiáng)的等一會(huì)兒。
當(dāng)然，如果用戶對(duì)于這個(gè)DML操作及時(shí)性沒有這么高。如數(shù)據(jù)庫管理員在晚上員工沒有使用數(shù)據(jù)庫時(shí)創(chuàng)建索引時(shí)，則可以不帶這個(gè)選項(xiàng)。在限制用戶對(duì)基礎(chǔ)表進(jìn)行DML操作的同時(shí)，提高數(shù)據(jù)庫創(chuàng)建索引的效率。
可選項(xiàng)五：PARALLEL，多服務(wù)進(jìn)程創(chuàng)建索引
默認(rèn)情況下，Oracle數(shù)據(jù)庫系統(tǒng)不采用這個(gè)選項(xiàng)。這并不是說這個(gè)選項(xiàng)不可用，而是因?yàn)榇蠖鄶?shù)情況下企業(yè)部署Oracle數(shù)據(jù)庫時(shí)所采用的數(shù)據(jù)庫服務(wù)器往往只有單個(gè)CPU。此時(shí)數(shù)據(jù)庫系統(tǒng)是用一個(gè)服務(wù)進(jìn)程來創(chuàng)建索引的。
如果企業(yè)的服務(wù)器有多個(gè)CPU的話，則可以在創(chuàng)建索引時(shí)采用這個(gè)選項(xiàng)。因?yàn)橹灰捎昧诉@個(gè)選項(xiàng)，則數(shù)據(jù)庫就會(huì)使用多個(gè)服務(wù)進(jìn)程來并行的創(chuàng)建索引，以提高索引創(chuàng)建的速度。為此，在同等條件下，多服務(wù)并行創(chuàng)建進(jìn)索引并單服務(wù)創(chuàng)建索引速度要快的多。所以如果服務(wù)器中有多個(gè)CPU，而且需要?jiǎng)?chuàng)建的索引比較多或者基礎(chǔ)表中記錄比較多的話，則采用這個(gè)選項(xiàng)能夠大幅度的提高索引的創(chuàng)建效率。
故筆者建議，如果采用多CPU的服務(wù)器時(shí)，最好在創(chuàng)建索引時(shí)使用這個(gè)選項(xiàng)。不能夠浪費(fèi)了服務(wù)器的CPU呀。不然的話，多CPU服務(wù)器的優(yōu)勢(shì)就體現(xiàn)不出來了。為此采用這個(gè)選項(xiàng)，也是物盡其用。希望本文講到的內(nèi)容對(duì)大家能有所幫助。
【編輯推薦】
