Oracle 索引的失效和重建

標籤：

查詢指定表的索引

SELECT T1.TABLE_NAME,T1.INDEX_NAME,T1.INDEX_TYPE,T1.UNIQUENESS,T1.TABLE_OWNER,T1.STATUS,T1.FUNCIDX_STATUSFROM ALL_INDEXES T1WHERE T1.TABLE_OWNER = UPPER(‘&Owner‘)AND T1.TABLE_NAME = UPPER(‘&Table_Name‘)ORDER BY T1.STATUS DESC;

 

普通索引失效,主鍵和唯一性索引除外(為保障資料的規範性)

SELECT ‘ALTER INDEX ‘ || TABLE_OWNER || ‘.‘ || INDEX_NAME || ‘ UNUSABLE;‘ UNUSABLE_INDEXFROM ALL_INDEXESWHERE TABLE_OWNER = UPPER(‘&Owner‘)AND TABLE_NAME = UPPER(‘&Table_Name‘)AND INDEX_TYPE = ‘NORMAL‘AND UNIQUENESS <> ‘UNIQUE‘;

 

並行(PARALLEL)重建索引，提升重建速度(在伺服器空閑時執行)

SELECT ‘ALTER INDEX ‘ || TABLE_OWNER || ‘.‘ || INDEX_NAME ||‘ REBUILD PARALLEL;‘FROM ALL_INDEXESWHERE TABLE_OWNER = UPPER(‘&Owner‘)AND TABLE_NAME = UPPER(‘&Table_Name‘)AND INDEX_TYPE = ‘NORMAL‘AND UNIQUENESS <> ‘UNIQUE‘AND STATUS = ‘UNUSABLE‘;

 

查詢索引的狀態和DEGREE並行度
注意：並行度預設設定為1，如果顯示為default，表示資料庫分配的cpu核心*2,重建完索引後並行度都改成1

SELECT ROWNUM,S.INDEX_NAME,S.INDEX_TYPE,S.TABLE_OWNER,S.TABLE_NAME,S.UNIQUENESS,S.STATUS,S.DEGREE,S.TABLESPACE_NAMEFROM ALL_INDEXES SWHERE TABLE_OWNER = UPPER(‘&Owner‘)AND TABLE_NAME = UPPER(‘&Table_Name‘);

 

將索引並行度不是1的還原回1

SELECT ‘ALTER INDEX ‘ || S.TABLE_OWNER || ‘.‘ || S.INDEX_NAME ||‘ NOPARALLEL;‘FROM ALL_INDEXES SWHERE TABLE_OWNER = UPPER(‘&Owner‘)AND TABLE_NAME = UPPER(‘&Table_Name‘)AND S.DEGREE <> 1;

 

Oracle 索引的失效和重建