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

小编典典

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

sql

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

Oracle文档中CASE指出：

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

对于COALESCE()
文档，类似地指出：

Oracle数据库使用短路评估。数据库评估每个expr值并确定它是否为NULL，而不是在确定所有expr值是否为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小提琴。

但是，从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语言参考或数据库概念概述序列。

确实的短路评价CASE和COALESCE()在SQL中使用时发生的序列？有文件记录吗？

如果感兴趣的话，我们就在11.2.0.3.5上。

阅读 224

2021-03-17

共1个答案

小编典典

关于短路评估为何不适用于序列的原因的解释如下。什么是序列？抛开内部因素，它是序列定义（记录在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 = 94442 op =’SEQUENCE
SEQ1 …
STAT＃1016171528 id = 2 cnt = 1 pid = 1 pos = 1 obj = 1 obj = 0 op =’FAST
DUAL 。 ..
CLOSE＃1016171528：c = 0，e = 12，dep = 0，type = 0，tim = 12896600071500 /
关闭光标 /

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

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表已经两次包含在执行计划中。

急于做出与子查询的类比。当然，区别多于相似。序列是绝对不同的机制。但是，优化器将序列视为行源，并且只要nextval在select顶级查询列表中看不到直接引用序列的伪列，它就不会评估该序列，否则无论是否使用短路评估逻辑，该序列都将递增。显然，PL
/ SQL引擎（从Oracle 11g r1开始）具有访问序列值的不同方法。应该注意的是，在以前的11gR1版本的RDBMS中，我们应该编写查询以引用PL
/ SQL块中的序列，PL / SQL引擎将其直接发送到SQL引擎。

关于“ 为什么序列在优化程序生成执行计划期间会增加 的问题”的答案在于序列的内部实现。

2021-03-17