One more COALESCE vs NVL example to finish the week...

How many times do you think you've ever written something like this
nvl('x',user)

Small cost? What if you executed it all the time.

What if we wrote it as
coalesce('x',user)

No biggy?

Executed on my laptop with tracing on

TRACING SAGE@sw10g> begin
  2  for i in 1..power(2,17) loop
  3    if nvl('x',user) = 'x' then
  4    null;
  5    end if;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.42

Without tracing it was still about 4 seconds.

TRACING SAGE@sw10g> begin
  2  for i in 1..power(2,17) loop
  3    if coalesce('x',user) = 'x' then
  4    null;
  5    end if;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Why?

Check out the tkprof output...

SELECT USER 
FROM
 SYS.DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute 131072      2.21       1.04          0          0          0           0
Fetch   131072      2.81       0.53          0          0          0      131072
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   262144      5.03       1.58          0          0          0      131072

Every reference to USER in your PL/SQL or Forms code executes that statement from dual.

Sometimes we need to consider the little things, and considered using COALESCE instead of NVL.