Różnice Oracle między NVL i Coalesce

Czy istnieją nieoczywiste różnice między NVL a Coalesce w Oracle?

Oczywiste różnice polegają na tym, że łączenie zwróci pierwszy niepusty element na liście parametrów, podczas gdy nvl przyjmuje tylko dwa parametry i zwraca pierwszy, jeśli nie jest pusty, w przeciwnym razie zwraca drugi.

Wygląda na to, że NVL może być po prostu wersją koalescencji w „przypadku podstawowym”.

Czy coś brakuje?

COALESCEjest bardziej nowoczesną funkcją będącą częścią ANSI-92standardu.

NVLjest Oraclespecyficzny, został wprowadzony 80zanim pojawiły się jakiekolwiek standardy.

W przypadku dwóch wartości są to synonimy.

Są one jednak wdrażane inaczej.

NVLzawsze ocenia oba argumenty, podczas gdy COALESCEzwykle zatrzymuje ocenę za każdym razem, gdy znajdzie pierwszą inną niż NULL(istnieją pewne wyjątki, takie jak sekwencja NEXTVAL):

SELECT  SUM(val)
FROM    (
        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

Działa to prawie przez 0.5kilka sekund, ponieważ generuje SYS_GUID()s, mimo 1że nie jest NULL.

SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

To rozumie, że 1to nie jest NULLi nie ocenia drugiego argumentu.

SYS_GUIDnie są generowane, a zapytanie jest natychmiastowe.

NVL dokona niejawnej konwersji na typ danych pierwszego parametru, więc poniższe błędy nie powodują błędu

select nvl('a',sysdate) from dual;

COALESCE oczekuje spójnych typów danych.

select coalesce('a',sysdate) from dual;

zgłasza „niespójny błąd typu danych”

NVL i COALESCE są używane do osiągnięcia tej samej funkcjonalności, co zapewnienie wartości domyślnej w przypadku, gdy kolumna zwraca NULL.

Różnice są następujące:

1. NVL akceptuje tylko 2 argumenty, podczas gdy COALESCE może przyjmować wiele argumentów
2. NVL ocenia zarówno argumenty, jak i zatrzymuje COALESCE przy pierwszym wystąpieniu wartości innej niż Null.
3. NVL dokonuje niejawnej konwersji typu danych na podstawie pierwszego podanego argumentu. COALESCE oczekuje, że wszystkie argumenty będą tego samego typu danych.
4. COALESCE podaje problemy w zapytaniach korzystających z klauzul UNION. Przykład poniżej
5. COALESCE jest standardem ANSI, gdzie NVL jest specyficzne dla Oracle.

Przykłady trzeciego przypadku. Inne przypadki są proste.

select nvl('abc',10) from dual; działałoby, ponieważ NVL wykona niejawną konwersję liczby 10 na ciąg.

select coalesce('abc',10) from dual; zakończy się niepowodzeniem z błędem - niespójne typy danych: oczekiwany CHAR ma NUMBER

Przykład przypadku użycia UNION

SELECT COALESCE(a, sysdate) 
from (select null as a from dual 
      union 
      select null as a from dual
      );

nie działa z 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
      ) ;

udaje się.

Więcej informacji: http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html

Istnieje również różnica w obsłudze planu.

Oracle jest w stanie stworzyć zoptymalizowany plan z konkatenacją filtrów oddziałów, gdy wyszukiwanie zawiera porównanie nvlwyników z kolumną indeksowaną.

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)

łączyć:

---------------------------------------------------------------------------------------
| 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))

Kredyty są dostępne na stronie http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html .

Kolejny dowód na to, że coalesce () nie zatrzymuje oceny z pierwszą wartością inną niż null:

SELECT COALESCE(1, my_sequence.nextval) AS answer FROM dual;

Uruchom to, a następnie sprawdź my_sequence.currval;

Właściwie nie mogę zgodzić się na każde oświadczenie.

„COALESCE oczekuje, że wszystkie argumenty będą tego samego typu danych”.

To źle, patrz poniżej. Argumenty mogą być różnymi typami danych, co jest również udokumentowane : Jeśli wszystkie wystąpienia wyrażenia są liczbowym typem danych lub dowolnym nieliczbowym typem danych, który można niejawnie przekonwertować na numeryczny typ danych, wówczas baza danych Oracle określa argument o najwyższym pierwszeństwie liczbowym, niejawnie konwertuje pozostałe argumenty na ten typ danych i zwraca ten typ danych.. W rzeczywistości jest to nawet sprzeczne z powszechnym wyrażeniem „COALESCE zatrzymuje się przy pierwszym wystąpieniu wartości innej niż Null”, w przeciwnym razie przypadek testowy nr 4 nie powinien zgłaszać błędu.

Również zgodnie z przypadkiem testowym nr 5 COALESCEdokonuje domyślnej konwersji argumentów.

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!

Chociaż ten jest oczywisty, a nawet wspomniany w sposób postawiony przez Toma, który zadał to pytanie. Ale odłóżmy jeszcze raz.

NVL może mieć tylko 2 argumenty. Łączenie może mieć więcej niż 2.

select nvl('','',1) from dual;// Wynik:: ORA-00909nieprawidłowa liczba argumentów // Wynik
select coalesce('','','1') from dual; : zwraca 1

NVL: Zamień null na wartość.

ŁĄCZYĆ: Zwraca pierwsze niepuste wyrażenie z listy wyrażeń.

Tabela: PRICE_LIST

+----------------+-----------+
| Purchase_Price | Min_Price |
+----------------+-----------+
| 10             | null      |
| 20             |           |
| 50             | 30        |
| 100            | 80        |
| null           | null      |
+----------------+-----------+   

Poniżej znajduje się przykład

[1] Ustaw cenę sprzedaży z dodaniem 10% zysku do wszystkich produktów.
[2] Jeśli nie ma ceny zakupu, cena sprzedaży jest ceną minimalną. Do wyprzedaży.
[3] Jeśli nie ma również ceny minimalnej, ustaw cenę sprzedaży jako cenę domyślną „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

Wyjaśnij praktyczny przykład z życia.

+----------------+-----------+-----------------+----------------------+
| 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 |
+----------------+-----------+-----------------+----------------------+

Widać, że dzięki NVL możemy osiągnąć reguły [1], [2]
Ale dzięki COALSECE możemy osiągnąć wszystkie trzy reguły.