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

CASE和COALESCE()文档中描述的短路评估是否适用于SQL中使用的序列？这种情况似乎没有发生。

Oracle关于CASE的文档指出：

Oracle数据库使用短路评估。对于一个简单的CASE表达式。。。如果以前的comparison_expr等于expr，Oracle从不计算comparison\uexpr。对于搜索到的CASE表达式，数据库。。。如果前一个条件为true，则从不计算该条件。

类似地，对于COALESCE()，文档说明：

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

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

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调用时，序列是而不是递增的：

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相同的结果：

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

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

在SQL中使用时，序列是否会发生CASE和COALESCE()的短路评估？这有记录吗？

如果感兴趣的话，我们是11.2.0.3.5。

对于PL/SQL，Oracle确保将使用短路评估：

在计算逻辑表达式时，PL/SQL使用短路评价也就是说，PL/SQL在它可以确定结果。因此，您可以编写表达式否则可能会导致错误。

来源：2 PL/SQL语言基础

当您在SQL代码中使用nextval时，我们会有不同的情况。

首先，我们必须记住currval和nextval是伪列：

伪列的行为类似于表列，但实际上并没有存储在表格中。您可以从伪列中进行选择，但不能插入、更新或删除它们的值。伪列也是类似的到一个没有自变量的函数（请参阅第5章，"功能"。但是，没有参数的函数通常返回结果集中每行的值相同，而伪列通常为每一行返回不同的值。

发件人：3个伪列。

现在的问题是：Oracle为什么要评估nextval？或者这种行为是在什么地方说的？

在包含对NEXTVAL的引用的单个SQL语句中，Oracle将序列递增一次：

对于SELECT语句的外部查询块返回的每一行。这样的查询块可以出现在以下位置：

顶级SELECT语句

插入。。。SELECT语句（单表或多表）。对于多任务插入，必须引用NEXTVAL出现在VALUES子句中，并且为子查询返回的每一行，即使NEXTVAL可能在多任务插入的多个分支中引用

创建表。。。AS SELECT语句

创建物化视图。。。AS SELECT语句

对于UPDATE语句中更新的每一行，

对于每个包含VALUES子句的INSERT语句，

对于由MERGE语句合并的每一行。对NEXVAL的引用可以出现在merge_insert_clause或merge_update_clause中，或者二者都NEXTVALUE值对于更新的每一行和插入的每一行，即使序列号在更新或插入操作。如果NEXTVAL被指定多次在这些位置中的任何一个位置，则序列递增一次每一行，并为的NEXTVAL的所有出现返回相同的值那一排。

来自：序列伪列

你的情况显然是"1。一个顶级SELECT语句"，但这并不意味着短路逻辑没有到位，只是nextval总是被评估

如果你对短路逻辑感兴趣，那么最好从等式中删除nextval。

像这样的查询不会评估子查询：

select 6 c
  from dual
where  'a' = 'a' or 'a' = (select dummy from dual)

但是，如果尝试对coalesce或case执行类似的操作，我们将看到Oracle Optimizer决定执行子查询：

select 6 c
  from dual
where  'a' = coalesce('a', (select dummy from dual) )

我在这个SQLFiddle演示中创建了带注释的测试来展示这一点。

看起来Oracle只在具有OR条件时应用短路逻辑，但对于coalesce和case，它必须评估所有分支。

我认为您在PL/SQL中的第一次测试表明，coalsce和case在PL/SSQL中使用了短路逻辑，正如Oracle所说的那样。您的第二个测试（包括SQL语句中的序列）显示，在这种情况下，即使没有使用结果，也会对nextval进行评估，Oracle也会对此进行记录。

把这两件事放在一起看起来有点奇怪，因为coalesce和case的行为似乎也不一致，但我们也必须记住，该逻辑的实现取决于实现（这里是我的源代码）

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

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

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

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=94442op=‘序列1
STAT#1016171528 id=2 cnt=1 pid=1 pos=1 obj=0op='FAST DUAL
关闭#1016171528:c=0，e=12，dep=0，type=0，tim=1286600071500/*关闭光标*/

执行计划将向我们展示基本相同的内容：

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

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

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

"为什么在优化器生成执行计划的过程中序列会增加"问题的答案在于序列的内部实现。

相关内容

最新更新

热门标签：