背景:数仓ETL环节通过sqoop抽取某个Oracle业务表时,突然在某天提示sqoop导出错误,重试仍然失败。经过查询源库发现当前增量抽取sql耗时特别久,涉及oracle数据库date类型比较时的查询效率问题。
- 原始增量查询sql
select * from ora_table where to_char(update_time,'yyyymmdd') = '20220222';
注:在Oracle源库update_time字段为date类型,上述sql意为取2022-02-22一天的记录
- 问题原因分析
经过测试发现用原始增量查询sql去查询源库特别慢,片刻思考觉悟,oracle数据库date类型的数据存储都是用时间戳,即最终比较还是用数值比较。而to_char()转换后比较就变成用字符比较了,即通过字节码比较,相比较来说,肯定还是数值比较效率较高。
- 优化查询方式
既然使用date类型直接比较效率较高,那就需要将待比较的字符转换为date类型
select * from ora_table where update_time = to_date('20220222','yyyy-mm-dd');
使用上面的sql时,问题就来了,to_date()函数参数只传日期(不加时间)的话,转换后的日期值会默认加上时间00:00:00,这样用于条件判断时,是取不到2022-02-22这一天记录的
我们可以使用to_char()函数格式化一下to_date('20220222','yyyy-mm-dd')这个date类型值来验证
select to_char(to_date('20220222','yyyy-mm-dd'),'yyyy-mm-dd hh24:mi:ss) from ora_table where rownum=1;
会发现取到的值是2022-02-22 00:00:00,如果按日期类型匹配的话,取2022-02-22这一天的记录,我们希望的取值范围应该是2022-02-22 00:00:00至2022-02-22 23:59:59(左右闭区间),所以需要to_date()函数格式化的时候加上时间限制,下面2种方案均可实现
方案一
--通过between..and(闭区间)
select * from ora_table where update_time between to_date('20220222 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('20220222 23:59:59','yyyy-mm-dd hh24:mi:ss');
--通过>=和<=
select * from ora_table where update_time >= to_date('20220222 00:00:00','yyyy-mm-dd hh24:mi:ss') and update_time <= to_date('20220222 23:59:59','yyyy-mm-dd hh24:mi:ss');
但是建议使用>=符号,因为between..and
如果不想限制时间格式,也可以通过将右边范围加一天,然后通过<的范围限定形式来实现,这样取值范围就是2022-02-22 00:00:00至2022-02-23 00:00:00(左闭,右开)
方案二
select * from ora_table where update_time >= to_date('20220222','yyyy-mm-dd') and update_time < to_date('20220223','yyyy-mm-dd');