We have queries in procedures in oracle like,

    SELECT  ......
    WHERE   .....
    AND (SomeColumn IS NULL OR SomeColumn = SomeThing)

But our DBA is saying this is not good, and saying use NVL instead. We are using this for long time and haven't found any issue. Should we change IS NULL with NVL? from NVL I mean NVL(SomeColumn, '') = SomeThing

  • Try both and see which one works best for your specific query.
    – Mat
    Aug 21 '16 at 7:08
  • @Mat is there any known issue using AND (SomeColumn IS NULL OR SomeColumn = SomeThing)
    – user960567
    Aug 21 '16 at 8:15
  • 1
    I guess the reason for NVL may be that Oracle doesn't include NULLs into index, thus SomeColumn IS NULL will cause full scan even if index on SomeColumn exists. On the other hand , Oracle support function based index, and you can build index on NVL(SomeColumn,'some_value'). In any case, I'd suggest asking your DBA why she/he wants you to use NVL
    – a1ex07
    Aug 21 '16 at 22:22

I'm not sure why nvl should be better than is null, except it is an oracle function(not available on other dbms).

I've also preferred is null over nvl on oracle; I would not use nvl because it's a function and can(most unlikely) cause a full table scan.

-> Let it explain to you, why it's "bad" and also said in comments: try it

  • The argument that it will cause a table scan only applies if the column is indexed. In other cases, you may already have limited the candidate set with another index. For other languages COALESCE, performs the same function. However, NVL and COALESCE are best used in the result set, when NULL replacement is appropriate.
    – BillThor
    Aug 21 '16 at 13:34
  • @BillThor can you explain more?
    – user960567
    Aug 21 '16 at 15:34
  • @user960567 I've added an answer with more details.
    – BillThor
    Aug 22 '16 at 0:38

Many databases including Oracle have a COALESCE function that behaves like NVL, but takes two or more values. This is useful to return a value when there are multiple columns that could have the desired value and/or to provide a default value.

If all columns in an index are NULL, Oracle does not index the row, so it will require a table scan to retrieve rows when you have a NULL value in the match condition. In other cases, you have values that are not NULL, to match on and only one column that could be NULL. This is common in OUTER JOIN when you want to select only one value in the outer table.

In any case, unless you have in index where the NULL value is indexed, a search of all candidate records will be required. If you have an index that can be used to reduce the number of possible matches to less than 4% of the the records, then the candidate set may consist of those rows. Oracle will generally use a table scan for small tables, or if there is no index that can limit the candidate rows sufficiently. Above 4% (possibly lower) the preference will be for a table scan.

You have three options for the conditional:

NVL(column, value) = value
COALESCE(column, value) = value
column = value OR column IS NULL

In my experience, all three are equivalent. You can verify for yourself, by running an explain plan on the query.

It is possible to influence the plan with hints. However, this can easily result in sub-optimal plans.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.