Оценка короткого замыкания CASE и COALESCE работает с последовательностями в PL/SQL, но не в SQL

Используется ли оценка короткого замыкания, описанная в документации для CASE и COALESCE() к последовательностям при использовании в SQL? Это, похоже, не происходит.

Документация Oracle на CASE гласит:

База данных Oracle использует оценку короткого замыкания. Для простого выражения CASE... Oracle никогда не оценивает сравнение_expr, если предыдущее compare_expr равно expr. Для найденного выражения CASE база данных... никогда не оценивает условие, если предыдущее условие было истинным.

Аналогично для COALESCE() документации указано, что:

База данных Oracle использует оценку короткого замыкания. База данных оценивает каждое значение expr и определяет, является ли она NULL, а не оценивает все значения expr перед определением, является ли любой из них NULL.

При вызове последовательности из 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

Вызов последовательности в SQL из PL/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

В документации, похоже, ничего нет; руководство администратора для управления последовательностями, ссылка на SQL-язык в последовательности psuedocolumns, ссылку на язык PL/SQL на CURRVAL и NEXTVAL или обзор концепций баз данных последовательностей.

Существует ли оценка короткого замыкания CASE и COALESCE() для последовательностей при использовании в SQL? Является ли это документированным?

Мы находимся на 11.2.0.3.5, если это интересно.

Ответы

Ответ 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 = 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 была включена в план выполнения дважды.Забастовкa >

Аналогия с подзапросом была сделана в спешке. Конечно, есть больше различий, чем сходство. Последовательности - абсолютно разные механизмы. Но последовательности рассматриваются оптимизатором как источник строки, и до тех пор, пока он не видит псевдокоманду nextval последовательности, непосредственно ссылающейся в списке select запроса верхнего уровня, он выиграл ' t оценить последовательность, иначе последовательность будет увеличиваться, независимо от того, используется ли логика оценки короткого замыкания или нет. Механизм PL/SQL, очевидно, (начиная с Oracle 11g r1) имеет другой способ доступа к значению последовательности. Следует отметить, что в предыдущих версиях РСУБД 11gR1 мы должны написать запрос для ссылки на последовательность в блоке PL/SQL, механизм PL/SQL отправленный непосредственно в механизм SQL.

Ответ на вопрос "почему последовательность получает приращение во время генерации плана выполнения с помощью оптимизатора", заключается во внутренней реализации последовательностей.

Ответ 2

Для PL/SQL Oracle гарантирует, что он будет использовать оценку короткого замыкания:

При оценке логического выражения PL/SQL использует короткое замыкание оценка. То есть PL/SQL перестает оценивать выражение, как только он может определить результат. Поэтому вы можете писать выражения, которые могут вызвать ошибки.

От: 2 Основы языка PL/SQL

Когда вы используете код nextval в SQL, у нас другая ситуация.

Прежде всего, мы должны иметь в виду, что currval и nextval являются псевдоколониями:

Псевдоколонка ведет себя как столбец таблицы, но на самом деле не хранится в таблице. Вы можете выбрать из псевдокоманд, но вы не можете вставлять, обновлять или удалять их значения. Псевдоколонка также аналогична к функции без аргументов (см. главу 5, "Функция". Однако функции без аргументов обычно возвращают одинаковое значение для каждой строки в результирующем наборе, тогда как псевдоколонны обычно возвращают другое значение для каждой строки.

От: 3 Pseudocolumns.

Теперь возникает вопрос: почему Oracle оценивает nextval? или Это поведение указано где-то?

В одном выражении SQL, содержащем ссылку на NEXTVAL, Oracle увеличивает последовательность один раз:

  • Для каждой строки, возвращаемой внешним блоком запроса инструкции SELECT. Такой блок запроса может отображаться в следующих местах:

    • Оператор SELECT верхнего уровня
    • Оператор INSERT... SELECT (одностолбцовый или многопользовательский). Для многопользовательской вставки ссылка на NEXTVAL должна появляются в предложении VALUES, и последовательность обновляется один раз для каждая строка, возвращаемая подзапросом, хотя NEXTVAL может быть ссылается на несколько ветвей многопозиционной вставки.
    • CREATE TABLE... инструкция SEL SELECT
    • CREATE MATERIALIZED VIEW... AS SELECT

  • Для каждой строки, обновленной в инструкции UPDATE

  • Для каждого оператора INSERT, содержащего предложение VALUES

  • Для каждой строки, объединенной оператором MERGE. Ссылка на NEXTVAL может отображаться в файле 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/SQL, как утверждает Oracle. Второй тест, включая последовательность в операторах SQL, показывает, что в этом случае nextval оценивается в любом случае, даже если результат не используется, и Oracle также документирует это.

Совмещение двух вещей выглядит немного странно, потому что поведение coalesce и case тоже кажется мне непоследовательным, но мы также должны иметь в виду, что реализация этой логики зависит от реализации (здесь мой источник)