oracle 索引何時需要重建問題的解決

關於索引何時需要重建，我在oracle文件中還沒找到明確的說法，
但可以通過一個檢視（index_stats），參考裡面的數值來判斷是否需要重建。

select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct        
from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>10;

del_lf_rows：索引刪除行數
lf_rows：索引總行數

這裡 round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0) 求的是刪除行數除以總行數的百分比，
我在相關書籍上看到是，比率大於10%需要重建，當然看情況而定，也可以是是20%，但不要超過30%。

當然得到這個結果要首先分析索引：
analyze index index_name validate structure;

因為index_stats每次只存入一個索引的統計資訊，所以要是很多索引都檢測就不方便，
下面是我寫的儲存過程，用於批量分析。

1，建立臨時表用來存放資料
create table index_rebuid (name varchar2(30),del_lf_rows number(12),lf_rows number(12),frag_pct varchar2(10));
2，授予使用者許可權
grant select  on  dba_ind_columns to 使用者名稱;
3，建立儲存過程
create or replace procedure reb
is
tx varchar2(1000);
begin
for i in
(
select 'analyze index '||index_owner||'.'||index_name||' validate structure' as sql_text from dba_ind_columns
where index_owner='使用者名稱')  loop
tx :=i.sql_text;
execute immediate tx;
insert into index_rebuid (name,del_lf_rows,lf_rows,frag_pct)
select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct        
from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>10;
end loop;
commit;
end;
4，執行儲存
execute reb;
5，檢測
select * from index_rebuid;

oracle 如何判斷索引是否被使用

索引會增加io，增加空間，也會增加一些資料庫的額外開銷，
對於沒有用到得索引，應儘量避免不建，建立了的，把沒用的索引根據情況刪除。有唯一約束，主鍵的列不要刪除。
建議一個表的所以在4-5個左右，不要太多，這是一個參考值，當然還要具體分析，

下面是判斷的方法：
1，分析索引
alter index index_name monitoring usage;
2，產看,used是否為YES，當然要觀察一段時間才能確定是否被使用。
select table_name,index_name,used from v$object_usage;

下面是我寫的批量分析索引是否被使用的儲存過程：
1，授予許可權
grant select on dba_indexes to 使用者名稱;
2，建立儲存過程
create or replace procedure us
is
tx varchar2(1000);
begin
for i in
(
select 'alter index '||index_name||'  monitoring usage' as sql_text from dba_indexes where wner='使用者名稱'
)
loop
tx :=i.sql_text;
execute immediate tx;
end loop;
end;
3，執行儲存過程
execute wd;
4，檢測
select table_name,index_name,used from v$object_usage;

如果我們新介入一個新環境，對應用不是很瞭解，對各個表的關係也不瞭解，即使分析出相關的索引沒有被用到，我們在刪除時也要謹慎，
如果不是對資料庫有很大的影響，在可控的範圍內，可暫時不刪除。

注：
如果我們在v$object_usage看不到統計資訊
執行下面的指令碼。
CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE
(
OWNER,
INDEX_NAME,
TABLE_NAME,
MONITORING,
USED,
START_MONITORING,
END_MONITORING
)
AS
select u.name, io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and io.owner# = u.user#;

COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS 'Record of all index usage';
GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC";
CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE FOR SYS.V$ALL_OBJECT_USAGE;

索引資訊

當索引被分析時， 索引統計資訊就放在dba_indexes這個檢視中。這個檢視裡面也存放了很多對結構化查詢語言的優化很重要的資訊，當然還有很多其他的東西需要注意。 Oracle提供了分析xxx索引驗證結構的命令，這個命令會收集一些有關索引的額外統計資訊，並且把它放到一個名為index_stats的臨時表中。 但是，這些資訊需要儲存下來，因為每次執行這個命令的時候，原來的資訊都會被覆蓋掉。

為了得到一個索引的完全資料，我們需要上面兩個表的資料。同時，顯而易見，其中的某些列比其他列更重要：

* CLUSTERING_FACTOR（簇因素）――這是一個很重要的索引統計引數，因為它表示索引列的存放順序與表中資料的順序的相似程度。如果這個引數 很低（大約與表段的dba_segments.blocks數量差不多），則索引鍵的存放順序和表中行的順序一樣，索引範圍掃描將會非常有效，耗用最小的 磁碟輸入輸出。 當這個引數增加(最大至dba_tables.num_rows)時，索引鍵和表中行的順序相差就會很明顯。Oracle的“基於代價的優化”在很大程度 上依賴簇這個引數來決定是否使用索引。

* HEIGHT（高度）――當插入新行時，索引塊需要進行拆分。當一個索引結點分裂到一個預定的最高層數時，索引就會擴充套件一個新層。

* BLOCKS(塊數)――這個表示索引用掉的塊的數量。它依賴於資料庫塊的大小（db_block_size）。 在Oracle9i，以及更高的版本中，資料庫管理員（DBA）更傾向於用很大的塊來構建索引（例如db_32k_block_size），因為這樣，索 引擴充套件新層的可能性就更小。Robin Schumacher已經在他的書《Oracle效能故障解決》中提到了這一點。

“正如你所看到的，只是簡單地使用16k的表空間和16k的資料快取，邏輯讀的數量就減少了一半。很明顯，在oracle 9i及更高版本中正確使用新的資料緩衝區和多塊表空間很值得你在你自己的書刊中進行研究與試驗。”

* PCT_USED――這個欄位很有迷惑性，因為它看起來和dba_indexes的pct_used列一樣，但是意義卻不同。正常情況 下，pct_used的極限值是自由列表的未連線極限值，但在index_stats裡的pct_used則表示在為b樹索引所分配的空間裡實際所用到的 百分比。

是否有重建索引的標準？

簡單的講，沒有。沒有一個百分百完善、權威的索引重建標準。但是，可以從下面所列的幾條建議做起：

* 索引層次數大於3
* 佔用空間百分比小於75%
* 多於20%的行被刪除（空間不會自動被重用）
* 如果針對表有頻繁的插入/更新/刪除操作，儘管解除安裝、重排、過載一個表的資料或許能提供更高的效能，但索引將變得非聚簇，效能也將下降（導致需要讀的塊增加）， 這是需要額外執行的維護，並且保持行的正確順序也非常困難。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/32939/viewspace-614878/，如需轉載，請註明出處，否則將追究法律責任。