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

2021-12-24 00:00:00 sql sequence oracle11g oracle plsql

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

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

In terms of evaluation, referencing a sequence directly in a query, roughly the same as including a correlated sub-query. That correlated sub-query will always be evaluated by the optimizer:

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

我们可以看到dual表已经被两次包含在执行计划中.

We can see that dual table has been included in the execution plan twice.

与子查询的类比是匆忙进行的.当然，不同之处多于相似之处.序列是完全不同的机制.但是，优化器将序列视为行源，只要它没有看到 select 列表中直接引用的序列的 nextval 伪列对于顶级查询，它不会评估序列，否则无论是否使用短路评估逻辑，序列都会递增.显然，PL/SQL 引擎(从 Oracle 11g r1 开始)有一种不同的方式来访问序列值.需要注意的是，在以前的 11gR1 版本的 RDBMS 中，我们应该编写一个查询来引用 PL/SQL 块中的序列，PL/SQL 引擎直接将其发送给 SQL 引擎.

The analogy with a sub-query was made in a rush. There are more differences than similarities, of course. Sequences are absolutely different mechanisms. But, a sequences are viewed by the optimizer as a row source, and as long as it doesn't see the nextval pseudocolumn of a sequence being directly referenced in the select list of a top-level query, it won't evaluate the sequence, otherwise sequence will be incremented, whether a short-circuit evaluation logic is being used or not. PL/SQL engine,obviously, (starting from Oracle 11g r1) has a different way to access a sequence value. Should be noted that in previous 11gR1 versions of RDBMS we should write a query to reference a sequence in PL/SQL block, which PL/SQL engine sent directly to the SQL engine.

为什么在优化器生成执行计划期间序列会递增"问题的答案在于序列的内部实现.

The answer to the "why a sequence gets incremented during generating an execution plan by the optimizer" question, lies in the internal implementation of sequences.

相关文章