Oracle关于date类型的查询方式及效率浅析

优化Oracle数据库date类型查询效率：从字符到数值比较

最新推荐文章于 2024-03-15 11:36:30 发布

原创最新推荐文章于 2024-03-15 11:36:30 发布 · 9.2k 阅读

19 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #date

ORACLE 专栏收录该内容

13 篇文章

订阅专栏

本文探讨了一则在ETL过程中遇到的Oracle数据库date类型查询效率问题。原始SQL通过to_char()函数进行字符比较导致查询缓慢。分析指出，date类型直接比较效率更高。提出了两种优化方案：一是使用between..and或>=和<=结合to_date()函数精确匹配日期区间；二是通过to_date()函数配合时间限制，利用>=和<的范围限定。这两种方法解决了查询效率问题并确保获取正确日期范围的记录。

背景：数仓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');