Oracle效能分析8：建立索引，oracle效能索引

Oracle效能分析8：建立索引，oracle效能索引

在建立索引時，我們往往希望能夠預估索引大小，以評估對現有工程環境的影響，我們也希望建立索引的過程能夠最小化的影響我們正在啟動並執行工程環境，並能查看索引的狀況。

預估索引大小

預估索引大小，最好的辦法是在測試環境中建立它，測試環境最好包含完整的工程環境資料，否則只有通過部分資料來推算完整的索引大小。
如果不能搭建測試環境，Oracle提供了預存程序DBMS_SPACE.CREATE_INDEX_COST來估算索引的大小，下面是一個例子：

declare  used_bytes  number(10);  alloc_bytes number(10);begin  dbms_stats.gather_table_stats(user, 'HISTORYALARM');  dbms_space.create_index_cost('create index idx_historyalarm on historyalarm(position1)',                               used_bytes      => used_bytes,                               alloc_bytes     => alloc_bytes);  dbms_output.put_line('used_bytes : ' || used_bytes);  dbms_output.put_line('alloc_bytes : ' || alloc_bytes);end;

在計算索引大小的時候，你需要先收集表的統計資訊，因為Oracle是根據表的資料資訊來推算的，下面是輸出的結果：

used_bytes : 151994511alloc_bytes : 251658240

建立索引

在估算了索引大小後，如果沒有問題，就可以開始實際的建立索引了：

create index idx_historyalarm on historyalarm(position1) tablespace uep4x_fm_index

建立索引的索引會對錶加排他DDL鎖（Exclusive DDL lock），這會防止其他會話得到他們自己的DDL鎖或TM（DML）鎖，也就是在建立索引期間你能夠查詢一個表，但是無法以任何方式修改這個表。這導致建立索引的操作在工程環境中實施時會存在問題，因此，Oracle企業版提供了線上建立索引的方式：

create index idx_historyalarm on historyalarm(position1) tablespace uep4x_fm_index online

online將改變具體建立索引的過程，Oracle不會再加一個排他DDL鎖來防止資料修改，改為試圖得到表上的一個低級（mode 2）TM鎖，這將有效地防止其他DDL（data definition language，包括CREATE、ALTER、DROP等）操作發生，但允許DML（data manipulation language，包括SELECT、UPDATE、INSERT、DELETE）操作正常進行。在Oracle的內部，Oracle會將建立索引期間做的DML操作放到一個暫存資料表中，等建立索引操作完成後，再將DML操作所做的修改同步到新的索引。這樣就有效解決了工程環境中實施的問題。

查看索引資訊

可以通過上面的方式查看建立成功後的索引：

select * from user_indexes where index_name = upper('idx_historyalarm')

可以通過下面的方式顯示所佔用的空間的實際數額：

select bytes from user_segments where segment_name = upper('idx_historyalarm')

下面是輸出結果，空間分配位元組數的估計量略小於實際使用量：

BYTES--------------------------293601280

隨著記錄插入到表中，該索引將增加，對索引大小監控可以確保有足夠的磁碟空間，以適應未來的資料增加需求。

Oracle中表建立聯合索引後，其中的一個欄位是否可以用來進行索引

1、表的主鍵、外鍵必須有索引；
2、資料量超過300的表應該有索引；
3、經常與其他表進行串連的表，在串連欄位上應該建立索引；
4、經常出現在Where子句中的欄位，特別是大表的欄位，應該建立索引；
5、索引應該建在選擇性高的欄位上；
6、索引應該建在小欄位上，對於大的文字欄位甚至超長欄位，不要建索引；
7、複合索引的建立需要進行仔細分析；盡量考慮用單欄位索引代替：
A、正確選擇複合索引中的主欄欄位，一般是選擇性較好的欄位；
B、複合索引的幾個欄位是否經常同時以AND方式出現在Where子句中？單欄位查詢是否極少甚至沒有？如果是，則可以建立複合索引；否則考慮單欄位索引；
C、如果複合索引中包含的欄位經常單獨出現在Where子句中，則分解為多個單欄位索引；
D、如果複合索引所包含的欄位超過3個，那麼仔細考慮其必要性，考慮減少複合的欄位；
E、如果既有單欄位索引，又有這幾個欄位上的複合索引，一般可以刪除複合索引；
8、頻繁進行資料操作的表，不要建立太多的索引；
9、刪除無用的索引，避免對執行計畫造成負面影響；
以上是一些普遍的建立索引時的判斷依據。一言以蔽之，索引的建立必須謹慎，對每個索引的必要性都應該經過仔細分析，要有建立的依據。因為太多的索引與不充分、不正確的索引對效能都毫無益處：在表上建立的每個索引都會增加儲存開銷，索引對於插入、刪除、更新操作也會增加處理上的開銷。另外，過多的複合索引，在有單欄位索引的情況下，一般都是沒有存在價值的；相反，還會降低資料增加刪除時的效能，特別是對頻繁更新的表來說，負面影響更大。

看看這個你就明白了
 
oracle資料庫什情況下建立索引比較好

索引就好象一本字典的目錄。憑藉字典的目錄，我們可以非常迅速的找到我們所需要的條目。資料庫也是如此。憑藉Oracle資料庫的索引，相關語句可以迅速的定位記錄的位置，而不必去定位整個表。
　 　雖然說，在表中是否建立索引，不會影響到Oracle資料庫的使用，也不會影響資料庫語句的使用。這就好像即使字典沒有目錄的話，使用者仍然可以使用它一 樣。可是，若字典沒有目錄，那麼可想而知，使用者要查某個條目的話，其不得不翻遍整本字典。資料庫也是如此。若沒有建立相關索引的話，則資料庫在查詢記錄的 時候，不得不去查詢整個表。當表中的記錄比較多的時候，其查詢效率就會很低。所以，合適的索引，是提高資料庫運行效率的一個很好的工具。
　 　不過，並不是說表上的索引越多越好。過之而不及。故在資料庫設計過程中，還是需要為表選擇一些合適的索引。寧缺勿濫，這是建立索引時的一個遵循標準。在 理論上，雖然一個表可以設定無限的索引。但是，資料庫管理員需要知道，表中的索引越多，維護索引所需要的開銷也就越大。每當資料表中記錄有增加、刪除、更 新變化的時候，資料庫系統都需要對所有索引進行更新。故資料庫表中的索引絕對不是多多益善。具體來說，在索引建立上，筆者對大家有如下建議。
　　建議一：在基數小的欄位上要善於使用位元影像索引。
create bitmap index index_name on table_name (column_name);
　　基數是位元影像索引中的一個基本的定義，它是指資料庫表中某個欄位內容中不重複的數值。如在員工資訊表中的性別欄位，一般就只有男跟女兩個值，所以，其基數為2;婚姻狀況欄位的話，則其只有已婚、未婚、離婚三種狀態，其基數就為3;民族一覽內也是只有有限的幾個值。
　　對於要查詢基數小的欄位，如現在使用者想尋找所有婚姻狀況為已婚的女性時，利用位元影像索引可以提高查詢的效率。這主要是因為標準索引是通過在索引中儲存排序過的索引列以及對應的ROWID來實現的。若我們在基數小的列上建立標準索引的話，則其會返回大量的記錄。
　 　而當我們在建立位元影像索引的時候，在Oracle會對整個表進行掃描，並且會為索引列的每個取值建立一個位元影像。若內容相同，則在位元影像上會以一個相同的數字 表示。此時，若這個欄位的基數比較小的話，則若需要實現對整個欄位的查詢的話，效率就會非常的高。因為此時，資料庫只要位元影像中數字相同的內容找出來即可。
　 　除了在資料表某列基數比較小的情況下，採用位元影像索引外，我們往往在一些特殊的情況下，也會建議採用位元影像索引。最常見的情況是，在Where限制條件中， 若我們多次採用AND或者OR條件時，也建議採用位元影像索引。因為當一個查詢飲用了一些部署了位元影像索引的列的時候，這些位元影像可以很方便的與AND或者Or 運算子操作結合以快速的找出使用者所需要的記錄。
　　但是，這裡要注意，不是在條件陳述式中包含運算子的時候，採用位元影像索引都能夠提供比較高的 效率。一般來說，只有AND 或者OR運算子的時候，位元影像索引才會比較具有優勢。若此時使用者採用大於符號或者不等號作為條件陳述式中的限制條件的時候，則往往採用標準索引具有更大的優 勢。
　　所以，筆者在資料庫設定中，一般只有在三種情況下才採用位元影像索引。一是列的基數比較小，而有可能需要根據這些欄位的內容尋找相關的 記錄;二是在條件陳述式中，用到了AND或者OR運算子的時候。除了這兩種情況外，最好能夠採用其他適合的索引。第三種情況是，需要用到NULL作為查詢的 限制條件。因為標準查詢一般情況下，會忽略所有的NULL值列。也就是說，若需要查詢所有沒有社會安全號碼碼的員工的資訊......餘下全文>>