Различия Oracle между NVL и Coalesce
Есть ли неочевидные различия между NVL и Coalesce в Oracle?
Очевидные различия заключаются в том, что coalesce вернет первый ненулевой элемент в своем списке параметров, тогда как nvl принимает только два параметра и возвращает первый, если он не равен нулю, в противном случае он возвращает второй.
Похоже, что NVL может быть просто "базовым" вариантом объединения.
Я что-то пропустил?
8 ответов
COALESCE более современная функция, которая является частью ANSI-92 стандарт.
NVL является Oracle конкретный, он был введен в 80Раньше были какие-то стандарты.
В случае двух значений они являются синонимами.
Однако они реализованы по-разному.
NVL всегда оценивает оба аргумента, в то время как COALESCE обычно останавливает оценку всякий раз, когда находит первыйNULL (есть некоторые исключения, такие как последовательность NEXTVAL):
SELECT SUM(val)
FROM (
SELECT NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)
Это работает почти 0.5 секунд, так как он генерирует SYS_GUID()х, несмотря на 1 быть не NULL,
SELECT SUM(val)
FROM (
SELECT COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)
Это понимает, что 1 это не NULL и не оценивает второй аргумент.
SYS_GUIDне генерируются и запрос мгновенный.
NVL выполнит неявное преобразование в тип данных первого параметра, поэтому следующее не приводит к ошибке
select nvl('a',sysdate) from dual;
COALESCE ожидает последовательных типов данных.
select coalesce('a',sysdate) from dual;
выдаст "непоследовательную ошибку типа данных"
NVL и COALESCE используются для достижения одинаковой функциональности предоставления значения по умолчанию в случае, если столбец возвращает NULL.
Различия:
- NVL принимает только 2 аргумента, тогда как COALESCE может принимать несколько аргументов
- NVL оценивает оба аргумента, и COALESCE останавливается при первом появлении ненулевого значения.
- NVL выполняет неявное преобразование типов данных на основе первого аргумента, переданного ему. COALESCE ожидает, что все аргументы будут одного типа.
- COALESCE дает проблемы в запросах, которые используют предложения UNION. Пример ниже
- COALESCE - это стандарт ANSI, где NVL - это специфика Oracle.
Примеры для третьего случая. Другие случаи просты.
select nvl('abc',10) from dual; будет работать, так как NVL будет выполнять неявное преобразование числа 10 в строку.
select coalesce('abc',10) from dual; потерпит неудачу с ошибкой - несовместимые типы данных: ожидаемый CHAR получил NUMBER
Пример использования UNION
SELECT COALESCE(a, sysdate)
from (select null as a from dual
union
select null as a from dual
);
не удается с ORA-00932: inconsistent datatypes: expected CHAR got DATE
SELECT NVL(a, sysdate)
from (select null as a from dual
union
select null as a from dual
) ;
преуспевает.
Дополнительная информация: http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html
Существует также разница в обработке плана.
Oracle может сформировать оптимизированный план с конкатенацией отраслевых фильтров, когда поиск содержит сравнение nvl результат с индексированным столбцом.
create table tt(a, b) as
select level, mod(level,10)
from dual
connect by level<=1e4;
alter table tt add constraint ix_tt_a primary key(a);
create index ix_tt_b on tt(b);
explain plan for
select * from tt
where a=nvl(:1,a)
and b=:2;
explain plan for
select * from tt
where a=coalesce(:1,a)
and b=:2;
NVL:
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 2 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 26 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_TT_B | 7 | | 1 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 26 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | IX_TT_A | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:1 IS NULL)
3 - filter("A" IS NOT NULL)
4 - access("B"=TO_NUMBER(:2))
5 - filter(:1 IS NOT NULL)
6 - filter("B"=TO_NUMBER(:2))
7 - access("A"=:1)
сливаться:
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 26 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TT_B | 40 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=COALESCE(:1,"A"))
2 - access("B"=TO_NUMBER(:2))
Кредиты идут по http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html.
Еще одно доказательство того, что coalesce() не останавливает вычисление с первым ненулевым значением:
SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;
Запустите это, затем проверьте my_sequence.currval;
NVL: заменить ноль на значение.
COALESCE: вернуть первое ненулевое выражение из списка выражений.
Таблица: PRICE_LIST
+----------------+-----------+
| Purchase_Price | Min_Price |
+----------------+-----------+
| 10 | null |
| 20 | |
| 50 | 30 |
| 100 | 80 |
| null | null |
+----------------+-----------+
Ниже приведен пример
[1] Установите цену продажи, добавив 10% прибыли ко всем продуктам.
[2] Если нет цены по прейскуранту, тогда минимальная цена продажи. Для распродажи.
[3] Если также нет минимальной цены, установите цену продажи в качестве цены по умолчанию "50".
SELECT
Purchase_Price,
Min_Price,
NVL(Purchase_Price + (Purchase_Price * 0.10), Min_Price) AS NVL_Sales_Price,
COALESCE(Purchase_Price + (Purchase_Price * 0.10), Min_Price,50) AS Coalesce_Sales_Price
FROM
Price_List
Объясните на практике реальный пример.
+----------------+-----------+-----------------+----------------------+
| Purchase_Price | Min_Price | NVL_Sales_Price | Coalesce_Sales_Price |
+----------------+-----------+-----------------+----------------------+
| 10 | null | 11 | 11 |
| null | 20 | 20 | 20 |
| 50 | 30 | 55 | 55 |
| 100 | 80 | 110 | 110 |
| null | null | null | 50 |
+----------------+-----------+-----------------+----------------------+
Вы можете видеть, что с NVL мы можем достичь правил [1],[2]
Но с COALSECE мы можем достичь всех трех правил.
На самом деле я не могу согласиться с каждым утверждением.
"COALESCE ожидает, что все аргументы будут одного типа".
Это неправильно, см. Ниже. Аргументы могут быть разных типов данных, что также задокументировано: если все вхождения expr являются числовым типом данных или любым нечисловым типом данных, который можно неявно преобразовать в числовой тип данных, то Oracle Database определяет аргумент с наивысшим числовым приоритетом, неявно преобразует оставшиеся аргументы в этот тип данных и возвращает этот тип данных., На самом деле это даже противоречит общему выражению "COALESCE останавливается при первом появлении ненулевого значения", в противном случае контрольный пример № 4 не должен вызывать ошибку.
Также согласно тесту № 5 COALESCE делает неявное преобразование аргументов.
DECLARE
int_val INTEGER := 1;
string_val VARCHAR2(10) := 'foo';
BEGIN
BEGIN
DBMS_OUTPUT.PUT_LINE( '1. NVL(int_val,string_val) -> '|| NVL(int_val,string_val) );
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('1. NVL(int_val,string_val) -> '||SQLERRM );
END;
BEGIN
DBMS_OUTPUT.PUT_LINE( '2. NVL(string_val, int_val) -> '|| NVL(string_val, int_val) );
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('2. NVL(string_val, int_val) -> '||SQLERRM );
END;
BEGIN
DBMS_OUTPUT.PUT_LINE( '3. COALESCE(int_val,string_val) -> '|| COALESCE(int_val,string_val) );
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('3. COALESCE(int_val,string_val) -> '||SQLERRM );
END;
BEGIN
DBMS_OUTPUT.PUT_LINE( '4. COALESCE(string_val, int_val) -> '|| COALESCE(string_val, int_val) );
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('4. COALESCE(string_val, int_val) -> '||SQLERRM );
END;
DBMS_OUTPUT.PUT_LINE( '5. COALESCE(SYSDATE,SYSTIMESTAMP) -> '|| COALESCE(SYSDATE,SYSTIMESTAMP) );
END;
Output:
1. NVL(int_val,string_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
2. NVL(string_val, int_val) -> foo
3. COALESCE(int_val,string_val) -> 1
4. COALESCE(string_val, int_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
5. COALESCE(SYSDATE,SYSTIMESTAMP) -> 2016-11-30 09:55:55.000000 +1:0 --> This is a TIMESTAMP value, not a DATE value!
Хотя это очевидно, и даже упомянуто в том виде, в каком он был поставлен Томом, который задал этот вопрос. Но давайте снова смиримся.
NVL может иметь только 2 аргумента. Коалесция может иметь более 2.
select nvl('','',1) from dual; //Результат: ORA-00909: неверное количество аргументовselect coalesce('','','1') from dual; // Вывод: возвращает 1