CASE 和 COALESCE 短路评估适用于 PL/SQL 中的序列，但不适用于 SQL

问题描述

CASE 和 COALESCE() 文档中描述的短路评估是否适用于在 SQL 中使用的序列?这似乎没有发生.

Does the short-circuit evaluation described in the documentation for CASE and COALESCE() apply to sequences when used in SQL? This does not appear to be happening.

Oracle 关于CASE的文档 声明:

The Oracle documentation on CASE states that:

Oracle 数据库使用短路评估.对于一个简单的CASE 表达式...如果先前的 compare_expr 等于 expr，则 Oracle 永远不会评估 compare_expr.对于搜索的 CASE 表达式，如果前一个条件为真，数据库...从不评估条件.

Oracle Database uses short-circuit evaluation. For a simple CASE expression... Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr. For a searched CASE expression, the database... never evaluates a condition if the previous condition was true.

类似于COALESCE() 文档 表示:

Oracle 数据库使用短路评估.数据库评估每个 expr 值并确定它是否为 NULL，而不是在确定其中任何一个为 NULL 之前评估所有 expr 值.

Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

当从 SQL 调用序列时，情况似乎并非如此；如您所见，没有发生短路并且序列递增.

When calling a sequence from SQL this does not appear to be the case; as you can see no short circuiting occurs and the sequence is incremented.

SQL> create sequence tmp_test_seq start with 1 increment by 1;
SQL> select tmp_test_seq.nextval from dual;

   NEXTVAL
----------
         1
SQL> select tmp_test_seq.currval from dual;

   CURRVAL
----------
         1
SQL> select coalesce(1, tmp_test_seq.nextval) from dual;

COALESCE(1,TMP_TEST_SEQ.NEXTVAL)
--------------------------------
                               1
SQL> select tmp_test_seq.currval from dual;

   CURRVAL
----------
         2
SQL> select case when 1 = 1 then 1 else tmp_test_seq.nextval end as s from dual;


         S
----------
         1
SQL> select tmp_test_seq.currval from dual;

   CURRVAL
----------
         3

SQL Fiddle.

但是，当从 PL/SQL 调用时，序列不递增:

However, when calling from PL/SQL the sequence is not incremented:

SQL> create sequence tmp_test_seq start with 1 increment by 1;
SQL> declare
  2     i number;
  3  begin
  4     i := tmp_test_seq.nextval;
  5     dbms_output.put_line(tmp_test_seq.currval);
  6     i := coalesce(1, tmp_test_seq.nextval);
  7     dbms_output.put_line(i);
  8     dbms_output.put_line(tmp_test_seq.currval);
  9     i := case when 1 = 1 then 1 else tmp_test_seq.nextval end;
 10     dbms_output.put_line(i);
 11     dbms_output.put_line(tmp_test_seq.currval);
 12  end;
 13  /
1
1
1
1
1
SQL> select tmp_test_seq.nextval from dual;

   NEXTVAL
----------
         2

从 PL/SQL 调用 SQL 中的序列与 SQL 发生相同的结果:

Calling the sequence in SQL from PL/SQL the same results as with SQL happens:

SQL> create sequence tmp_test_seq start with 1 increment by 1;
SQL> declare
  2     i number;
  3  begin
  4     select tmp_test_seq.nextval into i from dual;
  5     dbms_output.put_line(tmp_test_seq.currval);
  6     select coalesce(1, tmp_test_seq.nextval) into i from dual;
  7     dbms_output.put_line(i);
  8     dbms_output.put_line(tmp_test_seq.currval);
  9     select case when 1 = 1 then 1 else tmp_test_seq.nextval end into i
 10       from dual;
 11     dbms_output.put_line(i);
 12     dbms_output.put_line(tmp_test_seq.currval);
 13  end;
 14  /
1
1
2
1
3

文档中似乎没有关于此的任何内容；管理序列的管理员指南，关于序列伪列的 SQL 语言参考，CURRVAL 和 NEXTVAL 上的 PL/SQL 语言参考 或 序列的数据库概念概述.

There doesn't seem to be anything in the documentation about this; the Administrator's guide for managing sequences, the SQL language reference on sequence psuedocolumns, the PL/SQL language reference on CURRVAL and NEXTVAL or the database concepts overview of sequences.

CASE 和 COALESCE() 在 SQL 中使用时是否会出现对序列的短路评估?这有记录吗?

Does the short-circuit evaluation of CASE and COALESCE() occur for sequences when used in SQL? Is this documented?

如果有兴趣，我们将使用 11.2.0.3.5.

We're on 11.2.0.3.5 if it's of interest.

推荐答案

为什么短路评估不适用于序列的解释如下.什么是序列?撇开内部不谈，它是序列定义(记录在 seq$ 数据字典表中)和一些内部 SGA 组件的组合，它不是一个函数，可以考虑，尽管文档 没有直接说明(但执行计划会)作为行源.每次在查询的选择列表中直接引用序列时，优化器在搜索最佳执行计划时都必须对其进行评估.在形成最佳执行计划的过程中，如果引用了 nextval 伪列，则序列会递增:

Explanation of why the short-circuit evaluation does not apply to sequences might be the following. What is a sequence? Putting internals aside, it's a combination of sequence definition(record in seq$ data dictionary table) and some internal SGA component, it's not a function and might be considered, although the documentation does not state it directly(but execution plan does) as row source. And every time a sequence is being referenced directly in the select list of a query, it has to be evaluated by the optimizer when it searches for optimal execution plan. During the process of forming an optimal execution plan a sequence gets incremented if nextval pseudocolumn is referenced:

SQL> create sequence seq1;
Sequence created

这是我们的顺序:

SQL> select o.obj#
  2       , o.name
  3       , s.increment$
  4       , s.minvalue
  5       , s.maxvalue
  6       , s.cache
  7    from sys.seq$ s
  8    join sys.obj$ o
  9       on (o.obj# = s.obj#)
 10    where o.name = 'SEQ1'
 11  ;


      OBJ# NAME    INCREMENT$   MINVALUE   MAXVALUE      CACHE
---------- ------- ---------- ---------- ---------- ----------
     94442 SEQ1             1          1       1E28         20

让我们跟踪下面的查询，并看看它的执行计划

Lets trace below query, and also take a look at its execution plan

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';
Session altered

SQL> select case
  2           when 1 = 1 then 1
  3           when 2 = 1 then seq1.nextval
  4         end as res
  5    from dual;

       RES
----------
         1

/* sequence got incremented by 1 */

SQL> select seq1.currval from dual;

   CURRVAL
----------
         3

跟踪文件信息:

STAT #1016171528 id=1 cnt=1 pid=0 pos=1 obj=94442 op='SEQUENCE SEQ1 ...
STAT #1016171528 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL ...
CLOSE #1016171528:c=0,e=12,dep=0,type=0,tim=12896600071500/* 关闭光标 */

STAT #1016171528 id=1 cnt=1 pid=0 pos=1 obj=94442 op='SEQUENCE SEQ1 ...
STAT #1016171528 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL ...
CLOSE #1016171528:c=0,e=12,dep=0,type=0,tim=12896600071500 /* close the cursor */

执行计划将向我们显示基本相同:

The execution plan will show us basically the same:

SQL> explain plan for select case
  2                            when 1 = 1 then 1
  3                            else seq1.nextval
  4                          end
  5                      from dual
  6  /
Explained
Executed in 0 seconds

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 51561390
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SEQUENCE        | SEQ1 |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
9 rows selected
Executed in 0.172 seconds

在评估方面，直接在查询中引用一个序列，与包含相关子查询大致相同.该相关子查询将始终由优化器评估:

SQL> explain plan for select case
  2                            when 1 = 1 then 1
  3                            when 2 = 1 then (select 1
  4                                               from dual)
  5                          end as res
  6                      from dual;
Explained
Executed in 0 seconds

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
Plan hash value: 1317351201
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     4   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
|   2 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
9 rows selected
Executed in 0.063 seconds