Думаю, многим, кто давно работает с ораклом, знакома функция NULLIF. Изначально добавленная в целях обеспечения совместимости с другими БД, иногда она бывает довольно полезной. Суть ее в чем — NULLIF(a, b) даст NULL, если a = b, либо a в противном случае. То есть, NULLIF(0, 0) будет NULL, NULLIF(10, 0) будет 10.
В частоности, Льюис предлагает использовать ее, чтобы не получить ошибку divide by zero. Согласитесь, это удобно — делаешь выборку значений x / NULLIF(y, 0), и в случаях, когда y = 0, получаешь 1/NULL, т.е. NULL для всего выражения.
Однако, данная функция таит в себе несколько подводных камней, о причинах которых я напишу в самом конце. Для начала посмотрим на простой генератор псевдослучайных чисел, в данном случае он возвращает либо 0, либо 1.
Допустим, мы хотим вывести ряд случайных чисел, а также NULL (например, в случае, когда число = 0). Логично предположить такой вариант написания: NULLIF(TRUNC(dbms_random.value(0, 2)), 0). Что ж, давайте проверим:
И вот мы получили 0, хотя строго «оговорили» — дать NULL, если первый аргумент окажется равен 0! Если повторить запрос несколько раз, можно увидеть, что он возвращает то 0, то 1, то NULL. Что же происходит?
А происходит вот что — сначала считается первый аргумент, и если он равен второму, то возвращается NULL, если же нет, то первый аргумент считается заново и результат возвращается. То есть сначала dbms_random.value вернул 1, оракл проверил, что 1 != 0, и подставил в результат первое выражение (которое при повторном подсчете оказалось равным 0). Такие вот дела.
А причина кроется в простом — как я уже упоминал выше, NULLIF был введен в целях совместимости, и по сути не является отдельным оператором. Проанализировав трассу 10053, мы видим, во что превратился наш запрос:
То есть, все выражения NULLIF(a, b) трансформируются оптимизатором в CASE WHEN a=b THEN NULL ELSE a END, и логично, что в таком случае выражение dbms_random.value будет подсчитано дважды.
Чем это чревато помимо вышеописанной ситуации? Ну, к примеру, если вы укажете в качестве первого аргумента тяжелую (и не объявленную как DETERMINISTIC) функцию, то она будет вызвана два раза вместо одного, что отнимет лишнее время. А невинные, на первый взгляд, выражения типа NULLIF(NULLIF(NULLIF(a, 0), 1), 2) и вовсе трансформируются в гигантские многоэтажные контрукции CASE с множеством подсчетов исходного выражения.
Так что вот. Ни в коем случае не призываю отказываться от NULLIF, просто имейте в виду — на самом деле это обычный CASE, со всеми вытекающими.
В частоности, Льюис предлагает использовать ее, чтобы не получить ошибку divide by zero. Согласитесь, это удобно — делаешь выборку значений x / NULLIF(y, 0), и в случаях, когда y = 0, получаешь 1/NULL, т.е. NULL для всего выражения.
Однако, данная функция таит в себе несколько подводных камней, о причинах которых я напишу в самом конце. Для начала посмотрим на простой генератор псевдослучайных чисел, в данном случае он возвращает либо 0, либо 1.
SQL> SELECT TRUNC(dbms_random.value(0, 2)) FROM dual;
TRUNC(DBMS_RANDOM.VALUE(0,2))
-----------------------------
0
SQL> SELECT TRUNC(dbms_random.value(0, 2)) FROM dual;
TRUNC(DBMS_RANDOM.VALUE(0,2))
-----------------------------
1
Допустим, мы хотим вывести ряд случайных чисел, а также NULL (например, в случае, когда число = 0). Логично предположить такой вариант написания: NULLIF(TRUNC(dbms_random.value(0, 2)), 0). Что ж, давайте проверим:
SQL> SELECT NULLIF(TRUNC(dbms_random.value(0, 2)), 0) FROM dual;
NULLIF(TRUNC(DBMS_RANDOM.VALUE(0,2)),0)
---------------------------------------
0
И вот мы получили 0, хотя строго «оговорили» — дать NULL, если первый аргумент окажется равен 0! Если повторить запрос несколько раз, можно увидеть, что он возвращает то 0, то 1, то NULL. Что же происходит?
А происходит вот что — сначала считается первый аргумент, и если он равен второму, то возвращается NULL, если же нет, то первый аргумент считается заново и результат возвращается. То есть сначала dbms_random.value вернул 1, оракл проверил, что 1 != 0, и подставил в результат первое выражение (которое при повторном подсчете оказалось равным 0). Такие вот дела.
А причина кроется в простом — как я уже упоминал выше, NULLIF был введен в целях совместимости, и по сути не является отдельным оператором. Проанализировав трассу 10053, мы видим, во что превратился наш запрос:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT CASE WHEN TRUNC("SYS"."DBMS_RANDOM"."VALUE"(0,2))=0 THEN NULL ELSE TRUNC("SYS"."DBMS_RANDOM"."VALUE"(0,2)) END "NULLIF(TRUNC(DBMS_RANDOM.VALUE" FROM "SYS"."DUAL" "DUAL"
То есть, все выражения NULLIF(a, b) трансформируются оптимизатором в CASE WHEN a=b THEN NULL ELSE a END, и логично, что в таком случае выражение dbms_random.value будет подсчитано дважды.
Чем это чревато помимо вышеописанной ситуации? Ну, к примеру, если вы укажете в качестве первого аргумента тяжелую (и не объявленную как DETERMINISTIC) функцию, то она будет вызвана два раза вместо одного, что отнимет лишнее время. А невинные, на первый взгляд, выражения типа NULLIF(NULLIF(NULLIF(a, 0), 1), 2) и вовсе трансформируются в гигантские многоэтажные контрукции CASE с множеством подсчетов исходного выражения.
Так что вот. Ни в коем случае не призываю отказываться от NULLIF, просто имейте в виду — на самом деле это обычный CASE, со всеми вытекающими.