oracle索引分類rebuild案例集

 ========================================================================================================
                                                              索引rebuild案例總結
========================================================================================================                   

公司平時用到分割槽表較多，根據年月的複合分割槽表，每到年末需要做分割槽擴充套件，這其中索引(包括普通索引、分割槽索引)rebuild過程必不可少，結合實踐加總結研究,本貼就將索引重建案例總結分享出來。

 

(1)普通B-Tree索引:

  普通索引在user_indexes查詢status,狀態(valid:有效;unusable:失效，N/A:分割槽索引有效)，注意，要查詢分割槽索引有效與否，需要查詢另外的表，請參見接下來的內容:

索引重建SQL:alter index index_name rebuild pararllel 4 nologging;

--注意:這裡的並行，可以不加，如果有條件有需要可根據實際情況設定.

 

(2)分割槽索引:

  --&gt>查詢分割槽索引是全域性分割槽索引還是本地分割槽索引，取決於user_part_indexes中locality欄位:全域性分割槽索引(LOCAL),本地分割槽索引(GLOBAL);

 

--&gt>由於全域性分割槽索引要麼全部USABLE,要麼全部UNUSABLE[分割槽表索引狀態也就只有那兩種].所以一般不rebuild,便捷的方法是先drop再create.這裡主要以本地分割槽索引為主:

A1.本地單分割槽索引

   ---&gt>查詢分割槽資訊在user_ind_partitions表中查詢,複合分割槽索引狀態在這裡顯示的是N/A,若要確定子分割槽索引狀態在user_ind_subpartitions中查詢.

---&gt>重建分割槽索引SQL:

 Alter index index_name rebuild partition partition_name pararllel 4 nologging;

A2.本地複合分割槽索引:

查詢分割槽資訊在user_ind_subpartitions表中查詢:

---&gt>重建子分割槽索引SQL:

 Alter index index_name rebuild subpartition subpartition_name  pararllel 4 nologging;

(3注意事項:

   這裡只是針對schema級別查詢，用的都是user_*，當然也可以DBA級別，dba_*;

   還有需要注意的是本地複合分割槽表如果某一分割槽下多個子分割槽索引失效，不能簡單rebuild該一級分割槽，而需要逐個rebuild子分割槽，其實這應該是分割槽索引的優點所在。

(4)友情分享:
將上面本類索引批次rebuild小編整理了個儲存過程，內容如下，多指正哈:
-------------------------------------------------------------------------------------------------------------------------------------&gt>
create or replace PACKAGE BODY           "DAWN_BATCH_REBUILD_INDEX" is


  S_SQL VARCHAR2(500); 
  ACCOUNT_normal NUMBER := 0;
  ACCOUNT_partition NUMBER := 0;
  ACCOUNT_subpartition NUMBER := 0; 
  
  --normal index rebuild
  procedure normal_index_rebuild AS
   begin
      FOR LINE2 IN (select t4.index_name,t4.tablespace_name from user_indexes t4 where  t4.status='UNUSABLE')LOOP             
      S_SQL := 'alter index ' || LINE2.INDEX_NAME || ' rebuild tablespace '||LINE2.TABLESPACE_NAME||'  pararllel 4 nologging';
      DBMS_OUTPUT.PUT_LINE(S_SQL);
      ACCOUNT_normal := ACCOUNT_normal + 1; 
      EXECUTE IMMEDIATE S_SQL; 
     END LOOP; 
     DBMS_OUTPUT.PUT_LINE('normal index rebuild numbers:'||ACCOUNT_normal); 
  EXCEPTION 
  WHEN OTHERS THEN 
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
  
  END normal_index_rebuild;
 
 --partition index rebuild
  procedure partition_index_rebuild AS
   
   begin
       FOR LINE2 IN (select t2.index_name,t2.partition_name,t2.tablespace_name from user_ind_partitions t2
                      where t2.subpartition_count=0 and t2.status='UNUSABLE')LOOP 


      S_SQL := 'alter index '|| LINE2.INDEX_NAME || ' rebuild partition '||line2.partition_name||' tablespace '||LINE2.TABLESPACE_NAME||'  pararllel 4 nologging';
      DBMS_OUTPUT.PUT_LINE(S_SQL);
      ACCOUNT_partition := ACCOUNT_partition + 1; 
      EXECUTE IMMEDIATE S_SQL; 
     END LOOP; 
     DBMS_OUTPUT.PUT_LINE('partition index rebuild numbers:'||ACCOUNT_partition); 
     EXCEPTION 
       WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
  
  END partition_index_rebuild;
 
 --subpartition index rebuild
  procedure subpartition_index_rebuild AS
    
    begin
      FOR LINE2 IN (select t3.index_name,t3.tablespace_name,t3.partition_name,t3.subpartition_name,t3.status
                     from user_ind_subpartitions t3 where t3.status='UNUSABLE')LOOP 


      S_SQL := 'alter index ' || LINE2.INDEX_NAME || ' rebuild subpartition '||line2.subpartition_name ||' tablespace '||LINE2.TABLESPACE_NAME||'pararllel 4';
      DBMS_OUTPUT.PUT_LINE(S_SQL);
      ACCOUNT_subpartition := ACCOUNT_subpartition + 1; 
      EXECUTE IMMEDIATE S_SQL; 
     END LOOP; 
     DBMS_OUTPUT.PUT_LINE('subpartition index rebuild numbers:'||ACCOUNT_subpartition); 
  EXCEPTION 
  WHEN OTHERS THEN 
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
  
    END subpartition_index_rebuild;
 
end DAWN_BATCH_REBUILD_INDEX;

 -------&gt>最後若想了解關於分割槽表種類操作對分割槽表的不同影響請參見:http://blog.itpub.net/29119536/viewspace-1145136/