<legend id='KAOAD'><style id='KAOAD'><dir id='KAOAD'><q id='KAOAD'></q></dir></style></legend>
    1. <small id='KAOAD'></small><noframes id='KAOAD'>

      • <bdo id='KAOAD'></bdo><ul id='KAOAD'></ul>
      <tfoot id='KAOAD'></tfoot>
      <i id='KAOAD'><tr id='KAOAD'><dt id='KAOAD'><q id='KAOAD'><span id='KAOAD'><b id='KAOAD'><form id='KAOAD'><ins id='KAOAD'></ins><ul id='KAOAD'></ul><sub id='KAOAD'></sub></form><legend id='KAOAD'></legend><bdo id='KAOAD'><pre id='KAOAD'><center id='KAOAD'></center></pre></bdo></b><th id='KAOAD'></th></span></q></dt></tr></i><div id='KAOAD'><tfoot id='KAOAD'></tfoot><dl id='KAOAD'><fieldset id='KAOAD'></fieldset></dl></div>

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

      CASE and COALESCE short-circuit evaluation works with sequences in PL/SQL but not in SQL(CASE 和 COALESCE 短路评估适用于 PL/SQL 中的序列,但不适用于 SQL)
        <legend id='eMYdH'><style id='eMYdH'><dir id='eMYdH'><q id='eMYdH'></q></dir></style></legend>

        • <bdo id='eMYdH'></bdo><ul id='eMYdH'></ul>

                <tbody id='eMYdH'></tbody>

            1. <i id='eMYdH'><tr id='eMYdH'><dt id='eMYdH'><q id='eMYdH'><span id='eMYdH'><b id='eMYdH'><form id='eMYdH'><ins id='eMYdH'></ins><ul id='eMYdH'></ul><sub id='eMYdH'></sub></form><legend id='eMYdH'></legend><bdo id='eMYdH'><pre id='eMYdH'><center id='eMYdH'></center></pre></bdo></b><th id='eMYdH'></th></span></q></dt></tr></i><div id='eMYdH'><tfoot id='eMYdH'></tfoot><dl id='eMYdH'><fieldset id='eMYdH'></fieldset></dl></div>
            2. <tfoot id='eMYdH'></tfoot>

              <small id='eMYdH'></small><noframes id='eMYdH'>

                本文介绍了CASE 和 COALESCE 短路评估适用于 PL/SQL 中的序列,但不适用于 SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                CASECOALESCE() 文档中描述的短路评估是否适用于在 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.

                CASECOALESCE() 在 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.

                这篇关于CASE 和 COALESCE 短路评估适用于 PL/SQL 中的序列,但不适用于 SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?(如何将 DBMS_OUTPUT.PUT_LINE 的输出重定向到文件?)
                How do I get column datatype in Oracle with PL-SQL with low privileges?(如何使用低权限的 PL-SQL 在 Oracle 中获取列数据类型?)
                Get a list of all functions and procedures in an Oracle database(获取 Oracle 数据库中所有函数和过程的列表)
                Why cannot I create triggers on objects owned by SYS?(为什么我不能在 SYS 拥有的对象上创建触发器?)
                Returning result even for elements in IN list that don#39;t exist in table(即使对于表中不存在的 IN 列表中的元素也返回结果)
                Reset Sequence in oracle 11g(oracle 11g 中的重置序列)
                <i id='SeNt1'><tr id='SeNt1'><dt id='SeNt1'><q id='SeNt1'><span id='SeNt1'><b id='SeNt1'><form id='SeNt1'><ins id='SeNt1'></ins><ul id='SeNt1'></ul><sub id='SeNt1'></sub></form><legend id='SeNt1'></legend><bdo id='SeNt1'><pre id='SeNt1'><center id='SeNt1'></center></pre></bdo></b><th id='SeNt1'></th></span></q></dt></tr></i><div id='SeNt1'><tfoot id='SeNt1'></tfoot><dl id='SeNt1'><fieldset id='SeNt1'></fieldset></dl></div>
                    <tbody id='SeNt1'></tbody>

                    <small id='SeNt1'></small><noframes id='SeNt1'>

                          <bdo id='SeNt1'></bdo><ul id='SeNt1'></ul>
                          <legend id='SeNt1'><style id='SeNt1'><dir id='SeNt1'><q id='SeNt1'></q></dir></style></legend>

                          <tfoot id='SeNt1'></tfoot>