DISQUS

Laurent Schneider · 3 years ago

(maybe more?)

select decode(x,0,null,x) from t;

is

select nullif(x,0) from t;

Karl · 3 years ago

Hey guys!
be honest - i don not beleave that 60% used coalesce?
i always used nvl- the classic NULL -killer :-)
Karl

David Aldridge · 3 years ago

I remember reading recently (on Ask Tom I believe) that Nvl() has the disadvantage that expr2 is always evaluated even when expr1 is not null, which can be expensive if expr2 is non-trivial -- a PL/SQL function call for example.

That makes Coalesce the winner for me.

Robert Vollman · 3 years ago

I believe this is the article to which David is referring:

http://www.oracledba.co.uk/tips/plsql_nvl_costs.htm

I remember there being another, but I can't find it.

For the record, I use coalesce in Sybase, but in Oracle that's the one I don't use. Don't know why. Probably DECODE is the one I use the most.

gsalem · 3 years ago

Robert,
I think that the difference in perf mentionned on donald's site (oracledba) is mostly due to the operations used in the tests other than the first one (e.g. nvl(c,to_date('0001','yyyy')) != nvl(d,to_date('0001','yyyy')) where the to_date costs a lot). If you workaround these, then the timing differences are much less important.
rgds

Eddie Awad · 3 years ago

In certain situations in 8i, COALESCE did not behave the same as NVL, in fact it errored out. An example:

<pre>
user@8i> create table t (id int, f blob)
2 /

Table created.

user@8i> insert into t (id) values (1)
2 /

1 row created.

user@8i> select
2 id
3 ,dbms_lob.getlength(f)
4 ,nvl(dbms_lob.getlength(f),0)
5 from t
6 /

ID DBMS_LOB.GETLENGTH(F) NVL(DBMS_LOB.GETLENGTH(F),0)
---------- --------------------- ----------------------------
1 0

user@8i> select
2 id
3 ,dbms_lob.getlength(f)
4 ,coalesce(dbms_lob.getlength(f),0)
5 from t
6 /
,coalesce(dbms_lob.getlength(f),0)
*
ERROR at line 4:
ORA-00904: invalid column name
</pre>

The above worked in 9i (and XE)

<pre>
user@9i> create table t (id int, f blob)
2 /

Table created.

user@9i> insert into t (id) values (1)
2 /

1 row created.

user@9i> select
2 id
3 ,dbms_lob.getlength(f)
4 ,nvl(dbms_lob.getlength(f),0)
5 from t
6 /

ID DBMS_LOB.GETLENGTH(F) NVL(DBMS_LOB.GETLENGTH(F),0)
---------- --------------------- ----------------------------
1 0

user@9i> select
2 id
3 ,dbms_lob.getlength(f)
4 ,coalesce(dbms_lob.getlength(f),0)
5 from t
6 /

ID DBMS_LOB.GETLENGTH(F) COALESCE(DBMS_LOB.GETLENGTH(F),0)
---------- --------------------- ---------------------------------
1 0

</pre>

sumanth · 3 years ago

how to use nvl function and insert statement together... or... usage of nvl is limited to select statements.. please help

Eddie Awad · 3 years ago

No, nvl is not limited to select statements.
For example: insert into t (comm) values (nvl(mycomm, 0))