Using NVL vs. Using Case

A Friend of mine (thanks Tal) brought my attention to a problem that needed to be solved.

Lets take a look at these SELECT statements (based on scott sample scheme with an addition of a foriegn key of emp_id to department as manager_id):

SELECT priQuery.ename,
nvl(mgr,
(select manager_id
from dept subQuery
where subQuery.deptno = priQuery.deptno)) as MGR
FROM emp priQuery;

SELECT priQuery.ename,
CASE WHEN priQuery.mgr is null THEN
(select manager_id
from dept subQuery
where subQuery.deptno = priQuery.deptno)
ELSE
priQuery.mgr
END as MGR
FROM emp priQuery;

Both of these statments implements the logic of retrieving the manager id from department table in case there is no manager at the emp table (mgr is null).

I Know thre's also an option of using a join, but in my scheme, most of the employees have manager.

So, Which is better?
Well, when I first thought of this I immediately said the CASE is better because there is no PL/SQL context switch - anyway, it was an intereting question so I made a test case to check.

I've create a counter implemented as a table:

create table LOG_TABLE (counter number);
insert into log_table values (0);


I've also created a function (so i can call it from my SELECT) that increase counter count

function counter return number
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update log_table
set counter = counter + 1;
commit;
return 0;
end;

The results for the default data in the table (just one emp has missing manager id) were as the following:

The NVL function executed the select for each deptno (althogh it runs for each dept one time - the function is determenistic) while the CASE executed the SELECT just once (and it's also determenistic) ...

in this CASE :) using the case will be smarter (and for the pl/sql context switching - oracle 11g is making it quite transparent).