PL/SQL 查询优化，在 where 子句中使用函数调用

Question

我正在尝试优化一个查询，我在 where 子句中使用 function（） 调用。

函数（）只是更改日期的时区。

当我调用该函数作为 SELECT 的一部分时，它的执行速度非常快（对数十万行的表<0.09 秒）

select 
  id, 
  fn_change_timezone (date_time, 'UTC', 'US/Central') AS tz_date_time,
  value 
from a_table_view
where id = 'keyvalue'
and date_time = to_date('01-10-2014','mm-dd-yyyy')

但是，这个版本"永远"运行[意味着我在无数分钟后停止它]

select id, date_time, value 
from a_table_view
where id = 'keyvalue'
and fn_change_timezone (date_time, 'UTC', 'US/Central')  = to_date('01-10-2014','mm-dd-yyyy')

（我知道我必须更改要比较的日期，例如）

所以我的问题是双重的：

如果函数在 where 子句之外

1. 如此之快，为什么它比使用 TRUNC（） 或其他函数慢得多（显然 trunc（） 不像我的函数那样执行表查找 - 但该函数在 where 子句之外仍然非常非常快）

2. 在 where 子句之外，还有什么替代方法可以实现这一点？

我

尝试了这个作为替代方案，这似乎并没有更好，它仍然运行，直到我停止查询：

select
  tz.date_time,
  v.id, 
  v.value
from 
  (select
    fn_change_timezone(to_date('01/10/2014-00:00:00', 'mm/dd/yyyy-hh24:mi:ss'), 'UTC',     'US/Central') as date_time
    from dual
    ) tz
  inner join   
 (
  select 
    id, 
    fn_change_timezone (date_time, 'UTC', 'US/Central') AS v_date_time,
    value 
  from a_table_view
  where id = 'keyvalue'
  ) v ON
    v.tz_date_time = tz.date_time

希望我能很好地解释这个问题。

Answer 1

在 WHERE 子句中使用函数至少有四个潜在问题：

1. 函数可能会阻止索引。 基于函数的索引可以解决此问题。
2. 函数可能会阻止分区修剪。 硬编码值或虚拟列分区是可能的解决方案，尽管在这种情况下两者都没有帮助。
3. 函数可能会运行缓慢。 即使函数很便宜，在SQL和PL/SQL之间切换通常也非常昂贵。 一些可能的解决方案是DETERMINISTIC、PARALLEL_ENABLE、函数结果缓存，在纯SQL中定义逻辑，或者用12c在SQL中定义函数。
4. 函数可能会导致错误的基数估计。 优化器很难猜测正常条件的结果，添加过程代码会使它更加困难。 使用 ASSOCIATE STATISTICS 可以向优化程序提供有关函数的开销和基数的一些信息。

如果没有更多信息（例如解释计划），则很难知道此查询的具体问题是什么。

Answer 2

WHERE 子句中的函数调用是一件坏事。问题在于，可能会为表中的每一行调用该函数，该函数可能比所选集多得多。这可能是一个真正的性能杀手（不要问我怎么知道的：-）。在 SELECT 列表中调用函数的第一个版本中，只有在选择一行并被添加到结果集中时才会调用该函数 - 在第二个版本中，很可能会为表中的每一行调用该函数。此外，根据您使用的 Oracle 版本，从 SQL 调用用户函数可能会产生大量开销，但我认为这种惩罚在 10g 以来的版本中已基本消除。

祝你好运。

分享和享受。