Oracle带时间戳的日期字段等值比较无返回结果如何解决

问题原因分析 #

1. 日期等值查询无返回的原因 #

你存储的CRTUPDDT字段是带时分秒（甚至纳秒）的时间类型（DATE/TIMESTAMP），而to_date('28-10-21','DD-MM-YY')转换后的结果是2021-10-28 00:00:00，只有字段值恰好等于这个时间点的行才会被匹配到，你返回的结果里所有数据都带毫秒级时间后缀，自然没有符合等值匹配条件的结果。

2. distinct去重性能差的原因 #

distinct需要对全量结果集做排序去重，当数据量很大时，排序操作会占用大量内存和IO资源，导致查询耗时飙升。

解决方案 #

日期等值匹配的修复方案 #

推荐优先用范围查询，不需要对字段做函数处理，可以直接命中CRTUPDDT上的普通索引，性能最优：

select * 
from valhist_tbl --dmh
where 
CRTUPDDT >=  to_date('28-10-21','DD-MM-YY')
and CRTUPDDT <  to_date('29-10-21','DD-MM-YY') -- 小于次日零点
and metricvalue  <> 'Device Found'
order by CRTUPDDT asc

如果你的业务场景允许使用函数处理字段，也可以用TRUNC函数截断时间部分后匹配：

select * 
from valhist_tbl --dmh
where 
TRUNC(CRTUPDDT) =  to_date('28-10-21','DD-MM-YY')
and metricvalue  <> 'Device Found'
order by CRTUPDDT asc

注意：如果要使用TRUNC写法且需要索引优化，可以单独建立TRUNC(CRTUPDDT)的函数索引。

大数据量去重方案 #

针对不同场景可以选择以下性能优于distinct的方案：

• 仅需要返回指定去重字段时，使用GROUP BY：

-- 示例：按CRTUPDDT、METRICVALUE两个字段去重
select CRTUPDDT, METRICVALUE
from valhist_tbl
where 
CRTUPDDT >=  to_date('28-10-21','DD-MM-YY')
and CRTUPDDT <  to_date('29-10-21','DD-MM-YY')
and metricvalue  <> 'Device Found'
group by CRTUPDDT, METRICVALUE

• 需要保留整行数据去重时，使用ROW_NUMBER()窗口函数：

-- 示例：按METRICVALUE去重，保留每个值最早的一条记录
select * from (
    select t.*,
    row_number() over(partition by METRICVALUE order by CRTUPDDT asc) rn
    from valhist_tbl t
    where 
    CRTUPDDT >=  to_date('28-10-21','DD-MM-YY')
    and CRTUPDDT <  to_date('29-10-21','DD-MM-YY')
    and metricvalue  <> 'Device Found'
) t1 where rn = 1

内容的提问来源于stack exchange，提问作者Michele