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

在之前的博文中介绍过如何通过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);
begin
for i in 4..110 loop
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;');
end loop;
end;
/

在绝大多数的场景里这种数据导入方式是没有问题的,但是有一种场景会报下面的错误。比如

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');
这种方式就可以了。如果希望该成这种方式也可以,脚本变化不大,我就不列举了。
所以通过这个通过时间戳导入数据的案例来看,还是有不少的坑的,还是需要不断验证,大胆猜想,小心求证。     

上一篇:SpringMVC 中 数据绑定 数据转换 自定义数据转换器


下一篇:文件系统和磁盘挂载、卸载