關於修改分割槽表的準備和操作細則

在之前的博文中，討論過一個根據分割槽鍵值發現效能問題的案例。90%以上的資料都分佈在了一個分割槽上，其它的分割槽要麼沒有資料要麼資料很少，這是很明顯的分割槽問題。當然這個過程中也發現了分割槽的劃分從開發角度和資料角度還是存在很大的差別，導致了分割槽的問題。
透過分割槽鍵值發現效能問題 http://blog.itpub.net/23718752/viewspace-1263068/

發現了問題，以點帶面，發現一些相關的分割槽表也有類似的問題，最後確認和分析後，發現收到影響的表有20多個，而且資料量都不小。
看來又得是一個忙碌的夜晚來修復這個問題了。
如果要準備相應的指令碼，也要考慮很多的問題，我大體列了幾個步驟。相應的指令碼也會按照這個步驟處理。
大體的思路和資料遷移有些類似,相比來說增加了分割槽的操作。
 step1_dump_bak  關於備份，最好存有兩份備份，物理備份和邏輯備份
 step2_truncate  分割槽之前，需要清空表中的資料。這個過程中需要考慮disable foreign key和trigger.
 step3_drop_par 重新分割槽的時候，只保留一個預設分割槽maxvalue,然後使用drop partition命令完成。
 step4_pre_par  在正式分割槽之前，可以先把表設為nologging,index設定為nologging,lob欄位也設定為nologging.作為後面資料匯入的時候的最佳化準備。
 step5_par_one  開始正式的分割槽修改，這個操作依賴於預設的maxvalue分割槽，不斷的split，因為沒有了資料所以速度還是很快的。這個部分處理分割槽鍵值為一個的表
 step6_par_two  開始正式的分割槽修改，這個部分處理分割槽鍵值為2個的表。
 step7_post_par 這個部分需要在資料匯入之前再次驗證分割槽的規則和分割槽資料是否和預期一致，在資料匯入之後檢查就太晚了。
 step8_data_append  確認之後，開始資料的匯入，這個部分使用資料遷移中的外部表方式，速度還是很快的，在反覆比較了imp/impdp,sqlldr之後，外部表處理和控制要更好一些。
 step9_stat_gather 這個部分是在資料匯入之後。需要重新收集統計資訊，儘管表的資料條數沒有變化，但是分割槽級的統計資訊是極大的變化。這個也需要考慮。

#1 step1_dump_bak 
關於備份,個人建議還是最好有兩種不同的備份，比如exp/expdp一種備份，這個作為物理備份,外部表匯出來作為另外一種備份，同時在資料載入的時候用到，有了這兩種備份，就不會在出現問題的時候慌亂了,如果外部表匯出因為空間等不可知因素，還有希望去彌補。

#step2_truncate 
這個部分，就是直接truncate來完成了。可以開幾個session來並行執行。

 step3_drop_par
這個部分可以參考如下的指令碼來完成,會刪除掉其它的分割槽，只保留預設分割槽,當然如果分割槽規則有變化也需要適當的做一些變更。這個部分完全可以用shell寫成批處理指令碼。
set pages 0
set linesize 150
set feedback off
select 'alter table '||table_name||' drop partition '||partition_name||' ;' from user_tab_partitions where table_name='XXXXXX' and partition_name not like '%MAXVALUE%'
 step4_pre_par
這個過程中需要設定表為Nologging..使用的指令碼比較長,可以參考http://blog.itpub.net/23718752/viewspace-1192153/

 step5_par_one 
 step6_par_two
關於分割槽的修改部分,之前自己寫了一個Pl/sql來處理，花了不少的時間。指令碼雖然完成了，但是不夠通用，最後發現本來幾十行的pl./sql用幾行shell就完成了。
比如我們修改分割槽的時候，語句類似下面的格式。
alter table XXXXX SPLIT PARTITION CMAXVALUE_MMAXVALUEat (2,2) INTO ( PARTITION C2_M2, PARTITION CMAXVALUE_MMAXVALUE);

如果分割槽鍵值為2個，類似下面的方式。注意ENTITY_KEY是按照5n的方式來遞增的。
for i in {0..199}
do
for ((j=5;j<=100;j=$j+5))
do
echo 'alter table '$1' split partition '$2' at('$i','$j') into (partition P'$i'_C'$j',partition '$2');'
done
done

指令碼執行後的效果如下,就完全可以脫離資料庫環境來完成。
alter table XXXXX split partition PMAXVALUE at(37) into (partition P37,partition PMAXVALUE);
.....
alter table XXXXX split partition PMAXVALUE_CMAXVALUE at(198,90) into (partition P198_C90,partition PMAXVALUE_CMAXVALUE);
....

 step7_post_par
關於分割槽的檢查和驗證，可以根據具體的業務邏輯來判斷。比如我可以使用如下的方式來做一個簡單驗證。
這個指令碼能夠得到一個資料條數的列表，能夠清晰的判斷出來，不用全部分割槽的資料都查，可以根據自己的選擇針對性來查就可以了。
set pages 0
set linesize 150
set feedback off
select 'alter table '||table_name||' drop partition '||partition_name||' ;' from user_tab_partitions where table_name='XXXXX' and partition_name not like '%MAX%';
以上是一個直觀的驗證，還需要再做一層驗證，看看資料的分割槽是不是和需求一致的。這個檢查至關重要。比如分割槽P55_C10存放的資料和鍵值的匹配情況。
如果粗放的檢查完，不做這一層次的檢查，如果出現問題，後面的步驟全都沒有意義了。
SQL> select period_key,ENTITY_KEY from XXXXX partition( P55_C10)  group by period_key,customer_key order by customer_key;
PERIOD_KEY ENTITY_KEY 
---------- ------------
        55            5
        55            6
        55            7
        55            8
        55            9
SQL> select period_key,ENTITY_KEY from XXXXXX partition( P55_C5) group by period_key,customer_key order by customer_key;
PERIOD_KEY ENTITY_KEY 
---------- ------------
        55            0
        55            1
        55            2
        55            3
        55            4
step8_data_append
這個部分就開始正式的資料匯入了。外部表的資料遷移方式，可以參考我的文集，就不從頭贅述了。http://blog.itpub.net/special/show/sid/383/
 step9_stat_gather
最後就是收集統計資訊了，這個部分可能會消耗一定的時間，可以先在檢查後開放環境給開發來做確認，畢竟收集統計資訊是可以online完成的，讓他們先確認業務，後臺併發跑一些session收集，可以節省較多的時間。