COALESCE “умнее” чем NVL

Такие операторы как NVL встречаются очень часто. Ещё в версии 8.1.7 это была единственная доступная функция, которая фильтровала null.

В версии 9i появилась функция coalesce которую называли обобщением NVL функции.


По сей день встречаются разработчики, которые никогда не слышали о ней, и уж тем более, не использовали. Чем больше я думал об приемуществах coaleasce, тем больше я считал возможным прекратить использовать NVL, и приобрести вместо этого привычку использовать coaleasce.

Первая причина - это большая динамичность. Мы можем определить первое не-NULL выражение в списке. Люди все время используют CASE, вместо coalesce.

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Вторая причина, и более важная - Oracle использует вычисление короткого круга (short-circuit evaluation), по ANSI стандартам.

Иногда, когда мы сравниваем 2 значения, одно из них может быть дорого по оценке чтения значения, например если это запрос к таблице. В сценарии ниже я упростил это до наличия функции, которая спит некоторое указанное количество секунд.

10g>  create or replace function sw_delay (pn_seconds number) return number is
  2  begin
  3     dbms_lock.sleep(pn_seconds);
  4     return pn_seconds;
  5  end;
  6  /

Function created.

10g>  SET TIMING ON
10g>  -- this will always delay 1 second
10g>  select sw_delay(1) from dual;

SW_DELAY(1)
-----------
          1

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- While my function should never evaluate, the query still takes 1s
10g>  select nvl(2, sw_delay(1)) from dual;

NVL(2,SW_DELAY(1))
------------------
                 2

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- The same applies for nvl2
10g>  select nvl2(1, 2, sw_delay(1)) from dual;

NVL2(1,2,SW_DELAY(1))
---------------------
                    2

1 row selected.

Elapsed: 00:00:01.00
10g>  
10g>  -- The ANSI standard does not evaluate unnecessary expressions.
10g>  select coalesce(2, sw_delay(1)) from dual;

COALESCE(2,SW_DELAY(1))
-----------------------
                      2

1 row selected.

Elapsed: 00:00:00.00
10g>  
10g>  -- Decode is acceptable
10g>  select decode(1,1,2,sw_delay(1)) from dual;

DECODE(1,1,2,SW_DELAY(1))
-------------------------
                        2

1 row selected.

Elapsed: 00:00:00.01
10g>  
10g>  -- As is Case 
10g>  select case when 1 = 1 then 2 else sw_delay(1) end from dual;

CASEWHEN1=1THEN2ELSESW_DELAY(1)END
----------------------------------
                                 2

1 row selected.

Elapsed: 00:00:00.00
10g>  select case 1 when 1 then 2 else sw_delay(1) end from dual;

CASE1WHEN1THEN2ELSESW_DELAY(1)END
---------------------------------
                                2

1 row selected.

Elapsed: 00:00:00.00

Как показано выше, здесь не происходит вычисление второго выражения. Большинство программистов вначале помещают дешевые для вычисления выражения, чтобы сэкономить время работы процессора. Функции NVL и NVL2 не помогают в этом. А вот COALESCE, DECODE и оба CASE выражения вычисления короткого круга (short-circuit evaluation) - первейшее выражение вернувшее true, делает ненужным продолжение вычисление остальных выражений.

Другой способ продемонстрировать это:

10g>  create or replace function sw_plsql (pn_seconds number) return varchar2 is
  2  begin
  3    if nvl(pn_seconds, sw_delay(1)) = 1 then
  4      return 'do this';
  5    else
  6      return 'do that';
  7    end if;
  8  end;
  9  /

Function created.

10g>  select sw_plsql(null) from dual;

SW_PLSQL(NULL)
-------------------------
do this

1 row selected.

Elapsed: 00:00:01.00
10g>  select sw_plsql(1) from dual;

SW_PLSQL(1)
-------------------------
do this

1 row selected.

Elapsed: 00:00:01.00

Источник.