根據時間字段導入資料的問題總結

在之前的博文中介紹過如何通過exchange partition,split partition達到快速的資料切換，對于上百G的大表來說，速度都在秒級完成

對于大分區重新分區來說，上面的步驟已經夠用了，但是對于資料清理來說，工作才剛剛開始，這是一種邏輯的資料清理，因為目前分區表中重新分區後沒有資料，對于曆史資料可以選擇按照分區邏輯使用insert append的方式進行資料導入。

因為分區規則是按照時間字段，是以在資料導入的時候犯了一個錯誤，就是簡單使用下面的形式來導入資料。

Insert /*+append*/ into TEST_LOG select * from LOG_CLNUP where time =to_date('20150721','YYYYMMDD');

簡單以為這種情況會吧2015年7月21日的資料導入，但是從測試情況來說，資料量是在少得厲害，才發現其實這種情況下，預設是會使用0時0分0秒的情況。

我們把随便一個日期進行按日期格式化，然後使用精細化的格式輸出。

SQL> select to_char(to_date('2014-10-05','yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(TO_DATE('20

-------------------

2014-10-05 00:00:00

當然了，這個錯誤是一個低級錯誤，我們來看我們要做的正事。

分區規則是按照月份，即每個月都有一個對應的分區，則我們計劃使用下面的格式來插入資料。

 Insert /*+append*/ into TEST_LOG select * from LOG_CLNUP where time between to_date('20150721','YYYYMMDD')and to_date('20150722','YYYYMMDD') ;

按照這樣的邏輯應該是沒有問題的。不過還是有一定的隐患，後面會單獨說。

按照表的資料量，每天的增量資料都在百萬，千萬，是以按照天來導入還是比較合理的，如果按照月，可能時間會很長，而且不好控制。是以按照天來進行資料導入就需要使用動态sql。

第一個思路就是使用Pl/sql來做。比如對于表TEST_LOG我們這麼做，其實還有好幾個類似的表。方法雷同。

begin

for i in 4..110 loop

  dbms_output.put_line('select sysdate-'||to_number(i+1)||' from dual;');

  dbms_output.put_line('Insert /*+append*/ into TEST_LOG select * from LOG_CLNUP where time  between to_date(to_char(sysdate-'||to_number(i+1)||','||chr(39)||'yyyymmdd'||chr(39)||'),'||chr(39)||'YYYYMMDD'||chr(39)||') and to_date(to_char(sysdate-'||i||','||chr(39)||'yyyymmdd'||chr(39)||'),'||chr(39)||'YYYYMMDD'||chr(39)||');'||chr(10)||'commit;');

end loop;

end;

/

這種情況下會生成相應的語句來動态插入。

比如

SYSDATE-31

2015-06-20 17:30:23

我們就根據sysdate-i的方式來得到相應的日期。這種方式相對來說也能接受，不過比如你在晚上10點運作腳本，結果過了淩晨，這個時候sysdate就會發生變化，有些日子的資料很可能就會導入兩次。是以說這種方式也是不夠合理的。就算在當天完成，你去檢視sysdate-i的時候也不是很友善，至少我通過這個不能很快知道我要插入資料的日期。還得推算，有的月31天，有的月30天。。。

是以相對還是這種方式要好一些。

Insert /*+append*/ into TEST_LOG partition(P_2015_07) select * from LOG_CLNUP where time  between to_date('20150701','YYYYMMDD') and to_date('20150702','YYYYMMDD');

如果希望使用動态sql來完成，可以這麼做。

set linesize 200

set serveroutput on

declare

tmp_date_a varchar2(30);

tmp_date_b varchar2(30);

tmp_partition_name varchar2(30);

select to_char(sysdate-i,'yyyymmdd') into tmp_date_a from dual;

select to_char(sysdate-(i+1),'yyyymmdd') into tmp_date_b from dual;

select 'P_'||to_char(sysdate-(i+1),'yyyy_mm') into tmp_partition_name from dual;

    dbms_output.put_line('Insert /*+append*/ into TEST_LOG partition('||tmp_partition_name||') select * from LOG_CLNUP where time  between to_date('||chr(39)||tmp_date_b||chr(39)||','||chr(39)||'YYYYMMDD'||chr(39)||') and to_date('||chr(39)||tmp_date_a||chr(39)||','||chr(39)||'YYYYMMDD'||chr(39)||');'||chr(10)||'commit;');

在絕大多數的場景裡這種資料導入方式是沒有問題的，但是有一種場景會報下面的錯誤。比如

Insert /*+append*/ into TEST_LOG partition(P_2015_06) select * from LOG_CLNUP where time  between to_date('20150630','YYYYMMDD') and to_date('20150701','YYYYMMDD')

                        *

ERROR at line 1:

ORA-14401: inserted partition key is outside specified partition

這個問題還是在時間戳上出了問題，因為時間戳跨分區了。是以在檢查的時候會有一些問題。

可以這麼改。

Insert /*+append*/ into M_START_LOG partition(P_2015_06) select * from M_START_LOG_CLNUP where time  between to_date('20150630 00:00:00','YYYYMMDD hh24:mi:ss') and to_date('20150630 23:59:59','YYYYMMDD hh24:mi:ss');

這種方式就可以了。如果希望該成這種方式也可以，腳本變化不大，我就不列舉了。

是以通過這個通過時間戳導入資料的案例來看，還是有不少的坑的，還是需要不斷驗證，大膽猜想，小心求證。