CASE和COALESCE短路评估适用于PL/SQL中的序列，但不适用于SQL中的序列 [英] CASE and COALESCE short-circuit evaluation works with sequences in PL/SQL but not in SQL
问题描述
在SQL中使用时，CASE和COALESCE()文档中描述的短路评估是否适用于序列?这似乎没有发生.
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.
CASE 上的Oracle 文档指出:
The Oracle documentation on CASE states that:
Oracle数据库使用短路评估.对于简单的
CASE表达式...如果先前的compare_expr等于expr，则Oracle永远不会评估compare_expr.对于搜索到的CASE表达式，数据库...如果先前的条件为true，则永远不会评估条件.
Oracle Database uses short-circuit evaluation. For a simple
CASEexpression... 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，而不是在确定所有expr值是否为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
但是，从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
文档中似乎没有关于此的任何内容； 用于管理序列的管理员指南，关于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.
在SQL中使用序列时，是否发生CASE和COALESCE()的短路评估?有记录吗?
Does the short-circuit evaluation of CASE and COALESCE() occur for sequences when used in SQL? Is this documented?
如果感兴趣的话，我们正在11.1.2.3.5上运行.
We're on 11.2.0.3.5 if it's of interest.
推荐答案
以下是为什么短路评估不适用于序列的解释.什么是序列?抛开内部因素，它是序列定义(记录在seq$数据字典表中)和一些内部SGA组件的组合，它不是函数，可以考虑使用，尽管文档
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伪列，它就不会t评估顺序，否则无论是否使用短路评估逻辑，顺序都会递增.显然，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.
这篇关于CASE和COALESCE短路评估适用于PL/SQL中的序列，但不适用于SQL中的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
