Re: about nvl2 function

From: Andrew M <andrew.markiewicz_at_gmail.com>
Date: Wed, 9 Jun 2010 06:45:28 -0700 (PDT)
Message-ID: <40527049-b505-43a2-9210-edc1cc5e93f4_at_i28g2000yqa.googlegroups.com>


Another reason to use coalessce is that it will short circuit the parameters while nvl and nvl2 will not. This might seem trivial and is if your arguments are literals or columns. But if they are functions, sequences or some computed value it can make a difference in performance or correct data.

sql->create or replace package atst
  2 as
  3
  4 function getNextVal return number;
  5 end;
  6 /

Package created.

Elapsed: 00:00:00.02
sql->show err
No errors.

sql->
sql->--------------------------------------
sql->create or replace package body atst
  2 as
  3
  4 x number;
  5
  6 function getNextVal return number
  7 is
  8 begin
  9      x := x + 1;
 10      return x;

 11 end;
 12
 13
 14 begin
 15 x := 0;
 16 end;
 17 /

Package body created.

Elapsed: 00:00:00.02
sql->show err
No errors.

sql->
sql->col nxtval format 99999
sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual;

NXTVAL


     1

Elapsed: 00:00:00.00
sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual;

NXTVAL


     3

Elapsed: 00:00:00.00
sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual;

NXTVAL


     5

Elapsed: 00:00:00.01
sql->select nvl2(1,atst.getNextVal, atst.getNextVal) nxtval from dual;

NXTVAL


     7

Elapsed: 00:00:00.00

sql->
sql->--============================================================
sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from
dual;

NXTVAL


     9

Elapsed: 00:00:00.00
sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from dual;

NXTVAL


    10

Elapsed: 00:00:00.00
sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from dual;

NXTVAL


    11

Elapsed: 00:00:00.01
sql->select coalesce(atst.getNextVal, atst.getNextVal) nxtval from dual;

NXTVAL


    12 Received on Wed Jun 09 2010 - 08:45:28 CDT

Original text of this message