Choosing COALESCE over NVL

The NVL and the COALESCE functions are basically the same. They return the first NOT NULL argument and if there are no NOT NULL arguments, they return null. The main difference is that the NVL function accepts only two arguments, but the COALESCE function accepts a minimum of 2 and a maximum of 65535 arguments.

Another important difference between the NVL and COALESCE functions is that the NVL function evaluates both the arguments, but the COALESCE function stops its evaluation at the first occurrence of the not null argument. Because of this, there is a performance loss in the NVL function if the first argument is not null and the second argument consumes more time in its evaluation. This scenario is handled perfectly by the COALESCE function with two arguments.

In the below example, both the NVL and the COALESCE functions are passed with the literal “1” as their first argument and a function returning the literal “2” after a 5 second time gap as their second argument. The NVL function returns the first argument (literal “1”) as it is the first NOT NULL argument but takes 5 seconds to finish the task as it also evaluates the second argument. The COALESCE function, on the other hand, returns the first argument (literal “1”) as it is the first NOT NULL argument in no time as it doesn't have to evaluate the second argument.

DECLARE
  l_n_start_time NUMBER;
  l_n_end_time   NUMBER;
  l_n_var1       NUMBER;
  FUNCTION func_sleep
    RETURN NUMBER
  IS
  BEGIN
    dbms_lock.sleep(5);
    RETURN 2;
  END;
BEGIN
  l_n_start_time:=dbms_utility.get_time;
  l_n_var1      :=COALESCE(1,func_sleep);
  l_n_end_time  :=dbms_utility.get_time;
  dbms_output.put_line('Run time taken by the COALESCE function is '||(l_n_end_time-l_n_start_time)/100||' Seconds');
  l_n_start_time:=dbms_utility.get_time;
  l_n_var1      :=NVL(1,func_sleep);
  l_n_end_time  :=dbms_utility.get_time;
  dbms_output.put_line('Run time taken by the NVL function is '||(l_n_end_time-l_n_start_time)/100||' Seconds');
END;
/

Result:

Run time taken by the COALESCE function is 0 Seconds
Run time taken by the NVL function is 5 Seconds


Thank you,
Boobal Ganesan