Oracle различия между NVL и Coalesce

Question

Oracle различия между NVL и Coalesce

существуют ли неочевидные различия между NVL и Coalesce в Oracle?

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

кажется, что NVL может быть просто" базовым случаем " версии coalesce.

Я что-то пропустила?

8 169

sql system.data.oracleclient coalesce nvl

8 ответов:

NVL будет выполнять неявное преобразование в тип данных первого параметра, поэтому следующее не приводит к ошибке
select nvl('a',sysdate) from dual;
COALESCE ожидает последовательные типы данных.
select coalesce('a',sysdate) from dual;
выдаст "несогласованную ошибку типа данных"

159

существует также разница в плане обработки.

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.

15

NVL и COALESCE используются для достижения той же функциональности предоставления значения по умолчанию в случае, если столбец возвращает NULL.

различия заключаются в следующем:

NVL принимает только 2 аргумента, тогда как COALESCE может принимать несколько аргументы

НВЛ оценивает аргументы обеих сторон и сливаются останавливается на первом возникновение ненулевого значения.

NVL выполняет неявное преобразование типа данных на основе первого аргумента отдается ему. СПЛОТИТЬСЯ ожидает, что все аргументы должны быть одного типа.

COALESCE дает проблемы в запросах, которые используют предложения объединения. Образец ниже

COALESCE-это стандарт ANSI, где as NVL является специфичным для Oracle.

примеры для третьего случая. Другие случаи просты.

select nvl('abc',10) from dual; будет работать как NVL будет делать неявное преобразование числового 10 в строку.

select coalesce('abc',10) from dual; произойдет сбой с ошибкой-несогласованные типы данных: ожидаемый символ получил Номер

пример для UNION use-case
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

13

еще одно доказательство того, что coalesce () не останавливает вычисление с первым ненулевым значением:
SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;
запустите это, а затем проверьте my_sequence.currval;

4

хотя это очевидно, и даже упоминается в пути, поставленном Томом, который задал этот вопрос. Но давайте снова мириться.

NVL может иметь только 2 аргумента. Коалесцировать может иметь более 2.

select nvl('','',1) from dual; //результат: ORA-00909: неверное количество аргументов
select coalesce('','','1') from dual; / / вывод: возвращает 1

3

на самом деле я не могу согласиться с каждым утверждением.

" 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!

3

NVL: заменить null на значение.

слияние: возвращает первое ненулевое выражение из списка выражений.

таблица: 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 мы можем достичь всех трех правил.

2

Quassnoi · Accepted Answer · 2018-02-13 22:53:01

COALESCE - это более современная функция, которая является частью ANSI-92 стандартные.

NVL и Oracle конкретно, он был введен в 80раньше не было никаких стандартов.

в случае двух значений, они являются синонимами.

однако они реализованы по-разному.

NVL всегда вычисляет оба аргумента, в то время как COALESCE обычно останавливает оценку всякий раз, когда он находит первый non-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()'s, несмотря на 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 ' s не генерируются, и запрос выполняется мгновенно.