NVL smarts


Oracle appears to have a new smart means for handling NVL around bind variables.

A common issue for reports and any query where users can pass parameters is how to handle the "optional" parameter. Here's a typical example:

Table CP can be queried where column X is equal to optional parameter P.

Should we code:


select *
from CP
where ( X = :P or :P is null)

OR

select *
from CP
where X = NVL(:P,X)

(As always) the best way to find this out, is with a test case - first some test data

SQL> create table CP ( x number not null, y number);

Table created.

SQL> insert into cp select rownum,rownum
  2  from all_objects
  3  where rownum < 20000;

19999 rows created.

SQL> commit;

Commit complete.

SQL> create index cp1 on cp (x );

Index created.

SQL> analyze table cp estimate statistics;

Table analyzed.

Now lets see what happens with each scenario

SQL> variable p number
SQL> set autotrace traceonly explain
SQL> select *
  2  from cp
  3  where ( x = :p or :p is null )
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=1001 Bytes=68068)
   1    0   TABLE ACCESS (FULL) OF 'CP' (Cost=33 Card=1001 Bytes=68068)

SQL> select *
  2  from cp
  3  where x = nvl(:p,x);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=2 Bytes=136)
   1    0   CONCATENATION
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'CP' (Cost=1 Card=1 Bytes=68)
   4    1     FILTER
   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'CP' (Cost=1 Card=1 Bytes=68)
   6    5         INDEX (RANGE SCAN) OF 'CP1' (NON-UNIQUE) (Cost=1 Card=1)

The second one possibly looks worse because it looks like a full scan and an index scan, but look at the cost for the full scan.  This looks a little odd - the cost is "1" whereas the previous explain plan thinks the full scan costs "33".  In the second case, what Oracle is doing is some smarts, where it will defer the decision on the whether to do the full scan or the index scan based on whether the parameter is actually provided or not. This apparently has come in somewhere around the 8i stage, it does not appear to do this on 8.0.  We can prove this "smart-choice" with some timing results:

SQL> set autotrace traceonly statistics
SQL> exec :p := 123;

PL/SQL procedure successfully completed.

SQL> select *
  2  from cp
  3  where x = nvl(:p,x);

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          3  physical reads
          0  redo size
        307  bytes sent via SQL*Net to client
        214  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

So we used the index to quickly get to the row, but when we null out the parameter

SQL> exec :p := null;

PL/SQL procedure successfully completed.

SQL> select *
  2  from cp
  3  where x = nvl(:p,x);

19999 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
       1537  consistent gets
        210  physical reads
          0  redo size
     623623  bytes sent via SQL*Net to client
      92189  bytes received via SQL*Net from client
       1336  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      19999  rows processed

And here we did the full scan (the best option because no parameter was given).

You'll find that if you use the other syntax, you will get a full tablescan every time. The reason for this is that the queries actually could return a different result. It all all depends if the column being queried does not contain any nulls. If the column could contain nulls, then of course, the check:

where x = nvl(:p,x)

will not pick up any rows for which X is null (whereas the other query will)

Moral of the story
a) Ensure that any columns that will not be null are defined as such in the database
b) Use the NVL clause on those columns to handle optional parameters
c) If the column can contain nulls, if possible, dissolve the SQL into two queries:

if :p is null then
select *
from CP
else
select *
from CP
where x = :p
end if;

to at least get the index benefit when the parameter is actually given.