NVL和Coalesce之间的Oracle差异
NVL和Coalesce之间的Oracle差异
提问于 2009-06-04 11:58:55
在Oracle中，NVL和Coalesce之间是否有不明显的区别？
明显的区别是，coalesce将返回其参数列表中的第一个非空项目，而nvl只接受两个参数，如果不为空，则返回第一个，否则返回第二个。
看起来NVL可能只是coalesce的“基本情况”版本。
我是不是遗漏了什么？
回答 5
Stack Overflow用户
回答已采纳
发布于 2009-06-04 12:03:35
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，并且查询是即时的。
Stack Overflow用户
发布于 2009-06-05 05:13:20
NVL将对第一个参数的数据类型进行隐式转换，因此以下代码不会出错
select nvl('a',sysdate) from dual;COALESCE需要一致的数据类型。
select coalesce('a',sysdate) from dual;将引发“数据类型不一致的错误”
Stack Overflow用户
发布于 2014-10-08 02:15:19
还有一个区别是在计划处理方面。
当搜索包含nvl结果与索引列的比较时，Oracle能够通过连接分支过滤器来形成优化计划。
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。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/950084
复制相关文章
相似问题