根据时间字段导入数据的问题总结

在之前的博文中介绍过如何通过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');

这种方式就可以了。如果希望该成这种方式也可以，脚本变化不大，我就不列举了。

所以通过这个通过时间戳导入数据的案例来看，还是有不少的坑的，还是需要不断验证，大胆猜想，小心求证。