oracle 索引(3)

　　4、如果一個表很大，建立索引的時間很長，因為建立索引也會產生大量的redo信息，所以在創建索引時可以設置不產生或少產生redo信息。只要表數據存在，索引失敗了大不了再建，所以可以不需要產生redo信息。

　　5、建索引的時候應該根據具體的業務SQL來創建，特別是where條件，還有where條件的順序，盡量將過濾大范圍的放在后面，因為SQL執行是從后往前的。(小李飛菜刀)

　　索引常見操作

　　改變索引：

　　SQL> alter index employees_last _name_idx storage(next 400K maxextents 100);

　　索引創建后，感覺不合理，也可以對其參數進行修改。詳情查看相關文檔

　　調整索引的空間：

　　新增加空間

　　SQL> alter index orders_region_id_idx allocate extent (size 200K datafile '/disk6/index01.dbf');

　　釋放空間

　　SQL> alter index oraers_id_idx deallocate unused;

　　索引在使用的過程中可能會出現空間不足或空間浪費的情況，這個時候需要新增或釋放空間。上面兩條命令完成新增與釋放操作。關于空間的新增oracle可以自動幫助，如果了解數據庫的情況下手動增加可以提高性能。

　　重新創建索引：

　　所引是由oracle自動完成，當我們對數據庫頻繁的操作時，索引也會跟著進行修改，當我們在數據庫中刪除一條記錄時，對應的索引中并沒有把相應的索引只是做一個刪除標記，但它依然占據著空間。除非一個塊中所有的標記全被刪除的時，整個塊的空間才會被釋放。這樣時間久了，索引的性能就會下降。這個時候可以重新建立一個干凈的索引來提高效率。

　　SQL> alter index orders_region_id_idx rebuild tablespace index02;

　　通過上面的命令就可以重現建立一個索引，oracle重建立索引的過程：

　　1、鎖表，鎖表之后其他人就不能對表做任何操作。

　　2、創建新的(干凈的)臨時索引。

　　3、把老的索引刪除掉

　　4、把新的索引重新命名為老索引的名字

　　5、對表進行解鎖。

　　移動所引：

　　其實，我們移動索引到其它表空間也同樣使用上面的命令，在指定表空間時指定不同的表空間。新的索引創建在別位置，把老的干掉，就相當于移動了。

　　SQL> alter index orders_region_id_idx rebuild tablespace index03;

　　在線重新創建索引：

　　上面介紹，在創建索引的時候，表是被鎖定，不能被使用。對于一個大表，重新創建索引所需要的時間較長，為了滿足用戶對表操作的需求，就產生的這種在線重新創建索引。

　　SQL> alter index orders_id_idx rebuild online;

　　創建過程：

　　1、鎖住表

　　2、創建立臨時的和空的索引和IOT表用來存在on-going DML。普通表存放的鍵值，IOT所引表直接存放的表中數據;on-gong DML也就是用戶所做的一些增刪改的操作。

　　3、對表進行解鎖

　　4、從老的索引創建一個新的索引。

　　5、IOT表里存放的是on-going DML信息，IOT表的內容與新創建的索引合并。

　　6、鎖住表

　　7、再次將IOT表的內容更新到新索引中，把老的索引干掉。

　　8、把新的索引重新命名為老索引的名字

　　9、對表進行解鎖

　　整合索引碎片：

　　如上圖，在很多索引中有剩余的空間，可以通過一個命令把剩余空間整合到一起。

　　SQL> alter index orders_id_idx coalesce;

　　刪除索引：

　　SQL> drop index hr.departments_name_idx;

　　分析索引

　　檢查所引的有效果，前面介紹，索引用的時間久了會產生大量的碎片、垃圾信息與浪費的剩余空間了?？梢酝ㄟ^重新創建索引來提高所引的性能。

　　可以通過一條命令來完成分析索引，分析的結果會存放在在index_stats表中。

　　查看存放分析數據的表：

　　SQL> select count(*) from index_stats;

　　COUNT(*)

　　----------

　　0

　　執行分析索引命令：

　　SQL> analyze index my_bit_idx validate structure;

　　Index analyzed.

　　再次查看 index_stats 已經有了一條數據

　　SQL> select count(*) from index_stats;

　　COUNT(*)

　　----------

　　1

　　把數據查詢出來：

　　SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

　　HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS

　　---------- ---------------------------------------------------------------------- ---------- -----------

　　2 MY_BIT_IDX 1000 3 100

　　分析數據分析：

　　(HEIGHT)這個所引高度是2 ，(NAME)索引名為MY_BIT_IDX ，(LF_ROWS)所引表有1000行數據，(LF_BLKS)占用3個塊，(DEL_LF_ROWS)刪除100條記錄。

　　這里也驗證了前面所說的一個問題，刪除的100條數據只是標記為刪除，因為總的數據條數依然為1000條，占用3個塊，那么每個塊大于333條記錄，只有刪除的數據大于333條記錄，這時一個塊被清空，總的數據條數才會減少。

原文轉自：http://www.cnblogs.com/fnng/archive/2012/10/10/2719221.html