當索引出現問題時，會導致嚴重的性能問題，索引問題包括索引不可用、索引碎片導致性能下降，我們需要一些手段在檢測索引的問題，並解決這些問題。這一篇將為你講述怎麼定位索引問題，並提供了解決的辦法。
索引不可用的原因有很多，包括：
1）索引空間耗盡，導致SQL*Loader更新索引失敗；
2）創建索引的過程中實例失敗；
3）唯一鍵有重復值；
4）某個索引的順序與sorted indexes子句中指定的順序不同；
5）移動表或表分區（alter table move和alter table move partition）；
6）對表執行在線重定義；
7）截斷表分區（alter table truncate partition）；
8）導入分區；
9）刪除表分區；
10）拆分表的分區或子分區（alter table split partition）；
11）分區索引的維護操作（alter index split partition）。
除了上述的這些原因之外，你還可以手動的將索引標注為不可用，這樣可以使批量加載速度更快，下面是把索引的狀態改變為不可用的方法：
alter index IDX_HISTORYALARM_HOUR$01 unusable
如果你的索引為分區索引，這個操作將導致所有分區的索引都不可用，你也可以指定某個分區的索引不可用：
alter index IDX_HISTORYALARM$02 modify partition HISTORYALARM20140731 unusable
通過下面的方法可以查看索引的狀態：
select ind.INDEX_NAME,ind.status,ind.PARTITIONED from user_indexes ind where index_name like '%HISTORYALARM%' INDEX_NAME STATUS PARTITIONED --------------------------------------------------------------------- IDX_HISTORYALARM_HOUR$01 UNUSABLE NO IDX_HISTORYALARM$02 N/A YES
可以看到，全局索引的狀態已經變為UNUSABLE，但本地索引的狀態標識為N/A，通過下面的方法可以查看本地索引在每一個分區中的索引狀態：
INDEX_NAME PARTITION_NAME STATUS ------------------------------------------------------------------------------------------ IDX_HISTORYALARM$02 HISTORYALARM20140731 UNUSABLE IDX_HISTORYALARM$02 HISTORYALARM20140801 USABLE IDX_HISTORYALARM$02 HISTORYALARM20140802 USABLE IDX_HISTORYALARM$02 HISTORYALARM20140803 USABLE IDX_HISTORYALARM$02 HISTORYALARM20140804 USABLE IDX_HISTORYALARM$02 HISTORYALARM20140805 USABLE ......
可以看到分區HISTORYALARM20140731的索引已經標注為UNUSABLE。
當索引被標注為不可用後，優化器就會忽略這些索引，Oracle在DML更改表時也不再維護這些索引，如果希望優化器再次使用它，就必須先重建（rebuild）索引。
隨著時間的推移，由於大量的刪除操作，索引可能會產生碎片。Oracle文檔（Performance Tuning Manual for Oracle DataBase 11.2）建議運行"analyze...validate"語句來識別需要重建的索引，這個操作會將索引的統計數據放到INDEX_STATS視圖中，下面是該視圖中的關鍵列：
1）高度（HEIGHT）：索引的高度，從1開始，1代表只有根的索引；
2）塊數（BLOCKS）：分配給索引的塊數；
3）葉行數（LF_ROWS）：葉行數（包括已刪除的行）；
4）已刪除的葉行數（DEL_LF_ROWS）：已刪除尚未清理的葉行條目數；
5）已用空間（USED_SPACE）：索引內使用的總空間（包括已刪除的條目）；
6）已用百分比（PCT_USED）：索引內使用空間的百分比（包括已刪除的條目）。。計算公式：(USED_SPACE / BTREE_SPACE) * 100;
7）B樹空間（BTREE_SPACE）：索引的總大小（包括已刪除的條目）。
下面通過一個例子來學習該視圖的使用。
先創建一個測試表格，並在上面創建索引：
create table test as select rownum id,'Test' text from dual connect by level <= 100000; create index idx_test on test(id);
然後執行索引分析語句：
analyze index idx_test validate structure;
注意在執行分析語句之前INDEX_STATS視圖是空的，現在查詢該視圖來檢查被刪除的葉行數：
select lf_rows,lf_blks,del_lf_rows from index_stats; LF_ROWS LF_BLKS DEL_LF_ROWS -------------------------------------------------------------------------------- 100000 222 0
這裡可以看出刪除的葉行數為0，接下來我們刪除表中大量的行，再次運行分析語句：
delete test where id <= 99999; commit; analyze index idx_test validate structure;
然後查詢被刪除的葉行數：
select lf_rows,lf_blks,del_lf_rows from index_stats; LF_ROWS LF_BLKS DEL_LF_ROWS ----------------------------------------------------------- 100000 222 99999
為了讓Oracle能夠得到正確的執行計劃，我們先收集表和索引的統計信息：
begin
dbms_stats.gather_table_stats(ownname => user,
tabname => 'TEST',
cascade => TRUE);
end;
然後執行一個索引范圍掃描的查詢：
select * from test where id > 10;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.27 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 1.53 56 224 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 1.81 56 224 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST (cr=224 pr=56 pw=0 time=1538212 us)
1 INDEX RANGE SCAN IDX_TEST (cr=223 pr=56 pw=0 time=1527442 us)(object id 58594)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.79 0.83
db file sequential read 56 0.03 0.41
********************************************************************************
查詢使用了索引范圍掃描，雖然整個表只有一條數據，但由於索引中的索引碎片，導致索引掃描任然讀取了56個數據塊。下面重建該索引：
alter index IDX_TEST rebuild;
然後按上面的方法生成並查看索引信息：
LF_ROWS LF_BLKS DEL_LF_ROWS ----------------------------------- 1 1 0
可以看出索引碎片消失。
注意事項：
1）執行索引分析會鎖定表，直到索引分析完畢才解除鎖定；
2）在大多數情況下，Oracle會盡可能的重用索引中已刪除條目的空間。
實際上，重建（rebuild）索引就是重新創建索引，但它比刪除原索引再重新創建索引的做法要好，因為在重建索引時存儲空間已分配給索引，而不需要再指定索引創建語句。
關於索引重建有很多依據，但其中有一些並不准確，下面列舉了一些：
1）Oracle的B樹索引隨著時間的推移變得不平衡
由於B樹索引的根塊和所有的葉塊之間的高度始終是一致的，所以這不正確。
2）索引中被刪除的空間無法重用
實際上Oracle會重用被刪除的空間。
3）達到一定層數的索引是低效的
索引的層數取決於索引有多少條目，重建不能解決問題。
4）具有糟糕的聚蔟因子的索引，可以通過重建修復
重建索引並不能改變表中數據行或索引的順序，因此聚蔟因子（見使用索引的聚蔟因子）完全不受索引重建影響。如果想改進聚蔟因子，實際上需要重建表。
那麼具體為什麼重建索引呢？
當索引不可用時，應該重建索引，但我們是否應該重建索引來消除索引碎片呢？
當你的查詢大部分都是通過索引訪問讀取單個行，那麼重建索引對性能影響很小。但對於范圍查詢，由於大量的索引碎片會導致查詢增加大量的無效IO，因此重建索引是有意義的，即使Oracle會重用索引碎片，但重建索引也可以使索引變得更加緊湊，從而提高查詢的效率。
上面已經使用到重建索引的方法：
alter index IDX_TEST rebuild;
但重建索引的過程中會對表加鎖，阻止其他對表的操作，直到索引重建完成。從Oracle 10g開始，Oracle提供了在線重建索引的方法：
<pre class="sql" name="code">alter index IDX_TEST rebuild online;
在線重建索引不會再導致索引鎖定。
重建分區索引則需要帶上分區信息：
alter index IDX_TEST rebuild partition partition_name online;
Oracle也為創佳和重建索引提供了一些參數，用於提高創建和重建索引的效率。
為了創建索引，數據庫需要進行全表掃描，並行創建可以加快索引的創建速度，速度的提升由並行度和CPU數量決定：
create index IDX_TEST on test(id) parallel 4 online;
也可以用於重建索引：
alter index IDX_TEST rebuild parallel 4 online;
需要注意的是這個操作將使索引的並行度（見使用索引）永遠變為這個值，如下：
select degree from user_indexes where index_name = 'IDX_TEST'; DEGREE -------------------- 4
如果打算讓數據庫在處理你的索引時使用並行機制，則正好，否則，你需要在執行了並行創建和重建操作後禁用並行：
alter index IDX_TEST noparallel;
如果忘記禁用並行，可能會導致嚴重的性能問題。
不把創建或者重建的索引項寫入重做日志，可以大大縮短索引創建或重建的時間：
create index IDX_TEST on test(id) nologging online;
也可以在重建索引時使用：
alter index IDX_TEST rebuild nologging online;
nologging不僅可以極大地提高性能，而且不填充多個重做日志文件，節省空間。
在非唯一索引中使用壓縮，可以減少重復鍵占用的空間：
compress <數字，並小於等於索引包括的字段值>
一個實例如下：
create index IDX_TEST on test(id) compress 1 online;
同樣可以用於重建索引：
alter index IDX_TEST rebuild compress 1 online;
