Diferencias de Oracle entre NVL y Coalesce
¿Existen diferencias no obvias entre NVL y Coalesce en Oracle?
Las diferencias obvias son que coalesce devolverá el primer elemento no nulo en su lista de parámetros, mientras que nvl solo toma dos parámetros y devuelve el primero si no es nulo; de lo contrario, devuelve el segundo.
Parece que NVL puede ser simplemente una versión de "caso base" de coalesce.
¿Me estoy perdiendo de algo?
COALESCEEs una función más moderna que forma parte del ANSI-92estándar.
NVLes Oracleespecífico, se introdujo en 80antes de que existieran estándares.
En caso de dos valores, son sinónimos.
Sin embargo, se implementan de manera diferente.
NVLsiempre evalúa ambos argumentos, mientras que COALESCEgeneralmente detiene la evaluación cada vez que encuentra el primero que no es NULL(hay algunas excepciones, como la secuencia NEXTVAL):
SELECT SUM(val)
FROM (
SELECT NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)
Esto dura casi 0.5segundos, ya que genera SYS_GUID()'s, a pesar de 1no ser un NULL.
SELECT SUM(val)
FROM (
SELECT COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
FROM dual
CONNECT BY
level <= 10000
)
Este entiende que 1no es un NULLargumento y no lo valora.
SYS_GUIDLos 's no se generan y la consulta es instantánea.
NVL realizará una conversión implícita al tipo de datos del primer parámetro, por lo que lo siguiente no genera errores
select nvl('a',sysdate) from dual;
COALESCE espera tipos de datos consistentes.
select coalesce('a',sysdate) from dual;
arrojará un 'error de tipo de datos inconsistente'
NVL y COALESCE se utilizan para lograr la misma funcionalidad de proporcionar un valor predeterminado en caso de que la columna devuelva un NULL.
Las diferencias son:
- NVL acepta solo 2 argumentos mientras que COALESCE puede aceptar múltiples argumentos
- NVL evalúa ambos argumentos y COALESCE se detiene ante la primera aparición de un valor no nulo.
- NVL realiza una conversión de tipo de datos implícita basada en el primer argumento que se le da. COALESCE espera que todos los argumentos sean del mismo tipo de datos.
- COALESCE da problemas en consultas que utilizan cláusulas UNION. Ejemplo a continuación
- COALESCE es el estándar ANSI, mientras que NVL es específico de Oracle.
Ejemplos para el tercer caso. Otros casos son simples.
select nvl('abc',10) from dual;funcionaría ya que NVL hará una conversión implícita del número 10 a cadena.
select coalesce('abc',10) from dual;fallará con Error - tipos de datos inconsistentes: el CHAR esperado obtuvo el NÚMERO
Ejemplo de caso de uso UNION
SELECT COALESCE(a, sysdate)
from (select null as a from dual
union
select null as a from dual
);
falla conORA-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
) ;
tiene éxito.
Más información: http://www.plsqlinformation.com/2016/04/difference-between-nvl-and-coalesce-in-oracle.html
También hay una diferencia en el manejo del plan.
Oracle puede formar un plan optimizado con concatenación de filtros de rama cuando la búsqueda contiene una comparación del nvlresultado con una columna indexada.
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)
juntarse:
---------------------------------------------------------------------------------------
| 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))
Los créditos van a http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html .
