为何同时使用两个BETWEEN日期条件时查询1秒完成，删任意一个就长时间运行？

这是典型的数据库优化器谓词估算偏差导致的执行计划选错问题，和SQL本身的逻辑过滤关系不大：

• 两个针对同一字段的BETWEEN条件，优化器的处理逻辑存在天然差异：
  • 第一个筛选条件完全由Oracle内置系统函数（ADD_MONTHS、TRUNC、LAST_DAY）计算生成时间范围，数据库在解析SQL阶段就可以直接算出精确的上下界值，结合表上的字段统计信息，能准确估算符合条件的行数，顺利触发ARRIVE_DT_TM字段上的B树索引范围扫描，走高效查询路径。
  • 第二个筛选条件嵌套了自定义函数cclsql_cnvtdatetimeutc，这类非内置的用户自定义函数，优化器默认无法提前预判其返回值、没有对应的函数维度统计信息时，会严重偏差地估算谓词过滤能力——通常会直接选择全表扫描、或者低效的全索引扫描路径，大表场景下就会出现长时间无法返回结果的情况。
• 两个条件同时存在时查询速度正常，本质是AND逻辑的范围交集触发了正确的执行路径：同一字段的多个范围过滤求交集时，优化器会优先使用可精确计算的常量范围条件触发索引扫描，两个条件的交集结果集规模和单个条件的返回规模几乎没有差异，自然就回到了1秒返回的高效状态。

• 先确认两个时间范围的实际等价性：手动执行如下SQL，把四个边界值全部打印出来核对：

SELECT
  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) AS native_start,
  LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)+1) AS native_end,
  cclsql_cnvtdatetimeutc(trunc(add_months(sysdate, -1),'MONTH'),1,126,1) AS tz_start,
  cclsql_cnvtdatetimeutc(trunc(sysdate,'MONTH')-(1/86400),1,126,1) AS tz_end
FROM DUAL;

如果两个区间的覆盖范围基本完全重合，不需要额外保留两个重复的筛选条件。

• 单独带自定义函数的查询慢的修复方式：
  • 直接把上面查出来的时区换算后的固定时间值硬编码到SQL的BETWEEN条件里，不在查询逻辑中嵌套调用cclsql_cnvtdatetimeutc，彻底消除优化器对自定义函数的估算偏差，让数据库识别这是个普通的日期范围过滤，直接命中现有索引。
  • 如果业务上必须在SQL中保留函数调用，可针对函数返回值的计算逻辑建立对应函数索引，或手动为自定义函数收集维度统计信息，让优化器能准确判断过滤效果。
• 单独带原生系统函数的查询慢的修复方式：
  检查对应表的统计信息是否过期、执行计划是否错选了低效路径，重新收集表和对应字段的最新统计信息，即可让优化器重新选择正确的索引扫描路径。

注：不要为了让查询变快刻意保留两个冗余的过滤条件，冗余的谓词不仅会增加SQL的维护成本，极端场景下还可能因为两个时间范围的细微时差导致结果集错误。

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