WHERE子句用包装函数时SELECT查询性能骤降问题排查与解决

我在查询视图时，通过日期范围过滤数据，使用了一个自定义的日期转换包装函数，但该函数在不同环境下性能表现差异极大——有时和直接用to_date性能一致，有时却导致查询耗时飙升，甚至同一环境下不同用户执行速度天差地别。

示例查询 #

使用包装函数的查询：

select *
from my_view
where regdate >= dates.date_from_iso_local_datetime_string('2023-12-01T00:00:00')
  and regdate < dates.date_from_iso_local_datetime_string('2024-01-01T00:00:00')
order by id;

功能完全等价的to_date版本：

select *
from my_view
where regdate >= to_date('2023-12-01T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')
  and regdate < to_date('2024-01-01T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')
order by id;

性能差异表现 #

• 部分环境下，两个版本性能完全一致
• 某环境中，to_date版本耗时约0.8秒，包装函数版本耗时约7秒
• 第三个环境中，to_date版本耗时约1秒，包装函数版本耗时超7分钟；且该环境下，以schema所有者身份执行速度正常，其他用户执行则极慢

视图与函数定义 #

实际使用的视图由三个SELECT语句通过UNION组合而成，所有语句均基于同一个包含regdate列的基表。

自定义日期函数的定义如下：

gcIso8601DateTimeFormat constant varchar2(23) := 'YYYY-MM-DD"T"HH24:MI:SS';

function date_from_iso_local_datetime_string( pDateTimeString in varchar2 ) return date is
begin
   if trim(pDateTimeString) is null then
      return null;
   else
      return to_date(pDateTimeString, gcIso8601DateTimeFormat);
   end if;
end;

场景细节与尝试 #

性能最差的环境中，视图总数据量约400万条，但本次查询仅返回1条结果。

我原本认为数据库能识别函数输入是常量，只需执行一次再应用过滤条件，但怀疑数据库实际在逐行执行该函数（尽管逻辑上应该先过滤再排序）。

为了让优化器识别到只需执行一次函数，我尝试用CTE提前计算日期值，但没有效果：

with foo as (
   select dates.date_from_iso_local_datetime_string('2023-12-01T00:00:00') fromdate,
          dates.date_from_iso_local_datetime_string('2024-01-01T00:00:00') todate
   from dual ) 
select *
from myview,
     foo
where regdate >= foo.fromdate
  and regdate < foo.todate
order by id;

使用包装函数的初衷是统一API的日期调用格式，当前查询由Java API发起，我手动执行排查问题。

问题 #

1. 核心问题：如何解决该性能问题？比如能否通过给函数添加注解，让优化器识别到只需执行一次，无需逐行重复执行？（注：我知道可以修改Java代码直接用to_date，但更倾向于在SQL层面解决，保留包装函数作为通用工具）
2. 我的推测是否正确？数据库确实在逐行执行函数，还是存在其他性能瓶颈？
3. 为何不同环境下性能差异如此极端？数据模型一致，仅数据量或数据分布有差异，但这种差异远超预期。在无法获取环境权限的情况下，应该从哪些方向排查？

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