Oracle數據庫中索引的維護

　　本文只討論Oracle中最常見的索引即是Btree索引本文中涉及的數據庫版本是Oraclei

一 查看系統表中的用戶索引

　　在Oracle中SYSTEM表是安裝數據庫時自動建立的它包含數據庫的全部數據字典存儲過程包函數和觸發器的定義以及系統回滾段

　　一般來說應該盡量避免在SYSTEM表中存儲非SYSTEM用戶的對象因為這樣會帶來數據庫維護和管理的很多問題一旦SYSTEM表損壞了只能重新生成數據庫我們可以用下面的語句來檢查在SYSTEM表內有沒有其他用戶的索引存在

　　select count(*) from dba_indexes where tablespace_name = SYSTEM and owner not in (SYSSYSTEM)

二 索引的存儲情況檢查

　　Oracle為數據庫中的所有數據分配邏輯結構空間數據庫空間的單位是數據塊（block）范圍（extent）和段（segment）

　　Oracle數據塊（block）是Oracle使用和分配的最小存儲單位它是由數據庫建立時設置的DB_BLOCK_SIZE決定的一旦數據庫生成了數據塊的大小不能改變要想改變只能重新建立數據庫（在Oraclei中有一些不同不過這不在本文討論的范圍內）

　　Extent是由一組連續的block組成的一個或多個extent組成一個segment當一個segment中的所有空間被用完時Oracle為它分配一個新的extent

　　Segment是由一個或多個extent組成的它包含某表空間中特定邏輯存儲結構的所有數據一個段中的extent可以是不連續的甚至可以在不同的數據文件中

　　一個object只能對應於一個邏輯存儲的segment我們通過查看該segment中的extent可以看出相應object的存儲情況

　　（）查看索引段中extent的數量

　　select segment_name count(*) from dba_extents where segment_type=INDEX and owner=UPPER(&owner) group by segment_name /

　　（）查看表空間內的索引的擴展情況

　　select substr(segment_name) SEGMENT NAME bytes count(bytes) from dba_extents
where segment_name in
( select index_name from dba_indexes where tablespace_name=UPPER(&表空間) ) group by segment_name bytes order by segment_name /

三 索引的選擇性

　　索引的選擇性是指索引列中不同值的數目與表中記錄數的比如果一個表中有條記錄表索引列有個不同的值那麼這個索引的選擇性就是/=

　　一個索引的選擇性越接近於這個索引的效率就越高

　　如果是使用基於cost的最優化優化器不應該使用選擇性不好的索引如果是使用基於rule的最優化優化器在確定執行路徑時不會考慮索引的選擇性（除非是唯一性索引）並且不得不手工優化查詢以避免使用非選擇性的索引

　　確定索引的選擇性可以有兩種方法手工測量和自動測量

　　（）手工測量索引的選擇性

　　如果要根據一個表的兩列創建兩列並置索引可以用以下方法測量索引的選擇性

　　列的選擇性=不同值的數目/行的總數 /* 越接近越好 */

　　select count(distinct 第一列||%||第二列)/count(*) from 表名

　　如果我們知道其中一列索引的選擇性（例如其中一列是主鍵）那麼我們就可以知道另一列索引的選擇性

　　手工方法的優點是在創建索引前就能評估索引的選擇性

　　（）自動測量索引的選擇性

　　如果分析一個表也會自動分析所有表的索引

　　第一為了確定一個表的確定性就要分析表

　　analyze table 表名 compute statistics

　　第二確定索引裡不同關鍵字的數目

　　select distinct_keys from user_indexes where table_name=表名 and index_name=索引名

　　第三確定表中行的總數

　　select num_rows from user_tables where table_name=表名

　　第四索引的選擇性=索引裡不同關鍵字的數目/表中行的總數

　　select idistinct_keys/tnum_rows from user_indexes i user_tables t
where itable_name=表名 and iindex_name=索引名 and itable_name=ttable_name

　　第五可以查詢USER_TAB_COLUMNS以了解每個列的選擇性

　　表中所有行在該列的不同值的數目

　　select column_name num_distinct from user_tab_columns where table_name=表名

　　列的選擇性=NUM_DISTINCT/表中所有行的總數查詢USER_TAB_COLUMNS有助測量每個列的選擇性但它並不能精確地測量列的並置組合的選擇性要想測量一組列的選擇性需要采用手工方法或者根據這組列創建一個索引並重新分析表

四 確定索引的實際碎片

　　隨著數據庫的使用不可避免地對基本表進行插入更新和刪除這樣導致葉子行在索引中被刪除使該索引產生碎片插入刪除越頻繁的表索引碎片的程度也越高碎片的產生使訪問和使用該索引的I/O成本增加碎片較高的索引必須重建以保持最佳性能

　　（）利用驗證索引命令對索引進行驗證

　　這將有價值的索引信息填入index_stats表

　　validate index 用戶名索引名

　　（）查詢index_stats表以確定索引中刪除的未填滿的葉子行的百分比

　　select name del_lf_rows lf_rows round((del_lf_rows/(lf_rows+))*) Frag Percent
from index_stats

　　（）如果索引的葉子行的碎片超過%考慮對索引進行重建

　　alter index 用戶名索引名 rebuild tablespace 表空間名 storage(initial 初始值 next 擴展值) nologging

　　（）如果出於空間或其他考慮不能重建索引可以整理索引

　　alter index用戶名索引名 coalesce

　　（）清除分析信息

　　analyze index 用戶名索引名 delete statistics

五 重建索引

　　（）檢查需要重建的索引

　　根據以下幾方面進行檢查確定需要重建的索引

　　第一查看SYSTEM表空間中的用戶索引

　　為了避免數據字典的碎片出現要盡量避免在SYSTEM表空間出現用戶的表和索引

　　select index_name from dba_indexes where tablespace_name=SYSTEM and owner not in (SYSSYSTEM)

　　第二確保用戶的表和索引不在同一表空間內

　　表和索引對象的第一個規則是把表和索引分離把表和相應的索引建立在不同的表空間中最好在不同的磁盤上這樣可以避免在數據管理和查詢時出現的許多I/O沖突

　　set linesize col OWNER format a col INDEX format a col TABLE format a col TABLESPACE format a select iowner OWNER iindex_name INDEX ttable_name TABLE itablespace_name TABLESPACE from dba_indexes i dba_tables t where iowner=towner and itable_name=ttable_name and itablespace_name=ttablespace_name and iowner not in (SYSSYSTEM) /

　　第三查看數據表空間裡有哪些索引

　　用戶的默認表空間應該不是SYSTEM表空間而是數據表空間在建立索引時如果不指定相應的索引表空間名那麼該索引就會建立在數據表空間中這是程序員經常忽略的一個問題應該在建索引時明確的指明相應的索引表空間

　　col segment_name format a select owner segment_name sum(bytes)
from dba_segments
where tablespace_name=數據表空間名
and segment_type=INDEX
group by ownersegment_name
/

　　第四查看哪個索引被擴展了超過次

　　隨著表記錄的增加相應的索引也要增加如果一個索引的next extent值設置不合理（太小）索引段的擴展變得很頻繁索引的extent太多檢索時的速度和效率就會降低

　　set linesize col owner format a col segment_name format a col tablespace_name format a select count(*) owner segment_name tablespace_name from dba_extents where segment_type=INDEX and owner not in (SYSSYSTEM) group by ownersegment_nametablespace_name having count(*) > order by count(*) desc /

　　（）找出需要重建的索引後需要確定索引的大小以設置合理的索引存儲參數

　　set linesize col INDEX format a col TABLESPACE format a select owner OWNER segment_name INDEX tablespace_name TABLESPACE bytes BYTES/COUNT sum(bytes) TOTAL BYTES round(sum(bytes)/(*)) TOTAL M count(bytes) TOTAL COUNT from dba_extents where segment_type=INDEX and segment_name in (索引名索引名 ) group by ownersegment_namesegment_typetablespace_namebytes order by ownersegment_name /

　　（）確定索引表空間還有足夠的剩余空間

　　確定要把索引重建到哪個索引表空間中要保證相應的索引表空間有足夠的剩余空間

　　select round(bytes/(*)) free(M) from sm$ts_free where tablespace_name=表空間名 /

　　（）重建索引

　　重建索引時要注意以下幾點

　　a如果不指定tablespace名索引將建在用戶的默認表空間
b如果不指定nologging將會寫日志導致速度變慢由於索引的重建沒有恢復的必要所以可以不寫日志
c如果出現資源忙表明有進程正在使用該索引等待一會再提交

　　alter index 索引名 rebuild tablespace 索引表空間名 storage(initial 初始值 next 擴展值) nologging /

　　（）檢查索引

　　對重建好的索引進行檢查

　　select * from dba_extents where segment_name=索引名

　　（）根據索引進行查詢檢查索引是否有效

　　使用相應的where條件進行查詢確保使用該索引看看使用索引後的效果如何

　　select * from dba_ind_columns where index_name like 表名%

　　然後根據相應的索引項進行查詢

　　select * from 表名% where

　　（）找出有碎片的表空間並收集其碎片

　　重建索引後原有的索引被刪除這樣會造成表空間的碎片

　　select alter tablespace ||tablespace_name|| coalesce; from dba_free_space_coalesced where percent_blocks_coalesced!= /

　　整理表空間的碎片

　　alter tablespace 表空間名 coalesce

From:http://tw.wingwit.com/Article/program/Oracle/201311/18791.html