有人可以帮我找出 Oracle (10g) AND/OR 短路吗？

Question

考虑以下查询并注意 CALCULATE_INCENTIVE 函数：

SELECT EMP.* FROM EMPLOYEES EMPS
WHERE
    EMP.STATUS = 1 AND
    EMP.HIRE_DATE > TO_DATE('1/1/2010') AND
    EMP.FIRST_NAME = 'JOHN' AND
    CALCULATE_INCENTIVE(EMP.ID) > 1000
ORDER BY EMPS.ID DESC;

我的印象是 Oracle 在其和/或逻辑中使用与 .NET 使用的相同（或类似）的短路。例如，如果 EMP.STATUS = 2，则不会费心计算表达式的其余部分，因为整个表达式无论如何都会返回 false。

就我而言，CALCULATE_INCENTIVE 函数是在数据库中的每个员工上调用的，而不是仅在前三个 WHERE 表达式返回的 9 条记录上调用。我什至尝试在我想要组合在一起进行短路评估的特定表达式周围加上括号，但我无法弄清楚。

如果前面的任何表达式返回 false，任何人都知道如何让 CALCULATE_INCENTIVE not 被评估吗？

Answer 1

一种方法是将主要条件放入 Oracle 无法优化的子查询中，然后将次要条件放入外部查询中。确保 Oracle 不会优化子查询的最简单方法是在 select 语句中包含 rownum：

SELECT * FROM (
   SELECT EMP.*, ROWNUM 
   FROM EMPLOYEES EMPS
   WHERE
       EMP.STATUS = 1
       AND EMP.HIRE_DATE > TO_DATE('1/1/2010')
       AND EMP.FIRST_NAME = 'JOHN')
WHERE CALCULATE_INCENTIVE(ID) > 1000
ORDER BY EMPS.ID DESC;

Answer 2

Oracle 支持 PL/SQL 中的短路评估。然而，在 SQL 中，优化器可以自由地按其所需的任何顺序评估谓词、将谓词推送到视图和子查询中，以及按照它认为合适的方式转换 SQL 语句。这意味着您不应依赖于按特定顺序应用的谓词，并使 WHERE 子句中出现的顺序谓词基本上不相关。可用的索引、存在的优化器统计信息、优化器参数和系统统计信息都比 WHERE 子句中谓词的顺序重要得多。

例如，在 PL/SQL 中，您可以使用一个在实际调用时抛出错误的函数来演示这一点。

SQL> ed
Wrote file afiedt.buf

  1  create function throw_error( p_parameter IN NUMBER )
  2    return number
  3  as
  4  begin
  5    raise_application_error( -20001, 'The function was called' );
  6    return 1;
  7* end;
SQL> /

Function created.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_num NUMBER;
  3  begin
  4    l_num := 1;
  5    if( l_num = 2 and throw_error( l_num ) = 2 )
  6    then
  7      null;
  8    else
  9      dbms_output.put_line( 'Short-circuited the AND' );
 10    end if;
 11    if( l_num = 1 or throw_error( l_num ) = 2 )
 12    then
 13      dbms_output.put_line( 'Short-circuited the OR' );
 14    end if;
 15* end;
 16  /
Short-circuited the AND
Short-circuited the OR

PL/SQL procedure successfully completed.

另一方面，在 SQL 中，操作顺序由优化器决定，而不是由您决定，因此优化器可以随意短路或不短路。 Jonathan Gennick 有一篇很棒的文章 子查询疯狂！ 详细讨论了这一点。在您的特定情况下，如果您在 (FIRST_NAME、HIRE_DATE、STATUS) 上有一个复合索引以及适当的统计信息，则优化器几乎肯定会使用该索引来评估前三个条件，然后仅调用 CALCULATE_INCENTIVE 满足其他三个条件的 ID 的函数。如果您在 CALCULATE_INCENTIVE(id) 上创建了基于函数的索引，优化器可能会使用该索引，而不是在运行时调用该函数。但是，如果优化器认为这样做会更有效，那么它可以完全自由地决定在任何一种情况下为每一行调用该函数。